mysql mgr 多主切换单主

多主模式切换为单主模式，主要切换步骤如下

1、停止所有节点mgr和mysql服务

mysql> stop group_replication;
Query OK, 0 rows affected (2.68 sec)
mysql> shutdown;
Query OK, 0 rows affected (1.12 sec)

2、修改my.ini配置文件

loose-group_replication_single_primary_mode=true
##开启单主模式运行
loose-group_replication_enforce_update_everywhere_checks= false
##关闭group replication 强制检查节点写操作

3、启动所有节点的mysql服务

nohup /usr/local/mysql/bin/mysqld_safe  &

4、选择170.17节点作为主节点，执行如下操作

#####将改节点设置为引导节点
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
#####开启组复制功能
mysql> start group_replication;
Query OK, 0 rows affected (2.68 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
#####关闭引导节点
Query OK, 0 rows affected (0.00 sec)
#####查询当前group replication内成员
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 12ed1804-ff04-11eb-810c-0050568f8774 | rac1        |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

5、依次开启其他节点group replication功能

#####在节点2、3执行开启group replication功能
mysql> start group_replication;
Query OK, 0 rows affected (2.68 sec)
#####在节点1查询group replication成员状态
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 12ed1804-ff04-11eb-810c-0050568f8774 | rac1        |        3306 | ONLINE       |
| group_replication_applier | 789c2972-ff04-11eb-b4e1-0050568fa18f | rac2        |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.01 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 12ed1804-ff04-11eb-810c-0050568f8774 | rac1        |        3306 | ONLINE       |
| group_replication_applier | 789c2972-ff04-11eb-b4e1-0050568fa18f | rac2        |        3306 | ONLINE       |
| group_replication_applier | 9278af53-ff04-11eb-892d-0050568fd5f5 | rac3        |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

6、验证单主集群

mysql> show global variables like 'group_replication_single_primary_mode';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | ON    |
+---------------------------------------+-------+
1 row in set (0.00 sec)
mysql> 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 | 12ed1804-ff04-11eb-810c-0050568f8774 | rac1        |        3306 | ONLINE       |
+----------------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

#####尝试在另外两个节点写入数据，抛只读异常
mysql> insert into mm values('a6');
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
mysql>

7、测试单主节点故障转移(停止掉170.17上的服务，主节点会转移到其他节点上)

2021-08-18T12:48:30.693676Z 0 [Warning] Plugin group_replication reported: 'Members removed from the group: rac1:3306'
2021-08-18T12:48:30.693733Z 0 [Note] Plugin group_replication reported: 'Primary server with address rac1:3306 left the group. Electing new Primary.'
2021-08-18T12:48:30.693851Z 0 [Note] Plugin group_replication reported: 'A new primary with address rac2:3306 was elected, enabling conflict detection until the new primary applies all relay logs.'
2021-08-18T12:48:30.693922Z 17 [Note] Plugin group_replication reported: 'This server is working as secondary member with primary member address rac2:3306.'
2021-08-18T12:48:30.693996Z 0 [Note] Plugin group_replication reported: 'Group membership changed to rac2:3306, rac3:3306 on view 16292786917612147:4.'
#####group replication检测到rac1异常，并在组内移除了rac1服务器。rac2节点被选举成了新的主节点

#####检查当前主库
mysql> 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 | 789c2972-ff04-11eb-b4e1-0050568fa18f | rac2        |        3306 | ONLINE       |
+----------------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

#####在新的主节点，验证数据写入
mysql> insert into db.mm values('a6');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into db.mm(name) values('a6');
Query OK, 1 row affected (0.19 sec)

mysql> select * from db.mm where name='a6';
+----+------+
| id | name |
+----+------+
| 58 | a6   |
+----+------+
1 row in set (0.00 sec)

mysql>