最近發(fā)現(xiàn)MySQL備份服務(wù)器竟然出現(xiàn)數(shù)據(jù)不同步的問題。
報(bào)錯(cuò)信息：
mysql> show slave status \G
*************************** 1. row ***************************
? ? ? ? ? ? ? Slave_IO_State: Waiting for master to send event
? ? ? ? ? ? ? ? ? Master_Host: 192.168.11.202
? ? ? ? ? ? ? ? ? Master_User: 223backup
? ? ? ? ? ? ? ? ? Master_Port: 3306
? ? ? ? ? ? ? ? Connect_Retry: 60
? ? ? ? ? ? ? Master_Log_File: binlog.000021
? ? ? ? ? Read_Master_Log_Pos: 705618329
? ? ? ? ? ? ? Relay_Log_File: localhost-relay-bin.002879
? ? ? ? ? ? ? ? Relay_Log_Pos: 515550
? ? ? ? Relay_Master_Log_File: binlog.000021
? ? ? ? ? ? Slave_IO_Running: Yes
? ? ? ? ? ? Slave_SQL_Running: No
? ? ? ? ? ? ? Replicate_Do_DB:
? ? ? ? ? Replicate_Ignore_DB: mysql,test
? ? ? ? ? Replicate_Do_Table:
? ? ? Replicate_Ignore_Table:
? ? ? Replicate_Wild_Do_Table:
? Replicate_Wild_Ignore_Table:
? ? ? ? ? ? ? ? ? Last_Errno: 1594
? ? ? ? ? ? ? ? ? Last_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.
? ? ? ? ? ? ? ? Skip_Counter: 0
? ? ? ? ? Exec_Master_Log_Pos: 676992028
? ? ? ? ? ? ? Relay_Log_Space: 29206302
? ? ? ? ? ? ? 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: 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.
? Replicate_Ignore_Server_Ids:
? ? ? ? ? ? Master_Server_Id: 2
1 row in set (0.00 sec)
報(bào)錯(cuò)信息：?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.
二、原因分析
? ? ? 報(bào)錯(cuò)信息為從庫“無法讀取relay log 里的條目”，可能原因?yàn)閙aster庫的binglog錯(cuò)誤，或slave庫的中繼日志錯(cuò)誤。或者為網(wǎng)絡(luò)問題及bug原因。
? ? ? 一般是由于網(wǎng)絡(luò)故障或slave庫壓力過大，導(dǎo)致relay-log格式錯(cuò)誤造成的。找到當(dāng)前已經(jīng)同步的時(shí)間點(diǎn)，重新設(shè)置主從同步，就會(huì)產(chǎn)生新的中繼日志，恢復(fù)正常
三、問題處理
從"show? slave ?status\G"的輸出中，找到如下信息：
Relay_Master_Log_File:? binlog.000021 ? ? //slave庫已讀取的master的binlog
Exec_Master_Log_Pos:?676992028 ? ? ? ? ? //在slave上已經(jīng)執(zhí)行的position位置點(diǎn)
四.問題解決：
停掉slave，以slave已經(jīng)讀取的binlog文件，和已經(jīng)執(zhí)行的position為起點(diǎn)，重新設(shè)置同步。會(huì)產(chǎn)生新的中繼日志，問題解決
mysql>stop slave;
mysql>change master to master_log_file='binlog.000021' , master_log_pos=676992028?
//在slave上已經(jīng)執(zhí)行的position位置點(diǎn);
mysql>start slave;
四、驗(yàn)證結(jié)果
再次查看，錯(cuò)誤已經(jīng)解決，slave 開始追 master 的日志
mysql>show ?slave status\G
Exec_Master_Log_Pos: 702086896 ? ? ? ?//slave上已經(jīng)執(zhí)行的position已經(jīng)變化
Seconds_Behind_Master: 79006? ? ? ? ? ? //slave ?落后主庫的時(shí)間，單位秒
過幾秒鐘，再次查看。離與master同步更近了
mysql>show ?slave status\G
Exec_Master_Log_Pos: 703743817
Seconds_Behind_Master: 60672
