1.新建主从mysql节点 docker 创建

  1. #创建myqsl主节点 master 3307
  2. sudo docker run -p 3307:3306 --name mysql-master \
  3. -v /mydata/mysql/master/log:/var/log/mysql \
  4. -v /mydata/mysql/master/data:/var/lib/mysql \
  5. -v /mydata/mysql/master/conf:/etc/mysql \
  6. -e MYSQL_ROOT_PASSWORD=Xuwei19960413 \
  7. -d mysql:5.7
  9. #slave-01
  10. sudo docker run -p 3308:3306 --name mysql-slave-01 \
  11. -v /mydata/mysql/slave01/log:/var/log/mysql \
  12. -v /mydata/mysql/slave01/data:/var/lib/mysql \
  13. -v /mydata/mysql/slave01/conf:/etc/mysql \
  14. -e MYSQL_ROOT_PASSWORD=Xuwei19960413 \
  15. -d mysql:5.7
  17. #slave-02
  18. sudo docker run -p 3309:3306 --name mysql-slave-02 \
  19. -v /mydata/mysql/slave02/log:/var/log/mysql \
  20. -v /mydata/mysql/slave02/data:/var/lib/mysql \
  21. -v /mydata/mysql/slave02/conf:/etc/mysql \
  22. -e MYSQL_ROOT_PASSWORD=Xuwei19960413 \
  23. -d mysql:5.7

2.编辑master配置文件 vi /mydata/mysql/master/cnf/my.cnf

  1. [client]
  2. default-character-set=utf8
  3. [mysql]
  4. default-character-set=utf8
  5. [mysqld]
  6. init_connect='SET collation_connection = utf8_unicode_ci'
  7. init_connect='SET NAMES utf8'
  8. character-set-server=utf8
  9. collation-server=utf8_unicode_ci
  10. skip-character-set-client-handshake
  11. skip-name-resolve
  12. #=======================master节点配置===================================
  13. server_id=1
  14. log-bin=mysql-bin
  15. read-only=0
  17. binlog-do-db=ums
  18. binlog-do-db=pms
  19. binlog-do-db=wms
  21. replicate-ignore-db=mysql
  22. replicate-ignore-db=sys
  23. replicate-ignore-db=information_schema
  24. replicate-ignore-db=performance_schema
  26. #=======================slave节点配置===================================
  27. server_id=2
  28. log-bin=mysql-bin
  29. read-only=1
  31. binlog-do-db=ums
  32. binlog-do-db=pms
  33. binlog-do-db=wms
  35. replicate-ignore-db=mysql
  36. replicate-ignore-db=sys
  37. replicate-ignore-db=information_schema
  38. replicate-ignore-db=performance_schema

3.节点常见和错误排查

  1. #Master 节点执行 授权从节点账号 账号：backup 密码：123456
  2. GRANT REPLICATION SLAVE ON *.* TO 'backup'@'%' IDENTIFIED BY '123456'
  4. #查看授权状态
  5. show master status
  7. #从节点连接主机
  8. CHANGE MASTER TO master_host='t.freefish.info',master_user='backup',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=0,master_port=3307;
  10. #开始同步
  11. start slave
  12. #停止同步
  13. stop slave
  14. #从节点状态
  15. show slave STATUS
  17. #出现错误[ERROR] Slave SQL for channel '': Error 'Can't drop database 'ums'; 
  18. #database doesn't exist' on query. Default database: 'ums'. Query: 'DROP DATABASE `ums`',
  19. #Error_code: 1008  原因是在slave创建了一个和master一样的数据库  解决方法指针向下移动一位
  20. SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
  23. #如果主从配置始终都是Slave_sql_running No 注意看状态 多试几次，多偏移几次就会为yes
  24. stop slave;    
  25. SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;          
  26. start slave;    
  27. SHOW SLAVE STATUS;

