mysql復(fù)制出錯(cuò)怎么辦

146次閱讀

共計(jì) 2141 個(gè)字符，預(yù)計(jì)需要花費(fèi) 6 分鐘才能閱讀完成。

丸趣 TV 小編給大家分享一下 mysql 復(fù)制出錯(cuò)怎么辦，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

mysql 復(fù)制又出錯(cuò)了, 同事在用 navicat 操作主, 引起不同步.
1. show slave stauts\G
Master_Log_File: -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

根據(jù)提示檢查到底是主 binary log 還是 relay log 出錯(cuò).
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
如果兩個(gè) log 中有錯(cuò)誤的話, 上面命令是執(zhí)行不成功的必須加上 ndash;f 參數(shù).
然后在 master.sql 中找到如下命令, 其中 Unknown event 就是不能被識別執(zhí)行的命令,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;
當(dāng)然如果有多個(gè)這樣的 Unknown event 事件, 這樣做就很麻煩了.
可以先把 master.sql 在 slave 上執(zhí)行, 然后找到文件最后一個(gè)的 postion 的值, 重新設(shè)置 change master 也是可行的.

不知道 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N; 會不會對這種情況有效, 剛開始怎么沒想到要試一試呢?