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 |
+----------------------------------+--------------------------------------+-------------+-------------+--------------+
网友评论