3、Mysql集群主从方式

  1. 1、Mysql MMM:两个Master 一个Monitor 再加从节点 （不好用）
  2. 2、InnerDb Cluster（官方提供）三部分组成：Mysql Shell （单独的管理客户端）,Mysql Ronter（mysql 连接器连接到Mysql Route,根据集群实例自动调度读写）,Mysql集群
  4. #重要 无法解决单表数据过大
  5. 3、Mycat、DnProxy 后台集群代理，写交给Master节点（还有备用主节点，主节点挂了就会提升他），读交给从节点（复制主节点数据），多个从节点职责分工，大部分用来web生产环境访问，部分用来后台管理系统使用，还有一些备份节点。（从节点角色区分）

4、Sharding-Proxy

4.1、docker Sharding-Proxy安装

  1. #先下载mysql驱动到映射目录
  2. cd /mydata/sharding/ext-lib
  4. wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar
  6. #下载运行Sharding
  7. docker run -d -v /mydata/sharding/conf:/opt/sharding-proxy/conf -v /mydata/sharding/ext-lib:/opt/sharding-proxy/ext-lib --env PORT=3308 -p 13308:3308 apache/sharding-proxy:4.1.1

4.2、Sharding-Proxy 代理

  1. # vi server.yaml
  2. authentication:
  3.   users:
  4.     root:
  5.       password: Xuwei19960413
  6.     sharding:
  7.       password: Xuwei19960413
  8.       authorizedSchemas: db_proxy
  9. props:
  10.   executor.size: 16
  11.   sql.show: true

4.3、Sharding-Proxy分库分表规则

  1. # vi config-sharding.yaml 配置分库分表规则 order_id分表  user_id分库
  2. # 现在主从配置好的master 创建 demo_ds_0 demo_ds_1数据库 ，此处只用两个
  3. schemaName: db_proxy
  4. dataSources:
  5.   ds_0:
  6.     url: jdbc:mysql://t.freefish.info:3307/demo_ds_0?serverTimezone=UTC&useSSL=false
  7.     username: root
  8.     password: Xuwei19960413
  9.     connectionTimeoutMilliseconds: 30000
  10.     idleTimeoutMilliseconds: 60000
  11.     maxLifetimeMilliseconds: 1800000
  12.     maxPoolSize: 50
  13.   ds_1:
  14.     url: jdbc:mysql://t.freefish.info:3307/demo_ds_1?serverTimezone=UTC&useSSL=false
  15.     username: root
  16.     password: Xuwei19960413
  17.     connectionTimeoutMilliseconds: 30000
  18.     idleTimeoutMilliseconds: 60000
  19.     maxLifetimeMilliseconds: 1800000
  20.     maxPoolSize: 50
  21. shardingRule:
  22.   tables:
  23.     t_order:
  24.       actualDataNodes: ds_${0..1}.t_order_${0..2} 
  25.       databaseStrategy: 
  26.         inline:
  27.           shardingColumn: user_id
  28.           algorithmExpression: ds_${user_id % 2}
  29.       tableStrategy: 
  30.         inline:
  31.           shardingColumn: order_id
  32.           algorithmExpression: t_order_${order_id % 3}
  33.       keyGenerator:
  34.         type: SNOWFLAKE
  35.         column: order_id
  36.     t_order_item:
  37.       actualDataNodes: ds_${0..1}.t_order_item_${0..2}
  38.       databaseStrategy:
  39.         inline:
  40.           shardingColumn: user_id
  41.           algorithmExpression: ds_${user_id % 2}
  42.       tableStrategy:
  43.         inline:
  44.           shardingColumn: order_id
  45.           algorithmExpression: t_order_item_${order_id % 3}
  46.       keyGenerator:
  47.         type: SNOWFLAKE
  48.         column: order_item_id
  49.   bindingTables:
  50.     - t_order,t_order_item
  51.   defaultTableStrategy:
  52.     none:

4.4、Sharding-Proxy配置主从 读写分离规则

