今天mysql復制又出錯瞭,同事在用navicat操作主數據庫,引起不同步.
1. show slave stauts\G
Master_Log_File: mysql-bin.000027
Read_Master_Log_Pos: 604734247
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 85998
Relay_Master_Log_File: mysql-bin.000027
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: 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: 588269801
Relay_Log_Space: 16550444
根據提示檢查到底是主binary log還是relay log 出錯.
1. 檢查主binary log
mysqlbinlog –no-defaults –start-position=588269801 mysql-bin.000027 > master.sql
2.檢查從relay log
mysqlbinlog –no-defaults –start-position=85998 mysqld-relay-bin.000002 > slave.sql
如果兩個log中有錯誤的話,上面命令是執行不成功的必須加上 –f 參數.
然後在master.sql中找到如下命令,其中Unknown event就是不能被識別執行的命令,relay_log就卡在這裡瞭.
# at 588269801
#110816 14:45:50 server id 1 end_log_pos 588269874 Query thread_id=7249444 exec_time=0 error_code=0
SET TIMESTAMP=1313477150/*!*/;
BEGIN
/*!*/;
# at 588269874
#110816 14:45:50 server id 1 end_log_pos 588269941
# Unknown event
# at 588269941
#110816 14:45:50 server id 1 end_log_pos 588270071
# Unknown event
# at 588270071
#110816 14:45:50 server id 1 end_log_pos 588270145 Query thread_id=7249444 exec_time=0 error_code=0
SET TIMESTAMP=1313477150/*!*/;
COMMIT
知道瞭原因就好辦瞭,直接跳過即可.
1. stop slave;
2. change master to master_host='192.168.1.13', master_user='slave', master_password='slavepasswd', MASTER_LOG_FILE='mysql-bin.000027', MASTER_LOG_POS=588270071;
3. start slave;
當然如果有多個這樣的Unknown event事件,這樣做就很麻煩瞭.
可以先把master.sql 在slave上執行,然後找到文件最後一個的postion的值, 重新設置change master 也是可行的.
不知道SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N;會不會對這種情況有效,剛開始怎麼沒想到要試一試呢?