mysql从库io进程停止,发错如下:
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 'xxxx-master-binlog.000504' at 4212238517, the last event read from '/xxxx/xxxx/logs/binlog/xxxx-master-binlog.000504' at 123, the last byte read from '/xxxx/xxxx/logs/binlog/xxxx-master-binlog.000504' at 4212238536.'
此类报错和max_allowed_packet相关。
首先max_allowed_packet控制着主从复制过程中,一个语句产生的二进制binlog event大小,它的值必须是1024的倍数 。
出现此类错误的常见原因是:
1 该参数在主备库的配置大小不一样,主库的配置值大于从库的配置值。 从主库传递到备库的binlog event大小超过了主库或者备库的max_allowed_packet大小。
2 主库有大量数据写入时,比如在主库上执行 laod data,insert into … select 语句,产生大事务。
当主库向从库传递一个比从库的max_allowed_packet 大的packet ,从库接收该packet失败,并报 “log event entry exceeded max_allowed_packet“。
解决方法:
需要确保主备配置一样,然后尝试调大该参数的值。
set global max_allowed_packet =1024*1024*1024;
stop slave;
start slave
另外,5.6 版本中的 slave_max_allowed_packet_size 参数控制slave 可以接收的最大的packet 大小,该值通常大于而且可以覆盖 max_allowed_packet 的配置, 进而减少由于上面的问题导致主从复制中断。
查看本库设置:
mysql> show variables like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| max_allowed_packet | 1073741824 |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)
已经是1g了,如果设置成2g的话,没用:
mysql> set global max_allowed_packet=2147483648;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect max_allowed_packet value: '2147483648' |
+---------+------+------------------------------------------------------------+
1 row in set (0.00 sec)
为了保证效果,可以重新登录下mysql
mysql> show variables like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| max_allowed_packet | 1073741824 |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)
因此,只能重新做从库了。