sharding-proxy数据库中间件demo

部署sharding-proxy和mysql

1. 下载sharding-proxy的example的代码， 进入sharding文件夹 (cd docker/sharding-proxy/sharding)

[root@ymaster sharding]# ll
total 4
drwxr-xr-x 2 root root   53 Nov 29 14:51 conf
-rwxr-xr-x 1 root root 1574 Nov 18 17:23 docker-compose.yml
  • conf文件夹下是数据库分库分表的配置，详细配置见下面章节--分库分表配置介绍
  • docker-compose.yaml文件是编排mysql和sharding-proxy的例子，docker-compose文件详情见步骤2

2. 执行命令 (docker-compose up -d) 启动mysql和sharding-proxy

version: '3'
services:
  mysql:
    ## mysql version, you could get more tags at here : https://hub.docker.com/_/mysql?tab=tags
    image: "mysql:5.7"
    ## default port is 3306, you could change to 33060 or any other port doesn't conflict MySQL on your OS
    ports:
     - "33060:3306"
    container_name: sharding-sphere-mysql
    ## launch mysql without password
    ## you could access the mysql in container by following command :
    ## docker exec -it sharding-sphere-mysql mysql -uroot
    environment:
     - MYSQL_ALLOW_EMPTY_PASSWORD=yes
    ## if you insist to use password in mysql, remove MYSQL_ALLOW_EMPTY_PASSWORD=yes and uncomment following args
    #  - MYSQL_ROOT_PASSWORD=root
    volumes:
      - ../../../src/resources/manual_schema.sql:/docker-entrypoint-initdb.d/manual_schema.sql

  proxy:
    ## get more versions of proxy here : https://hub.docker.com/r/shardingsphere/sharding-proxy/tags
    image: "shardingsphere/sharding-proxy:3.1.0"
    container_name: sharding-sphere-proxy
    depends_on:
     - mysql
    ## wait-for-it.sh will make proxy entry point wait until mysql container 3306 port open
    entrypoint: >
     /bin/sh -c "/opt/wait-for-it.sh sharding-sphere-mysql:3306 --timeout=20 --strict --
     && /opt/sharding-proxy/bin/start.sh 3308
     && tail -f /opt/sharding-proxy/logs/stdout.log"
    ports:
     - "13308:3308"
    links:
     - "mysql:mysql"
    volumes:
     - ./conf/:/opt/sharding-proxy/conf
     - ../../tools/wait-for-it.sh:/opt/wait-for-it.sh
    environment:
     - JVM_OPTS="-Djava.awt.headless=true"

可见docker-compose.yaml文件启动了两个服务mysql和sharding -proxy ,mysql的启动脚本执行了manual_schema.sql文件初始化数据库，把分库分表都初始化好了。

manual_schema.sql如下：

DROP SCHEMA IF EXISTS demo_ds;
DROP SCHEMA IF EXISTS demo_ds_0;
DROP SCHEMA IF EXISTS demo_ds_1;

DROP SCHEMA IF EXISTS demo_ds_master;
DROP SCHEMA IF EXISTS demo_ds_slave_0;
DROP SCHEMA IF EXISTS demo_ds_slave_1;

DROP SCHEMA IF EXISTS demo_ds_master_0;
DROP SCHEMA IF EXISTS demo_ds_master_0_slave_0;
DROP SCHEMA IF EXISTS demo_ds_master_0_slave_1;
DROP SCHEMA IF EXISTS demo_ds_master_1;
DROP SCHEMA IF EXISTS demo_ds_master_1_slave_0;
DROP SCHEMA IF EXISTS demo_ds_master_1_slave_1;

CREATE SCHEMA IF NOT EXISTS demo_ds;
CREATE SCHEMA IF NOT EXISTS demo_ds_0;
CREATE SCHEMA IF NOT EXISTS demo_ds_1;

CREATE SCHEMA IF NOT EXISTS demo_ds_master;
CREATE SCHEMA IF NOT EXISTS demo_ds_slave_0;
CREATE SCHEMA IF NOT EXISTS demo_ds_slave_1;

CREATE SCHEMA IF NOT EXISTS demo_ds_master_0;
CREATE SCHEMA IF NOT EXISTS demo_ds_master_0_slave_0;
CREATE SCHEMA IF NOT EXISTS demo_ds_master_0_slave_1;
CREATE SCHEMA IF NOT EXISTS demo_ds_master_1;
CREATE SCHEMA IF NOT EXISTS demo_ds_master_1_slave_0;
CREATE SCHEMA IF NOT EXISTS demo_ds_master_1_slave_1;

等mysql创建好了，直接连接mysql数据库可以看到数据库情况如下：

mysql> show databases;
+--------------------------+
| Database                 |
+--------------------------+
| information_schema       |
| demo_ds                  |
| demo_ds_0                |
| demo_ds_1                |
| demo_ds_master           |
| demo_ds_master_0         |
| demo_ds_master_0_slave_0 |
| demo_ds_master_0_slave_1 |
| demo_ds_master_1         |
| demo_ds_master_1_slave_0 |
| demo_ds_master_1_slave_1 |
| demo_ds_slave_0          |
| demo_ds_slave_1          |
| mysql                    |
| performance_schema       |
| sys                      |
+--------------------------+
16 rows in set (0.00 sec)

3. 执行命令连接sharding-proxy, 然后sharding-proxy会连接到mysql

命令 mysql -h<host-ip> -P13308 -proot -uroot
可以看到sharding-proxy连接之后只有sharding-db这一个库，看不到直连mysql查出来的12个库

# mysql -h x.x.x.x -uroot -proot -P13308
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.0-Sharding-Proxy 3.1.0

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+-------------+
| Database    |
+-------------+
| sharding_db |
+-------------+
1 row in set (0.00 sec)

