[TOC]

MGR简介

​ 基于传统异步复制和半同步复制的缺陷——数据的一致性问题无法保证，MySQL官方在5.7.17版本正式推出组复制（MySQL Group Replication，简称MGR），以插件形式提供，实现了分布式下数据的最终一致性。

MGR特点如下：
1）高一致性，基于原生复制及paxos协议的组复制技术，并以插件的方式提供，提供一致数据安全保证；
2）高容错性，只要不是大多数节点坏掉就可以继续工作，有自动检测机制，当不同节点产生资源争用冲突时，不会出现错误，按照先到者优先原则进行处 理，并且内置了自动化脑裂防护机制；
3）高扩展性，节点的新增和移除都是自动的，新节点加入后，会自动从其他节点上同步状态，直到新节点和其他节点保持一致，如果某节点被移除了，其他节点自动更新组信息，自动维护新的组信息；
4）高灵活性，有单主模式和多主模式，在同一个group内，不允许两种模式同时存在。在单主模式下，主节点宕机时，会自动重新选择新的master，当旧的master恢复加入后，新master不会发生改变。所有更新操作都在主节点上进行；在多主模式下，所有的server成员都可以同时接受更新。
5) 由若干个节点共同组成一个复制组，一个事务的提交，必须经过组内大多数节点（N/2+1）决议并通过，才能得以提交。

MGR主要限制条件

• 仅支持InnoDB表，并且每张表一定要有一个主键,或者非Null+唯一键

• 必须打开GTID特性，二进制日志格式必须设置为ROW

• 不支持复制过滤，如果有节点设置了复制过滤，将影响节点间决议的达成。

• 不支持超大事务。

• MGR忽略表锁和命名锁，在MGR中lock tables. unlock tables. get_lock. release_lock等这些表锁和命名锁将被忽略。

• 多主模式中，默认不支持Serializable隔离级别。

• 多主模式下，对同一个对象进行并发的有冲突的ddl和dml操作导致这种冲突在部分成员节点中无法检测到，最终可能导致数据不一致。

• 多主模式下可能导致死锁，比如select…for update在不同节点执行，由于多节点锁无法共享，很容易导致死锁。

其他等限制参考官方文档：

MySQL :: MySQL 8.0 Reference Manual :: 18.3.1 Group Replication Requirements

一、创建3台MySQL环境

1.1 申请3台linux主机

1.2. 安装mysql

安装过程过程省略。。。

-- 3台版本都是8.0.30
mysql> select version(); 
+-----------+
| version() |
+-----------+
| 8.0.30    |
+-----------+
1 row in set (0.01 sec)
复制

二、修改MySQL参数

-- mgr1
cat > /etc/my.cnf <<"EOF"
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 80301
default-time-zone = '+8:00'
log_timestamps = SYSTEM
log-bin = 
binlog_format=row
binlog_checksum=NONE
skip-name-resolve
log_slave_updates = 1
gtid-mode=ON
enforce-gtid-consistency=on
default_authentication_plugin=mysql_native_password
max_allowed_packet = 500M
lower_case_table_names = 1
expire_logs_days= 60
max_connections = 2000
innodb_buffer_pool_size=1024M
slow_query_log = ON
slow_query_log_file=/usr/local/mysql/data/mysql-slow.log

#安装mysql_replication引擎前提
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=lhrmgr31-relay-bin

#同步算法，官网建议设置该参数使用 XXHASH64 算法
transaction_write_set_extraction=XXHASH64
#集群的uuid，组名必须是一个uuid
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#是否随着服务启动集群，这里设置否 
loose-group_replication_start_on_boot=OFF
#集群本机端口，和服务端口不同，以本机端口 33061 接受来自组中成员的传入连接
loose-group_replication_local_address= "172.72.0.11:33061"
#集群包含的所有节点
loose-group_replication_group_seeds= "172.72.0.11:33061,172.72.0.12:33061,172.72.0.13:33061"
#是否设置为主节点，当创建集群时其他加入的节点都以该节点为目标加入集群
loose-group_replication_bootstrap_group=OFF
#设置白名单
loose-group_replication_ip_whitelist="172.72.0.11,172.72.0.12,172.72.0.13"

report_host=172.72.0.11
report_port=3306
EOF


-- mgr2
cat >  /etc/my.cnf <<"EOF"
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 80302
default-time-zone = '+8:00'
log_timestamps = SYSTEM
log-bin = 
binlog_format=row
binlog_checksum=NONE
log_slave_updates = 1
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
default_authentication_plugin=mysql_native_password
max_allowed_packet = 500M
lower_case_table_names = 1
expire_logs_days= 60
max_connections = 2000
innodb_buffer_pool_size=1024M
slow_query_log = ON
slow_query_log_file=/usr/local/mysql/data/mysql-slow.log

