Mysql max_allowed_packet 大小设置

这个问题貌似大家遇到的都比较多，我简单的记录一下今天遇到的问题。

查看错误日志发现：

131124  7:47:08 [ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master ( server_errno=1236)
131124  7:47:09 [ERROR] Slave I/O: 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', Error_code: 1236
131124  7:47:09 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.001450', position 1044070969
131124 10:30:17 [Note] Error reading relay log event: slave SQL thread was killed
131124 10:30:20 [Note] Slave I/O thread: connected to master 'xxxx@192.168.101.210:3306',replication started in log 'mysql-bin.001450' at position 1044070969
131124 10:30:20 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.001450' at position 1044070867, relay log './hostname-relay-bin.004449' position: 1044071013

比较熟悉的1236错误。

通过查询master 与  slave的参数设置:

max_allowed_packet

slave:

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

20M.

master:上执行此命令

1M.

异常中的错误提示也非常明显：

131124  7:47:08 [ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master ( server_errno=1236)

通过设置global属性

mysql> set global max_allowed_packet = 32*1024*1024
    -> ;
Query OK, 0 rows affected (0.00 sec)

将master的max_allowed_packet增大到30M,slave也同样增加的30M;

退出，重新登录，设置生效。

还有一种方式是修改my.cnf中的配置。但是这种方式的缺点是需要重启mysql。所以不采用。

增加错误日志的监控，观察几天之后，效果如何。

---------------------------------------

疑虑：

属性 max_allowed_packet的大小设置多少是比较合适？这里只是单纯的增大设置。目前还不明确合理的大小设置需要参照哪些标准。希望有经验的朋友指点一下。

对于max_allowed_packet的了解还需要继续深入一下。后续还会更新此blog.先记录一下处理的方式。

望有经验的朋友指点一下。