How to Fix MySQL Replication Error 1594: “Relay Log Read Failure”

Occasionally, MySQL replication fails because of corrupted relay binlogs, and just running the ‘start slave’ command will not solve the issue. Here’s a step-by-step method for resolving this issue.

MySQL replication on the slave has stopped. When checked the slave status, it showed Yes for Slave_IO_Running but No for Slave_SQL_Running.  It seems that the current slave’s relay log was corrupted.

Following is the full output from the display slave status command on a slave server.

mysql> SHOW SLAVE STATUS\G
***********************1. row ***********************
Slave_IO_State: Waiting for master to send event
Master_Host: *.*.*.*
Master_User: replicant
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.013184
Read_Master_Log_Pos: 319962954
Relay_Log_File: db1-relay-bin.007401
Relay_Log_Pos: 992575394
Relay_Master_Log_File: mysql-bin.012684
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1594
Last_Error: 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.
Skip_Counter: 0
Exec_Master_Log_Pos: 992575181
Relay_Log_Space: 537058497755
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1594
Last_SQL_Error: 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.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 240627 13:09:22
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

To resolve this error, erase the current binlog files on the slave and set a new position. Before proceeding, keep a note of the values for Relay_Master_Log_File and Exec_Master_Log_Pos.

Relay_Master_Log_File: mysql-bin.012684
Exec_Master_Log_Pos: 992575181

To relaunch replication, use the following commands.

1. First stop the slave using

mysql> stop slave;

2. Then reset the slave’s replication position in the master binary log.

mysql> reset slave;

3. Configure the slave to resume reading from its previous stop position.

mysql> change master to master_log_file='mysql-bin.012684', master_log_pos=992575181;

Replace the values noted before.

4. Finally start the slave

mysql> start slave;

After following the above commands, check the slave status using the command

mysql> SHOW SLAVE STATUS\G

When the Seconds_Behind_Master value equals 0, the Master and Slave servers will be synced. Note that “reset slave” will erase master.info, relay-log.info, and all relay log files, reducing the need to manually clean up the /var/lib/mysql directory. Once all commands have been executed, the slave will reconnect with the master and start to read SQL statements. 

If you’re facing the MySQL replication error 1594: “Relay Log Read Failure,”. Our team of experts can provide comprehensive support and swift solutions to resolve this issue and ensure your database runs smoothly. Contact Skynats for professional assistance and minimize downtime with our reliable support services.