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.
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
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.
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
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.