master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=lhrmgr32-relay-bin

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "172.72.0.12:33061"
loose-group_replication_group_seeds= "172.72.0.11:33061,172.72.0.12:33061,172.72.0.13:33061"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_ip_whitelist="172.72.0.11,172.72.0.12,172.72.0.13"

report_host=172.72.0.12
report_port=3306
EOF

-- mgr3
cat > /etc/my.cnf <<"EOF"
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 80303
default-time-zone = '+8:00'
log_timestamps = SYSTEM
log-bin = 
binlog_format=row
binlog_checksum=NONE
log_slave_updates = 1
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
default_authentication_plugin=mysql_native_password
max_allowed_packet = 500M
lower_case_table_names = 1
expire_logs_days= 60
max_connections = 2000
innodb_buffer_pool_size=1024M
slow_query_log = ON
slow_query_log_file=/usr/local/mysql/data/mysql-slow.log

master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=lhrmgr32-relay-bin

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "172.72.0.13:33061"
loose-group_replication_group_seeds= "172.72.0.11:33061,172.72.0.12:33061,172.72.0.13:33061"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_ip_whitelist="172.72.0.11,172.72.0.12,172.72.0.13"

report_host=172.72.0.13
report_port=3306
EOF
复制

三、重启MySQL环境

-- 重启MySQL
[root@mgr1 ~]# systemctl restart mysqld
[root@mgr2 ~]# systemctl restart mysqld
[root@mgr3 ~]# systemctl restart mysqld


-- 查看MySQL的主机名、server_id和server_uuid
[root@mgr1 ~]# mysql -uroot -proot123 -e "select @@hostname,@@server_id,@@server_uuid"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid                        |
+------------+-------------+--------------------------------------+
| mgr1       |       80301 | 63f351cc-cdf1-11ed-a386-0242ac48000b |
+------------+-------------+--------------------------------------+

[root@mgr2 /]# mysql -uroot -proot123 -e "select @@hostname,@@server_id,@@server_uuid"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid                        |
+------------+-------------+--------------------------------------+
| mgr2       |       80302 | 3401b339-cdf1-11ed-9b3f-0242ac48000c |
+------------+-------------+--------------------------------------+

[root@mgr3 /]# mysql -uroot -proot123 -e "select @@hostname,@@server_id,@@server_uuid"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid                        |
+------------+-------------+--------------------------------------+
| mgr3       |       80303 | 32b2fe4a-cdf1-11ed-989c-0242ac48000d |
+------------+-------------+--------------------------------------+
复制

四、安装MGR插件（所有节点执行）

mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.08 sec

MySQL [(none)]> show plugins;
+---------------------------------+----------+--------------------+----------------------+---------+
| Name                            | Status   | Type               | Library              | License |
+---------------------------------+----------+--------------------+----------------------+---------+
...................................                           
| group_replication               | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+---------------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.01 sec)
复制

五、设置复制账号（所有节点执行）

-- 创建账号不记录binlog
mysql> SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER repl@'%' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.04 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

-- 从8.0.23开始，使用如下命令
mysql> change replication source to source_user='repl', source_password='repl' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
复制

六、启动MGR单主模式

6.1 启动MGR

-- 只在主库(172.72.0.11)上执行
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, 1 warning (1.16 sec)

mysql> set global group_replication_bootstrap_group=off;
Query OK, 0 rows affected (0.02 sec)

-- 查看mgr组信息 
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 63f351cc-cdf1-11ed-a386-0242ac48000b | 172.72.0.11 |        3306 | ONLINE       | PRIMARY     | 8.0.30         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+-------
1 row in set (0.03 sec)
复制

6.2 其他节点加入MGR

-- 在从库（172.72.0.12，172.72.0.13）上执行
mysql> start group_replication;
Query OK, 0 rows affected, 1 warning (4.01 sec)

-- 再次查看MGR组信息
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 32b2fe4a-cdf1-11ed-989c-0242ac48000d | 172.72.0.13 |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
| group_replication_applier | 3401b339-cdf1-11ed-9b3f-0242ac48000c | 172.72.0.12 |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
| group_replication_applier | 63f351cc-cdf1-11ed-a386-0242ac48000b | 172.72.0.11 |        3306 | ONLINE       | PRIMARY     | 8.0.30         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+-------
3 rows in set (0.00 sec)
复制

结论：可以看到，3个节点状态为online，172.72.0.11为PRIMARY主节点，只有主节点可以写入，其他节点只读，MGR单主模式搭建成功，单主也是默认模式。

– 其他SECONDARY节点，执行写操作报错：

mysql> create database test;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
复制

七、测试同步

在主节点上创建测试数据，然后在其它节点查询：

