Mysql中max_allowed_packet限制導致主從同步出錯

Mysql主從運行有一段時間了，沒有出過什麼問題。但最近接著出了兩次問題，記錄下方便後面排查！

Slave_IO_Running和Slave_SQL_Running均為YES，主從同步出錯

首先還是確認下各伺服器狀態。查看主庫狀態正常，binlog position一直在變，進程狀態也正常。

mysql> show master status;+------------------+-----------+--------------+------------------+| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+-----------+--------------+------------------+| mysql-bin.000364 | 232554068 |              |                  |+------------------+-----------+--------------+------------------+ mysql> show processlist;+-------------+----------+-----------------------------------------------------------------------------+| Command     | Time     | State                                                                       |+-------------+----------+-----------------------------------------------------------------------------+| Connect     | 14536445 | Slave has read all relay log; waiting for the slave I/O thread to update it || Binlog Dump |    22459 | Master has sent all binlog to slave; waiting for binlog to be updated       |+-------------+----------+-----------------------------------------------------------------------------+

查看重庫狀態，整體上看重庫只是有延遲。

mysql> show slave status\G; Master_Log_File: mysql-bin.000364Read_Master_Log_Pos: 246924389Relay_Log_File: mysql-relay-bin.3831269Relay_Log_Pos: 244389572Relay_Master_Log_File: mysql-bin.000363Slave_IO_Running: YesSlave_SQL_Running: YesSeconds_Behind_Master: 23423 mysql> show processlist;+---------+-------+-----------------------------------------------------------------------------+------------------+| Command | Time  | State                                                                       | Info             |+---------+-------+-----------------------------------------------------------------------------+------------------+| Connect | 22800 | Waiting for master to send event                                            | NULL             || Connect |    99 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |+---------+-------+-----------------------------------------------------------------------------+------------------+

但等一段時間查看重庫卻一直不更新，重啟後Seconds_Behind_Master為0，Slave_IO_Running和Slave_SQL_Running狀態均為YES。確認了Master_Host、Master_User等參數，也匹配了Master_Server_Id都是正常的。在網上也查到了SQL_SLAVE_SKIP_COUNTER來跳過一步操作，但因為對資料完整性要求比較高，擔心產生資料異常而不敢操作。於是到此基本上就沒轍了。

等一天還找不到就打算重做了，但重做也不是辦法，總得找到問題，資料比較多也不可能每次去重做。之前查看過Binlog沒有明顯發現，於是還是得再去查看下Binlog看能不能發現什嗎？

mysqlbinlog mysql-relay-bin.3831269 --start-position=244389572 --stop-position=246924461 | moremysqlbinlog mysql-relay-bin.3831269 --start-datetime="2014-08-07 21:30:00" --stop-datetime="2014-08-07 21:35:00" --base64-output=decode-rows -v | more

binlog基於行的複製帶上了--base64-output=decode-rows -v參數。

慢慢的還真的發現了點東西，發現有執行很多的刪除語句，當通過wc統計時發現竟然有70多萬。在通過業務查看是有執行一條SQL，刪除表中的所有記錄，資料太多，此時查看主從這個表的記錄，主庫為空白，重庫記錄全在，那可能就是這個原因導致的。該操作可以跳過，於是嘗試跳過之：

mysql>slave stop;mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;mysql>slave start;

跳過後Mysql恢複正常，最後手動清空重庫中該表的資料。至於為什麼這個大的刪除導致重庫停止，還有待深究。

max_allowed_packet限制導致主從同步出錯

產生的原因也是執行了一個較大的更新，往資料庫中更新幾十兆的資料（可見更新的不合理），導致主從同步出錯，查看重庫狀態顯示

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:
'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master'

有明顯的錯誤描述好查很多，描述上說增大主庫的max_allowed_packet。

max_allowed_packet

mysql 服務是通過網路包來傳輸資料的(通訊資訊包是指發送至MySQL伺服器的單個SQL語句或發送至用戶端的單一行),mysql協議能夠識別的資料包的大小是由max_allowed_packet控制的。當MySQL用戶端或mysqld伺服器收到大於max_allowed_packet位元組的資訊包時,將發出“log event entry exceeded max_allowed_packet;”錯誤,並關閉串連。就像此次主從複製遇到的，IO 進程從主庫擷取日誌，但是單個日誌中的sql 大小超過了max_allowed_packet的限制，於是報錯,IO thread 進程停止，sql thread 顯示為yes。 對於用戶端,如果通訊資訊包過大,在執行查詢期間,可能會遇到“丟失與MySQL伺服器的串連”錯誤。

停止重庫，主從都調整下，然後啟動重庫即可！

stop slave;set global max_allowed_packet=1035543552;start slave;