一、搭建mysql主从复制集群

3307为主节点，3317为从节点

1、创建主从节点基础容器：

1.1、创建Master实例并启动：

docker run -p 3307:3306 --name mysql-master --restart always \
-v /mydata/mysql/master/log:/var/log/mysql \
-v /mydata/mysql/master/data:/var/lib/mysql \
-v /mydata/mysql/master/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7.26

1.2、创建Slave实例并启动：

docker run -p 3317:3306 --name mysql-slave --restart always \
-v /mydata/mysql/slave/log:/var/log/mysql \
-v /mydata/mysql/slave/data:/var/lib/mysql \
-v /mydata/mysql/slave/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7.26

1.3、使用navicat连接，正常：

2、修改master和slave的基本配置：

2.1、首先配置所有mysql的经典配置（master和slave的共性配置）：

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve

2.2、master的定制化配置：

server_id=1              #每个节点不一样
log-bin=mysql-bin        #代表是以二进制日志的方式进行同步
read-only=0              #主节点可写可读（非只读）
binlog-do-db=test1       #需要同步的表
binlog-do-db=test2

replicate-ignore-db=mysql   #不需要同步的表
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

2.3、slave的定制化配置：

server_id=2
log-bin=mysql-bin
read-only=1            #从节点只可读不可写（只读）
binlog-do-db=test1
binlog-do-db=test2

replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

3、重启master和slave节点：

root@jiguiquan:/mydata/mysql# docker restart mysql-master mysql-slave
mysql-master
mysql-slave

4、为master授权“一个专门用来数据复制得用户”可以连接它，并同步它的数据

4.1、授权语句如下：

-- 授权backup账号，可以on任何主机(*)，使用密码'123456'登录，并有REPLICATION复制的权限
GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' IDENTIFIED BY '123456';

4.2、查看master节点的状态：

SHOW MASTER STATUS;

记住：

mysql-bin.000001：二进制日志的分割文件；

position 708：当前磁盘中的位置；

Binlog_Do_DB：需要记录日志的数据库；

5、在Slave节点数据库中，配置它将要复制得Master信息：

5.1、执行以下命令：

-- master_log_pos可以正确填写，也可以直接写0，会自动从上次同步的位置，继续向下同步
CHANGE MASTER TO master_host = '192.168.174.142',
 master_user = 'backup',
 master_password = '123456',
 master_log_file = 'mysql-bin.000001',
 master_log_pos = 708,
 master_port = 3307;

5.2、开始同步：

START SLAVE;

5.3、查看从节点状态：

SHOW SLAVE STATUS;

结果如下：

到这里，整个主从复制的集群就搭建完成了！

6、进行测试：

我们在mysql-master的机器上，进行如下操作：

新增数据库test1——>创建user表——>新增一条user数据；

当我们打开mysql-slave机器的时候，以上的记录就都已经存在了；

所以，主从复制节点，测试完成！！！

二、ShardingSphere——分库分表&读写分离配置

使用mysql的主从复制方案，虽然能解决数据的高可用、容灾、备份、读写分离等问题，但是却突破不了单服务器的数据上限问题；

要想实现单服务器的数据上限，我们就得进行数据库的分库分表，现在标记流行的有mycat、shardingsphere等中间件；

ShardingSphere的官网：http://shardingsphere.apache.org/index_zh.html

Apache ShardingSphere 是一套开源的分布式数据库中间件解决方案组成的生态圈，它由 JDBC、Proxy 和 Sidecar（规划中）这 3 款相互独立，却又能够混合部署配合使用的产品组成。

1、ShardingSphere-JDBC（侵入性）

定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。 它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。

• 适用于任何基于 JDBC 的 ORM 框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC。

• 支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP 等。

• 支持任意实现 JDBC 规范的数据库，目前支持 MySQL，Oracle，SQLServer，PostgreSQL 以及任何遵循 SQL92 标准的数据库。

快速使用手册：https://shardingsphere.apache.org/document/current/cn/quick-start/shardingsphere-jdbc-quick-start/

显然，我们需要在每一个应用服务中，添加shardingsphere-jdbc依赖，并进行配置，具有一定的侵入性；

2、ShardingSphere-Proxy（非侵入性）

定位为透明化的数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。 目前提供 MySQL 和 PostgreSQL 版本，它可以使用任何兼容 MySQL/PostgreSQL 协议的访问客户端(如：MySQL Command Client, MySQL Workbench, Navicat 等)操作数据，对 DBA 更加友好。

• 向应用程序完全透明，可直接当做 MySQL/PostgreSQL 使用。

• 适用于任何兼容 MySQL/PostgreSQL 协议的的客户端。

快速使用手册：https://shardingsphere.apache.org/document/current/cn/quick-start/shardingsphere-proxy-quick-start/

既然ShardingSphere-Proxy的定位是一个“数据库代理端”，那么我们就可以把它当成是一个数据库，我们所有的程序直接连接Proxy即可，所有的分库分表策略，我们就配置在ShardingSphere-Proxy代理中，算是一个标准的中间件（与ShardingSphere-JDBC不同）

