原 【DB宝18】在Docker中安装使用MySQL高可用之MGR
👉 本文共约1452个字，系统预计阅读时间或需6分钟。
目录
六、启动MGR单主模式
6.1、启动MGR，在主库(172.72.0.15)上执行
1 2 3 4 5 6 7 | SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF; -- 查看MGR组信息 SELECT * FROM performance_schema.replication_group_members; |
执行过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | MySQL [(none)]> SET GLOBAL group_replication_bootstrap_group=ON; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> START GROUP_REPLICATION; Query OK, 0 rows affected (3.49 sec) MySQL [(none)]> SET GLOBAL group_replication_bootstrap_group=OFF; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 | 3306 | ONLINE | PRIMARY | 8.0.20 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 1 row in set (0.01 sec) |
6.2、其他节点加入MGR，在从库（172.72.0.16，172.72.0.17）上执行
1 2 3 | START GROUP_REPLICATION; -- 查看MGR组信息 SELECT * FROM performance_schema.replication_group_members; |
执行结果：
1 2 3 4 5 6 7 8 9 | MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 | 3306 | ONLINE | PRIMARY | 8.0.20 | | group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 | 3306 | ONLINE | SECONDARY | 8.0.20 | | group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 | 3306 | ONLINE | SECONDARY | 8.0.20 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.01 sec) |
可以看到，3个节点状态为online，并且主节点为172.72.0.15，只有主节点可以写入，其他节点只读，MGR单主模式搭建成功。
七、多主和单主模式切换
7.1、查询当前模式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | MySQL [(none)]> show variables like '%group_replication_single_primary_mode%'; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | group_replication_single_primary_mode | ON | +---------------------------------------+-------+ 1 row in set (0.01 sec) MySQL [(none)]> SELECT @@group_replication_single_primary_mode; +-----------------------------------------+ | @@group_replication_single_primary_mode | +-----------------------------------------+ | 1 | +-----------------------------------------+ 1 row in set (0.00 sec) |
参数group_replication_single_primary_mode为ON，表示单主模式。
7.2、函数实现多主和单主切换
函数切换：从MySQL 8.0.13开始，可以使用函数进行在线修改MGR模式。
1 2 3 4 5 6 7 | -- 单主切多主 select group_replication_switch_to_multi_primary_mode(); -- 多主切单主，入参需要传入主库的server_uuid select group_replication_switch_to_single_primary_mode('@@server_uuid') ; -- 查看组信息 SELECT * FROM performance_schema.replication_group_members; |
7.2.1、单主切多主模式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | MySQL [(none)]> SELECT @@group_replication_single_primary_mode; +-----------------------------------------+ | @@group_replication_single_primary_mode | +-----------------------------------------+ | 1 | +-----------------------------------------+ 1 row in set (0.00 sec) MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 | 3306 | ONLINE | PRIMARY | 8.0.20 | | group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 | 3306 | ONLINE | SECONDARY | 8.0.20 | | group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 | 3306 | ONLINE | SECONDARY | 8.0.20 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec) MySQL [(none)]> select group_replication_switch_to_multi_primary_mode(); +--------------------------------------------------+ | group_replication_switch_to_multi_primary_mode() | +--------------------------------------------------+ | Mode switched to multi-primary successfully. | +--------------------------------------------------+ 1 row in set (1.01 sec) MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 | 3306 | ONLINE | PRIMARY | 8.0.20 | | group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 | 3306 | ONLINE | PRIMARY | 8.0.20 | | group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 | 3306 | ONLINE | PRIMARY | 8.0.20 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec) MySQL [(none)]> SELECT @@group_replication_single_primary_mode; +-----------------------------------------+ | @@group_replication_single_primary_mode | +-----------------------------------------+ | 0 | +-----------------------------------------+ |
7.2.2、多主切单主模式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | MySQL [(none)]> SELECT @@group_replication_single_primary_mode; +-----------------------------------------+ | @@group_replication_single_primary_mode | +-----------------------------------------+ | 0 | +-----------------------------------------+ 1 row in set (0.00 sec) MySQL [(none)]> select group_replication_switch_to_single_primary_mode('67090f47-d785-11ea-b76c-0242ac480010') ; +-----------------------------------------------------------------------------------------+ | group_replication_switch_to_single_primary_mode('67090f47-d785-11ea-b76c-0242ac480010') | +-----------------------------------------------------------------------------------------+ | Mode switched to single-primary successfully. | +-----------------------------------------------------------------------------------------+ 1 row in set (1.02 sec) MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 | 3306 | ONLINE | SECONDARY | 8.0.20 | | group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 | 3306 | ONLINE | PRIMARY | 8.0.20 | | group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 | 3306 | ONLINE | SECONDARY | 8.0.20 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec) MySQL [(none)]> SELECT @@group_replication_single_primary_mode; +-----------------------------------------+ | @@group_replication_single_primary_mode | +-----------------------------------------+ | 1 | +-----------------------------------------+ 1 row in set (0.00 sec) |
7.3、手动切换
MGR切换模式需要重新启动组复制，因些需要在所有节点上先关闭组复制，设置 group_replication_single_primary_mode=OFF 等参数，再启动组复制。
7.3.1、单主切多主模式
1、停止组复制(所有节点执行)：
1 2 3 | stop group_replication; set global group_replication_single_primary_mode=OFF; set global group_replication_enforce_update_everywhere_checks=ON; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
牛掰
你抢我名字啊