-- 创建测试库
mysql> create database testdb;
Query OK, 1 row affected (0.09 sec)
-- 创建测试表，并插入测试数据
mysql> CREATE TABLE testdb.`t1` (
    ->  `id` int(11) NOT NULL AUTO_INCREMENT,
    ->  `name` varchar(100) DEFAULT NULL,
    ->  PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.12 sec)

mysql> insert into testdb.t1 values (1,'tony');
Query OK, 1 row affected (0.09 sec)

mysql> insert into testdb.t1 values (2,'lion');
Query OK, 1 row affected (0.12 sec)

mysql> insert into testdb.t1 values (3,'jack');
Query OK, 1 row affected (0.05 sec)

mysql> select * from testdb.t1;
+----+------+
| id | name |
+----+------+
|  1 | tony |
|  2 | lion |
|  3 | jack |
+----+------+
3 rows in set (0.01 sec)

-- 其他2个节点查询出来的结果一样
mysql> select * from testdb.t1;
+----+------+
| id | name |
+----+------+
|  1 | tony |
|  2 | lion |
|  3 | jack |
+----+------+
3 rows in set (0.04 sec)
复制

八、多主和单主模式切换

7.1 查询当前模式

mysql> show variables like '%group_replication_single_primary_mode%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | ON    |
+---------------------------------------+-------+
1 row in set (0.08 sec)

mysql> 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模式，不需要重启组复制。

-- 单主切多主
select group_replication_switch_to_multi_primary_mode(); 

-- 多主切单主，入参需要传入主库的server_uuid
select group_replication_switch_to_single_primary_mode('@@server_uuid') ;
复制

7.2.1 单主切多主模式

