MySQL 8.x 高可用集群之MGR（组复制）

前言

MySQL Group Replication（MGR），全称MySQL Group Replication，是由MySQL官方于2016年12月推出的一项全新的高可用性与高扩展性解决方案。该解决方案旨在提供高可用、高扩展和高可靠的MySQL集群服务。在MySQL Group Replication出现之前，用户通常采用Master-Slave架构作为MySQL高可用性的主要方式。然而，随着业务需求的增长和发展，传统的Master-Slave架构可能会面临一些限制，例如单点故障和扩展性瓶颈。

MySQL 5.7版本开始支持无损半同步复制（lossless semi-synchronous replication），这一特性进一步提升了数据复制的强一致性。而MySQL Group Replication则在此基础上进一步发展，为用户提供了一种更为灵活和可靠的数据库复制方案。通过使用MySQL Group Replication，用户可以构建一个由多个MySQL实例组成的集群，这些实例可以相互协作，提供高可用性和扩展性，并保证数据的一致性和可靠性。

MySQL Group Replication采用基于Paxos协议的一致性算法，确保了集群中的所有节点在任何时候都保持一致的数据状态。同时还提供了自动故障检测和恢复机制，当集群中的某个节点发生故障时，集群可以自动进行故障切换，保证服务的持续可用性。

一、集群部署

本次部署共三台机器三个节点构成集群，mysql版本：8.0.31，操作系统版本：centos7

拓扑结构图：

1.1 修改主机名和关闭selinux

以下操作需要在三台机器分别执行

# 查看主机名
hostname

# 编辑主机名
vim /etc/hosts
# 新增以下内容：
192.168.0.123 mysql01
192.168.0.136 mysql02
192.168.0.163 mysql03

# 将 SELINUX=enforcing 改为 SELINUX=disabled
vi /etc/selinux/config

# 执行重启命令
reboot
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

1.2 MySQL安装

1.2.1 移除Linux自带安装的mariadb数据库

检查linux是否安装了mariadb数据库，mariadb数据库是mysql的分支。是免费开源的。由于mariadb和msyql会有冲突，安装mysql之前首先要检查是否安装了mariadb并卸载

检查命令 ：

yum list installed | grep mariadb
1

卸载命令：

# -y 参数确认删除
yum -y remove mariadb-libs.x86_64
1
2

1.2.2 下载安装文件

# 进入安装包目录,没有则创建
cd /home/mysql-8.0.31
# 使用wget获取rpm
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.31-1.el7.x86_64.rpm-bundle.tar
# 解压命令
tar -xvf mysql-8.0.31-1.el7.x86_64.rpm-bundle.tar 
1
2
3
4
5
6

1.2.3 顺序执行安装

rpm -ivh mysql-community-common-8.0.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-icu-data-files-8.0.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.31-1.el7.x86_64.rpm
1
2
3
4
5
6

1.2.4 启动mysql并修改密码

# 启动mysql服务
systemctl start mysqld
# 获取首次启动生成的随机密码
sudo grep 'temporary password' /var/log/mysqld.log
1
2
3
4

# 登录mysql
mysql -u root -p
1
2

# 修改root用户密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MySQL@2024';
# 切换使用mysql数据库
use mysql;
# 开启远程，host='%'这里是允许所有远程主机访问
update user set host='%' where user='root'; 
# 刷新权限
flush privileges;
1
2
3
4
5
6
7
8

1.2.5 开启防火墙端口允许访问

#查看防火墙状态
systemctl status firewalld 
#查看开放的端口
firewall-cmd --query-port=3306/tcp
#添加端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent 
#重载防火墙
firewall-cmd --reload 
#再次查看端口是否已经开放
firewall-cmd --query-port=3306/tcp
1
2
3
4
5
6
7
8
9
10

其他两处节点执行相同操作

1.3 修改mysql配置文件

vim /etc/my.cnf
1

mysql01增加配置文件:

# 数据目录
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# 日志目录
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# ===========================================根据服务器配置修改(可选)========================================================
# 最大连接数
max_connections=1000
# 禁用的存储引擎
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
# 表名大小写不敏感
#lower_case_table_names=1
# 时区设置
default-time_zone='+8:00'
# 字符集设置
character-set-server=UTF8MB4
# 默认存储引擎
default-storage-engine=INNODB
# InnoDB缓冲池大小
innodb_buffer_pool_size = 4G
# InnoDB线程并发度
innodb_thread_concurrency = 4
# 排序缓冲区大小
sort_buffer_size=10485760
# InnoDB排序缓冲区大小
innodb_sort_buffer_size=10485760

# ===========================================需要修改的配置 start========================================================
# 端口
port=3306
# 绑定地址，允许所有地址连接
bind-address=0.0.0.0
# 服务器ID,
server_id=3
# GTID模式
gtid_mode=ON
# 强制GTID一致性
enforce_gtid_consistency=ON
# 加载的插件
plugin_load_add= "group_replication.so;mysql_clone.so"
# 组复制相关配置
# 组的名字可以随便起,但不能用主机的GTID! 所有节点的这个组名必须保持一致！
group_replication_group_name="1d66a6f8-45c5-11ec-8364-e41f13bdecf8"
# 启动mysql时不自动启动组复制
group_replication_start_on_boot=off
# 本机IP地址或者映射，33061用于接收来自其他组成员的传入连接
group_replication_local_address= "mysql03:33061"
# 当前主机成员需要加入组时，Server先访问这些种子成员中的一个，然后它请求重新配置以允许它加入组
# 需要注意的是，此参数不需要列出所有组成员，只需列出当前节点加入组需要访问的节点即可。
group_replication_group_seeds= "mysql01:33061,mysql02:33061,mysql03:33061"
# 是否自动引导组。此选项只能在一个server实例上使用，通常是首次引导组时(或在整组成员关闭的情况下)，如果多次引导，可能出现脑裂。
group_replication_bootstrap_group=off
# ===========================================需要修改的配置 end========================================================

[mysql]
default-character-set=UTF8MB4

[client]
port=3306
default-character-set=UTF8MB4
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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

第二个节点修改内容如下：

server_id=2
loose-group_replication_local_address= "mysql02:33061"
1
2

第三个节点修改内容如下：

server_id=3
loose-group_replication_local_address= "mysql03:33061"
1
2

三个节点添加完配置信息之后，分别重启MySQL服务，以使配置生效

systemctl restart mysqld
# 开放组员通信防火墙端口
firewall-cmd --zone=public --add-port=33061/tcp --permanent 
#重载防火墙
firewall-cmd --reload 
1
2
3
4
5

1.4 创建复制账号

三个节点均需配置，使用root用户和修改后的密码分别登录，注意：三个节点的复制账户密码要保持一致

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'MySQL@2024';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='MySQL@2024' FOR CHANNEL 'group_replication_recovery';
1
2
3
4
5
6

1.5 安装MGR插件

# 安装插件，如果已经存在则跳过此步骤
install PLUGIN group_replication SONAME 'group_replication.so';
# 查看group replication组件
show plugins;
1
2
3
4

二、单主模式启动MGR集群

2.1 主节点执行mysql操作

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
1
2
3

• 如果报错：[GCS] The group communication engine failed to test connectivity to the local group communication engine on mysql01:33061. This may be due to one or more invalid configuration settings. Double-check your group replication local address, firewall, SE Linux and TLS configurations and try restarting Group Replication on this server.

  解决方法：

  # 将 SELINUX=enforcing 改为 SELINUX=disabled,然后重启服务器！
  vim /etc/selinux/config
  1
  2

2.1.1 查看组信息

    SELECT * FROM performance_schema.replication_group_members;
1

