当前位置： 首页 > article >正文

MySQL Group Replication

article 2025/3/11 9:21:04

参考文档：
https://dev.mysql.com/doc/refman/8.4/en/group-replication-configuring-instances.html

MySQL版本：

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.4.3     |
+-----------+
1 row in set (0.00 sec)

mysql>

-- 设置/etc/hosts （三个节点都需要设置）

192.168.2.11  mgr01
192.168.2.12  mgr02
192.168.2.13  mgr03

-- 关闭掉OS的防火墙 （三个节点都需要设置，略）

-- 第一个库的设置

-- 初始化MySQL （略）

-- MySQL配置文件（本测试，仅仅设置MGR所需的必须的参数）

[mysqld]

#
# Disable other storage engines
#
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

#
# Replication configuration parameters
#
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON

#
# Group Replication configuration
#
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "mgr01:33061"
group_replication_group_seeds= "mgr01:33061,mgr02:33061,mgr03:33061"
group_replication_bootstrap_group= off

-- 安装插件,(my.cnf中plugin_load_add='group_replication.so'，发现plogins已经是active了)

INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SHOW PLUGINS;

-- 创建用户

SET SQL_LOG_BIN=0;

CREATE USER rpl_user@'%' IDENTIFIED BY 'mysql';
alter user rpl_user@'%' identified with  mysql_native_password  by 'mysql';
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@'%';
FLUSH PRIVILEGES;

SET SQL_LOG_BIN=1;

-- 创建测试库 

CREATE DATABASE test;
USE test;
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
INSERT INTO t1 VALUES (1, 'Luis');
SHOW BINLOG EVENTS;

mysql> SHOW BINLOG EVENTS;
+---------------+-----+----------------+-----------+-------------+----------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                             |
+---------------+-----+----------------+-----------+-------------+----------------------------------+
| binlog.000001 |   4 | Format_desc    |         1 |         127 | Server ver: 8.4.3, Binlog ver: 4 |
| binlog.000001 | 127 | Previous_gtids |         1 |         158 |                                  |
| binlog.000001 | 158 | Stop           |         1 |         181 |                                  |
+---------------+-----+----------------+-----------+-------------+----------------------------------+
3 rows in set (0.00 sec)

mysql>

-- 启用复制 

CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user',SOURCE_PASSWORD='mysql' FOR CHANNEL 'group_replication_recovery';
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION USER='rpl_user', PASSWORD='mysql';
SET GLOBAL group_replication_bootstrap_group=OFF;

mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user',SOURCE_PASSWORD='mysql' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION USER='rpl_user', PASSWORD='mysql';
Query OK, 0 rows affected (1.08 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;

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 | e558e52f-b327-11ef-8266-000c291c3ef6 | mgr01       |        3306 | ONLINE       | PRIMARY     | 8.4.3          | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.01 sec)

mysql>

 

-- 添加第二个库  

-- 初始化MySQL、创建用户，同第一个库 （略）

-- 启动 

CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='mysql' FOR CHANNEL 'group_replication_recovery';

START GROUP_REPLICATION;
START GROUP_REPLICATION USER='rpl_user', PASSWORD='mysql';
SELECT * FROM performance_schema.replication_group_members;

mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='mysql' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> 

mysql> START GROUP_REPLICATION USER='rpl_user', PASSWORD='mysql';
Query OK, 0 rows affected (5.94 sec)

mysql> 
mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='mysql' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> START GROUP_REPLICATION USER='rpl_user', PASSWORD='mysql';
Query OK, 0 rows affected (1.89 sec)

mysql>

-- 查看复制状态 （状态为RECOVERING，说明有问题，通过后面的问题1234解决。设置时候可以参考各个mysql的log，以查看复制是否有问题）

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 | 47b589a3-b3c3-11ef-ba63-000c297e6af7 | mgr02       |        3306 | RECOVERING   | SECONDARY   | 8.4.3          | XCom                       |
| group_replication_applier | e558e52f-b327-11ef-8266-000c291c3ef6 | mgr01       |        3306 | ONLINE       | PRIMARY     | 8.4.3          | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
2 rows in set (0.00 sec)

mysql>

