MGR多主(Multi-Primary)模式和单主(Single-Primary)模式切换
一、单主切换到多主
1、对每一个 MySQL 节点配置如下参数,并写入 my.cnf 配置文件
stop group_replication;
#关闭单主模式的参数
set global group_replication_single_primary_mode=off;
#开启多主模式的参数
set global group_replication_enforce_update_everywhere_checks=on;
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=on
2、在其中一个节点,打开 group_replication_bootstrap_group 为 on,并开启组复制,成功后再关闭
set global group_replication_bootstrap_group=on;
start group_replication;
set global group_replication_bootstrap_group=off;
3、另外两个节点逐一开启组复制,加入集群环境,并通过视图查看集群情况
mysql> start group_replication;
Query OK, 0 rows affected (5.78 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 839d3796-a8a1-11ea-b1db-00505636b68c | ip136 | 3306 | ONLINE |
| group_replication_applier | e741120b-a7e9-11ea-9af7-0050563bbd20 | ip135 | 3306 | ONLINE |
| group_replication_applier | f44ecd83-a8d1-11ea-ad97-0050562512fc | ip137 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> show global variables like '%read_only%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF |
| super_read_only | OFF |
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+
5 rows in set (0.00 sec)
二、多主切换到单主
1、停止组复制
stop group_replication;
set global group_replication_enforce_update_everywhere_checks=off;
set global group_replication_single_primary_mode=on;
2、选择一个节点,打开 group_replication_bootstrap_group 为 on,并开启组复制,成功后再关闭
set global group_replication_bootstrap_group=on;
start group_replication;
set global group_replication_bootstrap_group=off;
3、另外两个节点逐一开启组复制,加入集群环境,并通过视图查看集群情况
start group_replication;
select * from performance_schema.replication_group_members;
#查看 Primary 成员
select *from performance_schema.replication_group_members where member_id =(select variable_value from performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member');