測試 Master / Slave 時遇到這個狀況
MySQL 5.0.45 (Master) --> MySQL 5.0.96 (Slave, 或更高版本 如 5.5、5.6)
當在 Master 上下了 CREATE TABLE、DROP TABLE xxx、FLUSH PRIVILEGES 或是 更動 "mysql" 資料庫裡頭的 table 時
會造成 slave 停擺：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.3.123
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000048
Read_Master_Log_Pos: 888456069
Relay_Log_File: box-relay-bin.000004
Relay_Log_Pos: 888345961
Relay_Master_Log_File: mysql-bin.000038 記下這個檔名
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: cccode
Replicate_Ignore_DB:
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: 888345815 記下這個數字
Relay_Log_Space: 888456413
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: 111
1 row in set (0.00 sec)
原因 還找不到
網路上還找不到類似的苦主
解決方法，就是讓 Slave 在跑 Master 過來的資料時， 跳過卡住的 sql 命令
注意上面 show slave status\G 兩個紅色的數據
在 MySQL Master 上：
user@box:/usr/local/mysql/var$ sudo ../bin/mysqlbinlog mysql-bin.000038 --start-position=888345815 |more
Password:
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 888345815
#130103 10:21:14 server id 111 end_log_pos 888345934 Query thread_id=192640087 exec_time=0 error_code=0
use mysql/*!*/;
SET TIMESTAMP=1357179674/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=1828724992/*!*/;
UPDATE user SET Reload_priv='n' WHERE Host='192.168.3.124'/*!*/; 就是這行卡住 slave，要跳過這行命令
# at 888345934 記下這個數字
#130103 10:21:24 server id 111 end_log_pos 888345962 Intvar
SET INSERT_ID=3377138/*!*/;
# at 888345962
#130103 10:21:24 server id
在 MySQL Slave 上：
mysq> stop slave;
mysq> reset slave;
mysq> change master to master_log_file='mysql-bin.000038',master_log_pos=888345934;
mysq> start slave;
Slave 即可恢復正常