3、ShardingSphere-Sidecar（TODO）——暂时了解即可——可以在k8s中使用

定位为 Kubernetes 的云原生数据库代理，以 Sidecar 的形式代理所有对数据库的访问。 通过无中心、零侵入的方案提供与数据库交互的的啮合层，即 Database Mesh，又可称数据库网格。（有点像服务网格Service Mesh）

Database Mesh 的关注重点在于如何将分布式的数据访问应用与数据库有机串联起来，它更加关注的是交互，是将杂乱无章的应用与数据库之间的交互进行有效地梳理。 使用 Database Mesh，访问数据库的应用和数据库终将形成一个巨大的网格体系，应用和数据库只需在网格体系中对号入座即可，它们都是被啮合层所治理的对象。

三、实战使用ShardingSphere实现分库分表&读写分离配置

shardingsphere-proxy的使用手册在此：https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-proxy/

实战的设计结构如下：

我们在刚刚创建的master数据库中创建两个数据库demo_db0和demo_db1；

然后在两个数据库中再根据一定的规则，对t_order进行分表，t_order0和t_order1；

关于，主从复制，上面已经测试过了，这里就不用再进行了；

准备工作：

• 下载shardingsphere-proxy的最新版

我们可以根据我们使用docker进行安装，也可以直接下载二进制压缩包（这里我们选择此方式）；

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

• 我们如果要连接mysql，我们就需要下载mysql驱动：

如果后端连接MySQL数据库，需要下载MySQL Connector/J， 解压缩后，将mysql-connector-java-5.1.47.jar拷贝到${sharding-proxy}\lib目录。

• 配置数据源与分片配置（重点）：

https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-proxy/configuration/

Sharding-Proxy支持多逻辑数据源，每个以config-前缀命令的yaml配置文件，即为一个逻辑数据源。

正式开始：

1、配置server.yaml：

# If you want to configure orchestration, authorization and proxy properties, please refer to this file.
# 
######################################################################################################
#
#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:
#授权两个用户，root和sharding，且sharding用户只有sharding_db这个库的权限
 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: true #打印sql
#  allow.range.query.with.inline.sharding: false

2、分库分表：config-sharding.yaml：

######################################################################################################
#
# If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.
#
######################################################################################################

schemaName: sharding_db    #我们的应用将会直接连接sharding_db这个数据库，而对具体的db0、db1等无感
#
dataSources:
 ds_0:
   url: jdbc:mysql://192.168.174.142:3307/demo_ds_0?serverTimezone=UTC&useSSL=false
   username: root
   password: root
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
 ds_1:
   url: jdbc:mysql://192.168.174.142:3307/demo_ds_1?serverTimezone=UTC&useSSL=false
   username: root
   password: root
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50

#shardingRule就是代表分库分表规则
shardingRule:
 tables:
   t_order:    #订单表的分表策略
     actualDataNodes: ds_${0..1}.t_order_${0..1}  #共ds_0/ds_1与t_order_0/t_order_1组合
     tableStrategy:  #分表策略
       inline:
         shardingColumn: order_id  #使用order_id作为分表策略
         algorithmExpression: t_order_${order_id % 2}  #分表算法，对2取余，如果3张表，就可以对3取余，以此类推
     keyGenerator:
       type: SNOWFLAKE   #雪花算法
       column: order_id
   t_order_item:   #订单项表的分表策略（同上）
     actualDataNodes: ds_${0..1}.t_order_item_${0..1}
     tableStrategy:
       inline:
         shardingColumn: order_id   #也以order_id为依据，好处是，同一订单对应的订单项肯定在同一数据库中，相当于手动bindingTables
         algorithmExpression: t_order_item_${order_id % 2}
     keyGenerator:
       type: SNOWFLAKE
       column: order_item_id
 bindingTables:
   - t_order,t_order_item   #这两张表是一组绑定表，告诉sharding-proxy订单和订单项表在同一数据库中，不需要去所有数据库中搜寻结果，提高效率
 defaultDatabaseStrategy:   #上面都是分表策略，这个是数据库的分库策略
   inline:
     shardingColumn: user_id   #以user_id作为数据库的分库策略依据
     algorithmExpression: ds_${user_id % 2}   #算法同上
 defaultTableStrategy:     #上面已经有具体的分表策略了，这里就不需要默认的分表策略了，也可以以所有主键id作为默认分表策略
   none:

3、主从复制下的读写分离：config-master_slave.yaml和config-master_slave2.yaml：

两个配置文件是因为我们有2个分库，2个分库就会有两个salve复制：

config-master_slave.yaml：

