MySQL Group Replication 多主结构的3节点需要切到另外三个节点上，任务需要先加3个节点到集群中，再删掉原来的3个节点。
环境
-
原实例：
- 172.0.2.30:3309
- 172.0.2.31:3309
- 172.0.2.32:3309
-
新实例：
- 172.0.2.83:3309
- 172.0.2.84:3309
- 172.0.2.85:3309
修改host
- 修改6台主机的/etc/hosts
172.0.2.30 dba-mysql3309-230 dba-mysql3309-230.dboop.com
172.0.2.31 dba-mysql3309-231 dba-mysql3309-231.dboop.com
172.0.2.32 dba-mysql3309-232 dba-mysql3309-232.dboop.com
172.0.2.83 dba-mysql3309-83 dba-mysql3309-83.dboop.com
172.0.2.84 dba-mysql3309-84 dba-mysql3309-84.dboop.com
172.0.2.85 dba-mysql3309-85 dba-mysql3309-85.dboop.com
旧实例上增加seed
- 检查状态
mysqlw -h 172.0.2.30 -P 3309 -e "show global variables like 'group_replication_group_seeds'";
mysqlw -h 172.0.2.31 -P 3309 -e "show global variables like 'group_replication_group_seeds'";
mysqlw -h 172.0.2.32 -P 3309 -e "show global variables like 'group_replication_group_seeds'";
- 检查集群状态
mysqlw -h 172.0.2.30 -P 3309 -e " SELECT * FROM performance_schema.replication_group_members ";
mysqlw -h 172.0.2.31 -P 3309 -e " SELECT * FROM performance_schema.replication_group_members ";
mysqlw -h 172.0.2.32 -P 3309 -e " SELECT * FROM performance_schema.replication_group_members ";
- 修改seed
mysqlw -h 172.0.2.30 -P 3309 -e "set global group_replication_group_seeds='172.0.2.30:33095,172.0.2.31:33095,172.0.2.32:33095,172.0.2.83:33095,172.0.2.84:33095,172.0.2.85:33095'"
mysqlw -h 172.0.2.31 -P 3309 -e "set global group_replication_group_seeds='172.0.2.30:33095,172.0.2.31:33095,172.0.2.32:33095,172.0.2.83:33095,172.0.2.84:33095,172.0.2.85:33095'"
mysqlw -h 172.0.2.32 -P 3309 -e "set global group_replication_group_seeds='172.0.2.30:33095,172.0.2.31:33095,172.0.2.32:33095,172.0.2.83:33095,172.0.2.84:33095,172.0.2.85:33095'"
- 检查状态
mysqlw -h 172.0.2.30 -P 3309 -e "show global variables like 'group_replication_group_seeds'";
mysqlw -h 172.0.2.31 -P 3309 -e "show global variables like 'group_replication_group_seeds'";
mysqlw -h 172.0.2.32 -P 3309 -e "show global variables like 'group_replication_group_seeds'";
加入新节点1(2.84)
安装插件
mysqlw -h 172.0.2.84 -P 3309 -e "show plugins" |grep "group_replication" #检查是否安装
mysqlw -h 172.0.2.84 -P 3309 -e "install plugin group_replication soname 'group_replication.so'";
mysqlw -h 172.0.2.84 -P 3309 -e "show plugins" |grep "group_replication"
修改配置文件：
- vim /data/mysql3309/my.cnf
- 注意要修改：group_replication_local_address
#add group replic
relay_log = relay
binlog_checksum = NONE
master_info_repository = table
relay_log_info_repository = table
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
slave_preserve_commit_order = 1
transaction_write_set_extraction = XXHASH64
plugin_load_add = 'group_replication.so'
group_replication_unreachable_majority_timeout=10
group_replication_exit_state_action = offline_mode
loose-group_replication_group_name = "e0dbceb7-f505-11eb-91ee-e43d1a075b40"
loose-group_replication_start_on_boot = 0
loose-group_replication_local_address = "172.0.2.84:33095"
loose-group_replication_group_seeds = "172.0.2.30:33095,172.0.2.31:33095,172.0.2.32:33095,172.0.2.83:33095,172.0.2.84:33095,172.0.2.85:33095"
loose-group_replication_bootstrap_group = 0
#mutli master
loose-group_replication_single_primary_mode=0
loose-group_replication_enforce_update_everywhere_checks=1
停原主从复制
mysqlw -h 172.0.2.84 -P 3309 -e "show slave status \G"
mysqlw -h 172.0.2.84 -P 3309 -e "stop slave "
mysqlw -h 172.0.2.84 -P 3309 -e "reset slave all"
mysqlw -h 172.0.2.84 -P 3309 -e "show slave status \G"
重启实例
- 注意是重启新节点2.84
service mysqld3309 restart
新节点加入
mysqlw -h 172.0.2.84 -P 3309 -e "start GROUP_REPLICATION USER='repl2', PASSWORD='repl.dba6'"
mysqlw -h 172.0.2.84 -P 3309 -e " SELECT * FROM performance_schema.replication_group_members ";
mysqlw -h 172.0.2.84 -P 3309 -e "SELECT * FROM performance_schema.replication_connection_status \G";
- 检查集群状态
mysqlw -h 172.0.2.30 -P 3309 -e " SELECT * FROM performance_schema.replication_group_members ";
mysqlw -h 172.0.2.31 -P 3309 -e " SELECT * FROM performance_schema.replication_group_members ";
mysqlw -h 172.0.2.32 -P 3309 -e " SELECT * FROM performance_schema.replication_group_members ";
mysqlw -h 172.0.2.30 -P 3309 -e "show processlist"
mysqlw -h 172.0.2.31 -P 3309 -e "show processlist"
mysqlw -h 172.0.2.32 -P 3309 -e "show processlist"
继续添加节点（如果不是立即切换和迁移，请保证集群中的节点数为奇数）
….
删除节点(2.32）
- 修改seed
mysqlw -h 172.0.2.30 -P 3309 -e "set global group_replication_group_seeds='172.0.2.30:33095,172.0.2.31:33095,172.0.2.83:33095,172.0.2.84:33095,172.0.2.85:33095'"
mysqlw -h 172.0.2.31 -P 3309 -e "set global group_replication_group_seeds='172.0.2.30:33095,172.0.2.31:33095,172.0.2.83:33095,172.0.2.84:33095,172.0.2.85:33095'"
mysqlw -h 172.0.2.32 -P 3309 -e "set global group_replication_group_seeds='172.0.2.30:33095,172.0.2.31:33095,172.0.2.83:33095,172.0.2.84:33095,172.0.2.85:33095'"
mysqlw -h 172.0.2.83 -P 3309 -e "set global group_replication_group_seeds='172.0.2.30:33095,172.0.2.31:33095,172.0.2.83:33095,172.0.2.84:33095,172.0.2.85:33095'"
mysqlw -h 172.0.2.84 -P 3309 -e "set global group_replication_group_seeds='172.0.2.30:33095,172.0.2.31:33095,172.0.2.83:33095,172.0.2.84:33095,172.0.2.85:33095'"
mysqlw -h 172.0.2.85 -P 3309 -e "set global group_replication_group_seeds='172.0.2.30:33095,172.0.2.31:33095,172.0.2.83:33095,172.0.2.84:33095,172.0.2.85:33095'"
- 停2.32
mysqlw -h 172.0.2.32 -P 3309 -e "stop GROUP_REPLICATION"
- 检查集群状态
mysqlw -h 172.0.2.30 -P 3309 -e " SELECT * FROM performance_schema.replication_group_members ";
mysqlw -h 172.0.2.31 -P 3309 -e " SELECT * FROM performance_schema.replication_group_members ";
mysqlw -h 172.0.2.32 -P 3309 -e " SELECT * FROM performance_schema.replication_group_members ";
mysqlw -h 172.0.2.30 -P 3309 -e "show processlist"
mysqlw -h 172.0.2.31 -P 3309 -e "show processlist"
mysqlw -h 172.0.2.32 -P 3309 -e "show processlist"
