使用版本： 8.0.26

集群 my.cnf 配置

loose 前缀的作用在于第一次启动时还没加载组复制的 plugin,可以让 mysql server 忽略该参数继续启动。
不同节点需修改 server_id 和 group_replication_local_address
该配置需启动时加载，要确保 /etc/my.cnf 包含 /etc/mysql/conf.d/ 目录下的配置文件

  1. [mysqld]
  2. server_id=1
  3. gtid_mode=ON
  4. enforce_gtid_consistency=ON
  6. disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
  8. binlog_checksum=NONE
  10. log_bin=binlog
  11. #log_slave_updates=ON # 5.7
  12. log_replica_updates=ON
  13. binlog_format=ROW
  14. # 8.0 后为默认配置
  15. #master_info_repository=TABLE
  16. #relay_log_info_repository=TABLE
  17. #transaction_write_set_extraction=XXHASH64
  19. plugin_load_add='group_replication.so'
  20. # 可以使用 SELECT UUID() 生成一个
  21. loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
  22. # 配置成员后，可以设置 group_replication_start_on_boot 为 on 在服务器启动时自动启动组复制。
  23. loose-group_replication_start_on_boot=off
  24. # 配置 group_replication_local_address 设置成员用于与组中其他成员进行内部通信的网络地址和端口。
  25. loose-group_replication_local_address= "s1:33061"
  26. loose-group_replication_group_seeds= "s1:33061,s2:33061,s3:33061"
  27. loose-group_replication_bootstrap_group=off
  28. # 小于 8.0.22 的配置 group_replication_ip_whitelist=127.0.0.1/8,192.168.1.1/24
  29. loose-group_replication_ip_allowlist=127.0.0.1/8,192.168.1.1/24
  30. loose-group_replication_recovery_get_public_key=ON

启动服务

  1. docker run -dit --name mysql --restart always --net host -v /path/mgr.cnf:/etc/mysql/conf.d/mgr.cnf -v /etc/localtime:/etc/locatime:ro -v /var/lib/mysql/:/var/lib/mysql/ -e MYSQL_ROOT_PASSWORD=password mysql/mysql-server

账户创建

  1. SET SQL_LOG_BIN=0;     
  2. CREATE USER rpl_user@'%' IDENTIFIED BY '123456';
  3. GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
  4. # 8.0
  5. GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
  6. FLUSH PRIVILEGES;
  7. SET SQL_LOG_BIN=1;
  9. CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
  10. # Or from MySQL 8.0.23:
  11. CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
  13. SELECT * FROM performance_schema.replication_group_members;
  14. SHOW STATUS LIKE 'group_replication_primary_member';

实例检查

需要每一个实例都执行

  1. SET SQL_LOG_BIN=0;     
  2. CREATE USER mgr@'%' IDENTIFIED BY '123456';
  3. FLUSH PRIVILEGES;
  4. SET SQL_LOG_BIN=1;
  6. mysqlsh root@localhost:3306
  8. dba.checkInstanceConfiguration()
  9. # 将报错无权限，按照提示授权，登录 mysql 终端重新执行授权语句
  10. SET SQL_LOG_BIN=0;     
  11. GRANT xxxxxxx
  12. FLUSH PRIVILEGES;
  13. SET SQL_LOG_BIN=1;
  15. # 登录后 mysqlsh 执行如下命令修复配置问题
  16. dba.configureInstance()
  17. # 执行后返回 ok
  18. dba.checkInstanceConfiguration()

创建集群

  1. var cluster = dba.createCluster('cluster_name');
  2. cluster.addInstance('mgr@ip:3306');
  3. # 查看状态
  4. cluster.describe()
  5. cluster.status()

实例恢复

  1. var cluster = dba.getCluster('cluster_name')
  2. cluster.rescan()
  3. cluster.rejoinInstance('root@ip:3306')
  4. cluster.removeInstance("root@ip:3306")

重启 mgr

  1. dba.rebootClusterFromCompleteOutage()
  3. var cluster = dba.getCluster('cluster_name')
  4. cluste.status()

启动 mgr

  1. START GROUP_REPLICATION;
  2. # 查看主从库信息
  3. SELECT * FROM performance_schema.replication_group_members;
  5. # 查看本机 MySQL 的 server_uuid
  6. show variables like '%server_%';
  8. # 切换主库命令
  9. SELECT group_replication_set_as_primary('换成目标库的server_uuid');
  11. # 查看切换进度
  12. SELECT event_name, work_completed, work_estimated FROM performance_schema.events_stages_current WHERE event_name LIKE "%stage/group_rpl%";

启动 MySQL Router

  1. docker run -dit --name mysql-router --restart always --net host -e MYSQL_HOST=192.168.1.1 -e MYSQL_PORT=3306 -e MYSQL_USER=mgr mysql/mysql-router

连接 6446 端口进行测试