MySQL-MGR单主模式高可用选主测试
前言:
本文主要介绍MGR单主模式下高可用的测试情况，主要测试主节点故障，两节点同时故障(一主一备)，两节点同时故障(两个备节点)。
单主MGR环境:
| 主节点一 | 192.168.2.240:3306 |
| 备节点二 | 192.168.2.241:3306 |
| 备节点三 | 192.168.2.242:3306 |
测试结果:
| 测试场景 | 自动选主 | 新主能否读写 | 数据一致性 |
| 主节点故障 | ✔ | ✔ | ✔ |
| 两节点同时故障(一主一备) | ✔ | ✔ | ✔ |
| 两节点同时故障(两个备节点) | ✔ | ✔ | ✔ |
单主选主算法：
自动选主过程每个成员都会查看组的新视图，对潜在的组成员排序优先级，选择出最合适的组成员，每个组成员会根据自身MySQL版本的选组算法做出自己的决定，因为全部的组成员需要做出一致的决定，所以如果组成员中有MySQL较低版本的成员，则成员需要会调整选主的算法，以达到与同组较低版本的成员具有相同的准则。
主成员选举有以下需要考虑的因素
1 第一个考虑的因素是组或者成员中运行的最低MySQL版本，如果全部成员都运行在MySQL8.0.17及以上，则按补丁号进行排序，如果有其中一个成员跑在5.7 or 8.0.16及以下，则按大版本排序，忽略补丁版本。
2 如果超过一个成员运行在MySQL低版本，则第二个考虑的因素为成员在组里面的权重,系统变量group_replication_member_weight所识别的大小，如果存在一个成员运行在MySQL 5.7版本的，则变量是无效的，需要忽略该因素。
3 如果超过一个成员运行在MySQL低版本并且其中一个以上的成员具有最高的成员权重（或成员权重被忽略），则考虑第三个因素每个成员产生的UUID的字段顺序，最低顺序的uuid成员将被选为主节点，这个因素是一个有保证并且适用于每个MySQL版本以确保在任何因素都无法确定的情况下，所有小组成员都能做出相同的决定。
模拟数据插入:
---创建测试表create table test(id bigint primary key auto_increment,name varchar(200),name1 varchar(200));---插入数据vi insert_mgr.sh#!/bin/bashwhile true do mysql -utest -ptest -h'192.168.2.240' -P3306 -e "insert into test.test(name,name1) values ('aaaaaaaaaaaa',sysdate())"done---执行脚本sh insert_mgr.sh主节点故障:
直接重启主节点一:
备节点二被选举为主节点:
root@mysql.sock 17:00: [(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 | e37983d6-d9b2-11ec-8a0a-000c29a6e7be | 192.168.2.241 | 3306 | ONLINE| PRIMARY | 8.0.22 || group_replication_applier | e3d12004-d9b2-11ec-a818-000c29d9d34c | 192.168.2.242 | 3306 | ONLINE| SECONDARY | 8.0.22 |+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+2 rows in set (0.01 sec)root@mysql.sock 17:01: [(none)]>节点二日志:
---节点二被选举主节点更新MGR view,并应用未完成的日志，再日志未应用之前，数据库无法读写，应用完日志后，关闭只读模式2022-05-29T17:00:12.276943+08:00 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: 192.168.2.240:3306'2022-05-29T17:00:12.277017+08:00 0 [System] [MY-011500] [Repl] Plugin group_replication reported: 'Primary server with address 192.168.2.240:3306 left the group. Electing new Primary.'2022-05-29T17:00:13.278644+08:00 0 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address 192.168.2.241:3306 was elected. The new primary will execute all previous group transactions before allowing writes.'2022-05-29T17:00:13.279135+08:00 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 192.168.2.241:3306, 192.168.2.242:3306 on view 16538106505267952:4.'2022-05-29T17:00:13.282147+08:00 32 [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'2022-05-29T17:00:13.285170+08:00 32 [System] [MY-011510] [Repl] Plugin group_replication reported: 'This server is working as primary member.'节点三日志:
---节点三被告知节点二已经为主节点，后续从主节点同步2022-05-29T17:00:12.086223+08:00 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: 192.168.2.240:3306'2022-05-29T17:00:12.086300+08:00 0 [System] [MY-011500] [Repl] Plugin group_replication reported: 'Primary server with address 192.168.2.240:3306 left the group. Electing new Primary.'2022-05-29T17:00:12.086445+08:00 0 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address 192.168.2.241:3306 was elected. The new primary will execute all previous group transactions before allowing writes.'2022-05-29T17:00:12.086929+08:00 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 192.168.2.241:3306, 192.168.2.242:3306 on view 16538106505267952:4.'2022-05-29T17:00:13.091704+08:00 31 [System] [MY-011511] [Repl] Plugin group_replication reported: 'This server is working as secondary member with primary member address 192.168.2.241:3306.'恢复节点一，重新加入MGR集群:
---执行start group_replication重新加入集群root@mysql.sock 17:06: [(none)]>START GROUP_REPLICATION;Query OK, 0 rows affected (3.38 sec)---加入集群会先进行recover，直到状态变为onlineroot@mysql.sock 17:06: [(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 | b87decaf-d9b2-11ec-91e6-000c29ed797a | 192.168.2.240 | 3306 | RECOVERING | SECONDARY | 8.0.22 || group_replication_applier | e37983d6-d9b2-11ec-8a0a-000c29a6e7be | 192.168.2.241 | 3306 | ONLINE| PRIMARY | 8.0.22 || group_replication_applier | e3d12004-d9b2-11ec-a818-000c29d9d34c | 192.168.2.242 | 3306 | ONLINE| SECONDARY | 8.0.22 |+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+3 rows in set (0.00 sec)root@mysql.sock 17:06: [(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 | b87decaf-d9b2-11ec-91e6-000c29ed797a | 192.168.2.240 | 3306 | ONLINE| SECONDARY | 8.0.22 || group_replication_applier | e37983d6-d9b2-11ec-8a0a-000c29a6e7be | 192.168.2.241 | 3306 | ONLINE| PRIMARY | 8.0.22 || group_replication_applier | e3d12004-d9b2-11ec-a818-000c29d9d34c | 192.168.2.242 | 3306 | ONLINE| SECONDARY | 8.0.22 |+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+3 rows in set (0.00 sec)节点一日志:
---重新加入集群，会从集群里面寻找一个节点(donor)进行增量恢复，并更新mgr view2022-05-29T17:06:29.101616+08:00 9 [System] [MY-013587] [Repl] Plugin group_replication reported: 'Plugin 'group_replication' is starting.'2022-05-29T17:06:29.103153+08:00 10 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'2022-05-29T17:06:29.105877+08:00 9 [Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the allowlist. It is mandatory that it is added.'2022-05-29T17:06:29.105996+08:00 9 [Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Automatically adding IPv6 localhost address to the allowlist. It is mandatory that it is added.'2022-05-29T17:06:29.107027+08:00 11 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.2022-05-29T17:06:31.478558+08:00 9 [System] [MY-011511] [Repl] Plugin group_replication reported: 'This server is working as secondary member with primary member address 192.168.2.241:3306.'2022-05-29T17:06:32.479244+08:00 0 [System] [MY-013471] [Repl] Plugin group_replication reported: 'Distributed recovery will transfer data using: Incremental recovery from a group donor'2022-05-29T17:06:32.479928+08:00 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 192.168.2.240:3306, 192.168.2.241:3306, 192.168.2.242:3306 on view 16538106505267952:5.'2022-05-29T17:06:32.494109+08:00 22 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='192.168.2.241', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.2022-05-29T17:06:32.505179+08:00 23 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.2022-05-29T17:06:32.508370+08:00 23 [System] [MY-010562] [Repl] Slave I/O thread for channel 'group_replication_recovery': connected to master 'rpl_user@192.168.2.241:3306',replication started in log 'FIRST' at position 42022-05-29T17:06:32.681251+08:00 25 [Warning] [MY-010956] [Server] Invalid replication timestamps: original commit timestamp is more recent than the immediate commit timestamp. This may be an issue if delayed replication is active. Make sure that servers have their clocks set to the correct time. No further message will be emitted until after timestamps become valid again.2022-05-29T17:06:34.683966+08:00 25 [Warning] [MY-010957] [Server] The replication timestamps have returned to normal values.2022-05-29T17:06:34.712612+08:00 22 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='192.168.2.241', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.2022-05-29T17:06:34.734170+08:00 15 [Warning] [MY-010956] [Server] Invalid replication timestamps: original commit timestamp is more recent than the immediate commit timestamp. This may be an issue if delayed replication is active. Make sure that servers have their clocks set to the correct time. No further message will be emitted until after timestamps become valid again.2022-05-29T17:06:35.354235+08:00 0 [System] [MY-011490] [Repl] Plugin group_replication reported: 'This server was declared online within the replication group.'主备数据一致:
---主节点二root@mysql.sock 17:15: [(none)]>show global variables like '%report_host%';+---------------+---------------+| Variable_name | Value |+---------------+---------------+| report_host | 192.168.2.241 |+---------------+---------------+1 row in set (0.00 sec)root@mysql.sock 17:16: [(none)]>select count(*),max(name1) from test.test;+----------+---------------------+| count(*) | max(name1) |+----------+---------------------+| 6919 | 2022-05-29 17:07:47 |+----------+---------------------+1 row in set (0.01 sec)---备节点一root@mysql.sock 17:06: [(none)]>show global variables like '%report_host%';+---------------+---------------+| Variable_name | Value |+---------------+---------------+| report_host | 192.168.2.240 |+---------------+---------------+1 row in set (0.02 sec)root@mysql.sock 17:14: [(none)]>select count(*),max(name1) from test.test;+----------+---------------------+| count(*) | max(name1) |+----------+---------------------+| 6919 | 2022-05-29 17:07:47 |+----------+---------------------+1 row in set (0.00 sec)root@mysql.sock 17:14: [(none)]>两节点同时故障(一主一备):
节点二(主)，节点一(备)先后reboot:
备节点三被选为主节点:
---MGR集群只剩一个主节点三root@mysql.sock 17:29: [(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 | e3d12004-d9b2-11ec-a818-000c29d9d34c | 192.168.2.242 | 3306 | ONLINE| PRIMARY | 8.0.22 |+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+1 row in set (0.00 sec)root@mysql.sock 17:29: [(none)]>节点三日志：
---节点二离开集群，节点一被选择为集群2022-05-29T17:26:53.225312+08:00 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: 192.168.2.241:3306'2022-05-29T17:26:53.225354+08:00 0 [System] [MY-011500] [Repl] Plugin group_replication reported: 'Primary server with address 192.168.2.241:3306 left the group. Electing new Primary.'2022-05-29T17:26:53.225414+08:00 0 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address 192.168.2.240:3306 was elected. The new primary will execute all previous group transactions before allowing writes.'2022-05-29T17:26:53.225564+08:00 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 192.168.2.240:3306, 192.168.2.242:3306 on view 16538106505267952:6.'2022-05-29T17:26:54.229251+08:00 35 [System] [MY-011511] [Repl] Plugin group_replication reported: 'This server is working as secondary member with primary member address 192.168.2.240:3306.'---节点一离开集群，节点三被选择为集群，并关闭只读2022-05-29T17:26:56.222488+08:00 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: 192.168.2.240:3306'2022-05-29T17:26:56.222536+08:00 0 [System] [MY-011500] [Repl] Plugin group_replication reported: 'Primary server with address 192.168.2.240:3306 left the group. Electing new Primary.'2022-05-29T17:26:57.224466+08:00 0 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address 192.168.2.242:3306 was elected. The new primary will execute all previous group transactions before allowing writes.'2022-05-29T17:26:57.225044+08:00 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 192.168.2.242:3306 on view 16538106505267952:7.'2022-05-29T17:26:57.225483+08:00 39 [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'2022-05-29T17:26:57.226159+08:00 39 [System] [MY-011510] [Repl] Plugin group_replication reported: 'This server is working as primary member.'MGR集群现在只有一个节点，但节点依然可以提供服务：
[root@mha ~]# mysql -utest -ptest -h'192.168.2.242' -P3306 -e "insert into test.test(name,name1) values ('aaaaaaaaaaaa',sysdate())"mysql: [Warning] Using a password on the command line interface can be insecure.[root@mha ~]#恢复节点一，二重新加入集群：
---执行start group_replication重新加入集群root@mysql.sock 17:06: [(none)]>START GROUP_REPLICATION;root@mysql.sock 17:36: [(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 | b87decaf-d9b2-11ec-91e6-000c29ed797a | 192.168.2.240 | 3306 | ONLINE| SECONDARY | 8.0.22 || group_replication_applier | e37983d6-d9b2-11ec-8a0a-000c29a6e7be | 192.168.2.241 | 3306 | ONLINE| SECONDARY | 8.0.22 || group_replication_applier | e3d12004-d9b2-11ec-a818-000c29d9d34c | 192.168.2.242 | 3306 | ONLINE| PRIMARY | 8.0.22 |+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+3 rows in set (0.00 sec)root@mysql.sock 17:37: [(none)]>主备数据一致:
---主节点三root@mysql.sock 17:39: [test]>show global variables like '%report_host%';+---------------+---------------+| Variable_name | Value |+---------------+---------------+| report_host | 192.168.2.242 |+---------------+---------------+1 row in set (0.01 sec)root@mysql.sock 17:39: [test]>select count(*),max(name1) from test.test;+----------+---------------------+| count(*) | max(name1) |+----------+---------------------+| 8305 | 2022-05-29 17:36:35 |+----------+---------------------+1 row in set (0.01 sec)root@mysql.sock 17:39: [test]>---备节点一root@mysql.sock 17:37: [(none)]>show global variables like '%report_host%';+---------------+---------------+| Variable_name | Value |+---------------+---------------+| report_host | 192.168.2.240 |+---------------+---------------+1 row in set (0.00 sec)root@mysql.sock 17:37: [(none)]>select count(*),max(name1) from test.test;+----------+---------------------+| count(*) | max(name1) |+----------+---------------------+| 8305 | 2022-05-29 17:36:35 |+----------+---------------------+1 row in set (0.01 sec)root@mysql.sock 17:38: [(none)]>两节点同时故障(二个备节点):
节点二(备)，节点三(备)先后reboot:
主节点一不受影响，写入不中断:
root@mysql.sock 17:56: [(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 | b87decaf-d9b2-11ec-91e6-000c29ed797a | 192.168.2.240 | 3306 | ONLINE| PRIMARY | 8.0.22 |+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+1 row in set (0.00 sec)
节点一日志:
节点二，节点三先后离开集群，mgr view完成更新2022-05-29T17:56:45.261683+08:00 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: 192.168.2.241:3306'2022-05-29T17:56:45.261804+08:00 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 192.168.2.240:3306, 192.168.2.242:3306 on view 16538106505267952:14.'2022-05-29T17:56:48.259506+08:00 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: 192.168.2.242:3306'2022-05-29T17:56:48.259618+08:00 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 192.168.2.240:3306 on view 16538106505267952:15.'恢复节点二，三，重新加入MGR集群，恢复同步:
2022-05-29T18:00:06.402073+08:00 0 [System] [MY-011492] [Repl] Plugin group_replication reported: 'The member with address 192.168.2.241:3306 was declared online within the replication group.'2022-05-29T18:00:17.089333+08:00 0 [System] [MY-011492] [Repl] Plugin group_replication reported: 'The member with address 192.168.2.242:3306 was declared online within the replication group.'root@mysql.sock 17:59: [(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 | b87decaf-d9b2-11ec-91e6-000c29ed797a | 192.168.2.240 | 3306 | ONLINE| PRIMARY | 8.0.22 || group_replication_applier | e37983d6-d9b2-11ec-8a0a-000c29a6e7be | 192.168.2.241 | 3306 | ONLINE| SECONDARY | 8.0.22 || group_replication_applier | e3d12004-d9b2-11ec-a818-000c29d9d34c | 192.168.2.242 | 3306 | ONLINE| SECONDARY | 8.0.22 |+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+主备数据一致:
---主节点root@mysql.sock 18:00: [(none)]>show global variables like '%report_host%';+---------------+---------------+| Variable_name | Value |+---------------+---------------+| report_host | 192.168.2.240 |+---------------+---------------+1 row in set (0.01 sec)root@mysql.sock 18:01: [(none)]>select count(*),max(name1) from test.test;+----------+---------------------+| count(*) | max(name1) |+----------+---------------------+| 30766 | 2022-05-29 18:00:47 |+----------+---------------------+1 row in set (0.03 sec)root@mysql.sock 18:01: [(none)]>---备节点root@mysql.sock 17:59: [(none)]>show global variables like '%report_host%';+---------------+---------------+| Variable_name | Value |+---------------+---------------+| report_host | 192.168.2.241 |+---------------+---------------+1 row in set (0.01 sec)root@mysql.sock 18:02: [(none)]>select count(*),max(name1) from test.test;+----------+---------------------+| count(*) | max(name1) |+----------+---------------------+| 30766 | 2022-05-29 18:00:47 |+----------+---------------------+1 row in set (0.02 sec)