4.连接sharding-proxy执行建表，插入数据，查询数据等操作

  • 建表
mysql> CREATE TABLE IF NOT EXISTS demo_ds_0.t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));
Query OK, 0 rows affected (0.70 sec)

mysql> CREATE TABLE IF NOT EXISTS demo_ds_1.t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));
Query OK, 0 rows affected (0.70 sec)

mysql>  CREATE TABLE IF NOT EXISTS demo_ds_0.t_order_item (order_item_id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_item_id));
Query OK, 0 rows affected (0.93 sec)

mysql>  CREATE TABLE IF NOT EXISTS demo_ds_1.t_order_item (order_item_id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_item_id));
Query OK, 0 rows affected (0.01 sec)


mysql> select * from t_order;
Empty set (0.02 sec)
  • 插入数据
mysql> INSERT INTO t_order (user_id,  status) VALUES (1,  'init');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO t_order (user_id,  status) VALUES (1,  'init');
Query OK, 1 row affected (0.02 sec)
  • sharding proxy看到的

mysql> INSERT INTO t_order (user_id,  status) VALUES (2,  'init');
Query OK, 1 row affected (0.02 sec)

mysql> select * from t_order;
+--------------------+---------+--------+
| order_id           | user_id | status |
+--------------------+---------+--------+
| 407052335232057344 |       2 | init   |
| 407051622900826112 |       1 | init   |
| 407051650910388225 |       1 | init   |
+--------------------+---------+--------+
3 rows in set (0.01 sec)
  • mysql看到的
mysql> show tables;
+---------------------+
| Tables_in_demo_ds_0 |
+---------------------+
| t_order_0           |
| t_order_1           |
| t_order_item_0      |
| t_order_item_1      |
+---------------------+
4 rows in set (0.00 sec)

mysql> select * from t_order_0;
+--------------------+---------+--------+
| order_id           | user_id | status |
+--------------------+---------+--------+
| 407052335232057344 |       2 | init   |
+--------------------+---------+--------+
1 row in set (0.01 sec)

mysql> select * from t_order_1;
Empty set (0.00 sec)

mysql> select * from t_order_item_0;
Empty set (0.00 sec)

mysql> select * from t_order_item_1;
Empty set (0.00 sec)

mysql> use demo_ds_1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t_order_0;
+--------------------+---------+--------+
| order_id           | user_id | status |
+--------------------+---------+--------+
| 407051622900826112 |       1 | init   |
+--------------------+---------+--------+
1 row in set (0.01 sec)

mysql> select * from t_order_1;
+--------------------+---------+--------+
| order_id           | user_id | status |
+--------------------+---------+--------+
| 407051650910388225 |       1 | init   |
+--------------------+---------+--------+
1 row in set (0.00 sec)

更多例子可以参考
https://github.com/apache/incubator-shardingsphere-example/tree/dev/sharding-proxy-example

分库分表配置介绍

conf文件夹下有 两个文件

config-sharding.yaml

该文件是定义分片规则的文件

schemaName: sharding_db

dataSources:
  ds_0:
    url: jdbc:mysql://sharding-sphere-mysql:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
    username: root
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_1:
    url: jdbc:mysql://sharding-sphere-mysql:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
    username: root
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

shardingRule:
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..1}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_${order_id % 2}
      keyGeneratorColumnName: order_id
    t_order_item:
      actualDataNodes: ds_${0..1}.t_order_item_${0..1}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_item_${order_id % 2}
      keyGeneratorColumnName: order_item_id
  bindingTables:
    - t_order,t_order_item
  defaultDatabaseStrategy:
    inline:
      shardingColumn: user_id
      algorithmExpression: ds_${user_id % 2}
  defaultTableStrategy:
    none:
  defaultKeyGeneratorClassName: io.shardingsphere.core.keygen.DefaultKeyGenerator
  • schemaName字段: 表示在连接sharding-proxy的时候看到的db的name
mysql> show databases;
+-------------+
| Database    |
+-------------+
| sharding_db |
+-------------+
  • dataSource字段: 表示该schema下真实的库有哪些，并且给出每个db的url\user\pwd等连接方法
  • shardingRule: 分库分表的规则，上面例子的意思就是根据user_id分库，根据order_id分表。
    user_id是双数，数据插入到ds_0库中，user_id是单数则数据插入到ds_1库中。然后根据order_id确定写哪个表，order_id是单数，写到t_order_0表中，order_id是双数，写到t_order_1表中

更多其他配置可以参考官方文档：
https://shardingsphere.apache.org/document/current/cn/manual/sharding-proxy/configuration/

server.yaml

改配置主要是注册中心、认证信息以及公用属性等信息

authentication:
  username: root
  password: root

props:
  max.connections.size.per.query: 1
  acceptor.size: 16  # The default value is available processors count * 2.
  executor.size: 16  # Infinite by default.
  proxy.frontend.flush.threshold: 128  # The default value is 128.
    # LOCAL: Proxy will run with LOCAL transaction.
    # XA: Proxy will run with XA transaction.
    # BASE: Proxy will run with B.A.S.E transaction.
  proxy.transaction.type: LOCAL
  proxy.opentracing.enabled: false
  sql.show: false

更多其他配置可以参考官方文档：
https://shardingsphere.apache.org/document/current/cn/manual/sharding-proxy/configuration/

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 202,607评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,047评论 2赞 379
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 149,496评论 0赞 335
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,405评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,400评论 5赞 364
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,479评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,883评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,535评论 0赞 256
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,743评论 1赞 295
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,544评论 2赞 319
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,612评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,309评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,881评论 3赞 306
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,891评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,136评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,783评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,316评论 2赞 342