MGR集群在文章结尾
1.实践架构
| IP | 主机名 | 版本 |
|---|---|---|
| 10.0.0.12 | rocky9-12-mgr01 | 8.0 |
| 10.0.0.15 | rocky9-15-mgr02 | 8.0 |
| 10.0.0.18 | rocky9-18-mgr03 | 8.0 |
2.前期准备
关闭selinux、防火墙
跨主机免密
删除原有密钥
rm -rf .ssh/*
生成密钥
ssh-keygen -t rsa -P "" -f /root/.ssh/id_rsa
本地认证
[root@rocky9-12-mgr01 ~]# ssh-copy-id root@127.0.0.1
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
root@127.0.0.1's password:
跨主机认证
[root@rocky9-12-mgr01 ~]# scp -r ~/.ssh root@10.0.0.15:
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
root@10.0.0.15's password:
id_rsa 100% 2610 2.1MB/s 00:00
id_rsa.pub 100% 574 561.2KB/s 00:00
known_hosts 100% 825 1.9MB/s 00:00
known_hosts.old 100% 91 316.0KB/s 00:00
[root@rocky9-12-mgr01 ~]# scp -r ~/.ssh root@10.0.0.18:
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
root@10.0.0.18's password:
id_rsa 100% 2610 2.1MB/s 00:00
id_rsa.pub 100% 574 797.3KB/s 00:00
known_hosts 100% 916 2.7MB/s 00:00
known_hosts.old 100% 91 353.1KB/s 00:00
验证
[root@rocky9-15-mgr02 ~]# ssh root@10.0.0.18
The authenticity of host '10.0.0.18 (10.0.0.18)' can't be established.
ED25519 key fingerprint is SHA256:iSl1whqPbNnR66iotpZ9Y+lDlPn2Be+8fPUc5K5lw7E.
This host key is known by the following other names/addresses:
~/.ssh/known_hosts:1: 127.0.0.1
~/.ssh/known_hosts:4: 10.0.0.15
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '10.0.0.18' (ED25519) to the list of known hosts.
Last login: Sat May 17 10:39:08 2025 from 10.0.0.12
[root@rocky9-18-mgr03 ~]# ssh root@10.0.0.12
The authenticity of host '10.0.0.12 (10.0.0.12)' can't be established.
ED25519 key fingerprint is SHA256:iSl1whqPbNnR66iotpZ9Y+lDlPn2Be+8fPUc5K5lw7E.
This host key is known by the following other names/addresses:
~/.ssh/known_hosts:1: 127.0.0.1
~/.ssh/known_hosts:4: 10.0.0.15
~/.ssh/known_hosts:5: 10.0.0.18
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '10.0.0.12' (ED25519) to the list of known hosts.
Last login: Sat May 17 10:39:58 2025 from 10.0.0.1设置主机名解析
cat >> /etc/hosts <<-eof
10.0.0.12 rocky9-12-mgr01
10.0.0.15 rocky9-15-mgr02
10.0.0.18 rocky9-18-mgr03
eof
将hosts文件同步到其他两台主机
for i in 15 18; do
scp /etc/hosts root@10.0.0.$i:/etc/hosts
done查看selinux
for i in 12 15 18;do ssh root@10.0.0.$i "echo '10.0.0.$i SELINUX状态：' $(getenforce)"; done
10.0.0.12 SELINUX状态： Disabled
10.0.0.15 SELINUX状态： Disabled
10.0.0.18 SELINUX状态： Disabled保证时间同步
for i in 12 15 18;do ssh root@10.0.0.$i "echo '10.0.0.$i 时间:' $(date)"; done
10.0.0.12 时间: 2025年 05月 17日 星期六 11:20:56 CST
10.0.0.15 时间: 2025年 05月 17日 星期六 11:20:56 CST
10.0.0.18 时间: 2025年 05月 17日 星期六 11:20:56 CST保证防火墙已禁用
[root@rocky9-12-mgr01 ~]# systemctl disable --now firewalld; nft flush ruleset
部署mysql-server环境
for i in 12 15 18 ;do ssh root@10.0.0.$i "sudo yum install mysql-server -y"; done
次元数据过期检查：0:39:34 前，执行于 2025年05月17日 星期六 10时51分16秒。
软件包 mysql-server-8.0.41-2.el9_5.x86_64 已安装。
依赖关系解决。
无需任何处理。
完毕！
上次元数据过期检查：1:28:03 前，执行于 2025年05月17日 星期六 10时02分47秒。
软件包 mysql-server-8.0.41-2.el9_5.x86_64 已安装。
依赖关系解决。
无需任何处理。
完毕！
上次元数据过期检查：0:28:58 前，执行于 2025年05月17日 星期六 11时01分53秒。
软件包 mysql-server-8.0.41-2.el9_5.x86_64 已安装。
依赖关系解决。
无需任何处理。
完毕！
for i in 12 15 18 ;do ssh root@10.0.0.$i "sudo systemctl enable --now mysqld.service"; done
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.for i in 12 15 18 ;do ssh root@10.0.0.$i "sudo systemctl is-active mysqld.service"; done
active
active
active3.集群部署
主机设定用户认证策略
echo "ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';" > mysql.sql
for i in 15 18 ;do scp mysql.sql root@10.0.0.$i: ;done
for i in 12 15 18;do ssh root@10.0.0.$i "mysql -e 'source /root/mysql.sql;'" ;done查看组复制模块（默认已安装）
[root@rocky9-12-mgr01 ~]# ls /usr/lib64/mysql/plugin/group_replication.so
/usr/lib64/mysql/plugin/group_replication.so
如果主机没有MGR插件，使用一下命令进行安装
mysql -uroot -p'123456' -e "INSTALL PLUGIN group_replication SONAME 'group_replication.so';"备份原配置文件
cp /etc/my.cnf.d/mysql-server.cnf{,.bak}
[root@rocky9-12-mgr01 my.cnf.d]# ls
client.cnf mysql-server.cnf
[root@rocky9-12-mgr01 my.cnf.d]# cp /etc/my.cnf.d/mysql-server.cnf{,.bak}
[root@rocky9-12-mgr01 my.cnf.d]# ls
client.cnf mysql-server.cnf mysql-server.cnf.bak
生成UUID
uuidgen 用于后面的配置文件
[root@rocky9-12-mgr01 ~]# uuidgen
42581666-9975-463b-be27-2da1633d3458
定制配置文件
echo "
default_authentication_plugin=mysql_native_password
plugin_dir=/usr/lib64/mysql/plugin
server_id=12
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
#relay_log_infor_repository=TABLE
#从 8.0.23 版本开始，该变量必须被省略
transaction_write_set_extraction=XXHASH64
plugin_load_add='group_replication.so'
group_replication_group_name=42581666-9975-463b-be27-2da1633d3458
group_replication_start_on_boot=off
group_replication_local_address=10.0.0.12:33061
group_replication_group_seeds=10.0.0.12:33061,10.0.0.15:33061,10.0.0.18:33061
group_replication_bootstrap_group=off
report_host=10.0.0.12
report_port=3306
" >> /etc/my.cnf.d/mysql-server.cnf 更改配置后重启MySQL服务
systemctl restart mysqld注意
配置文件中，节点需要更改配置文件中"server_id=12"
1.sever-id当前主机唯一ID，每个节点id不能重复""
2.MGR集群主机的地址"group_replication_local_address"
3.向集群报告自身的地址"report_host"
设定MGR集群认证脚本
echo "
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
create user 'remote'@'%' identified with mysql_native_password by '123456';
grant all privileges on *.* to remote@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
RESET MASTER;
" > cluster_auth_init.sql各节点执行集群脚本
mysql -uroot -p123456 < cluster_auth_init.sql for i in 15 18;do scp cluster_auth_init.sql root@10.0.0.$i:/root/ ; done
for i in 15 18;do ssh root@10.0.0.$i "mysql -uroot -p123456 < /root/cluster_auth_init.sql " ;done
MGR 集群主角色环境
8.0.21之前版本
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION
SET GLOBAL group_replication_bootstrap_group=OFF;
8.0.21之后版本
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION USER='rpl_user', PASSWORD='password';
SET GLOBAL group_replication_bootstrap_group=OFF; 进入主服务器mysql 控制台
[root@rocky9-12-mgr01 ~]# mysql -uroot -p123456
......
mysql>
输入命令
密码改为之前设定的密码
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION USER='rpl_user', PASSWORD='123456';
Query OK, 0 rows affected (1.42 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
确认执行效果
mysql> SELECT * FROM performance_schema.replication_group_members\G;
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 7cc2a607-32cf-11f0-a125-000c296d59ef
MEMBER_HOST: 10.0.0.12
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.41
MEMBER_COMMUNICATION_STACK: XCom
1 row in set (0.00 sec)
为了证明服务器确实处于集群中且能够处理负载，需创建一个表并向其中添加一些内容
增加数据前的二进制日志
mysql> show binlog events;
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| binlog.000001 | 4 | Format_desc | 12 | 126 | Server ver: 8.0.41, Binlog ver: 4 |
| binlog.000001 | 126 | Previous_gtids | 12 | 153 | |
| binlog.000001 | 153 | Gtid | 12 | 235 | SET @@SESSION.GTID_NEXT= '42581666-9975-463b-be27-2da1633d3458:1' |
| binlog.000001 | 235 | Query | 12 | 297 | BEGIN |
| binlog.000001 | 297 | View_change | 12 | 396 | view_id=17474660093807369:1 |
| binlog.000001 | 396 | Query | 12 | 464 | COMMIT |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
6 rows in set (0.00 sec)
mysql> create database testdb1;
Query OK, 1 row affected (0.00 sec)
添加数据
mysql> create database testdb1;
Query OK, 1 row affected (0.00 sec)
mysql> use testdb1
Database changed
mysql> create table t1(c1 int primary key ,c2 text not null);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(1,'luis');
Query OK, 1 row affected (0.10 sec)
mysql> select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | luis |
+----+------+
1 row in set (0.00 sec) 再次查看二进制日志
mysql> show binlog events;
+---------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------+
| binlog.000001 | 4 | Format_desc | 12 | 126 | Server ver: 8.0.41, Binlog ver: 4 |
| binlog.000001 | 126 | Previous_gtids | 12 | 153 | |
| binlog.000001 | 153 | Gtid | 12 | 235 | SET @@SESSION.GTID_NEXT= '42581666-9975-463b-be27-2da1633d3458:1' |
| binlog.000001 | 235 | Query | 12 | 297 | BEGIN |
| binlog.000001 | 297 | View_change | 12 | 396 | view_id=17474660093807369:1 |
| binlog.000001 | 396 | Query | 12 | 464 | COMMIT |
| binlog.000001 | 464 | Gtid | 12 | 544 | SET @@SESSION.GTID_NEXT= '42581666-9975-463b-be27-2da1633d3458:2' |
| binlog.000001 | 544 | Query | 12 | 657 | create database testdb1 /* xid=29 */ |
| binlog.000001 | 657 | Gtid | 12 | 737 | SET @@SESSION.GTID_NEXT= '42581666-9975-463b-be27-2da1633d3458:3' |
| binlog.000001 | 737 | Query | 12 | 880 | use `testdb1`; create table t1(c1 int primary key ,c2 text not null) /* xid=35 */ |
| binlog.000001 | 880 | Gtid | 12 | 962 | SET @@SESSION.GTID_NEXT= '42581666-9975-463b-be27-2da1633d3458:4' |
| binlog.000001 | 962 | Query | 12 | 1036 | BEGIN |
| binlog.000001 | 1036 | Table_map | 12 | 1090 | table_id: 119 (testdb1.t1) |
| binlog.000001 | 1090 | Write_rows | 12 | 1132 | table_id: 119 flags: STMT_END_F |
| binlog.000001 | 1132 | Xid | 12 | 1159 | COMMIT /* xid=36 */ |
+---------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------+
15 rows in set (0.00 sec)
如上文所示，数据库和表对象已创建，其对应的数据库定义语言（DDL）语句已写入二进制日志。此外，数据已插入表中并写入二进制日志，因此可通过从二进制日志进行状态转移，将其用于分布式恢复。MGR 集群从角色环境
8.0.21 之前的版本执行如下命令
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
mysql> START GROUP_REPLICATION;
8.0.21 之后的版本执行如下命令
mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user',SOURCE_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
mysql> START GROUP_REPLICATION USER='rpl_user', PASSWORD='password'; 编辑命令
echo "CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user',SOURCE_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION USER='rpl_user', PASSWORD='123456';
" > slave_environment.sqlfor i in 15 18;do scp slave_environment.sql root@10.0.0.$i:/root/ ; done
for i in 15 18;do ssh root@10.0.0.$i "mysql -uroot -p123456 < /root/slave_environment.sql " ;done从节点确认加入集群效果
mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 756d9449-317b-11f0-b187-0050563acce0
MEMBER_HOST: 10.0.0.15
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.41
MEMBER_COMMUNICATION_STACK: XCom
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 7cc2a607-32cf-11f0-a125-000c296d59ef
MEMBER_HOST: 10.0.0.12
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.41
MEMBER_COMMUNICATION_STACK: XCom
*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 83d031ee-32cf-11f0-a4bf-005056359d43
MEMBER_HOST: 10.0.0.18
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.41
MEMBER_COMMUNICATION_STACK: XCom
3 rows in set (0.00 sec)
查看数据同步情况
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb1 |
+--------------------+
5 rows in set (0.00 sec)
mysql> use testdb1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_testdb1 |
+-------------------+
| t1 |
+-------------------+
1 row in set (0.01 sec)
mysql> select * from t1
-> ;
+----+------+
| c1 | c2 |
+----+------+
| 1 | luis |
+----+------+
1 row in set (0.00 sec)
MGR集群中，默认情况下，只有主节点才可以操作数据，从节点无法操作数据
mysql> create table t2((c1 int primary key ,c2 text not null);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'c1 int primary key ,c2 text not null)' at line 1
查看集群状态
MySQL MGR集群搭建完毕，并且节点都是"ONLINE"状态。
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 | 756d9449-317b-11f0-b187-0050563acce0 | 10.0.0.15 | 3306 | ONLINE | SECONDARY | 8.0.41 | XCom |
| group_replication_applier | 7cc2a607-32cf-11f0-a125-000c296d59ef | 10.0.0.12 | 3306 | ONLINE | PRIMARY | 8.0.41 | XCom |
| group_replication_applier | 83d031ee-32cf-11f0-a4bf-005056359d43 | 10.0.0.18 | 3306 | ONLINE | SECONDARY | 8.0.41 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)
常见节点状态
"连接相关状态"
ONLINE：节点处于正常在线状态（主节点可读写，从节点只读 ）
OFFLINE：节点当前处于离线状态（由于节点主动关闭、硬件故障、网络故障等原因导致与集群断开连接）
DISCONNECTED：节点无法与集群中的其他节点通信，可能是网络中断、防火墙限制等网络相关问题，或配置错误导致无法建立连接
UNREACHABLE：组通信消息收发超时时，故障检测机制会将节点标记为该状态，怀疑其无法和其他节点连接，比如因意外断开连接 "故障与恢复相关状态"
RECOVERING：节点正在进行分布式恢复，可能是刚加入集群，或经历故障恢复后，正从节点利用clone 复制数据，或者传输 binlog ，等待完全加入集群并能正常提供服务
如果新加入的MGR节点状态为RECOVERING ，则需要登入到对应主机机器查看MySQL日志，需要重新启动MGR
mysql> STOP GROUP_REPLICATION;
mysql> START GROUP_REPLICATION;
ERROR：节点检测到错误，无法继续正常工作
SUSPICIOUS ：MGR 中当超过一定时间没收到某个节点的任何消息时，该节点会被标记为可疑状态，各正常存活节点会对其进行检测