7

How to Resolve "Error Reading Relay Log Event" After Relay Log Corrupt...

 2 years ago
source link: https://www.percona.com/blog/how-to-resolve-error-reading-relay-log-event-after-relay-log-corruption/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

How to Resolve "Error Reading Relay Log Event" After Relay Log Corruption

MySQL replication failureIn 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:

Shell
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 ):

Shell
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.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK