MySQL ( MGR ) 07-切换到多主模式 & 切回单主模

作者: 轻飘飘D | 来源:发表于2021-02-02 22:18 被阅读0次

MySQL ( MGR ) 07-切换到多主模式 & 切回单主模
MySQL Group Replication搭建笔记
MySQL MGR 集群搭建（单主模式&多主模式）
【MySQL】MySQL MGR组复制搭建（单主及多主模式）
MGR 单主模式&多主模式
MySQL ( MGR ) 06-MGR 单主模式和多主模式的集
yii2 配合 mysql-MGR 配置高可用链接
mysql 数据库主-主模式常见问题以及修复方式
MySQL组复制
MySQL MGR集群原理及实践

MGR切换模式需要重新启动组复制，因些需要在所有节点上先关闭组复制，
设置 group_replication_single_primary_mode=OFF 等参数，再启动组复制。
1.切换到多主模式
1.1.停止组复制(在所有MGR节点上执行)

stop group_replication;
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;

my.cnf文件中修改(如要永久生效则如下修改）
loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON

1.2.开启(group_replication_bootstrap_group=ON)引导功能 (随便选择某个MGR节点)

#这里选择xag211 上
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

1.3.其他的MGR节点（xag212，xag213）

START GROUP_REPLICATION;

root@xag213:testdb [:53: ] 13 SQL->SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211      |        3306 | ONLINE       |
| group_replication_applier | 2126c7e9-63c9-11eb-a1ba-000c299e2212 | xag212      |        3306 | ONLINE       |
| group_replication_applier | 2136c7e9-63c9-11eb-a1ba-000c299e2213 | xag213      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

root@xag213:testdb [:54: ] 17 SQL->show global variables like 'group_replication_single_primary_mode';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | OFF   |
+---------------------------------------+-------+

1.4.多主测试

root@xag213:testdb [:56: ] 21 SQL->delete from t1 where Id=5;

root@xag212:testdb [:57: ] 15 SQL->delete from t1 where Id=4;

root@xag211:testdb [:58: ] 26 SQL->delete from t1 where Id=3;

root@xag211:testdb [:58: ] 27 SQL->select * from t1;
+----+----------+
| Id | TestName |
+----+----------+
|  1 | a        |
|  2 | b        |
+----+----------+

2.切回单主模式
2.1 停止组复制(在所有MGR节点上执行)

stop group_replication;
set global group_replication_enforce_update_everywhere_checks=OFF;
set global group_replication_single_primary_mode=ON;

my.cnf文件中修改(如要永久生效则如下修改）
loose-group_replication_single_primary_mode = ON
loose-group_replication_enforce_update_everywhere_checks = OFF

2.2 选择一个节点作为主节点, 在主节点上执行 (这里选择xag211节点作为主节点)

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

root@xag211:testdb [:13: ] 39 SQL->select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

2.3 在其他剩余的节点, 也就是从库节点上执行 (这里从库节点指的就是xag212和xag213)

START GROUP_REPLICATION;

root@xag211:testdb [:15: ] 41 SQL->select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211      |        3306 | ONLINE       |
| group_replication_applier | 2126c7e9-63c9-11eb-a1ba-000c299e2212 | xag212      |        3306 | ONLINE       |
| group_replication_applier | 2136c7e9-63c9-11eb-a1ba-000c299e2213 | xag213      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

root@xag211:testdb [:15: ] 42 SQL->
SELECT ta.* ,tb.MEMBER_HOST,tb.MEMBER_PORT,tb.MEMBER_STATE FROM 
performance_schema.global_status ta,performance_schema.replication_group_members tb 
WHERE ta.VARIABLE_NAME='group_replication_primary_member' and ta.VARIABLE_VALUE=tb.MEMBER_ID;
+----------------------------------+--------------------------------------+-------------+-------------+--------------+
| VARIABLE_NAME                    | VARIABLE_VALUE                       | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+----------------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_primary_member | 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211      |        3306 | ONLINE       |
+----------------------------------+--------------------------------------+-------------+-------------+--------------+