主從同步功能多任用于多臺服務器之間數據的一個傳輸了,在此小編今天主來為各位介紹一篇在max_allowed_packet限制導致主從同步出錯問題解決方法.

Mysql主從運行有一段時間了,沒有出過什么問題,但最近接著出了兩次問題,記錄下方便后面排查.

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

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

1. mysql> show master status; 
2.  
3. +------------------+-----------+--------------+------------------+ 
4.  
5. | File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | 
6.  
7. +------------------+-----------+--------------+------------------+ 
8.  
9. | mysql-bin.000364 | 232554068 |              |                  | 
10.  
11. +------------------+-----------+--------------+------------------+ 
12. --Vevb.com 
13. mysql> show processlist; 
14.  
15. +-------------+----------+-----------------------------------------------------------------------------+ 
16.  
17. | Command     | Time     | State                                                                       | 
18.  
19. +-------------+----------+-----------------------------------------------------------------------------+ 
20.  
21. | Connect     | 14536445 | Slave has read all relay log; waiting for the slave I/O thread to update it | 
22.  
23. | Binlog Dump |    22459 | Master has sent all binlog to slave; waiting for binlog to be updated       | 
24.  
25. +-------------+----------+-----------------------------------------------------------------------------+ 

查看重庫狀態,整體上看重庫只是有延遲.

1. mysql> show slave status/G; 
2.  
3. Master_Log_File: mysql-bin.000364 
4.  
5. Read_Master_Log_Pos: 246924389 
6.  
7. Relay_Log_File: mysql-relay-bin.3831269 
8.  
9. Relay_Log_Pos: 244389572 
10.  
11. Relay_Master_Log_File: mysql-bin.000363 
12.  
13. Slave_IO_Running: Yes 
14.  
15. Slave_SQL_Running: Yes 
16.  
17. Seconds_Behind_Master: 23423 
18.  
19. mysql> show processlist; 
20.  
21. +---------+-------+-----------------------------------------------------------------------------+------------------+ 
22.  
23. | Command | Time  | State                                                                       | Info             | 
24.  
25. +---------+-------+-----------------------------------------------------------------------------+------------------+ 
26.  
27. | Connect | 22800 | Waiting for master to send event                                            | NULL             | 
28.  
29. | Connect |    99 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             | 
30.  
31. +---------+-------+-----------------------------------------------------------------------------+------------------+ 

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

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

1. mysqlbinlog mysql-relay-bin.3831269 --start-position=244389572 --stop-position=246924461 | more 
2.  
3. 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,刪除表中的所有記錄,數據太多,此時查看主從這個表的記錄,主庫為空,重庫記錄全在,那可能就是這個原因導致的,該操作可以跳過,于是嘗試跳過之:

1. mysql>slave stop; 
2.  
3. mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 
4.  
5. mysql>slave start; 

跳過后Mysql恢復正常,最后手動清空重庫中該表的數據,至于為什么這個大的刪除導致重庫停止,還有待深究.

max_allowed_packet限制導致主從同步出錯

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

1. Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 
2. '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服務器的連接”錯誤.

停止重庫,主從都調整下,然后啟動重庫即可.

1. stop slave; 
2.  
3. set global max_allowed_packet=1035543552; 
4.  
5. start slave;