• CHANNEL_NAME：通道名称。组复制插件创建两个复制通道。group_replication_recovery用于与分布式恢复阶段相关的复制更改。group_replication_applier用于来自组传入的更改，是应用直接来自组的事务的通道。
• MEMBER_ID：组成员实例的server_uuid。
• MEMBER_HOST：组成员主机名。如果配置了report_host参数，这里显示IP地址。
• MEMBER_ROLE：成员角色，主为PRIMARY，从为SECONDARY。
• MEMBER_VERSION：成员数据库实例版本。
• MEMBER_STATE：成员状态，取值和含义如下表所示：

2.2 从节点执行mysql操作

mysql02,mysql03分别执行下面操作

START GROUP_REPLICATION;
1

执行完成后，任一节点查看集群状态

SELECT * FROM performance_schema.replication_group_members;
1

若从节点state值一直为RECOVERING，同时查看日志报如下错：

Slave I/O for channel 'group_replication_recovery': error connecting to master 'rpl_user@mysql01:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061

**报错原因：**由于mysql8.0之后加密规则变成 caching_sha2_password，所以使用MGR方式复制时，需要打开公钥访问。

解决方案：在每个从节点上执行下面mysql语句

STOP GROUP_REPLICATION;
SET GLOBAL group_replication_recovery_get_public_key=ON;
START GROUP_REPLICATION;
1
2
3

再次查看MGR组信息

SELECT * FROM performance_schema.replication_group_members;
1

2.3 验证主从数据同步和读写操作

# 1、在主库节点master数据库上执行
CREATE DATABASE test;
USE test;
CREATE TABLE user (`id` INT(11) PRIMARY KEY, `name` varchar(256) NOT NULL);
INSERT INTO user VALUES (1, '章北海');
# 2、在3个节点查看, 可以看到均有相同的表和数据
select * from test.user;
1
2
3
4
5
6
7

在从节点测试写入，验证不支持写入操作

2.4 测试主从数据库宕机

2.4.1 停止主节点

# 将主库节点node1上从mgr组中去除
stop group_replication;
# 从库节点node2查看，发现仅剩2个节点,并且自动选举出主节点
SELECT * FROM performance_schema.replication_group_members;
1
2
3
4

数据写入测试，此时查看已移除的节点node1数据库并没有进行数据同步

2.4.2 停止从库节点

# 将从节点node3上从mgr组中去除
stop group_replication;
# 当前主节点node2查看
SELECT * FROM performance_schema.replication_group_members;
1
2
3
4

2.4.3 重启宕机节点

# 在mysql01，mysql02执行开启组复制
start group_replication;
# 当前主节点node2查看
SELECT * FROM performance_schema.replication_group_members;
1
2
3
4

可以看到宕机节点mysql01,mysql03已经作为从节点加入到新的主节点mysql02

查看数据同步，可以看到原先宕机的节点已经成功数据同步

select * from test.user;
1

三、MGR整合MySQL Router实现读写分离

3.1 安装MySQL Router

**说明：**读写分离这部分，选择从库节点进行安装部署（这里以 mysql02 从库节点举例说明）

1. 进入到/usr/local/mysql文件夹下，下载MySQL Router安装包，并解压重命名

wget https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-8.0.31-el7-x86_64.tar.gz
tar -xvf mysql-router-8.0.31-el7-x86_64.tar.gz
mv mysql-router-8.0.31-el7-x86_64 mysql-router-8.0.31
1
2
3

2. 进入到mysql-router-8.0.31文件夹下，执行如下命令，添加系统变量

pwd
echo "export PATH=$PATH:/usr/local/mysql/mysql-router-8.0.31/bin/" >> /etc/profile
source /etc/profile
1
2
3

3. 在当前文件夹下，查看MySQL Router是否安装成功

mysqlrouter --help
mysqlrouter -V
1
2

3.2 配置MySQL Router

1. 进入 /usr/local/mysql/mysql-router-8.0.31 文件夹下创建日志、数据文件夹

mkdir log
mkdir data
1
2

2. 进到 /usr/local/mysql/mysql-router-8.0.31/share/doc/mysqlrouter 文件夹下，复制sample_mysqlrouter.conf文件

