首页 > 代码库 > 案例 - percona-online-schema-change各种坑
案例 - percona-online-schema-change各种坑
线上环境复制使用ROW模式，对于上亿的表，使用pt online schema change 在把数据从旧表拷贝到临时表这步操作，会产生大量的binlog，这会导致主从延迟
在pt工具包2.1之前，pt-online-schema-change是不会打印binlog的，如果要在主从上加索引，需要分别在主库执行一次，在从库执行一次
它提供了一个--log-bin参数，并且默认是关闭binlog的
--bin-log
Allow binary logging (SET SQL_LOG_BIN=1). By default binary logging is turned off because in most cases the --tmp-tabledoes not need to be replicated.
而在pt工具2.2版本以后，会默认打binlog，好处是在不用分别在各个节点执行一次改表操作，只需要在主库执行一次改表，就会通过binlog让下面的从库的表都被修改
pt工具3.0版本，有一个 --set-vars=‘sql_log_bin=0‘ 参数能替代 --bin-log=0 效果
有一个1.5亿表加索引的需求，预计1.5亿生成的binlog预计会有20G，为了不产生binlog，准备在每个点执行一次，先在主库执行如下命令
pt-online-schema-change --host=主机 --port=端口号 --user=节点号 --database=数据库名 t=t_room_impeach --alter="ADD INDEX idx_psr(A,B,C)" --set-vars=‘sql_log_bin=0‘ --execute
这条语句一下去，主库下面的4个从库同步都中断了，show slave status报错
Last_SQL_Errno: 1146 Last_SQL_Error: Error executing row event: ‘Table ‘live_oss._t_room_impeach_new‘ doesn‘t exist‘
报错_t_room_impeach_new表存在，为什么这张临时表在从库要存在呢？
posc工具的原理是，先创建一个临时表，表名是 _原来的表名_new，这张临时表是已经加入了你想要的索引，不停把旧表的数据拷贝到这张临时表，新插入，修改，删除的旧表的数据，都会根据触发器，同样新插入，修改，删除到临时表，等拷贝数据，旧表和临时表就是一模一样了，这个时候把临时表rename成为就表的名字，而实际的旧表就会被drop掉，在线完成
当主库执行命令是会显示创建临时表，创建触发器
Creating new table... Created new table live_oss._t_room_impeach_new OK. Altering new table... Altered `live_oss`.`__t_room_impeach_new` OK. 2017-08-02T16:38:48 Creating triggers... 2017-08-02T16:38:48 Created triggers OK. 2017-08-02T16:38:48 Copying approximately 141559863 rows...
因为 --set-vars=‘sql_log_bin=0‘的原因，创建表的DDL语句，无法通过binlog在从库建表，所以从库是表不存在的，问题是从库不需要存在临时表啊，因为只操作主库一个点就足够了
这个是posc第一个坑，主库因触发器触发器产生的数据，会产生binlog，从而同步到从库，当从库要执行这些数据时，发现表不存在，导致同步中断
这时解决方法是在从库，去建立同样一张临时表 _xxxx_new，好让触发器的数据，能够顺利插入到这张表，当建了以后可以看到从库的临时表有数据了，再次验证sql_log_bin=0没有效果
explain select count(*) from __t_room_impeach_new; +----+-------------+----------------------+-------+---------------+------+---------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------------+-------+---------------+------+---------+------+----------+-------------+ | 1 | SIMPLE | __t_room_impeach_new | index | NULL | uid | 4 | NULL | 176| Using index | +----+-------------+----------------------+-------+---------------+------+---------+------+----------+-------------+
几个从库都有176条数据，再看看主库的临时表，有差不多1亿数据，因为除了触发器还有来自旧表的
explain select count(*) from __t_room_impeach_new; +----+-------------+----------------------+-------+---------------+------+---------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------------+-------+---------------+------+---------+------+----------+-------------+ | 1 | SIMPLE | __t_room_impeach_new | index | NULL | uid | 4 | NULL | 10527757 | Using index | +----+-------------+----------------------+-------+---------------+------+---------+------+----------+-------------
当时有个担心
主库临时表 __t_room_impeach_new 数据 = 触发器产生数据 + 旧表产生数据
从库临时表 __t_room_impeach_new数据 = 触发器产生的数据
如果所有点执行最后一步操作 rename 临时表__t_room_impeach_new to t_room_impeach 正式表，岂不是主从数据不一致,从库少了很多数据？
不过按道理这种情况不会发生，因为--set-vars=‘sql_log_bin=0‘会把rename这个DDL语句，像create table一样给阻隔掉，不会导致从库改表成功
为了不冒险，打算重新执行一次，这次加入2个参数，
--no-drop-old-table 即使执行完了命令，也不要drop表，让我确认旧表新表是一致的再手动drop
--no-drop-triggers 触发器也保留
执行命令之前，先把临时表，触发器都手动删除，正如提示说的
Not dropping triggers because the tool was interrupted. To drop the triggers, execute: DROP TRIGGER IF EXISTS `live_oss`.`pt_osc_live_oss_t_room_impeach_del`; DROP TRIGGER IF EXISTS `live_oss`.`pt_osc_live_oss_t_room_impeach_upd`; DROP TRIGGER IF EXISTS `live_oss`.`pt_osc_live_oss_t_room_impeach_ins`; Not dropping the new table `live_oss`.`_t_room_impeach_new` because the tool was interrupted. To drop the new table, execute: DROP TABLE IF EXISTS `live_oss`.`_t_room_impeach_new`; `live_oss`.`t_room_impeach` was not altered.
另外还有在从库先把临时表建立起来，这次执行到一半的时候，4个从库又报错，同步中断了
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table live_oss._t_room_impeach_new; Can‘t find record in ‘_t_room_impeach_new‘, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event‘s master log mysql-bin.056637, end_log_pos 41767716
这次的报错是update语句失败了，row模式的update语句是 set 新值 where 旧值 ，如果在从库的临时表上，找不到where的旧的值，就无法update成新值，，同样因为--set-vars=‘sql_log_bin=0‘，导致从库临时表，比主库临时表少很多数据，所以很可能一条update语句下来，就会因为找不到数据而中断
另外如果使用--no-drop-old-table和--no-drop-triggers参数，最终结果是命令到99%一直卡住，一直保持这个状态，也是一个坑
Copying `live_oss`.`t_room_impeach`: 99% 01:01 remain
Copying `live_oss`.`t_room_impeach`: 99% 00:47 remain
Copying `live_oss`.`t_room_impeach`: 99% 00:35 remain
Copying `live_oss`.`t_room_impeach`: 99% 00:21 remain
Copying `live_oss`.`t_room_impeach`: 99% 00:09 remain
它没有按照文档的去做，先重命名，然后不drop旧表
When the tool finishes copying data into the new table, it uses an atomic RENAME TABLE operation to simultaneously rename the original and new tables. After this is complete, the tool drops the original table.
还有一个坑就是2个业务反馈持续几分钟有如下，一张临时表不存在，但这张临时表应该是对业务透明才对的，业务不会感知到的，然而还是报一个这样一个错误
Base table or view not found: 1146 Table ‘live_oss.__t_room_impeach_new‘ doesn‘t exist thread run: Table ‘live_oss.__t_room_impeach_new‘ doesn‘t exist
结论：单点mysql或者仅修改从库表结构时，可以使用--set-vars=‘sql_log_bin=0‘来减少binlog产生，但在主从复制过程中，还是不要用这个参数，对主从复制最大的损伤是延迟，可以用--max-lag这个参数来限制延迟时间，比如限制在30秒，15秒，代价是改表时间会很长，但总比中断主从复制要好得多
关于触发器产生binlog的问题，在pt2.1,2.2,3.0的文档都没有提及，pt2.0文档有提及
Replicaiton will break if you alter a table on a master that does not exist on a slave
在修改过程中，主库有表，从库没表，会导致主从复制中断，这是上面遇到的问题
Although the tool sets SQL_BIN_LOG=0 by default (unless --bin-log is specified), triggers which track changes to the table being altered still write statements to the binary log
尽管使用了SQL_BIN_LOG=0 ，但triggers产生的数据变动，依然会已statements的模式写进binlog，估计pt3.0的--set-vars=‘sql_log_bin=0‘参数也是一样 WTF!
本文出自 “数据库运维” 博客，请务必保留此出处http://dadaman.blog.51cto.com/11373912/1953189
案例 - percona-online-schema-change各种坑