Phenomenon:
The application service sends a write request error to one of the MGR nodes
mysql> (3101, 'Plugin instructed the server to rollback the current transaction.')
Check the synchronization error of this node group
mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+--------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+--------------+-------------+--------------+ | group_replication_applier | xxxxxxxxxxxxxxxxxxxxxxxxxxxx | mysql-01 | 3306 | ONLINE | | group_replication_applier | xxxxxxxxxxxxxxxxxxxxxxxxxxx1 | mysql-02 | 3306 | RECOVERING | | group_replication_applier | xxxxxxxxxxxxxxxxxxxxxxxxxxx9 | mysql-03 | 3306 | ONLINE | +---------------------------+--------------------------------------+--------------+-------------+--------------+ 3 rows in set (0.00 sec) mysql>
Check the log error of this node
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
Solution steps:
1. Check the synchronization status of each node and find out the abnormal nodes
mysql> select * from performance_schema.replication_group_member_stats \G
2. View the GTID status of each node
mysql> show variables like '%GTID%' \G
3. Abnormal node stops group replication
mysql> stop group_replication;
4. Change the gtid purged of the abnormal node according to the gtid purged of the normal node. If the gtid of the exception node is not empty, reset the master first;
mysql> set global gtid_purged='3cccaa6c-d580-11e7-82f6-0050568d292a:1-40....';
5. Start group replication. The gtid "purged" of the exception node will start to replicate and recover after a period of time.
mysql> start group_replication;
6. Group replication returns to normal after replication
mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+--------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+--------------+-------------+--------------+ | group_replication_applier | xxxxxxxxxxxxxxxxxxxxxxxxxxxx | mysql-01 | 3306 | ONLINE | | group_replication_applier | xxxxxxxxxxxxxxxxxxxxxxxxxxx1 | mysql-02 | 3306 | ONLINE | | group_replication_applier | xxxxxxxxxxxxxxxxxxxxxxxxxxx9 | mysql-03 | 3306 | ONLINE | +---------------------------+--------------------------------------+--------------+-------------+--------------+ 3 rows in set (0.00 sec) mysql>