cd /usr/local/mysql/mysql-router-8.0.31/share/doc/mysqlrouter
cp sample_mysqlrouter.conf /etc/mysqlrouter.conf
1
2

3. 进到 /etc 文件夹下，编辑 mysqlrouter.conf 配置文件，添加如下信息

[DEFAULT]
logging_folder=/usr/local/mysql/mysql-router-8.0.31/log
runtime_folder=/usr/local/mysql/mysql-router-8.0.31/run
data_folder=/usr/local/mysql/mysql-router-8.0.31/data
connect_timeout=30
read_timeout=30

[logger]
level = INFO

[routing:primary]
bind_address = 0.0.0.0
# 端口7001
bind_port = 7001  
max_connections = 1024
# 可用的支持写操作的主库，或者主库共用的IP
destinations = 192.168.0.123:3306,192.168.0.136:3306,192.168.0.163:3306
routing_strategy = first-available

[routing:secondary] 
bind_address = 0.0.0.0 
# 端口7002
bind_port = 7002 
max_connections = 1024 
# 参与读负载均衡的从库
destinations = 192.168.0.136:3306,192.168.0.163:3306
routing_strategy = round-robin
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

4. 启动router

mysqlrouter --config=/etc/mysqlrouter.conf &

# 开放mysql router的防火墙端口
firewall-cmd --zone=public --add-port=7001/tcp --permanent 
firewall-cmd --zone=public --add-port=7002/tcp --permanent 
#重载防火墙
firewall-cmd --reload 
1
2
3
4
5
6
7

3.3 测试MySQL Router读写

测试从节点轮询读取

mysql -h 192.168.0.136 -u root -pMySQL@2024 -P 7002 -e "select @@hostname"
1

测试主节点读写

mysql -h 192.168.0.136 -u root -pMySQL@2024 -P 7001 -e "select @@hostname"
1

四、关于组复制的状态转移

本章节探讨关于集群中节点中成员状态的改变，关于mysql集群的部署前三章节已经结束。

4.1 成员状态

MEMBER_STATE：成员状态，取值和含义如下表所示：

​ 从上表可以知道，只有ONLINE和RECOVERING两种状态会在集群中得到同步。这个状态同步是指状态在所有成员上查询均能保持一致。对于OFFLINE、ERROR和UNREABLE：

• 只有在当前OFFLINE成员查询replication_group_members表才能得到OFFLINE状态，在其它成员上查询replication_group_members表，则没有该成员的状态，因为OFFLINE成员已经不属于这个复制组了。
• 只有在当前ERROR成员查询replication_group_members表才能得到ERROR状态，同上面的OFFLINE，在其它成员上查询也看不到该成员。
• 假设成员A与B网络通讯失败，那么在节点A上查询replication_group_members表，有可能得到B的状态为UNREACHABLE。

4.2 成员状态的转移

​ 当一个成员加进一个复制组，其状态首先变成RECOVERING，表示当前成员正处于集群恢复阶段。这个阶段下，成员会选择集群中一个成员作为捐赠者（donor），利用传统的异步复制做数据恢复。当数据能够成功追平，成员的状态将会变成ONLINE，这个过程中通过其他成员也可以看到该节点的状态，不管是RECOVERING还是最后的ONLINE。

​ 假如该成员在RECOVERING阶段出现了异常，如选择donor进行复制失败或者在追赶donor数据的过程中失败，那么该成员的状态将会变成ERROR。注意，这时候在其它成员上查询时，发现该RECOVERING节点已经从组里面被移除。

​ 另外，如果一个ONLINE成员失去与其它成员的通讯（可能因为成员宕机或者网络异常），则该成员在其他成员上面查询到的状态将会是UNREACHABLE。如果这个UNREACHABLE成员在规定的超时时间内没有恢复，那么成员将会被移除。这个规定的超时时间，取决于集群失去这个成员后还能不能达到可用状态。如果失去这个成员集群仍然可用，那么这个UNREACHABLE的超时时间很短，几乎看不到这个状态。但是，如果失去这个成员后集群马上不可用，那么这个成员将会一直处于UNREACHABLE状态。