schemaName: sharding_db_1
# 这里只是配置读写分离，但是真正的主从节点还是需要我们自己去配置
dataSources:
 master_0_ds:
   url: jdbc:mysql://192.168.174.142:3307/demo_ds_0?serverTimezone=UTC&useSSL=false
   username: root
   password: root
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
 slave_ds_0:
   url: jdbc:mysql://192.168.174.142:3317/demo_ds_0?serverTimezone=UTC&useSSL=false
   username: root
   password: root
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
# 数据源的主从规则（将上面的主从配置关联起来）
masterSlaveRule:
 name: ms_ds
 masterDataSourceName: master_0_ds
 slaveDataSourceNames:
   - slave_ds_0
 loadBalanceAlgorithmType: ROUND_ROBIN  #负载均衡策略

config-master_slave2.yaml：

schemaName: sharding_db_2
# 这里只是配置读写分离，但是真正的主从节点还是需要我们自己去配置
dataSources:
 master_1_ds:
   url: jdbc:mysql://192.168.174.142:3307/demo_ds_1?serverTimezone=UTC&useSSL=false
   username: root
   password: root
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
 slave_ds_1:
   url: jdbc:mysql://192.168.174.142:3317/demo_ds_1?serverTimezone=UTC&useSSL=false
   username: root
   password: root
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
# 数据源的主从规则（将上面的主从配置关联起来）
masterSlaveRule:
 name: ms_ds_1
 masterDataSourceName: master_1_ds
 slaveDataSourceNames:
   - slave_ds_1
 loadBalanceAlgorithmType: ROUND_ROBIN  #负载均衡策略

到这里，ShardingSphere-Proxy的配置就全部完成了（数据分片+主从读写分离）

4、在重新配置master和slave的主从复制配置，增加两个数据库：

binlog-do-db=demo_ds_0
binlog-do-db=demo_ds_1

5、到master中创建demo_ds_0库和demo_ds_1库

这样salve服务器上就会自动同步生成demo_ds_0和demo_ds_1库；

主从同步策略正常；

6、所有配置完成后，我们启动 /bin 目录下的 start.bat，可以指定端口号：

start.bat 3388

7、此时，我们测试用navicat连接 localhost:3388 即Sharding-Proxy服务；

连接正常，而且sharding-proxy根据我们的配置，已经创建了sharding_db_1和sharding_db_2这两个虚拟数据库；

根据配置，其中：

sharding_db_1对应的是config-master_slave.yaml配置文件中的master_0_ds和slave_ds_0这对主从库；

sharding_db_2对应的是config-master_slave2.yaml配置文件中的master_1_ds和slave_ds_1这对主从库；

而虚拟数据库sharding_db就是总的数据库（对应配置文件config-sharding.yaml），以后所有的服务连接的就是sharding_db这个虚拟数据库；

四、Sharding-Proxy实战测试（以后我们都是直接连接总代理库：sharding_db）

1、创建测试表：t_order：

CREATE TABLE `t_order` (
	`order_id` BIGINT (20) NOT NULL AUTO_INCREMENT,
	`user_id` INT (11) NOT NULL,
	`status` VARCHAR (50) COLLATE utf8_bin DEFAULT NULL,
	PRIMARY KEY (`order_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_bin;

2、创建测试表：t_order_item：

CREATE TABLE `t_order_item` (
	`order_item_id` BIGINT (20) NOT NULL AUTO_INCREMENT,
	`order_id` BIGINT (20) NOT NULL,
	`user_id` INT (11) NOT NULL,
	`content` VARCHAR (255) COLLATE utf8_bin DEFAULT NULL,
	`status` VARCHAR (50) COLLATE utf8_bin DEFAULT NULL,
	PRIMARY KEY (`order_item_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_bin;

3、随着我们在sharding_db总代理库中创建了这两张表后

sharding-proxy上的分代理表sharding_db_1和sharding_db_2中都建好了相应的表；

另外，实际的物理表中，也对应地都生成了4张表：

另外，咱们的slave服务，肯定也随同它的master，创建个各自的4张表：

4、我们向sharding_db的t_order中插入几条数据（主键不用指定，我们已经使用雪花算法了）

INSERT INTO t_order(user_id, status) VALUES(1, 1);
INSERT INTO t_order(user_id, status) VALUES(2, 1);
INSERT INTO t_order(user_id, status) VALUES(3, 1);
INSERT INTO t_order(user_id, status) VALUES(4, 1);
INSERT INTO t_order(user_id, status) VALUES(5, 1);

此时，总代理库，总表t_order中肯定是5条数据：

但是我们再来看看，通过策略，在sharding_db_1和sharding_db_2中的数据：

其中：

demo_ds_0.t_order_0无数据；demo_ds_0.t_order_1有2条数据；

demo_ds_1.t_order_0有3条数据；demo_ds_1.t_order_1无数据；

——原因：根据user_id进行分库，奇数在demo_ds_1（共3条），偶数在demo_ds_0（共2条）；

——其次：根据order_id进行分表，奇数在t_order_1（共2条），偶数在t_demo_0（共3条）；

这样再看上面的结果，就清晰了！！

至此，ShardingSphere-Proxy的简单实用就结束了；