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

共计 4485 个字符，预计需要花费 12 分钟才能阅读完成。

上周末和开发人员对线上库中的部分表的在线 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 ‘backup@192.168.100.18: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, 不解？

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-10/135986.htm