木易博客

MySQL Group Replication(MGR) 动态添加成员节点

young -

相关信息

MGR单主模式搭建完成
原MGR节点
192.168.3.252:3306 192.168.222.171:3306 192.168.222.70:3306

新增节点 192.168.222.78:3306
需要保证新增节点IP 在group_replication_ip_whitelist 中
我的配置为 group_replication_ip_whitelist="192.168.3.252,192.168.222.0/24"
新增节点在此网段中，所以不需要修改此配置（修改需重启）

修改已有节点配置

分别登陆test-1、test-2、test-3
修改配置

# 修改
set global group_replication_group_seeds="192.168.3.252:13306,192.168.222.171:13306,192.168.222.70:13306,192.168.222.78:13306";
# 查看
show variables like '%group_replication_group_seeds%';

安装mysql

(test-4)
mysql 安装修改配置文件及初始化（省略）详情 https://yangxx.net/?p=3657

配置MGR

创建授权用户（test-4）

set sql_log_bin=0;
grant replication slave,replication client on *.* to repuser@'%' IDENTIFIED BY 'amT_2Kj19';
grant replication slave,replication client on *.* to repuser@'127.0.0.1' IDENTIFIED BY 'amT_2Kj19';
grant replication slave,replication client on *.* to repuser@'localhost' IDENTIFIED BY 'amT_2Kj19' ;
set sql_log_bin=1;

配置同步使用的用户

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';

安装mysql group replication插件

# 备注：如果在my.cnf里写写入plugin_load="group_replication=group_replication.so" 这步就可以不用操作
install plugin group_replication soname 'group_replication.so';
# 通过show plugins;查看是否安装成功

启动

开启兼容模式

set global group_replication_allow_local_disjoint_gtids_join=ON;
START GROUP_REPLICATION;

检查

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 07a96b05-9bd1-11e9-bd95-000c29ceaa09 | test-3      |        3306 | ONLINE       |
| group_replication_applier | 9739c60d-9bda-11e9-9af3-000c29a21c95 | test-1      |        3306 | ONLINE       |
| group_replication_applier | cdbd0ca5-9e37-11e9-aa52-000c29daa6b3 | test-4      |        3306 | RECOVERING   |
| group_replication_applier | ec96355f-9bd0-11e9-8437-000c29fdc162 | test-2      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
# 刚加入是状态是 RECOVERING
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 07a96b05-9bd1-11e9-bd95-000c29ceaa09 | test-3      |        3306 | ONLINE       |
| group_replication_applier | 9739c60d-9bda-11e9-9af3-000c29a21c95 | test-1      |        3306 | ONLINE       |
| group_replication_applier | cdbd0ca5-9e37-11e9-aa52-000c29daa6b3 | test-4      |        3306 | ONLINE       |
| group_replication_applier | ec96355f-9bd0-11e9-8437-000c29fdc162 | test-2      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
# 数据同步完成是 就是ONLINE