使用Sharding-Proxy完成mysql分库分表和主从复制

上篇文章，实验了主从mysql复制，这次在上篇文章的基础上，完成mysql分库分表并测试主从复制。

下载Sharding-Proxy

https://archive.apache.org/dist/incubator/shardingsphere/4.0.0/

1. 下载apache-shardingsphere-incubating-4.0.1-sharding-proxy-bin.tar.gz包，
2. 解压到某一目录下如mydata目录下
3. 由于目录名太长，我将目录名称改为了sharding-proxy
4. 进入sharding-proxy/conf目录下

修改配置文件

1. 修改认证信息 server.yaml

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

props:
  executor.size: 16  # Infinite by default.
  sql.show: true

2. 修改分库分表配置 config-sharding.yaml

schemaName: sharding_db

dataSources:
  ds_0:
    # 库demo_ds_0的连接地址，根据自己的配置修改ip和端口
    url: jdbc:mysql://192.168.94.133:3307/demo_ds_0?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_1:
    # 库demo_ds_1的连接地址，根据自己的配置修改ip和端口
    url: jdbc:mysql://192.168.94.133:3307/demo_ds_1?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

# 规则
shardingRule:
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..1} # 根据user_id取模的结果，选择库；根据order_id取模的结果，选择表；选择库和表的规则在 algorithmExpression 做了配置
      tableStrategy:
        inline:
          shardingColumn: order_id  #表中的order_id列
          algorithmExpression: t_order_${order_id % 2} # 对order_id取模
      keyGenerator:
        type: SNOWFLAKE # 使用雪花算法，生成order_id
        column: 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}
      keyGenerator:
        type: SNOWFLAKE
        column: order_item_id
  bindingTables: # 绑定表规则，表示t_order和表t_order_item在做连接操作时，不会进行跨库操作
    - t_order,t_order_item
  defaultDatabaseStrategy: # 选择库使用的策略
    inline:
      shardingColumn: user_id
      algorithmExpression: ds_${user_id % 2}  # 根据user_id取模的结果，选择库
  defaultTableStrategy:
    none:

3. 配置主从复制规则

# 使用配置文件config-master_slave_0.yaml配置库demo_ds_0主从复制规则
schemaName: sharding_db0

dataSources:
  master_ds_0: #主库demo_ds_0配置
    url: jdbc:mysql://192.168.94.133:3307/demo_ds_0?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  slave_ds_0: # 从库demo_ds_0的配置，多个从库，可以继续添加配置
    url: jdbc:mysql://192.168.94.133:3317/demo_ds_0?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
 
masterSlaveRule:  #
    name: ms_ds_0 # 规则名称
    masterDataSourceName: master_ds_0 # 主数据源名称
    slaveDataSourceNames:
      - slave_ds_0 # 从数据源名称，slaveDataSourceNames数组中的一个元素
    loadBalanceAlgorithmType: ROUND_ROBIN

# 使用配置文件config-master_slave_1.yaml配置库demo_ds_1主从复制规则
schemaName: sharding_db1

dataSources:
  master_ds_1:
    url: jdbc:mysql://192.168.94.133: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.94.133: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_ds_1
    slaveDataSourceNames:
      - slave_ds_1
    loadBalanceAlgorithmType: ROUND_ROBIN

启动Sharding-Proxy

1. 进入bin目录下
2. 执行./start.sh 3388 ，都口号根据自己实际情况修改，默认是3307。
3. 看到如下日志，说明启动成功。

log1:
Starting the Sharding-Proxy ...
The port is configured as 3388
Please check the STDOUT file: /mydata/sharding-proxy/logs/stdout.log

log2:
...
[INFO ] 08:35:31.564 [main] c.a.icatch.provider.imp.AssemblerImp - USING: com.atomikos.icatch.force_shutdown_on_vm_exit = false
[INFO ] 08:35:31.564 [main] c.a.icatch.provider.imp.AssemblerImp - USING: com.atomikos.icatch.default_jta_timeout = 300000
[INFO ] 08:35:31.565 [main] c.a.icatch.provider.imp.AssemblerImp - Using default (local) logging and recovery...
[INFO ] 08:35:31.613 [main] c.a.d.xa.XATransactionalResource - resource-1-master_ds_0: refreshed XAResource
[INFO ] 08:35:31.627 [main] c.a.d.xa.XATransactionalResource - resource-2-slave_ds_0: refreshed XAResource
[INFO ] 08:35:31.669 [main] com.zaxxer.hikari.HikariDataSource - HikariPool-3 - Starting...
[INFO ] 08:35:31.677 [main] com.zaxxer.hikari.HikariDataSource - HikariPool-3 - Start completed.
[INFO ] 08:35:31.678 [main] com.zaxxer.hikari.HikariDataSource - HikariPool-4 - Starting...
[INFO ] 08:35:31.690 [main] com.zaxxer.hikari.HikariDataSource - HikariPool-4 - Start completed.
[INFO ] 08:35:31.709 [main] c.a.d.xa.XATransactionalResource - resource-3-ds_0: refreshed XAResource
[INFO ] 08:35:31.725 [main] c.a.d.xa.XATransactionalResource - resource-4-ds_1: refreshed XAResource
[INFO ] 08:35:32.553 [main] com.zaxxer.hikari.HikariDataSource - HikariPool-5 - Starting...
[INFO ] 08:35:32.560 [main] com.zaxxer.hikari.HikariDataSource - HikariPool-5 - Start completed.
[INFO ] 08:35:32.561 [main] com.zaxxer.hikari.HikariDataSource - HikariPool-6 - Starting...
[INFO ] 08:35:32.574 [main] com.zaxxer.hikari.HikariDataSource - HikariPool-6 - Start completed.
[INFO ] 08:35:32.586 [main] c.a.d.xa.XATransactionalResource - resource-5-master_ds_1: refreshed XAResource
[INFO ] 08:35:32.597 [main] c.a.d.xa.XATransactionalResource - resource-6-slave_ds_1: refreshed XAResource
[INFO ] 08:35:32.940 [epollEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x711df74e] REGISTERED
[INFO ] 08:35:32.943 [epollEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x711df74e] BIND: 0.0.0.0/0.0.0.0:3388
[INFO ] 08:35:32.946 [epollEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x711df74e, L:/0.0.0.0:3388] ACTIVE