MariaDB 複製失敗：致命錯誤 1236 - binlog 顯示“事件被截斷”

免責聲明：我不是經過認證的 DBA

我已經配置了 MariaDB Master - Slave 複製，一切正常，但現在有一個我無法通過的錯誤。

我在從伺服器上執行了以下

> show slave status\G

這是輸出

   Slave_IO_State: 
                 Master_Host: 192.168.193.116
                 Master_User: replication
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: violet-1.005377
         Read_Master_Log_Pos: 812448796
              Relay_Log_File: mariadb-relay-bin.000002
               Relay_Log_Pos: 536
       Relay_Master_Log_File: violet-1.005377
            Slave_IO_Running: No
           Slave_SQL_Running: Yes
             Replicate_Do_DB: violet
         Replicate_Ignore_DB: 
          Replicate_Do_Table: 
      Replicate_Ignore_Table: 
     Replicate_Wild_Do_Table: 
 Replicate_Wild_Ignore_Table: 
                  Last_Errno: 0
                  Last_Error: 
                Skip_Counter: 0
         Exec_Master_Log_Pos: 812448796
             Relay_Log_Space: 836
             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: 1236
               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 'violet-1.005377' at 812448796, the last event read from 'violet-1.005377' at 812448796, the last byte read from 'violet-1.005377' at 812448815.'
              Last_SQL_Errno: 0
              Last_SQL_Error: 
 Replicate_Ignore_Server_Ids: 
            Master_Server_Id: 1
              Master_SSL_Crl: 
          Master_SSL_Crlpath: 
                  Using_Gtid: No
                 Gtid_IO_Pos: 
     Replicate_Do_Domain_Ids: 
 Replicate_Ignore_Domain_Ids: 
               Parallel_Mode: conservative

master和slave上的max_allowed_pa​​cket都設置為1G

目前master狀態如下

顯示主狀態

+-----------------+-----------+--------------+------------------+
| File            | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+-----------+--------------+------------------+
| violet-1.005380 | 338731974 | violet       |                  |
+-----------------+-----------+--------------+------------------+

使用以下命令檢查 binlog

> mysqlbinlog --start-position=812448815 /var/lib/mysql/violet-1.005377 --hexdump

產生下面的輸出

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190611 23:26:18 server id 1  end_log_pos 249 
# Position
#           |Timestamp   |Type |Master ID   |Size        |Master Pos  |Flags
#        4  |8a 2a 00 5d |0f   |01 00 00 00 |f5 00 00 00 |f9 00 00 00 |00 00
#
#       17  04 00 31 30 2e 31 2e 33  35 2d 4d 61 72 69 61 44  |..10.1.35-MariaD|
#       27  42 00 6c 6f 67 00 00 00  00 00 00 00 00 00 00 00  |B.log...........|
#       37  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
#       47  00 00 00 00 00 00 00 00  13 38 0d 00 08 00 12 00  |.........8......|
#       57  04 04 04 04 12 00 00 dd  00 04 1a 08 00 00 00 08  |................|
#       67  08 08 02 00 00 00 0a 0a  0a 00 00 00 00 00 00 00  |................|
#       77  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
#       87  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
#       97  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
#       a7  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
#       b7  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
#       c7  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
#       d7  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
#       e7  00 00 00 00 00 00 00 00  00 04 13 04 00 00 6c ea  |..............l.|
#       f7  fc 09                                             |..|
#
# Event:    Start: binlog v 4, server v 10.1.35-MariaDB created 190611 23:26:18
BINLOG '
iioAXQ8BAAAA9QAAAPkAAAAAAAQAMTAuMS4zNS1NYXJpYURCAGxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA3QAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQAAGzq/Ak=
'/*!*/;
ERROR: Error in Log_event::read_log_event(): 'Event truncated', data_len: 1701407845, event_type: 32
ERROR: Could not read entry at offset 812448815: Error in log format or read error.
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

如果我執行 CHANGE MASTER TO …. MASTER_LOG_POSITION=812448815 然後我得到一個相同的錯誤，但位置將類似於 812448834 （基於我從初始錯誤開始執行此操作 4 次並且它一直失敗的事實，最後一個日誌位置報告為 812448796）

如何防止發生此錯誤並讓我的複制再次工作，而無需執行完整備份/還原和重置複製過程？

我清除了日誌，重新轉儲數據庫，恢復到從屬伺服器並再次啟動複製服務，複製工作順利。

一陣子。

然後我在幾個小時後遇到了同樣的問題，所以決定忘記複製，只使用相關數據表的定期備份。不理想，但它有效。

引用自：https://dba.stackexchange.com/questions/240395