mysql主从不同步之max_allowed_packet参数设置问题
服务器使用master-slave模式,突然发现slave上不同步master上的数据了。于是在slave上mysql>show slave status\G;结果为:
mysql> show slave status\G;
*************************** 1. row ***************************
Master_SSL_Verify_Server_Cert: No
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
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
+--------------------+-----------+
| 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后同步正常进行