mysql一主一从切换_MySQL ( MGR ) 07-切换到多主模式 & 切回单主模式

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 |

+----------------------------------+--------------------------------------+-------------+-------------+--------------+