MGR单主模式搭建实践(完结)
架构
主:172.17.100.101
从①:172.17.100.103
从②:172.17.100.104
MySQL版本:5.7.22
MGR配置
修改每个节点下对应的配置文件my.cnf,增加如下配置
#MGR
server-id = 1 #主节点设置为1,从节点从2-9,此次实验为1主2从
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="3db33b36-0e51-409f-a61d-c99756e90155" #这个name必须是一个uuid的格式
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.17.100.101:23306" #每个节点此处为本机的IP及端口
loose-group_replication_group_seeds= "172.17.100.101:23306,172.17.100.103:23306,172.17.100.104:23306" #MGR组的所有IP及端口
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=true #单主模式必须设置
loose-group_replication_enforce_update_everywhere_checks=false #单主模式必须设置
第一节点(主节点)
SET SQL_LOG_BIN=0;
grant replication slave on *.* to rpl@'172.17.100.%' identified by 'beacon';
SET SQL_LOG_BIN=1;
change master to master_user='rpl',master_password='beacon' for channel 'group_replication_recovery';
install plugin group_replication soname 'group_replication.so';
set global group_replication_bootstrap_group=on;
start group_replication;
set global group_replication_bootstrap_group=off;
第二节点
SET SQL_LOG_BIN=0;
grant replication slave on *.* to rpl@'172.17.100.%' identified by 'beacon';
SET SQL_LOG_BIN=1;
change master to master_user='rpl',master_password='beacon' for channel 'group_replication_recovery';
install plugin group_replication soname 'group_replication.so';
set global group_replication_allow_local_disjoint_gtids_join=ON;
start group_replication;
第三节点
SET SQL_LOG_BIN=0;
grant replication slave on *.* to rpl@'172.17.100.%' identified by 'beacon';
SET SQL_LOG_BIN=1;
change master to master_user='rpl',master_password='beacon' for channel 'group_replication_recovery';
install plugin group_replication soname 'group_replication.so';
set global group_replication_allow_local_disjoint_gtids_join=ON;
start group_replication;
MGR相关信息检测
#查询组成员
select * from performance_schema.replication_group_members;
![906](https://upload-images.jianshu.io/upload_images/9289816-5c9dc8cc07bd7140.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/906)
#查询主节点
select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member';
![1000](https://upload-images.jianshu.io/upload_images/9289816-e7553c2890d0879f.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1000)
搭建中遇到的报错
1.[ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 172.17.100.103:23306 on local port: 23306.'
![1000](https://upload-images.jianshu.io/upload_images/9289816-0445c9d11a22c106.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1000)
这个报错是主节点执行start group_replication时发生的
重新执行set global group_replication_bootstrap_group=on;
之后再次start可以正常启动
实际上之前我已经执行过set global group_replication_bootstrap_group=on这个语句了,为什么会出现这个报错,有点懵比,总之重新执行后解决该问题。
2.[ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: ce088f8c-8e5b-11e8-9c42-005056ba04d1:1-2 > Group transactions: 3db33b36-0e51-409f-a61d-c99756e90155:1-2,
![1000](https://upload-images.jianshu.io/upload_images/9289816-60d7d835a42b8369.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1000)
这个报错是在第二节点执行start的时候产生的,根本原因是因为第二节点没有执行join语句
执行set global group_replication_allow_local_disjoint_gtids_join=ON;
完成上述语句的执行之后,再次start,即可成功执行
3.主从库皆可执行写操作,下列语句执行后,结果为空
select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member';
![1000](https://upload-images.jianshu.io/upload_images/9289816-13a5d44111c69918.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1000)
检查参数中的下列2行是否配置正确
![718](https://upload-images.jianshu.io/upload_images/9289816-2c38b686e93dd0e4.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/718)
在配置相反的情况下,搭建的MGR为多主
MGR切换测试
主节点执行关闭数据库
![1000](https://upload-images.jianshu.io/upload_images/9289816-8375b167761fce6e.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1000)
从节点执行相关检测
![1000](https://upload-images.jianshu.io/upload_images/9289816-53cde89074342818.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1000)
重新启动之前的主节点,并执行start GR,将主节点加入到MGR组内,可以发现主节点已经变更为之前的从节点
![1000](https://upload-images.jianshu.io/upload_images/9289816-2c0e30bb7066ba5a.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1000)
![1000](https://upload-images.jianshu.io/upload_images/9289816-fd427890688a3b68.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1000)
小礼物走一走,来简书关注我