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

问题描述

环境是单主+2台Secondary节点，服务器OS版本及MySQL版本均一致。
其中一个Secondary节点的虚拟机需要迁移。
迁移前MGR状态正常，数据也均保持同步。

在迁移前手动关闭了此Secondary节点的mgr：

stop group_replication;

并且关停了mysqld：

systemctl stop mysqld

迁移后开机，ip地址等信息均不变。
然后准备开启MGR，执行:

change master to master_user='mgr',master_password='Hxxxxn@123mgr' for channel 'group_replication_recovery';
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.

排查

相关配置如下：

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="03f43914-7f38-4a00-919f-f748794c25ac"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="10.0.40.23:33061"
loose-group_replication_group_seeds="10.0.40.21:33061,10.0.40.22:33061,10.0.40.23:33061"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=on
loose-group_replication_enforce_update_everywhere_checks= off
loose-group_replication_transaction_size_limit=2147483647
group_replication_auto_increment_increment = 3

然后查看error log如下：

2025-01-23T05:22:58.912895Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error connecting to all peers. Member join failed. Local port: 33061'
2025-01-23T05:22:58.999074Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2025-01-23T05:23:01.517942Z 15 [ERROR] [MY-011640] [Repl] Plugin group_replication reported: 'Timeout on wait for view after joining group'
2025-01-23T05:23:01.518193Z 15 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'
2025-01-23T05:23:01.522680Z 20 [ERROR] [MY-010586] [Repl] Error running query, replica SQL thread aborted. Fix the problem, and restart the replica SQL thread with "START REPLICA". We stopped at log 'FIRST' position 0
2025-01-23T05:23:01.523253Z 15 [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'

其中MY-011735错误出现了很多次，内容均一样，这里就不重复写了。

于是根据网上资料和大模型的回答进行排障。

1.开始怀疑是连接问题，于是检查了防火墙和SELinux，也均关闭，检查网络33061端口也未有限制。本地端口也没冲突。

2.怀疑是不是主节点binlog被自动清理掉了，发现也并没有此情况。

3.配置文件也没有问题，没做过修改，后续重新部署的时候使用相同配置文件也成功了。

4.检查组中另两个成员没有处于错误状态，均为Online。

5.也未启用SSL。

6.又怀疑是关停MGR后进新事务了，但看了下此前出现这个问题的记录，报错会显示：

[ERROR] [MY-011526] [Repl] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 03f43914-7f38-4a00-919f-f748794c04ac:1-81391747 > Group transactions: 03f43914-7f38-4a00-919f-f748794c04ac:1-81391746, 03f43914-7f38-4a00-919f-f748794c25ac:1-7740846, eb32d345-e66a-11ee-beb0-8e3f7c640794:1-61756'

故也排除这种情况。

…
然后就没有思路了。