MySQL主從同步報error 1236

wzq609發表於2018-07-06
MySql主從同步Error
【一】前言：作為MySQL的資料庫管理員經常會碰到主從同步的問題，比較常見的有error 1236的報錯。剛好最近又碰到了這個報錯，以下是整理的文件；

【二】問題說明
1、環境資訊
mysql 版本：5.7.20
作業系統版本：centeros 6.5
2、報錯資訊

點選(此處)摺疊或開啟

  1. mysql> show slave status\G;
  2. *************************** 1. row ***************************
  3.                Slave_IO_State:
  4.                   Master_Host: 192.168.1.19
  5.                   Master_User: rep
  6.                   Master_Port: 3306
  7.                 Connect_Retry: 60
  8.               Master_Log_File: mysql-bin.000123
  9.           Read_Master_Log_Pos: 221693972
  10.                Relay_Log_File: DB-02-relay-bin.000011
  11.                 Relay_Log_Pos: 320
  12.         Relay_Master_Log_File: mysql-bin.000123
  13.              Slave_IO_Running: No
  14.             Slave_SQL_Running: Yes
  15.               Replicate_Do_DB: DB02
  16.           Replicate_Ignore_DB:
  17.            Replicate_Do_Table:
  18.        Replicate_Ignore_Table:
  19.       Replicate_Wild_Do_Table:
  20.   Replicate_Wild_Ignore_Table:
  21.                    Last_Errno: 0
  22.                    Last_Error:
  23.                  Skip_Counter: 0
  24.           Exec_Master_Log_Pos: 221693972
  25.               Relay_Log_Space: 535
  26.               Until_Condition: None
  27.                Until_Log_File:
  28.                 Until_Log_Pos: 0
  29.            Master_SSL_Allowed: No
  30.            Master_SSL_CA_File:
  31.            Master_SSL_CA_Path:
  32.               Master_SSL_Cert:
  33.             Master_SSL_Cipher:
  34.                Master_SSL_Key:
  35.         Seconds_Behind_Master: NULL
  36. Master_SSL_Verify_Server_Cert: No
  37.                 Last_IO_Errno: 1236
  38.                 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; the first event 'mysql-bin.000123' at 221693972, the last event read from '/data/binlog/mysql-bin.000123' at 123, the last byte read from '/data/binlog/mysql-bin.000123' at 221693991.'
  39.                Last_SQL_Errno: 0
  40.                Last_SQL_Error:
  41.   Replicate_Ignore_Server_Ids:
  42.              Master_Server_Id: 11
  43.                   Master_UUID: b688e640-f5d3-11e7-9275-005056a675fe
  44.              Master_Info_File: /data/mysql/master.info
  45.                     SQL_Delay: 0
  46.           SQL_Remaining_Delay: NULL
  47.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
  48.            Master_Retry_Count: 86400
  49.                   Master_Bind:
  50.       Last_IO_Error_Timestamp: 180705 23:22:44
  51.      Last_SQL_Error_Timestamp:
  52.                Master_SSL_Crl:
  53.            Master_SSL_Crlpath:
  54.            Retrieved_Gtid_Set:
  55.             Executed_Gtid_Set: b688e640-f5d3-11e7-9275-005056a666fe:1
  56.                 Auto_Position: 0
  57.          Replicate_Rewrite_DB:
  58.                  Channel_Name:
  59.            Master_TLS_Version:
【三】問題原因
Slave_IO_Running: No
 Slave_SQL_Running: Yes
透過以上兩個引數可以知道，當前binlog主庫傳送到從庫的時候就報錯了。已經傳送過來的binlog是可以正常執行的。

Last_IO_Error：max_allowed_packet顯示當前引數過小需要進行增加。
max_allowed_packet控制著主從複製過程binglog event傳送的大小，一般以下兩種情況主從就會報錯
1、引數在主備庫的配置大小不一樣，主庫的配置值大於從庫的配置值。 從主庫傳遞到備庫的binlog event大小超過了主庫或者備庫的max_allowed_packet大小。
2、主庫有大量資料寫入時，比如insert into .... select 語句，產生大事務。
當主庫向從庫傳遞一個比從庫的max_allowed_packet 大的packet ，從庫接收該packet失敗，並報 “log event entry exceeded max_allowed_packet“。

關於這個引數可以檢視mysql的官方文件：
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet
Property Value
Command-Line Format --max-allowed-packet=#
System Variable max_allowed_packet
Scope Global, Session
Dynamic Yes
Type integer
Default Value 4194304
Minimum Value 1024
Maximum Value 1073741824
該引數可以在session進行修改，最小1k，最大1GB,預設4M

【四】解決方法

點選(此處)摺疊或開啟

  1. 引數檢視的方法
  2. mysql> show VARIABLES like '%max_allowed_packet%';
  3. +--------------------------+------------+
  4. | Variable_name | Value |
  5. +--------------------------+------------+
  6. | max_allowed_packet | 1073741824 |
  7. | slave_max_allowed_packet | 1073741824 |
  8. +--------------------------+------------+
  9. 2 rows in set (0.00 sec)

  11. 不重啟資料庫的修改方法，但是修改之後要重新登陸mysql
  1. set global max_allowed_packet =512*1024*1024

  3. 重啟資料庫的修改方法
  4. 或者修改my.cnf引數
  5. [server]
    slave_max_allowed_packet = 512M

修改完之後，重新進行stop slave，再start slave

但是這只是其中的一個情況，最近遇到了一個資料庫發現max_allowed_packet引數已經進行了設定，但是無論如何主從也都不會同步。
最後發現由於使用者的誤操作，導致master_log_pos位置錯了，需要重新調整。
change master to master_log_file='mysql-bin.006738', master_log_pos=107;

總結：經過一頓折騰總算搞定了，有時候報錯資訊很明顯，但是怎麼解決都處理不了，這時候管理員的經驗就體現出來了。還有mysql這種開源的產品後續報錯的提示需要更清楚一點，管理起來也會輕鬆很多。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12679300/viewspace-2157460/，如需轉載，請註明出處，否則將追究法律責任。

相關文章