MySQL5.1主从同步出现Relay log read failure错误解决方法
众所周知MySQL5.1的Replication是比较烂的。MySQL的每一个版本更新关于同步方面每次都是可以看到一大堆。但MySQL5.1性能是比较突出的。所以经不住诱惑使用MySQL5.1。所以也要经常遇到一些Bug。如:
mysql>showslavestatus\G ***************************1.row*************************** Slave_IO_State:Waitingformastertosendevent Master_Host:192.168.10.118 Master_User:repl_wu Master_Port:3306 Connect_Retry:30 Master_Log_File:mysql-bin.005121 Read_Master_Log_Pos:64337286 Relay_Log_File:relay-bin.003995 Relay_Log_Pos:18446697137031827760 Relay_Master_Log_File:mysql-bin.005121 Slave_IO_Running:Yes Slave_SQL_Running:No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno:1594 Last_Error:Relaylogreadfailure:Couldnotparserelaylogevententry.Thepossiblereasonsare:themaster'sbinarylogiscorrupted(youcancheckthisbyrunning'mysqlbinlog'onthebinarylog),theslave'srelaylogiscorrupted(youcancheckthisbyrunning'mysqlbinlog'ontherelaylog),anetworkproblem,orabuginthemaster'sorslave'sMySQLcode.Ifyouwanttocheckthemaster'sbinarylogorslave'srelaylog,youwillbeabletoknowtheirnamesbyissuing'SHOWSLAVESTATUS'onthisslave. Skip_Counter:0 Exec_Master_Log_Pos:4 Relay_Log_Space:64337901 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 Master_SSL_Verify_Server_Cert:No Last_IO_Errno:0 Last_IO_Error: Last_SQL_Errno:1594 Last_SQL_Error:Relaylogreadfailure:Couldnotparserelaylogevententry.Thepossiblereasonsare:themaster'sbinarylogiscorrupted(youcancheckthisbyrunning'mysqlbinlog'onthebinarylog),theslave'srelaylogiscorrupted(youcancheckthisbyrunning'mysqlbinlog'ontherelaylog),anetworkproblem,orabuginthemaster'sorslave'sMySQLcode.Ifyouwanttocheckthemaster'sbinarylogorslave'srelaylog,youwillbeabletoknowtheirnamesbyissuing'SHOWSLAVESTATUS'onthisslave. 1rowinset(0.00sec)
从上面可以看到是中继日值或是Master上的日值出问题了。
首先如果是中继日值坏掉,那只需要找到同步的时间点,然后重新同步,这样就可以有新的中继日值了。如果Master上的日值坏了就麻烦了。
从经验来看,这是中继日值出问题了。处理方法:
需要找到同步的点。
日值为:Master_Log_File:mysql-bin.005121,Relay_Master_Log_File:mysql-bin.005121以Relay_Master_Log_File为准,Master_Log_File为参考。
日值执行时间点:
Exec_Master_Log_Pos:4
那么现在就可以:
mysql>stopslave; mysql>changemastertoMaster_Log_File='mysql-bin.005121',Master_Log_Pos=4; mysql>startslave; mysql>showslavestatus\G;
进行确认。
建议:
在使用MySQL-5.1.36以下的版本的同学,请尽快升级到MySQL-5.1.40&MySQL-5.1.37sp1