MySQL Replication Error with Relay log read failure Last_Errno: 1594()

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.

Recently I found this error with mysql replication on the slave server and the replication was broken. To view the slave status run show slave status.

mysql> show slave status \G;

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

Slave_IO_State:

Master_Host: 10.37.6.138

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000428

Read_Master_Log_Pos: 321127513

Relay_Log_File: relay-bin.001203

Relay_Log_Pos: 900979739

Relay_Master_Log_File: mysql-bin.000426

Slave_IO_Running: No

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: 900979593

Relay_Log_Space: 2468708504

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: 1

1 row in set (0.00 sec)

To fix this error, current binlog files on slave should be discarded and set new position. Before setting new binlog position note down Relay_Master_Log_File and Exec_Master_Log_Pos values. Note down these values after stoping slave.

Steps: 

mysql> stop slave;

mysql> show slave status \G;

note down, Relay_Master_Log_File: mysql-bin.000426 and Exec_Master_Log_Pos: 900979593

Reset slave so that the salve forget its replication position in the master's binary log

mysql> reset slave;

Change slave to start reading from stopped position

mysql> change master to master_log_file='mysql-bin.000426', master_log_pos=900979593;

Then Start slave

mysql> start slave;

Check the slave status, 

mysql> show slave status \G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 10.37.6.138

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000428

Read_Master_Log_Pos: 401489223

Relay_Log_File: relay-bin.000004

Relay_Log_Pos: 556888345

Relay_Master_Log_File: mysql-bin.000427

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 556888199

Relay_Log_Space: 1475232050

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: 7231

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

1 row in set (0.00 sec)