问题由来

今天要使用之前搭建的虚拟机里的MySQL MGR集群做测试，先检查集群状态，咦，集群主节点（node3）状态异常了！
以下是处理过程，记录之~

数据库：mysql 8.0.40

处理过程

检查集群状态

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 | 7b247d00-c2a9-11ef-b266-005056285fbc | node3       |        3306 | UNREACHABLE  | PRIMARY     | 8.0.40         | XCom                       |
| group_replication_applier | 803d29d4-c2a9-11ef-96f1-0050563b2ef8 | node1       |        3306 | ONLINE       | SECONDARY   | 8.0.40         | XCom                       |
| group_replication_applier | 831550eb-c2a9-11ef-b4f8-0050563a6146 | node2       |        3306 | ONLINE       | SECONDARY   | 8.0.40         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
复制

可以看到，node3(主节点)的状态：UNREACHABLE

node3(主节点)上操作

mysql> stop group_replication; 
Query OK, 0 rows affected (3.29 sec)

mysql> start group_replication;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
复制

可以看到，group_replication无法启动。

检查node3(主节点)的数据库日志

2024-12-27T20:34:07.756242+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error connecting to all peers. Member join failed. Local port: 33061'
2024-12-27T20:34:07.836865+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2024-12-27T20:34:10.707113+08:00 23 [ERROR]  [MY-011735]  [Repl] Plugin group_replication reported: 'Timeout on wait for view after joining group'
2024-12-27T20:34:10.763611+08:00 23 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'

复制

node3(主节点)上操作

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.12 sec)

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

复制

可以看到，group_replication成功启动。

其他节点上操作

mysql> stop group_replication; 
Query OK, 0 rows affected (3.29 sec)

mysql> start group_replication;
Query OK, 0 rows affected, 1 warning (5.44 sec)

mysql> 

复制

其他节点group_replication成功启动。

再次检查集群状态

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 | 7b247d00-c2a9-11ef-b266-005056285fbc | node3       |        3306 | ONLINE       | PRIMARY   | 8.0.40         | XCom                       |
| group_replication_applier | 803d29d4-c2a9-11ef-96f1-0050563b2ef8 | node1       |        3306 | ONLINE       | SECONDARY     | 8.0.40         | XCom                       |
| group_replication_applier | 831550eb-c2a9-11ef-b4f8-0050563a6146 | node2       |        3306 | ONLINE       | SECONDARY   | 8.0.40         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)
复制

集群状态恢复正常。

总结

主节点上启动group_replication的时候，要按以下步骤操作：

mysql> set global group_replication_bootstrap_group = on;
mysql> start group_replication; 
mysql> set global group_replication_bootstrap_group = off;
复制

问题虽然简单，但是要养成随手记录的习惯，毕竟好记性不如烂笔头~

关于作者：
专注于Oracle、MySQL、PG、OpenGauss和国产数据库的研究，热爱生活，热衷于分享数据库技术。
微信公众号：飞天online
墨天轮：https://www.modb.pro/u/15197
如有任何疑问，欢迎大家留言，共同进步~~~