In this blog, I explain how to recover from a replication failure caused by a corrupted relay log file.
MySQL replica stores data received from its source binary log in the relay log file. This file could be corrupted due to various reasons, mostly hardware failures. If this happens, replication will stop working, and the error log on the replica will have entries similar to:
2022-05-12T12:32:07.282374Z 2 [ERROR] Error in Log_event::read_log_event(): 'Event too small', data_len: 0, event_type: 0 2022-05-12T12:32:07.282386Z 2 [ERROR] Error reading relay log event for channel '': slave SQL thread aborted because of I/O ... 2022-05-12T12:32:07.282396Z 2 [ERROR] Slave SQL for channel '': Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594
Once you perform a suggested check and find that the reason for the failure was, indeed, corruption of the relay log file, you may recover replication by resetting the replica.
First, you must ensure that the source server’s binary log is not corrupted. You can do this with the help of the
mysqlbinlog
command. Simply run it on the binary log, and ensure it does not fail with an error.
To find out which binary log is current, run the command
SHOW REPLICA STATUS
(or
SHOW SLAVE STATUS
if you are running MySQL, older than 8.0.22).
Then find the value of
Relay_Source_Log_File
(
Relay_Master_Log_File
):
Relay_Source_Log_File: mysql-bin.000002
This will be the binary log from which the replica SQL thread executed the last statement.
Also, notice the value of the
Exec_Source_Log_Pos
(
Exec_Master_Log_Pos
): the latest executed position. This will be necessary for the next step.
If you are using GTIDs, you need to find the binary log that contains the last GTID in the
Executed_Gtid_Set
.
Once you ensure that the source’s binary log file is healthy, you can run the
RESET REPLICA
(
RESET SLAVE
) statement. As described at https://dev.mysql.com/doc/refman/8.0/en/reset-replica.html, “it clears the replication metadata repositories, deletes all the relay log files, and starts a new relay log file. It also resets to 0 the replication delay specified with the SOURCE_DELAY | MASTER_DELAY option of the CHANGE REPLICATION SOURCE TO statement (from MySQL 8.0.23) or CHANGE MASTER TO statement (before MySQL 8.0.23). “
Therefore you need to run the
CHANGE REPLICATION SOURCE TO
(or
CHANGE MASTER TO
) command after it. If you use position-based replication, point the replica to the
Relay_Source_Log_File
and
Exec_Source_Log_Pos
, recorded in the previous step.
For GTID-based replicas, use
SOURCE_AUTO_POSITION=1
(
MASTER_AUTO_POSITION=1
).
Conclusion
Relay log file on the replica stores changes that could be retrieved from the source server. Therefore it is safe to remove corrupted relay log files with the help of the
RESET REPLICA
statement, then allow replication to reload the data from the binary log files on the source server. Mind checking if the source server did not flush the required binary logs before performing this operation.