mysql主从不同步之max_allowed_packet参数设置问题

￡神魔★判官ぃ 2022-10-02 03:58 156阅读 0赞

## mysql主从不同步之max\_allowed\_packet参数设置问题 ##

服务器使用master-slave模式,突然发现slave上不同步master上的数据了。于是在slave上mysql>show slave status\\G;结果为：

mysql> show slave status\\G;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
               Slave\_IO\_State:  
                  Master\_Host: 192.168.0.236  
                  Master\_User: backupuser  
                  Master\_Port: 3306  
                Connect\_Retry: 60  
              Master\_Log\_File: mysql-bin.000032  
          Read\_Master\_Log\_Pos: 626998732  
               Relay\_Log\_File: slave-relay-bin.000069  
                Relay\_Log\_Pos: 251  
        Relay\_Master\_Log\_File: mysql-bin.000032  
             Slave\_IO\_Running: No  
            Slave\_SQL\_Running: Yes  
              Replicate\_Do\_DB:  
          Replicate\_Ignore\_DB: mysql  
           Replicate\_Do\_Table:  
       Replicate\_Ignore\_Table: qrjh.watchdog  
      Replicate\_Wild\_Do\_Table:  
  Replicate\_Wild\_Ignore\_Table:  
                   Last\_Errno: 0  
                   Last\_Error:  
                 Skip\_Counter: 1  
          Exec\_Master\_Log\_Pos: 626998732  
              Relay\_Log\_Space: 551  
              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: 1153  
                Last\_IO\_Error: Got a packet bigger than 'max\_allowed\_packet' bytes  
               Last\_SQL\_Errno: 0  
               Last\_SQL\_Error:  
1 row in set (0.00 sec)

查看slave的mysql-error log有以下信息：

120110 19:17:04 \[Note\] Slave I/O thread: connected to master ['backupuser@192.168.0.236:3306',replication][backupuser_192.168.0.236_3306_replication] started in log 'mysql-bin.0  
00032' at position 626998732  
120110 19:17:05 \[ERROR\] Error reading packet from server: Got packet bigger than 'max\_allowed\_packet' bytes ( server\_errno=2020)  
120110 19:17:05 \[ERROR\] Log entry on master is longer than max\_allowed\_packet (1049374) on slave. If the entry is correct, restart t  
he server with a higher value of max\_allowed\_packet  
120110 19:17:05 \[ERROR\] Slave I/O: Got a packet bigger than 'max\_allowed\_packet' bytes, Error\_code: 1153  
120110 19:17:05 \[Note\] Slave I/O thread exiting, read up to log 'mysql-bin.000032', position 626998732  
120110 19:18:04 \[Note\] Error reading relay log event: slave SQL thread was killed  
120110 19:18:09 \[Note\] Slave SQL thread initialized, starting replication in log 'mysql-bin.000032' at position 626998732, relay log  
 './slave-relay-bin.000065' position: 251  
120110 19:18:09 \[Note\] Slave I/O thread: connected to master ['backupuser@192.168.0.236:3306',replication][backupuser_192.168.0.236_3306_replication] started in log 'mysql-bin.0  
00032' at position 626998732  
120110 19:18:09 \[ERROR\] Error reading packet from server: Got packet bigger than 'max\_allowed\_packet' bytes ( server\_errno=2020)  
120110 19:18:09 \[ERROR\] Log entry on master is longer than max\_allowed\_packet (1049374) on slave. If the entry is correct, restart t  
he server with a higher value of max\_allowed\_packet  
120110 19:18:09 \[ERROR\] Slave I/O: Got a packet bigger than 'max\_allowed\_packet' bytes, Error\_code: 1153  
120110 19:18:09 \[Note\] Slave I/O thread exiting, read up to log 'mysql-bin.000032', position 626998732

从错误信息可以看出，是由于slave上的max\_allowed\_packet参数值比master上的max\_allowed\_packet值小。由mysql的配置文件my.cnf可以查看参数值，也可以用如下命令查看。

发现master上\[mysqld\]段的max\_allowed\_packet值为5M，而slave上\[mysqld\]段没有max\_allowed\_packet参数设置，MySQL的一个系统参数：max\_allowed\_packet，其默认值为1048576(1M)。这样slave上I/O无法操作超过1M的packet，导至同步失败。

解决办法：

在slave的配置文件my.cnf的\[mysqld\]段加上max\_allowed\_packet=5M

重启mysql后同步正常进行

[backupuser_192.168.0.236_3306_replication]: http://www.360doc.com/mailto:'%20rel=