mysql> select @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (0.01 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 32b2fe4a-cdf1-11ed-989c-0242ac48000d | 172.72.0.13 |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
| group_replication_applier | 3401b339-cdf1-11ed-9b3f-0242ac48000c | 172.72.0.12 |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
| group_replication_applier | 63f351cc-cdf1-11ed-a386-0242ac48000b | 172.72.0.11 |        3306 | ONLINE       | PRIMARY     | 8.0.30         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+-------
3 rows in set (0.01 sec)
-- 单主切多主
mysql>  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.38 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 32b2fe4a-cdf1-11ed-989c-0242ac48000d | 172.72.0.13 |        3306 | ONLINE       | PRIMARY     | 8.0.30         | XCom                       |
| group_replication_applier | 3401b339-cdf1-11ed-9b3f-0242ac48000c | 172.72.0.12 |        3306 | ONLINE       | PRIMARY     | 8.0.30         | XCom                       |
| group_replication_applier | 63f351cc-cdf1-11ed-a386-0242ac48000b | 172.72.0.11 |        3306 | ONLINE       | PRIMARY     | 8.0.30         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+-------
3 rows in set (0.03 sec)

mysql> select @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
1 row in set (0.02 sec)
复制

结论：所有成员角色都变为PRIMARY主节点，成功切换成多主模式。 

7.2.2 多主切单主模式

-- 重新切换成单主模式，主节点选择mgr1
mysql> select @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
1 row in set (0.02 sec)

mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 63f351cc-cdf1-11ed-a386-0242ac48000b |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select group_replication_switch_to_single_primary_mode('63f351cc-cdf1-11ed-a386-0242ac48000b') ;
+-----------------------------------------------------------------------------------------+
| group_replication_switch_to_single_primary_mode('63f351cc-cdf1-11ed-a386-0242ac48000b') |
+-----------------------------------------------------------------------------------------+
| Mode switched to single-primary successfully.                                           |
+-----------------------------------------------------------------------------------------+
1 row in set (0.59 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 32b2fe4a-cdf1-11ed-989c-0242ac48000d | 172.72.0.13 |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
| group_replication_applier | 3401b339-cdf1-11ed-9b3f-0242ac48000c | 172.72.0.12 |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
| group_replication_applier | 63f351cc-cdf1-11ed-a386-0242ac48000b | 172.72.0.11 |        3306 | ONLINE       | PRIMARY     | 8.0.30         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.01 sec)

mysql> select @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (0.00 sec)
复制

结论：成功切换成单主模式，主节点选择mgr1。

九 模拟主节点宕机

– 如果是其中一个SECONDARY宕机，则不影响集群运行，这里模拟主节点宕机。

9.1 查询当前MGR成员状态

mysql> select @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 32b2fe4a-cdf1-11ed-989c-0242ac48000d | 172.72.0.13 |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
| group_replication_applier | 3401b339-cdf1-11ed-9b3f-0242ac48000c | 172.72.0.12 |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
| group_replication_applier | 63f351cc-cdf1-11ed-a386-0242ac48000b | 172.72.0.11 |        3306 | ONLINE       | PRIMARY     | 8.0.30         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+-------
3 rows in set (0.01 sec)
复制

结论：当前为单主模式，所有成员都是online，主节点为mgr1。

9.2 模拟主节点宕机

[root@mgr1 ~]# ps -ef|grep mysqld
root      3023     1  0 13:41 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/mgr1.pid
mysql     3449  3023  0 13:41 ?        00:00:26 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=mgr1.err --pid-file=/usr/local/mysql/data/mgr1.pid --port=3306
root      6400   594  0 14:39 pts/0    00:00:00 grep --color=auto mysqld

[root@mgr1 ~]# kill -9 3023 3449
[root@mgr1 ~]# ps -ef|grep mysqld
root      6431   594  0 14:40 pts/0    00:00:00 grep --color=auto mysqld
复制

9.3 再次查看集群状态

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 32b2fe4a-cdf1-11ed-989c-0242ac48000d | 172.72.0.13 |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
| group_replication_applier | 3401b339-cdf1-11ed-9b3f-0242ac48000c | 172.72.0.12 |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
| group_replication_applier | 63f351cc-cdf1-11ed-a386-0242ac48000b | 172.72.0.11 |        3306 | UNREACHABLE  | PRIMARY     | 8.0.30         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+-------
3 rows in set (0.01 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 32b2fe4a-cdf1-11ed-989c-0242ac48000d | 172.72.0.13 |        3306 | ONLINE       | PRIMARY     | 8.0.30         | XCom                       |
| group_replication_applier | 3401b339-cdf1-11ed-9b3f-0242ac48000c | 172.72.0.12 |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+-------
2 rows in set (0.00 sec)
复制

结论：原主节点为mgr1状态变为UNREACHABLE，随后备剔除，主节点变为mgr3。

当进行自动主库选举时会按照下列优先级进行：

1.低版本优先，当组复制中同时存在高版本和低版本MySQL时，低版本会被选举为主库（向下兼容）。
2.如果有多个低版本，则会参考group_replication_member_weight(0-100)变量的值，默认为50，数字越大优先级越高。
3.如果版本，group_replication_member_weight都一样，最后考虑的因素是UUID的排序，UUID最小的选举为主库。

mysql> show variables like 'group_replication_member_weight';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| group_replication_member_weight | 50    |
+---------------------------------+-------+
1 row in set (0.00 sec)
复制

9.4 新的主节点上操作

mysql> insert into testdb.t1 values (4,'leo');
Query OK, 1 row affected (0.00 sec)

mysql> insert into testdb.t1 values (5,'Aaron');
Query OK, 1 row affected (0.00 sec)

mysql> select * from testdb.t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | tony  |
|  2 | lion  |
|  3 | jack  |
|  4 | leo   |
|  5 | Aaron |
+----+-------+
5 rows in set (0.00 sec)


-- 其他节点查询结果一致
mysql> select * from testdb.t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | tony  |
|  2 | lion  |
|  3 | jack  |
|  4 | leo   |
|  5 | Aaron |
+----+-------+
5 rows in set (0.00 sec)
复制

9.5 原主节点重新启动

[root@mgr1 /]# systemctl start mysqld
[root@mgr1 /]# mysql -uroot -proot123 
mysql> select * from performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier |           |             |        NULL | OFFLINE      |             |                |                            |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+-----------------
1 row in set (0.03 sec)
-- 以备库角色加入原有的MGR群
mysql> start group_replication;
Query OK, 0 rows affected, 1 warning (20.74 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 32b2fe4a-cdf1-11ed-989c-0242ac48000d | 172.72.0.13 |        3306 | ONLINE       | PRIMARY     | 8.0.30         | XCom                       |
| group_replication_applier | 3401b339-cdf1-11ed-9b3f-0242ac48000c | 172.72.0.12 |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
| group_replication_applier | 63f351cc-cdf1-11ed-a386-0242ac48000b | 172.72.0.11 |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+-------
3 rows in set (0.06 sec)

mysql> select * from testdb.t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | tony  |
|  2 | lion  |
|  3 | jack  |
|  4 | leo   |
|  5 | Aaron |
+----+-------+
5 rows in set (0.02 sec)
复制

结论：原主节点为mgr1 以备库角色加入到集群，并且新主节点的操作也同步到同步到mgr1中。

注意： 原主节点为mgr1 启动后，不要以主节点的形式，加入到MGR集群中，无法成功加入到mgr集群，甚至会因为一些错误操作，需要重新初始化该节点，才能正常加入集群。所以，当原来的主节点宕机后，注意检查参数loose-group_replication_start_on_boot、loose-group_replication_bootstrap_group，建议参数文件中都设置成OFF。