周顺的技术博客

  • 准备三台服务器
  • 6、MGR配置实战
  • 首页
  • 榜上有名
  • 文章归档
  • 读者排行
  • 豆瓣书影
  • 友情链接
  • DevOps (4)
  • Docker (4)
  • k8s (9)
  • linux (11)
  • Mysql (9)
  • Python (2)
  • Redis (1)
  • Shell (6)
  • 主题 (0)
  • 未分类 (0)

MGR组复制（MySQL高可用集群）

  • xiaoteng
  • 2025-02-11
  • 0

准备三台服务器

编号IP主机名角色
1192.168.88.101node1master
2192.168.88.102node2slave01
3192.168.88.103node3slave02

安装MySQL8.0.40数据库

第一步：关闭防火墙和SELINUX、设置IP与主机映射、时间同步，安装必备工具vim、wget、rsync

systemctl stop firewalld
systemctl disable firewalld

setenforce 0
vim /etc/selinux/config

vim /etc/hosts
尾部追加如下内容
192.168.88.101 node1 node1.xiaoteng.cn
192.168.88.102 node2 node2.xiaoteng.cn
192.168.88.103 node3 node3.xiaoteng.cn



dnf install vim wget rsync -y

第二步：使用MX连接node1、node2、node3

node1（master）

node2（slave01）

node3（slave02）

第三步：

把配对的master.sh与mysql软件包上传master节点

source master.sh

把配对的slave.sh与mysql软件包上传slave01/slave02节点

source slave.sh

第四步：

master节点：

systemctl stop mysqld
rm -rf /export/server/mysql/data/auto.cnf
rsync -av /export/server/mysql/data node2:/export/server/mysql/
rsync -av /export/server/mysql/data node3:/export/server/mysql/

6、MGR配置实战

第一步：node1主服务器配置

vim /etc/my.cnf
[mysqld]
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
port=3306
log-error=/export/server/mysql/master.err
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci

# Group Replication
server_id = 101  # 服务 ID
gtid_mode = ON  # 全局事务
enforce_gtid_consistency = ON # 强制 GTID 的一致性
master_info_repository = TABLE # 将 master.info 元数据保存在系统表中
relay_log_info_repository = TABLE # 将 relay.info 元数据保存在系统表中
binlog_checksum = NONE  # 禁用二进制日志事件校验
log_slave_updates = ON  # 级联复制
log_bin = binlog  # 开启二进制日志记录
binlog_format = ROW  # 以行的格式记录

transaction_write_set_extraction = XXHASH64 # 使用哈希算法将其编码为散列
loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856' # 加入的组名
loose-group_replication_start_on_boot = off # 不自动启用组复制集群
loose-group_replication_local_address = '192.168.88.101:33061' # 以本机端口 33061 接受来自组中成员的传入连接
loose-group_replication_group_seeds = '192.168.88.101:33061, 192.168.88.102:33062, 192.168.88.103:33063' # 组中成员访问表
loose-group_replication_bootstrap_group = off # 不启用引导组

注意：group_replication_group_name 组名称主要是通过 uuidgen 命令生成

重启MySQL服务

touch /export/server/mysql/master.err
chown -R mysql.mysql /export/server/mysql

systemctl restart mysqld

创建复制账号

mysql -uroot -p
Enter password: 123456

mysql> set SQL_LOG_BIN=0; # 停掉日志记录
mysql> create user repl@'%' identified with 'mysql_native_password' by '123';
mysql> grant replication slave,replication client on *.* to repl@'%';
mysql> flush privileges;
mysql> set SQL_LOG_BIN=1;  # 开启日志记录
mysql> change master to master_user='repl',master_password='123' for channel
'group_replication_recovery';  # 构建 group replication 集群

安装 group replication 插件

安装插件

mysql> install PLUGIN group_replication SONAME 'group_replication.so';

查看 group replication 组件

mysql> show plugins;

启动服务器 node1 上 MySQL 的 group replication

mysql> set global group_replication_bootstrap_group=ON;
mysql> start group_replication;
mysql> set global group_replication_bootstrap_group=OFF;
mysql> select * from performance_schema.replication_group_members;  # 查看状态

注意：在集群没有搭建完成之前，不要往任何数据库中插入数据，否则会导致三端数据不统一，集群容易出现故障！

第二步：node2从服务器配置

修改/etc/my.cnf 配置文件，方法和之前相同vim /etc/my.cnf

vim /etc/my.cnf
[mysqld]
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
port=3306
log-error=/export/server/mysql/slave.err
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci

# Group Replication
server_id = 102  # 注意服务 ID 不一样
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format= ROW

transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856'
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = '192.168.88.102:33062'
loose-group_replication_group_seeds = '192.168.88.101:33061,192.168.88.102:33062,192.168.88.103:33063'
loose-group_replication_bootstrap_group = off

重启MySQL

touch /export/server/mysql/slave.err
chown -R mysql.mysql /export/server/mysql
systemctl restart mysqld

安装 group replication 插件

mysql> install PLUGIN group_replication SONAME 'group_replication.so';

把实例添加到之前的复制组

mysql -u root -p
Enter password: 123456

mysql> set SQL_LOG_BIN=0; # 停掉日志记录
mysql> create user repl@'%' identified with 'mysql_native_password' by '123';
mysql> grant replication slave,replication client on *.* to repl@'%';
mysql> flush privileges;
mysql> set SQL_LOG_BIN=1;  # 开启日志记录

mysql> reset master;
mysql> -- 设置想要加入组信息
mysql> change master to master_user='repl',master_password='123' for channel 'group_replication_recovery'; 
mysql> start group_replication;

返回node1主服务器，查看复制组状态

mysql> select * from performance_schema.replication_group_members;

注意：在集群没有搭建完成之前，不要往任何数据库中插入数据，否则会导致三端数据不统一，集群容易出现故障！

第三步：node3从服务器配置

修改/etc/my.cnf 配置文件，方法和之前相同vim /etc/my.cnf

vim /etc/my.cnf
[mysqld]
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
port=3306
log-error=/export/server/mysql/slave.err
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci

# Group Replication
server_id = 103 #注意服务 ID 不一样
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format= ROW
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856'
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = '192.168.88.103:33063'
loose-group_replication_group_seeds = '192.168.88.101:33061,192.168.88.102:33062,192.168.88.103:33063'
loose-group_replication_bootstrap_group = off

进入mysql，安装 group replication 插件

touch /export/server/mysql/slave.err
chown -R mysql.mysql /export/server/mysql/slave.err

systemctl start mysqld
mysql -uroot -p
Enter password: 123456

mysql> install PLUGIN group_replication SONAME 'group_replication.so';

把实例添加到之前的复制组

mysql> set SQL_LOG_BIN=0; #停掉日志记录
mysql> create user repl@'%' identified with 'mysql_native_password' by '123';
mysql> grant replication slave,replication client on *.* to repl@'%';
mysql> flush privileges;
mysql> set SQL_LOG_BIN=1;  # 开启日志记录

mysql> reset master;
mysql> -- 设置想要加入组信息
mysql> change master to master_user='repl',master_password='123' for channel 'group_replication_recovery'; 
mysql> start group_replication;

在node1上查看复制组状态

mysql> select * from performance_schema.replication_group_members;

如果以上状态为ONLINE，则搭建完毕。如果以上状态为RECORYING，则代表两端数据无法同步到一致状态。解决方案：参考常见问题中问题2进行解决。

建议：当MGR高可用集群搭建完毕后，需要给每一个节点创建快照，因为下面的操作，如果数据不统一等等问题，可能会导致集群出现故障。

第四步：测试MySQL

回到master，登录mysql，执行如下指令：

mysql> create database test;
mysql> use test;
mysql> create table t1 (id int primary key,name varchar(20)); #注意创建主键
mysql> insert into t1 values (1,'jack');
mysql> select * from t1;
mysql> show binlog events;

返回node2或node3，查看数据库发现 test 库和 t1 表已经完成同步操作

mysql> show databases;

第五步：测试高可用效果

查看哪个节点是主节点，然后使用systemctl stop mysqld终止主节点，在查看集群变化

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 | 1b6e2247-2ae2-11f0-b1be-000c2961ec25 | node3.itcast.cn |        3306 | ONLINE       | SECONDARY   | 8.0.40         | XCom                                        |
| group_replication_applier | 7792fd47-2998-11f0-81a4-000c29d9e0c0 | node1.itcast.cn |        3306 | ONLINE       | PRIMARY     | 8.0.40         | XCom                                        |
| group_replication_applier | fb783c29-299a-11f0-b046-000c296d8526 | node2.itcast.cn |        3306 | ONLINE       | SECONDARY   | 8.0.40         | XCom                                        |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+--------                 --------------------+
3 rows in set (0.01 sec)


# 终止master主节点
systemctl stop mysqld

# 进入node2、node3查看集群变化
select * from performance_schema.replication_group_members;

以上单 master 节点的集群就搭建完毕!

© 2026 周顺的技术博客
Theme by Wing
  • {{ item.name }}
  • {{ item.name }}