Resolving MySQL Replication Failures Due to Corrupted Relay Binlogs

Sometimes MySQL replication crashes due to corrupted relay binlogs (error 1594), and it is not possible to restart it with a simple START SLAVE command. Here is a step-by-step guide on how to resolve this issue.

Step 1: Check the Current Slave Status

To check the current slave status, execute the following command:

show slave status\G

You should see a result similar to this:

************************** 1. row ***************************

        Slave_IO_State: Waiting for master to send event
           Master_Host: 192.168.194.74
           Master_User: replicator
           Master_Port: 3306
         Connect_Retry: 60
       Master_Log_File: mysql-bin.001274
   Read_Master_Log_Pos: 1045327404
        Relay_Log_File: 3_dbbackup.003821
         Relay_Log_Pos: 617884398
 Relay_Master_Log_File: mysql-bin.001273
      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: 617884110
     Relay_Log_Space: 3192816253
     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: 13
     Master_SSL_Crl: 
 Master_SSL_Crlpath: 
         Using_Gtid: No
          Gtid_IO_Pos: 
Replicate_Do_Domain_Ids: 
Replicate_Ignore_Domain_Ids: 
       Parallel_Mode: conservative

Important values to note: Relay_Master_Log_File and Exec_Master_Log_Pos. You will need them to correctly restart replication on your slave.

Step 2: Restart Replication

To restart replication, execute the following commands (replace the file and position with your actual values):

STOP SLAVE;

RESET SLAVE;

CHANGE MASTER TO master_log_file='mysql-bin.001273', master_log_pos=617884110;

START SLAVE;

Warning: RESET SLAVE will remove all relay logs and replication settings. Ensure you have the correct values for master_log_file and master_log_pos before running this command.

Step 3: Verify Replication Status

To check if replication is working again, execute the command:

show slave status\G

Before considering your slave as synchronized, check the value of the Seconds_Behind_Master parameter from the status command. In our case, the value was (5187 seconds):