-- 添加第三个库 

[mysqld]

#
# Disable other storage engines
#
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

#
# Replication configuration parameters
#
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON

#
# Group Replication configuration
#
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "mgr03:33061"
group_replication_group_seeds= "mgr01:33061,mgr02:33061,mgr03:33061"
group_replication_bootstrap_group= off

-- 初始化MySQL、创建用户，同第一个库 （略）

-- 启动复制 

 

CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user',SOURCE_PASSWORD='mysql' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION USER='rpl_user', PASSWORD='mysql';

-- 查看复制状态

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 | 47b589a3-b3c3-11ef-ba63-000c297e6af7 | mgr02       |        3306 | ONLINE       | SECONDARY   | 8.4.3          | XCom                       |
| group_replication_applier | 7bf19e8c-b3d1-11ef-b688-000c29e5c624 | mgr03       |        3306 | ONLINE       | SECONDARY   | 8.4.3          | XCom                       |
| group_replication_applier | e558e52f-b327-11ef-8266-000c291c3ef6 | mgr01       |        3306 | ONLINE       | PRIMARY     | 8.4.3          | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)

mysql>

-- 查询复制 
mgr01 上

mysql>  select * from performance_schema.replication_connection_status \G
*************************** 1. row ***************************
                                      CHANNEL_NAME: group_replication_applier
                                        GROUP_NAME: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
                                       SOURCE_UUID: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
                                         THREAD_ID: NULL
                                     SERVICE_STATE: ON
                         COUNT_RECEIVED_HEARTBEATS: 0
                          LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000
                          RECEIVED_TRANSACTION_SET: e558e52f-b327-11ef-8266-000c291c3ef6:1-4
                                 LAST_ERROR_NUMBER: 0
                                LAST_ERROR_MESSAGE: 
                              LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                           LAST_QUEUED_TRANSACTION: 
 LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
     LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
       LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
                              QUEUEING_TRANSACTION: 
    QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
   QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
        QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
*************************** 2. row ***************************
                                      CHANNEL_NAME: group_replication_recovery
                                        GROUP_NAME: 
                                       SOURCE_UUID: 
                                         THREAD_ID: NULL
                                     SERVICE_STATE: OFF
                         COUNT_RECEIVED_HEARTBEATS: 0
                          LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000
                          RECEIVED_TRANSACTION_SET: 
                                 LAST_ERROR_NUMBER: 0
                                LAST_ERROR_MESSAGE: 
                              LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                           LAST_QUEUED_TRANSACTION: 
 LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
     LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
       LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
                              QUEUEING_TRANSACTION: 
    QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
   QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
        QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
2 rows in set (0.01 sec)

mysql>

mgr02 上

mysql> select * from performance_schema.replication_connection_status \G
*************************** 1. row ***************************
                                      CHANNEL_NAME: group_replication_applier
                                        GROUP_NAME: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
                                       SOURCE_UUID: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
                                         THREAD_ID: NULL
                                     SERVICE_STATE: ON
                         COUNT_RECEIVED_HEARTBEATS: 0
                          LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000
                          RECEIVED_TRANSACTION_SET: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1,
e558e52f-b327-11ef-8266-000c291c3ef6:1-4
                                 LAST_ERROR_NUMBER: 0
                                LAST_ERROR_MESSAGE: 
                              LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                           LAST_QUEUED_TRANSACTION: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1
 LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2024-12-06 08:40:56.415873
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2024-12-06 08:40:56.415873
     LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2024-12-06 08:40:56.530630
       LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2024-12-06 08:40:56.530646
                              QUEUEING_TRANSACTION: 
    QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
   QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
        QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
*************************** 2. row ***************************
                                      CHANNEL_NAME: group_replication_recovery
                                        GROUP_NAME: 
                                       SOURCE_UUID: 
                                         THREAD_ID: NULL
                                     SERVICE_STATE: OFF
                         COUNT_RECEIVED_HEARTBEATS: 0
                          LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000
                          RECEIVED_TRANSACTION_SET: 
                                 LAST_ERROR_NUMBER: 0
                                LAST_ERROR_MESSAGE: 
                              LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                           LAST_QUEUED_TRANSACTION: 
 LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
     LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
       LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
                              QUEUEING_TRANSACTION: 
    QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
   QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
        QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
