Sometimes mysql replication crash with corrupted relay binlogs and it is not possible to restart it with simple "start slave" command. Here is step by step manual how to fix it.
To check the current slave status execute command:
show slave status\G
You should see 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 you should notice are Relay_Master_Log_File and Exec_Master_Log_Pos. You will need them to correctly restart replication on your slave.
To restart replication execute following commands:
STOP SLAVE; RESET SLAVE; CHANGE MASTER TO master_log_file='mysql-bin.001273', master_log_pos=617884110; START SLAVE;
To check if replication is working again, execute again command:
show slave status\G
Before you will call your slave as synced, check value of parameter Seconds_Behind_Master from the status command. In our case I have seen value (5187 seconds):
Seconds_Behind_Master: 5187
Within next few minutes was replication synced again with master and replication lag was 0s
Seconds_Behind_Master: 0
This is the time, when we can start using mysql slave again in the production.