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

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 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 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可以查看参数值，也可以用如下命令查看。

mysql> show VARIABLES like '%max_allowed_packet%';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| max_allowed_packet | 1048576|
+--------------------+-----------+
1 row in set (0.00 sec)

发现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后同步正常进行