MySQL生产库中添加修改表字段引起主从崩溃的问题总结

上周末和开发人员对线上库中的部分表的在线DDL和update，这过程中出现了一些意料之外的问题，现将过程、分析和解决方案在这里总结一下

一、 需求背景：

要在如下表中添加字段(modified_at)并且更改默认值
table_name {

baby_comp
baby_comp_status
baby_usr
baby_ad_user
baby_camp
baby_ord
baby_acc_eva

}
每张表执行如下操作
ALTER TABLE `$table_name` ADD COLUMN `modified_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间/最后修改时间'");
更新的语句
UPDATE `baby_camp`
SET `modified_at` = FROM_UNIXTIME(updated_time + 60)
WHERE `modified_at` <= '1970-01-01 08:00:00';
二、 数据库架构
master：192.168.100.18 >主库写数据复制源
slave1:192.168.100.17 >搜索用
slave2:192.168.100.19 >查询用
slave3:192.168.100.10 >查询用
slave4:192.168.100.15 >备份用
 

三、故障的相关信息截取：
问题 1. max binlog cache 不足引起的复制崩溃 涉及从库（192.168.100.17-搜索用 和 192.168.100.15-备份用）
161009 21:42:49 [ERROR] Slave SQL: Could not execute Write_rows event on table baby.baby_delta; Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again, Error_code: 1197; Writing one row to the row-based binary log failed, Error_code: 1534; handler error HA_ERR_RBR_LOGGING_FAILED; the event's master log mysql-bin.007759, end_log_pos 3856759100, Error_code: 1197
161009 21:42:49 [Warning] Slave: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again Error_code: 1197
161009 21:42:49 [Warning] Slave: Writing one row to the row-based binary log failed Error_code: 1534
161009 21:42:49 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.007759' position 633959791
161009 21:43:48 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
161009 21:43:48 [Note] Slave I/O thread killed while reading event
161009 21:43:48 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.007760', position 301659
161009 21:43:53 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.007759' at position 633959791, relay log './serverdb01-relay-bin.009618' position: 633959937
161009 21:43:53 [Note] Slave I/O thread: connected to master '[email protected]:3306',replication started in log 'mysql-bin.007760' at position 301659

++++
解释：
报错主要是：从库上对于表baby.baby_delta的操作不能写到binlog中，多语句的事物请求更多的max_binlog_cache_szie，增加max_binlog_cache_szie大小重试
++++

问题 2. max allowed packet 不足引起的复制崩溃 涉及从库（192.168.100.15-备份用）

161009 21:42:49 [ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master (server_errno=1236) 131118
161009 21:42:49 [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

++++
解释：
报错主要是：从库读取主库的binlog的packet的大小超出了设定的max_allowed_packet大小,在主库上增加此参数的值。
++++

四、 原因分析：
首先单独操作了表：baby_ord, 此表数据量大大概4百多万的数据条目.
其中在此表上有多个触发器涉及到INSERT\UPDATE\DELETE操作,会触发将相应的数据行插入到baby_delta表中,执行完除了主从延时并没有出现其他的状况
于是过于乐观的认为余下的表没有太大的数据量,除了主从延迟,不会造成其他的问题,索性就全部放在了集中一次发布中修改.
在DBMigrate后监控SQL在主库的执行,主库正常执行完成,从库17和15出现复制崩溃.
查看变更完最后一批表后的binlog大小,其中mysql-bin.007759这一文件达到了将近9G,配置文件中限定产生的binlog文件的最大大小是1G
因为后面一批的表字段添加变更执行是一个事务,同一个事务产生的binlog不会被分配到两个binlog文件中.导致出现上述问题 1和2
事后发现babysitter_campagin 表物理文件有6G大小,其中有几个列的数据类型是text.
但为什么binlog文件会变得这么大呢？超出了限定大小？
因为主库配置的binlog的格式是mixed,由系统根据SQL的类型判断是记录row格式还是stmt格式,但默认是记录stmt格式的,那什么时候会记录
row格式呢？

1.当SQL语句是update或者delete
  row格式的缺点就是将每条数据的变化都详细的记录下来,结果就是binlog文件很大,会占用更大的binlog cache.

mysql> show master logs;
+------------------+------------+
| Log_name | File_size |
+------------------+------------+
| mysql-bin.007758 | 2514487585 |
| mysql-bin.007759 | 9107651572 |
+------------------+------------+
 
mysql> desc baby_camp;
+-----------------------------------+-----------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------------+-----------------------+------+-----+-------------------+-----------------------------+
| content | mediumtext | YES | | NULL | |
| tweet_url | text | YES | | NULL | |
| note | text | YES | | NULL | |
| requirement | text | YES | | NULL | |
省略了部分内容。。。。
+-----------------------------------+-----------------------+------+-----+-------------------+-----------------------------+

mysql> select count(*) from baby_camp;
+----------+
| count(*) |
+----------+
| 1131460 |
+----------+
1 row in set (0.50 sec)

mysql> select count(*) from baby_delta;
+----------+
| count(*) |
+----------+
| 10136301 |
+----------+
1 row in set (1.12 sec)
 
五、再次有添加变更列,类型的需求的解决方案：
1.能不增加、不修改表列或者默认值尽量不要做,要求似乎不合情理啊,该做的还得做:(
2.多个表要变更字段等操作分批处理,减少binlog的产生,虽然麻烦一些,安全稳定重要
3.没有办法的办法就是暴力改动数据库的参数,缺点是有些参数需要重启数据库实例

六、疑惑之处：
复制的源主库只有一个,其中17和15出现复制崩溃（注：都开启了binlog,且格式是row,都会出现问题1,但是只有15问题1和2都出现了）,而查询专用的19和10两从库(注：两库都没有开启binlog)没有出现问题2,不解？