2 rows in set (0.00 sec)

mysql>

mgr03上 

mysql> select * from performance_schema.replication_connection_status \G
*************************** 1. row ***************************
                                      CHANNEL_NAME: group_replication_applier
                                        GROUP_NAME: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
                                       SOURCE_UUID: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
                                         THREAD_ID: NULL
                                     SERVICE_STATE: ON
                         COUNT_RECEIVED_HEARTBEATS: 0
                          LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000
                          RECEIVED_TRANSACTION_SET: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1,
e558e52f-b327-11ef-8266-000c291c3ef6:1-4
                                 LAST_ERROR_NUMBER: 0
                                LAST_ERROR_MESSAGE: 
                              LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                           LAST_QUEUED_TRANSACTION: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1
 LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2024-12-06 08:40:56.415873
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2024-12-06 08:40:56.415873
     LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2024-12-06 08:40:56.432417
       LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2024-12-06 08:40:56.432447
                              QUEUEING_TRANSACTION: 
    QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
   QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
        QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
*************************** 2. row ***************************
                                      CHANNEL_NAME: group_replication_recovery
                                        GROUP_NAME: 
                                       SOURCE_UUID: 
                                         THREAD_ID: NULL
                                     SERVICE_STATE: OFF
                         COUNT_RECEIVED_HEARTBEATS: 0
                          LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000
                          RECEIVED_TRANSACTION_SET: 
                                 LAST_ERROR_NUMBER: 0
                                LAST_ERROR_MESSAGE: 
                              LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                           LAST_QUEUED_TRANSACTION: 
 LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
     LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
       LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
                              QUEUEING_TRANSACTION: 
    QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
   QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
        QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
2 rows in set (0.00 sec)

mysql>

问题1 
group_replication_local_address    等，是mgr01:33061 或其他端口，不是mysql默认的3306 

问题2 
因为是clone的虚拟机，msyql的server_uuid是一样的，需要select uuid()生成一个后，修改，修改的文件是data目录下的auto.cnf ,而不是mysql的启动的时候使用的那个cnf文件

问题3 
mysql8.4 ，默认mysql_native_password 是disable的，如果需要启动，则需要在启动MySQL服务的时候加上--mysql-native-password=ON

问题4 
在启动mgr复制的时候，提示一下错误：
2024-12-06T12:05:35.826175Z 67 [ERROR] [MY-010584] [Repl] Replica I/O for channel 'group_replication_recovery': Error connecting to source 'rpl_user@mgr01:3306'. This was attempt 1/1, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061
需要设置为： mysql_native_password
该错误信息也可通过 select * from performance_schema.replication_connection_status \G 查询 
 

END 


http://www.kler.cn/a/427424.html

相关文章：

  • 【GESP】C++一级练习 luogu-P1035, [NOIP2002 普及组] 级数求和
  • 【opencv入门教程】9.视频加载
  • SecrureCRT设置每行的长度：
  • MySQL数据库（4）-基础-＞高阶查询
  • 乾元通渠道商中标福州市人防信息化建设项目
  • 魔改版kali分享(新增50多种渗透工具)
  • docker学习笔记（四）--DockerFile
  • 002-NoSQL介绍
  • spark3 sql优化：同一个表关联多次，优化方案
  • Web安全深度剖析
  • URL访问网址的全过程
  • [C#]利用opencvsharp 已知原图和mask掩码图像，抠出原图中人物，背景设置为透明色
  • 方案拆解 | 打击矩阵新规频出！2025矩阵营销该怎么玩？
  • 蓝桥杯2117砍竹子（简单易懂 包看包会版）
  • 常见限流算法介绍 和 Spring Cloud Sentinel使用方式
  • 企业级资源监控方案落地：Prometheus+Grafana+Export
  • 代码随想录-算法训练营day35(贪心算法05:无重叠区间,划分字母区间,合并区间)
  • oracle 数组分组
  • 电子应用设计方案-43：智能手机充电器系统方案设计
  • node.js常用的模块和中间件？