Sharding-Proxy分库分表、读写分离配置

1、简介

Apache ShardingSphere 是一套开源的分布式数据库增强计算引擎，由 JDBC、Proxy 和 Sidecar（规划中） 3 款产品组成。其通过可插拔架构构建基于数据库之上的生态系统，实现包括数据分片、弹性伸缩、加密脱敏等功能为代表的增强能力。
ShardingSphere 已于2020年4月16日成为 Apache 软件基金会的顶级项目。

Sharding-Proxy定位为透明化的数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。
目前先提供MySQL版本，它可以使用任何兼容MySQL协议的访问客户端(如：MySQL Command Client, MySQL Workbench、Navicat、Dbeaver等)操作数据，对DBA更加友好。
向应用程序完全透明，可直接当做MySQL使用。
适用于任何兼容MySQL协议的客户端。
官网：https://shardingsphere.apache.org/

2、下载

下载: https://shardingsphere.apache.org/document/current/cn/downloads/

本次介绍Windows下的Sharding-Proxy 4.1.1 分库分表、读写分离配置。
首先要jdbc驱动mysql-connector-java-5.1.49.jar加入到lib目录。

3、Sharding-Proxy分库分表配置

首先准备MySQL的库和表
数据库：coursedb
表：course_1、course_2

1
2
3
4
5
6
7
CREATE TABLE `course_1` (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `cstatus` varchar(10) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

配置server.yaml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
#orchestration:
#  orchestration_ds:
#    orchestrationType: registry_center,config_center,distributed_lock_manager
#    instanceType: zookeeper
#    serverLists: localhost:2181
#    namespace: orchestration
#    props:
#      overwrite: false
#      retryIntervalMilliseconds: 500
#      timeToLiveSeconds: 60
#      maxRetries: 3
#      operationTimeoutMilliseconds: 500

authentication:
  users:
    root:
      password: root
    sharding:
      password: sharding 
      authorizedSchemas: sharding_db
 
#
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
  proxy.hint.enabled: false
  query.with.cipher.column: true
  sql.show: trye
#  allow.range.query.with.inline.sharding: false

其中，orchestration是连接zookeeper注册中心，暂时用不到，将其注释掉。
authentication中，配置的是用户名和密码，以及授权的数据库，在这里，配置了两个用户，分别为：root/root和sharding/sharding，其中root默认授权所有的数据库，而sharding用户则授权sharding_db数据库。这里的数据库（schema）是逻辑数据库，在config-*.yaml中配置的。

配置config-sharding.yaml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
schemaName: sharding_db
dataSources:
  ds_1:
    url: jdbc:mysql://127.0.0.1:3306/coursedb?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_2:
    url: jdbc:mysql://127.0.0.1:3306/coursedb2?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
shardingRule:
  tables:
    course:
      actualDataNodes: ds_${1..2}.course_${1..2}
      tableStrategy:
        inline:
          shardingColumn: cid
          algorithmExpression: course_${cid % 2 + 1}
      keyGenerator:
        type: SNOWFLAKE
        column: cid
  defaultDatabaseStrategy:
    inline:
      shardingColumn: user_id
      algorithmExpression: ds_${user_id % 2 + 1}
  defaultTableStrategy:
    none:

schemaName：是逻辑数据库的名称，这里是sharding_db。在server.yaml文件中，授权的schema就是这里schemaName。
dataSources是数据源配置，这里配置了2个数据源ds_1和ds_2。
shardingRule：defaultTableStrategy，默认表的分片规则，这里配置的是none，没有。也就是说所有的分片表都要配置表的分片规则。
defaultDatabaseStrategy，默认数据库的分片规则，这里配置它的规则为行内表达式，分片字段为user_id，规则为ds_${user_id % 2}，当user_id为偶数时，数据源为ds_0，也就是前面配置的读写分离数据源；而当user_id为奇数时，数据源为ds_1。
tables,配置分片表规则，actualDataNodes，实际的数据节点，这个节点是在MySQL中真实存在的；tableStrategy，它的规则也是用行内表达式配置的，按cid进行分片，通过cid%2+1将cid为偶数的数据分到course_1表中，cid为奇数的数据分到course_2表中，插入数据时如果没有指定cid，将使用雪花算法SNOWFLAKE生成cid。

Windows下启动Sharding-Proxy：

1
start.bat 3307

出现如下信息说明启动成功：

[INFO ] 23:22:38.228 [nioEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x8ff62a66] REGISTERED
[INFO ] 23:22:38.236 [nioEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x8ff62a66] BIND: 0.0.0.0/0.0.0.0:3307
[INFO ] 23:22:38.241 [nioEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x8ff62a66, L:/0:0:0:0:0:0:0:0:3307] ACTIVE

如遇报错：
“Table name pattern can not be NULL or empty.”
产生错误的原因: 工程引用了mysql-connector-java-6.*.jar。
解决方法: 使用mysql-connector-java-5.1.49.jar,或更低版本

命令行连接到Sharding-Proxy

1
mysql -P3307 -usharding -p

输入密码后，进入mysql命令行界面。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> show databases;
+-------------+
| Database    |
+-------------+
| sharding_db |
+-------------+
1 row in set (0.01 sec)

mysql> use sharding_db;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_coursedb |
+--------------------+
| course             |
+--------------------+
1 row in set (0.02 sec)

这里看到的是逻辑库sharding_db和逻辑表course。
往表中插入8条数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
mysql> delete from course;
Query OK, 5 rows affected (0.02 sec)

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

mysql> insert into course(cname,user_id,cstatus) values('shardingproxy1',10000,1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into course(cname,user_id,cstatus) values('shardingproxy2',10001,1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into course(cname,user_id,cstatus) values('shardingproxy3',10002,1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into course(cname,user_id,cstatus) values('shardingproxy4',10003,1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into course(cname,user_id,cstatus) values('shardingproxy5',10004,1);
Query OK, 1 row affected (0.03 sec)

mysql> insert into course(cname,user_id,cstatus) values('shardingproxy6',10005,1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into course(cname,user_id,cstatus) values('shardingproxy7',10006,1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into course(cname,user_id,cstatus) values('shardingproxy8',10007,1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from course;
+--------------------+----------------+---------+---------+
| cid                | cname          | user_id | cstatus |
+--------------------+----------------+---------+---------+
| 720335604566458368 | shardingproxy7 |   10006 | 1       |
| 720335274130800641 | shardingproxy1 |   10000 | 1       |
| 720335342095302657 | shardingproxy3 |   10002 | 1       |
| 720335436538445825 | shardingproxy5 |   10004 | 1       |
| 720335308582813696 | shardingproxy2 |   10001 | 1       |
| 720335371224743936 | shardingproxy4 |   10003 | 1       |
| 720335466573856768 | shardingproxy6 |   10005 | 1       |
| 720335643200192513 | shardingproxy8 |   10007 | 1       |
+--------------------+----------------+---------+---------+
8 rows in set (0.01 sec)

使用sharding-proxy看到的是逻辑表course, 实际上是 course_1、course_2。
可以看到，cid是通过雪花算法自动生成；可连接到实际数据库，8条数据被分到2个库中的course_1和course_2表中。

使用Navicat登录Sharding-Proxy
数据库账号：sharding/shanrding、root/root 都可以登录。

4、Sharding-Proxy读写分离配置

首先准备数据库和表
数据库：ds_master、ds_slave0、ds_slave1
每个数据库新建表tb_user

1
2
3
4
5
CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

配置server.yaml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#orchestration:
#  orchestration_ds:
#    orchestrationType: registry_center,config_center,distributed_lock_manager
#    instanceType: zookeeper
#    serverLists: localhost:2181
#    namespace: orchestration
#    props:
#      overwrite: false
#      retryIntervalMilliseconds: 500
#      timeToLiveSeconds: 60
#      maxRetries: 3
#      operationTimeoutMilliseconds: 500

authentication:
  users:
    root:
      password: root
    sharding:
      password: sharding 
      authorizedSchemas: sharding_db
    master_slave:
      password: master_slave 
      authorizedSchemas: master_slave_db

root默认授权所有的数据库，master_slave用户授权master_slave_db数据库。

配置config-master_slave.yaml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
schemaName: master_slave_db

dataSources:
  ds_master:
    url: jdbc:mysql://127.0.0.1:3306/ds_master?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_slave0:
    url: jdbc:mysql://127.0.0.1:3306/ds_slave0?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_slave1:
    url: jdbc:mysql://127.0.0.1:3306/ds_slave1?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

masterSlaveRule:
  name: ms_ds
  masterDataSourceName: ds_master
  slaveDataSourceNames:
    - ds_slave0
    - ds_slave1

首先，定义逻辑数据库的名称，schemaName: master_slave_db。
然后在dataSources中定义数据源，这里配置了3个数据源，一主两从，master_ds（主）、slave_ds（从）和slave_ds_1（从）。
最后就是主从的规则masterSlaveRule，在单独的读写分离配置中，只能配置一个主从数据源。主从数据源的名字叫做ds_master，主数据源masterDataSourceName是master_ds，从数据源slaveDataSourceNames配置了两个，ds_slave0和ds_slave1。

Windows下启动服务：

1
start.bat 3307

使用命令行连接到Sharding-Proxy

1
mysql -P3307 -umaster_slave -p

输入密码master_slave后，进入mysql命令行界面。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> show databases;
+-----------------+
| Database        |
+-----------------+
| master_slave_db |
+-----------------+
1 row in set (0.01 sec)

mysql> use master_slave_db;
Database changed
mysql> show tables;
+---------------------+
| Tables_in_ds_master |
+---------------------+
| tb_user             |
+---------------------+
1 row in set (0.01 sec)

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

插入一条数据：

1
2
3
4
5
mysql> insert into tb_user(name) values('river');
Query OK, 1 row affected (0.12 sec)

mysql> select * from tb_user;
Empty set (0.01 sec)

使用sharding-proxy看到的是逻辑表tb_user, 当插入、更新数据时会到ds_master库，查询时会到ds_slave0和ds_slave1库。
插入数据再查询，没有数据，因为这里主从库没有配置， 实际上ds_master库中的tb_user表中有数据，但使用select查询时走从库，所以没有查询到数据。

使用Navicat连接Sharding-Proxy
数据库账号：master_slave/master_slave、root/root 都可以登录