Mysql Group Replication节点由于网络出现异常导致的错误解决方法

78 阅读 0 评论 52 点赞

我是靠谱客的博主寒冷冬日，最近开发中收集的这篇文章主要介绍Mysql Group Replication节点由于网络出现异常导致的错误解决方法，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

本来是用虚拟机安装好的4台server做的mgr，今早上来一看只剩下2台存活了，状态如下：

错误日志：

2018-05-16T11:17:09.890375Z 0 [ERROR] Plugin group_replication reported: 'Member was expelled from the group due to network failures, changing member status to ERROR.'

节点状态：

mysql>
select * from performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME
| MEMBER_ID
| MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e89b | qht131
|
3306 | ONLINE
|
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e89c | qht134
|
3306 | ONLINE
|
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

错误日志写了是由于网络出现异常导致节点被踢出的组复制，试着重新加入群组：

qht133:

mysql> start group_replication;
ERROR 3093 (HY000): The START GROUP_REPLICATION command failed since the group is already running.

好吧，先停掉这个节点的复制重新打开试下：

mysql> stop group_replication;
Query OK, 0 rows affected (7.18 sec)
mysql>
start group_replication;
Query OK, 0 rows affected (3.32 sec)

mysql>
select * from performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME
| MEMBER_ID
| MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e89b | qht131
|
3306 | ONLINE
|
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e89c | qht134
|
3306 | ONLINE
|
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e91b | qht133
|
3306 | ONLINE
|
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

就这样成功了，看下数据有没有同步过来：

mysql> select * from test_mgr;
+----+
| c1 |
+----+
|
1 |
|
2 |
|
3 |
|
4 |
+----+
4 rows in set (0.01 sec)

数据也被同步成最新的了

继续恢复qht132:

qht132:

mysql> stop group_replication;
Query OK, 0 rows affected (6.92 sec)
mysql>
start group_replication;
Query OK, 0 rows affected (3.62 sec)
mysql>
select * from performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME
| MEMBER_ID
| MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e89b | qht131
|
3306 | ONLINE
|
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e89c | qht134
|
3306 | ONLINE
|
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e90b | qht132
|
3306 | ONLINE
|
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e91b | qht133
|
3306 | ONLINE
|
+---------------------------+--------------------------------------+-------------+-------------+--------------+
4 rows in set (0.00 sec)
mysql> select * from l5m.test_mgr;
+----+
| c1 |
+----+
|
1 |
|
2 |
|
3 |
|
4 |
+----+
4 rows in set (0.00 sec)

至此所有节点都已恢复正常！