五、常用报错问题

1. [ERROR] [MY-011516] [Repl] Plugin group_replication reported: ‘There is already a member with server_uuid 69a66a80-a527-11ed-98ee-5254008f50ab. The member will now exit the group.’

   解决方案：此问题由于可能直接拷贝的数据目录导致两个节点的server_uuid一致。修改server_uuid，进入数据目录找到auto.cnf，然后修改即可

   

2. [Warning] [MY-011735] [Repl] Plugin group_replication reported: ‘[GCS] Connection attempt from IP address ::ffff:219.216.241.106 refused. Address is not in the IP allowlist.’
   2024-03-22T13:37:45.921547Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: ‘[GCS] Error connecting to all peers. Member join failed. Local port: 33061’

解决方案： 组复制开启之前，增加组成员的ip白名单

SET GLOBAL group_replication_ip_whitelist="119.216.241.206,119.216.241.207,119.216.241.208";
START GROUP_REPLICATION;
1
2

3. [ERROR] [MY-013117] [Repl] Slave I/O for channel ‘group_replication_recovery’: Fatal error: Invalid (empty) username when attempting to connect to the master server. Connection attempt terminated. Error_code: MY-013117

   报错原因：没有配置同步账号跟密码，使用的是空密码进行同步，需要为复制通道group replication recoverv设置同步信息

   解决方案：

   CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
   1

4. [ERROR] [MY-011582] [Repl] Slave I/O for channel ‘group_replication_recovery’: error connecting to master ‘rpl_user@Host1:4706’ - retry-time: 60 retries: 1 message: Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection. Error_code: MY-002061
   解决方案：所有库执行修改复制用户的密码，使用mysql_native_password加密

   ALTER USER 'rpl_user'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
   1

5. [ERROR] [MY-010584] [Repl] Slave SQL for channel ‘group_replication_recovery’: Worker 1 failed executing transaction ‘69a66a80-a527-11ed-98ee-5254008f50ab:8’ at master log binlog.000008, end_log_pos 6724; Could not execute Delete_rows event on table zkcx_ckcx_sysinfo._sys_repeatvaluei; Can’t find record in ‘_sys_repeatvaluei’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log FIRST, end_log_pos 6724, Error_code: MY-001032

   问题原因：从服务器上的数据与主服务器上的数据不一致。主服务器上执行了删除操作，但在复制到从服务器时，数据不一致导致无法找到 要删除的记录。

   解决方案：

   ​ 方案一：主库和从库停止mysql，同时把主库的数据目录直接打包复制到从库，然后注意修改数据目录中的auto.cnf中的server_uuid,保持 两边不一样即可，然后重新启动从节点

   ​ 方案二：参考：解决Worker 1 failed executing transaction ‘ANONYMOUS‘ at master log mall-mysql-bin.000001, end_log_pos_coordinator stopped because there were error(s) in-CSDN博客

6. [ERROR] [MY-011638] [Repl] Plugin group_replication reported: 'Can’t start group replication on secondary member with single-primary mode while asynchronous replication channels are running.

   问题原因：这个错误消息表明在尝试启动 Group Replication 的次要成员时，发现了异步复制通道在运行中，而单主模式下不能启动 Group Replication

   解决方案：使用 STOP SLAVE; 命令来停止异步复制通道，然后START GROUP_REPLICATION; 开启从节点

相关参考：

[1]. 基于MGR方式搭建MySQL8.0一主多从集群 - 星空流年 - 博客园 (cnblogs.com)

[2]. MySQL 8 复制（八）——组复制安装部署-腾讯云开发者社区-腾讯云 (tencent.com)

[3]. MySQL+MGR单主模式集群环境部署 - 粉色纽扣 - 博客园 (cnblogs.com)

[4]. 记录MySQL 8.0.32搭建单主模式、多主模式MGR过程itmtr的技术博客51CTO博客

[5].【MySQL高可用集群】MySQL的MGR搭建_mysql mgr-CSDN博客