MySQL Group Replication – Adding 4th Node Stuck at RECOVERING

mysql-5.7replication

Environment:

OS: Debian 9

MySQL: 5.7.23 (Running in group replication cluster)

Issue:

The cluster is running fine with 3 mysql nodes in group replication and all are working fine.
While adding a 4th node in the group replication cluster, the 4th node is able to connect to the master and replication starts, however, the 4th node always shows as "RECOVERING".
I checked the logs but there was no error in the logs.

Since the 4th mysql node is able to connect to the other members and able to replicate the data, I am sure that there is no issue in the mysql config.

One of the suspicion is that it is somewhere related to the binlog events. If I check the count of 'SHOW BINGLOG EVENTS', it shows almost 141957 rows, however, the other nodes are having haardly 3-4 rows. 

mysql> SHOW BINLOG EVENTS;
141957 rows in set (10.74 sec)

Please let me know how can I add the 4th node in mysql group replication cluster.

Best Answer

Finally, I found the issue and fixed it. The details are provided below:

1) The issue was happening because of the GTIDs executed in the current set of group replication nodes and the binlog configurations. Be default, the binlog expiration days in the current MySQL config was set to 0 and it means, it can store lot of binlog files which are basically used for row transactions and group replication. I purged the old binlog files and set the binlog expiration days to '3'. The commands used for that purpose.

mysql> PURGE BINARY LOGS TO 'binlog.000105';
mysql> SET GLOBAL expire_logs_days = 3;

2) After running the above commands on the MASTER server, I deleted all the old binlog files and now the replication should take long time. However, after purging the old binlog files, the MASTER cannot set the other nodes as the node connections to MASTER events were being purged which were in the older binlog files. Hence, we need to do some manual tasks.

3) Take the mysqldump of all databases from the current MASTER and restore it on the 4th mysql node you want to add.

# mysqldump --all-databases --single-transaction --triggers --routines --host=127.0.0.1 --port=mysql_port --user=username --password=password > dump.sql
# mysql -u root -ppassword < dump.sql

4) Once the restore gets completed, make sure that replication configs are already done in mysql configurations of all the nodes and then restart the node.

5) Restart replication on the 4th node and then replication should gets complete in few minutes or hours (Depends on the database size)

mysql> START GROUP_REPLICATION

6) Once the 4th node shows ONLINE, the node is part of the group replication cluster and can be used for other purposes.

Related Question