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

主從同步功能多任用於多台服務器之間數據的一個傳輸了，在此小編今天主來為各位介紹一篇在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.000364

Read_Master_Log_Pos: 246924389

Relay_Log_File: mysql-relay-bin.3831269

Relay_Log_Pos: 244389572

Relay_Master_Log_File: mysql-bin.000363

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_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 | more

mysqlbinlog 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;