MySQL主從複製報錯如下: 2018-04-11 09:11:16 2400 [Note] Slave SQL thread initialized, starting replication in log 'binlog.000042' at position 1934531, relay lo ...
MySQL主從複製報錯如下:
2018-04-11 09:11:16 2400 [Note] Slave SQL thread initialized, starting replication in log 'binlog.000042' at position 1934531, relay log '/mysql/data/relay_bin.000053' position: 1934691
2018-04-11 09:11:16 2400 [ERROR] Error in Log_event::read_log_event(): 'Event too small', data_len: 0, event_type: 0
2018-04-11 09:11:16 2400 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error
2018-04-11 09:11:16 2400 [ERROR] Slave SQL: 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. Error_code: 1594
2018-04-11 09:11:16 2400 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000042' position 1934531
三台虛擬機做的MHA主從複製,由於經常開關機導致會出現此類錯誤。如上所示:
We stopped at log 'binlog.000042' position 1934531
解決辦法是直接在slave上執行如下命令:
stop slave; reset slave all; change master to master_host='192.168.20.70',master_user='rep',master_password='rep',master_log_file='binlog.000042',master_log_pos=1934531; start slave;
就按報錯所示的binlog和pos進行主從複製的重置,之後就可以正常運行。
出現此報錯可能的原因:
如error日誌中所顯示的原因,可能是主庫的binlog損壞或者從庫的relaylog損壞,或者網路問題,導致主從複製異常中斷,此時只要重新執行change master即可。但是極端情況下由主從各自實例恢復造成的不一致無法進行恢復,只能重做主從。