In this howto i will describe how to fix MySql error ‘Could not parse relay log event entry’. On one of my client database replication cluster we got issue of ‘Could not parse relay log event entry’ on slaves. On investigating it found he has deleted some relay log files to free up the space. You can see the error in show slave status command on Last_Error field.

[root@server1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 179
Server version: 5.0.77 Source distribution

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.1
Master_User: mysqladmin
Master_Port: 3306
Connect_Retry: 5
Master_Log_File: mysql-bin.000019
Read_Master_Log_Pos: 18816335
Relay_Log_File: mysqld-relay-bin.000153
Relay_Log_Pos: 593
Relay_Master_Log_File: mysql-bin.000019
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB: test1
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error: 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: 18337925
Relay_Log_Space: 479619
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
1 row in set (0.00 sec)

ERROR:
No query specified

1) Get Log Filename and Position from Master.

To fix this we need two values from MySQL Master Server. i.e Master Log File Name and Master Log Position. To achieve this run following command on MySQL Master Server.

mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000020
Position: 5387330
Binlog_Do_DB: test1,test2
Binlog_Ignore_DB:
1 row in set (0.00 sec)

ERROR:
No query specified

2) Fix Slave Server

To fix slave, first you need to stop slave on it and then run change master command with require values and start slave again to start working.

mysql> slave stop
-> ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_log_file=”mysql-bin.000020″, master_log_pos=5382560;
Query OK, 0 rows affected (0.03 sec)

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

To check whether slave is OK or not, run slave status command on Slave Server.

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.252
Master_User: mysqladmin
Master_Port: 3306
Connect_Retry: 5
Master_Log_File: mysql-bin.000020
Read_Master_Log_Pos: 5459989
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 77672
Relay_Master_Log_File: mysql-bin.000020
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test1
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: 5459989
Relay_Log_Space: 77672
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: 0
1 row in set (0.00 sec)

ERROR:
No query specified

and you can see that there is no Last error and Seconds_Behind_Master value is 0 indicates we are totally sync with Master.

pixelstats trackingpixel