[MySQL] 记一次MGR组复制GTID（1236）异常的解决

今天药忘吃喽~ 2022-05-08 06:16 299阅读 0赞

## 现象： ##

应用服务向MGR其中一个节点发起写请求报错

mysql> (3101, 'Plugin instructed the server to rollback the current transaction.')

检查该节点组同步报错

检查该节点日志报错

2018-10-09T14:51:14.297753+08:00 63 [ERROR] Error reading packet from server for channel 'group_replication_recovery': The slave is connecting using CH1, but the master has purged binary logs containing GTIDs that the slave requires. (server_errno=1236)
    2018-10-09T14:51:14.297851+08:00 63 [ERROR] Slave I/O for channel 'group_replication_recovery': Got fatal error 1236 from master when reading data fromusing CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.', Error_code: 1236
    2018-10-09T14:52:14.341067+08:00 90 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefsing the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
    2018-10-09T14:52:14.857120+08:00 90 [ERROR] Error reading packet from server for channel 'group_replication_recovery': The slave is connecting using CH1, but the master has purged binary logs containing GTIDs that the slave requires. (server_errno=1236)
    2018-10-09T14:52:14.857185+08:00 90 [ERROR] Slave I/O for channel 'group_replication_recovery': Got fatal error 1236 from master when reading data fromusing CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.', Error_code: 1236

## 解决步骤： ##

1. 查看各节点同步状态，找出异常的节点

mysql> select * from performance_schema.replication_group_member_stats \G

2. 查看各节点GTID状态

mysql> show variables like '%GTID%' \G

3. 异常节点停止组复制

mysql> stop group_replication;

4. 根据正常节点的gtid\_purged，更改异常节点的gtid\_purged。如果异常节点的gtid\_purged非空，先要reset master;

mysql> set global gtid_purged='3cccaa6c-d580-11e7-82f6-0050568d292a:1-40....';

5. 启动组复制，异常节点的gtid\_purged会开始复制，一段时间复制完成即可恢复。

mysql> start group_replication;

6. 完成复制后组复制恢复正常