（1）vi config-master-slave-01 配置一个master节点

  1. schemaName: db_proxy_2
  2. dataSources:
  3.   master_1_ds:
  4.     url: jdbc:mysql://t.freefish.info:3307/demo_ds_1?serverTimezone=UTC&useSSL=false
  5.     username: root
  6.     password: Xuwei19960413
  7.     connectionTimeoutMilliseconds: 30000
  8.     idleTimeoutMilliseconds: 60000
  9.     maxLifetimeMilliseconds: 1800000
  10.     maxPoolSize: 50
  11.   slave_ds_1:
  12.     url: jdbc:mysql://t.freefish.info:3308/demo_ds_1?serverTimezone=UTC&useSSL=false
  13.     username: root
  14.     password: Xuwei19960413
  15.     connectionTimeoutMilliseconds: 30000
  16.     idleTimeoutMilliseconds: 60000
  17.     maxLifetimeMilliseconds: 1800000
  18.     maxPoolSize: 50
  19.   slave_ds_2:
  20.     url: jdbc:mysql://t.freefish.info:3309/demo_ds_1?serverTimezone=UTC&useSSL=false
  21.     username: root
  22.     password: Xuwei19960413
  23.     connectionTimeoutMilliseconds: 30000
  24.     idleTimeoutMilliseconds: 60000
  25.     maxLifetimeMilliseconds: 1800000
  26.     maxPoolSize: 50
  27. masterSlaveRule:
  28.   name: ms_ds_1
  29.   masterDataSourceName: master_1_ds
  30.   slaveDataSourceNames:
  31.     - slave_ds_1
  32.     - slave_ds_2

（1）vi config-master-slave-02 再配置一个master节点（此次测试使用两个）

  1. # vi config-master-slave-01  主sharding-proxy配置另外一个master
  2. schemaName: db_proxy_1
  3. dataSources:
  4.   master_0_ds:
  5.     url: jdbc:mysql://t.freefish.info:3307/demo_ds_0?serverTimezone=UTC&useSSL=false
  6.     username: root
  7.     password: Xuwei19960413
  8.     connectionTimeoutMilliseconds: 30000
  9.     idleTimeoutMilliseconds: 60000
  10.     maxLifetimeMilliseconds: 1800000
  11.     maxPoolSize: 50
  12.   slave_ds_0:
  13.     url: jdbc:mysql://t.freefish.info:3308/demo_ds_0?serverTimezone=UTC&useSSL=false
  14.     username: root
  15.     password: Xuwei19960413
  16.     connectionTimeoutMilliseconds: 30000
  17.     idleTimeoutMilliseconds: 60000
  18.     maxLifetimeMilliseconds: 1800000
  19.     maxPoolSize: 50
  20.   slave_ds_1:
  21.     url: jdbc:mysql://t.freefish.info:3309/demo_ds_0?serverTimezone=UTC&useSSL=false
  22.     username: root
  23.     password: Xuwei19960413
  24.     connectionTimeoutMilliseconds: 30000
  25.     idleTimeoutMilliseconds: 60000
  26.     maxLifetimeMilliseconds: 1800000
  27.     maxPoolSize: 50
  28. masterSlaveRule:
  29.   name: ms_ds_0
  30.   masterDataSourceName: master_0_ds
  31.   slaveDataSourceNames:
  32.     - slave_ds_0
  33.     - slave_ds_1

4.5 其他测试指令

  2. #测试数据库 都在db_proxy执行
  3. CREATE TABLE `t_order`(
  4. `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  5. `user_id` int(11) NOT NULL,
  6. `status` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  7. PRIMARY KEY(`order_id`))
  8. ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  10. CREATE TABLE `t_order_item` (
  11.     `order_item_id` BIGINT(20) NOT NULL,
  12.     `order_id` BIGINT(20) NOT NULL,
  13.     `user_id` int(11) NOT NULL,
  14. `content` VARCHAR(55) COLLATE utf8_bin DEFAULT NULL,
  15. `status` VARCHAR(55) COLLATE utf8_bin DEFAULT NULL,
  16. PRIMARY KEY (`order_item_id`)
  17. )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  19. #删除指令
  20. DROP TABLE t_order
  21. DROP TABLE t_order_item
  22. #插入数据
  23. insert into t_order(user_id,status) values(3,1)