新的节点需要提前准备如下参数,可参考已有节点的设置
确保uuid和server id和其他节点不一致
新节点
# BINARY LOGGING #
log-bin = /data/db5722/mysql-bin
relay-log = /data/db5722/relay-bin
expire-logs-days = 7
sync-binlog = 1
server-id = 13657
log_slave_updates = ON
# GTID #
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_format = ROW
binlog_checksum = NONE
master_info_repository = TABLE
relay_log_info_repository = TABLE
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4
slave_preserve_commit_order = ON
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
这里账号密码和集群保持一致
新节点
SET SQL_LOG_BIN=0;
CREATE USER replicate@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl@'%' IDENTIFIED BY 'rpl';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
新节点
安装
mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';
确认
mysql> SHOW PLUGINS;
| group_replication| ACTIVE| GROUP REPLICATION | group_replication.so | PROPRIETARY |
新节点
停止MYSQL服务后修改
记得添加到配置文件
group_replication_group_name 和其他节点一致
group_replication_start_on_boot暂时设置为OFF
group_replication_local_addres填写本机的IP group_replication_group_seeds 填写所有节点的IP
# MGR
#transaction_write_set_extraction = XXHASH64
group_replication_group_name = "69082df2-3dda-4703-8da1-7c8ecda9664f"
group_replication_start_on_boot = off
group_replication_local_address = "192.168.201.136:2101"
group_replication_group_seeds = "192.168.201.135:2101,192.168.201.136:2101,192.168.201.137:2101"
group_replication_bootstrap_group = OFF
group_replication_single_primary_mode = TRUE
group_replication_enforce_update_everywhere_checks = FALSE
修改完成后重启服务。
其中group_replication_group_seeds 需要在所有节点增加新加入的节点的IP
其他节点
set global group_replication_group_seeds="192.168.201.135:2101,192.168.201.136:2101,192.168.201.137:2101"
在数据最新的节点上执行mysqldump全量导出
其中一个节点
mysqldump -uroot -p123456 -q --single-transaction --master-data=2 -B DB_Name > /root/DB_Name.sql
新节点
之后在新增节点执行数据导入
reset master;
source DB_Name.sql
如果使用xtrabackup备份的,需要
reset master
set @@GLOBAL.GTID_PURGED='e99ae99a-811d-11e9-9ca2-0050568cef02:1-59387'
之后加入MGR集群
新节点
mysql> CHANGE MASTER TO MASTER_USER='rpl',MASTER_PASSWORD='rpl' FOR CHANNEL 'group_replication_recovery';
mysql> START GROUP_REPLICATION;
最后将新加入的节点MGR自启动配置为on,参数文件修改
group_replication_start_on_boot=on
最后我们检查是否加入成功
所有节点
查询下列视图
SELECT * FROM performance_schema.replication_group_members;
查询是否为写入节点
SELECT * FROM sys.gr_member_routing_candidate_status;