in mysql

MySQL slave log corrupted? Here’s how to fix it.

MySQL slave relay log corruption is one of the primary reason for the below replication error. Although the error could mean a master log corruption, in my case, it was due to maxed out disk usage in slave machine. Even if you’re not sure, below solution is non-destructive.

Last_IO_Errno: 1595
Last_IO_Error: Relay log write failure: could not queue event from master
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.

Solution

SHOW SLAVE STATUS\G

Record the below information from the output.

Master_Log_File: mysql-bin.007973
Read_Master_Log_Pos: 80226128
Relay_Log_File: mysql-relay-bin.000384
Relay_Log_Pos: 80226291
Relay_Master_Log_File: mysql-bin.007973
Exec_Master_Log_Pos: 80226128

In particular we’ll need Relay_Master_Log_File and Exec_Master_Log_Pos parameters.

First we want to make sure that the master log file is intact, so jump onto the master server and find the Relay_Master_Log_File (binary log files are often in /var/log/mysql or execute SHOW GLOBAL VARIABLES like '%log_bin%' to find the base path) and run the following command.

mysqlbinlog mysql-bin.007973

The log will be displayed.

Now login to slave server and run the below command on the slave relay log (relay log files are often in /var/lib/mysql or execute SHOW GLOBAL VARIABLES like '%relay_log%' to find the base path). Let us read the Relay_Log_File.

mysqlbinlog mysql-relay-bin.000384

You will likely see some errors which had caused the replication to stop.

Now we know the master log file ( Relay_Master_Log_File ) and log file position upto which slave has replicated (Exec_Master_Log_Pos). We can restart the replication from the above position in slave. Execute the below mysql commands in slave.

mysql> stop slave;
Query OK, 0 rows affected (0.14 sec)

mysql> reset slave all;
Query OK, 0 rows affected (0.43 sec)

mysql>  CHANGE MASTER TO MASTER_HOST='<master host name or ip here>', MASTER_USER='<your replication user>', MASTER_PASSWORD='<replication user password>', MASTER_LOG_FILE='mysql-bin.007973', MASTER_LOG_POS=80226128;
Query OK, 0 rows affected (0.93 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

Review your slave status periodically to check the status.

If you face any difficulties, please drop a comment below.

Write a Comment

Comment