Relay log read failure

MySQL 5.7 – Relay log read failure: Could not parse relay log event entry.

Error:

Yesterday, my replication (mysql 5.7) was getting error, i run show slave status; on the slave and getting a response as below:

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.

Causes:

There is only 2 possible problems, are:

The master’s binary log is corrupted, or
The slave’s relay log is corrupted

Solution:

If master binary log = corrupted, you have to recreate master replication
If slave relay log = corrupted, you have to reset your relay log, then reconfig CHANGE MASTER TO

Let’s Fix it!

Stop slave replication

First of all, we have to stop slave replication first. This step is required to prevent position of master binlog on the slave get changed.

STOP SLAVE;

Save result of ‘SHOW SLAVE STATUS’ on slave replication

Run query below to get all slave configuration:

SHOW SLAVE STATUS;

After executing, save the result to csv. This step is mandatory. We will focus on these fields on the next step:

Relay_Master_Log_File
Relay_Log_File
Exec_Master_Log_Pos

Check binlog on master

On master, check binlog under /var/log/mysql/

cd /var/log/mysql/
mysqlbinlog mysql-bin-changelog.267035  | grep ERROR

Where mysql-bin-changelog.267035 is value of Relay_Master_Log_File you get from .csv

If there is no error on the response, that means your binlog on the master is health (not corrupt)

If, any error? you have to rebuild master replication.

Check relaylog on slave

Move to slave, relaylog bin are stored under /var/lib/mysql/

cd /var/lib/mysql/
ls -al

The files with prefix relay-bin.xxx are the relaylog binary.

Now, we will check relaylog bin 121482, execute this command

mysqlbinlog cxxx1-relay-bin.121482 | grep ERR

Where cxxx1-relay-bin.121482 is value of Relay_Log_File on your .csv file

If you got error something like this, this means your relaylog = corrupted

Let’s fix the slave

To fix the slave, we have 2 steps..

Reset relay log (Reset Slave)
Reconfigure CHANGE MASTER TO

-Reset relay log (Reset Slave)

We need to reset slave, run command below:

RESET SLAVE;

After executing that command, your relay-bin.xxx under /var/lib/mysql/ will be deleted and reset to cxxx1-relay-bin.000001

-Reconfigure CHANGE MASTER TO

Open your .csv file, look at:

Relay_Master_Log_file: mysql-bin-changelog.267035
Exec_Master_Log_Pos: 932871

Run CHANGE MASTER TO on your slave replication:

CHANGE MASTER TO MASTER_HOST = '<your master replication server/host/IP>', 
MASTER_USER = '<your master user>, 
MASTER_PASSWORD = '<your master password>', 
MASTER_PORT = 3306,
MASTER_LOG_FILE = 'mysql-bin-changelog.267035', 
MASTER_LOG_POS = 932871;

If you get a warning, just click OK

Start your slave with this command:

START SLAVE;

Last step, check your slave, is it running without error? (Relay log read failure: Could not parse relay log event entry)

SHOW SLAVE STATUS;

If you get response like that, that means your slave is health now.

How to solve MySQL 5.7 replication error: Relay log read failure: Could not parse relay log event entry