mysql复制出错怎么办

42次阅读

共计 2141 个字符，预计需要花费 6 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下 mysql 复制出错怎么办，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

mysql 复制又出错了, 同事在用 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

根据提示检查到底是主 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 中有错误的话, 上面命令是执行不成功的必须加上 ndash;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; 会不会对这种情况有效, 刚开始怎么没想到要试一试呢?