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 distributionType ‘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.
about the author: Sohail Riaz
related posts
» Linux » How To Fix MySQL Error ‘Could...
15 Responses to How To Fix MySQL Error ‘Could not parse relay log event entry’
Leave a Reply
« How To Fix Access Points not shown in Fedora 13 (Broadcom driver) How To Fix Bluetooth in Fedora 15 »
I have to stop and say thank you !!!, I spent all night fretting and and part of the morning doing the same about this error.Thank you for taking the time to post this solution, it helped alot!!!!!! (and save me money from getting a tech to fix it)
wao..worked like a charm
nice..
thank u !! 🙂
Sohail:
By doing this how you guarantee that the data present on slave machine is 100% identical/similar to master mysql server. I mean how u make sure data consistency between master/slave model…
Thank you so much very good post was a big help 🙂
DON’T FOLLOW THESE INSTRUCTIONS!!!
You are telling your slave to skip all the instructions since it had an error and simply jump to the current master position. You will loose all your database changes between the time the slave hit the error and the current master position.
Unless there have been no database changes since the slave stopped replicating you will loose all those changes.
The proper way to get the master position and log is to do the following on the slave only (Note we are using the relay_master_log_file and exec_master_log_pos given to us from the slave’s status:
1. STOP SLAVE
2. SHOW SLAVE STATUS
3. Note down ‘Relay_Master_Log_File’ and ‘Exec_Master_Log_Pos’ entries.
4. RESET SLAVE
5. CHANGE MASTER TO ….. (use MASTER_LOG_FILE=relay_master_log_file and MASTER_LOG_POS=exec_master_log_pos from Step 3)
6. START SLAVE
Thank goodness!
This worked perfectly.
You’re a lifesaver… Many thanks for posting this.
These instructions are wrong…just because you have a caught-up slave in the end it is not consistent with the master since the wrong value is specified for the position to restart from (as noted by Mark on Apr 14, 2011) For everyone’s sake you should fix your instructions or take down this post since many people might find and follow this without reading all the comments.
This will make master slave replication start
but you will loose all the data which was in between the positions.
better use
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
this will skip that particular error.
You can make the counter as per your needs.
Also one can skip error by
“slave-skip-errors”
i.e. slave-skip-errors = 1062
to ask compiler to skip duplicate error.
To use these variables please understand the
functionality of replication. and root cause of the errors
occurring.
We keep checking number of records in master and slave
if any errors are occurring such that there is no data loss.
hop this helps…
🙂
The blog is erroneous in assuming that the DB is fixed just because the error no longer appears (slave is no longer a replica to the master). But Mark’s comments helped me recover from this error the proper way, i.e. not skipping all log positions. I ran “reset slave” followed by “CHANGE MASTER TO”. Thanks for the fix Mark.
[…] How To Fix MySQL Error ‘Could not parse relay log event … – 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… […]
[…] How To Fix MySQL Error ‘Could not parse relay log event … – 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… […]
[…] How To Fix MySQL Error ‘Could not parse relay log event … – on July 5 | in Linux | by Sohail Riaz | with 13 Comments. In this howto i will describe how to fix MySql error …… […]