分表案例

修改配置文件config-sharding.yaml如下

schemaName: sharding_db
dataSources:
  master:
    username: root
    password: 123456
    url: jdbc:mysql://192.168.136.160:3307/masterdb?serverTimezone=Asia/Shanghai&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useSSL=false
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 10
  slave1:
    username: root
    password: 123456
    url: jdbc:mysql://192.168.136.160:3308/masterdb?serverTimezone=Asia/Shanghai&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useSSL=false
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 10
  slave2:
    username: root
    password: 123456
    url: jdbc:mysql://192.168.136.160:3309/masterdb?serverTimezone=Asia/Shanghai&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useSSL=false
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 10
shardingRule:
  tables:
    goods:
      actualDataNodes: ds_ms1.goods_${0..1}
      tableStrategy:
        inline:
          shardingColumn: id
          algorithmExpression: goods_${id%2}
      keyGenerator:
        type: SNOWFLAKE
        column: id
  bindingTables: 
    - goods
  masterSlaveRules:
    ds_ms1:
      loadBalanceAlgorithmType: round_robin
      masterDataSourceName: master
      slaveDataSourceNames: 
        - slave1
        - slave2

MySQL主从复制

① 创建master容器

1. 创建配置

   
   mkdir -p /data/mysql/master/conf
   # master数据库配置
   sudo tee /data/mysql/master/conf/mysqld.cnf <<-'EOF'
   [mysqld]
   server-id = 1        # 节点ID，确保唯一
   
   # log config
   log-bin = mysql-bin     #开启mysql的binlog日志功能
   sync_binlog = 1         #控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
   binlog_format = mixed   #binlog日志格式，mysql默认采用statement，建议使用mixed
   expire_logs_days = 7                           #binlog过期清理时间
   max_binlog_size = 100m                    #binlog每个日志文件大小
   binlog_cache_size = 4m                        #binlog缓存大小
   max_binlog_cache_size= 512m              #最大binlog缓存大
   binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行
   
   auto-increment-offset = 1     # 自增值的偏移量
   auto-increment-increment = 1  # 自增值的自增量
   slave-skip-errors = all #跳过从库错误
   EOF
   
   

2. 创建容器

   
   # 创建master数据库
   docker run --name mysql-master -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 \
   -v /data/mysql/master/conf/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf \
   -v /data/mysql/master/data:/var/lib/mysql \
   -d mysql:5.7
   
   

② 创建2个slave容器