1. 导言
工作中十分常见的场景是一个业务表在线运行着，而且已经有大量的存量数据(千万)，因业务变更要改表Schema，如添加删除字段、修改分区等。直接在原表上执行DML，会导致锁表几分钟甚至几十分钟，这对在线业务来说是不能接受的。Percona pt-online-schema-change的出现就是为了解决这个问题。
2. pt-online-schema-change
pt-online-schema-change会先根据源表结构复制出一张新表，在源表上创建trigger(触发器)，自动将新写入/编辑的数据写入到新表，并分批导入历史数据(–chunk-time批的执行时间，根据时间自动调整批的大小)，当拷贝完成后，通过原子的事务，删除老表，新表重命名为原表。
这里有个坑是，如果其他表对原表有外键约束的话，需要修改外键约束到到新表，通过指定配置项--alter-foreign-keys-method，pt-online-schema-change会帮我们处理这个过程。 最终表会拥有和源表一样的外键约束，但是外键名称会有一点点变化，为了避免外键重名。
pt-online-schema-change默认不会执行修改，你需要显示的指定--execute选项才会真正的执行。
pt-online-schema-change采取一系列的措施避免可能出现的问题，包括自动发现从节点并连接，并做以下检查:
- 拒绝操作没有主键或者唯一键的表，查看
--alter了解更多。 (因为会创建一个DELETE的trigger，需要基于唯一键删除) - 拒绝操作主从节点间有复制过滤的表(replication filters)，查看
--[no]check-replication-filters了解更多。 (因为那张新表可能已经被完整的同步到从节点了，再重命名，结果从节点的数据是没过滤的数据) - 暂停复制操作，当发现从节点延迟大的时候，查看
--max-load和--critical-load了解更多。 - 暂停或取消操作，当发现数据库中同时执行了多个pt-online-schema-change任务的时候，查看
--max-load、--critical-load了解更多。 - 通过设置
innodb_lock_wait_timeout=1(MySQL 5.5之前)和lock_wait_timeout=60(MySQL 5.5及以后)，设置pt-online-schema-change等待锁的超时时间，如果在线业务有长期锁定数据的事务，可能会影响到改工具的操作。 这些操作可以通过--set-vars设置。 - 如果其他表有外键引用源表，除非你指定
--alter-foreign-keys-method，拒绝操作。 - 该工具不支持修改
Percona XtraDB Cluster的MyISAM表。
3. Options
3.1 --progress --print --statistics
pt-online-schema-change会打印执行过程中正在执行的动作，通过指定--progress将进度打印到STDERR中。 可以通过指定--print打印更相信的任务信息。
指定--statistics在执行完成后会打印一个执行过程中各种关键操作的次数报表，大概是长成这样的:
# Event Count
# ====== =====
# INSERT 1
3.2 --dry-run --execute
--dry-run和--execute是互斥的，二选一，--dry-run会尝试运行，但不真正的执行修改。 --execute执行实际修改。
3.3 --alter
后面跟一个字符串，是MySQL的DML语句(除了alter table部分)，如果有多个字段修改通过逗号分隔。
如果发生如下场景，pt-online-schema-change会异常中断:
1. 原表没有主键或唯一键
原表中没有主键或唯一键，唯一键(主键)必须存在，该工具会基于唯一键做一个DELETE的触发器。 有一个特殊场景是，原表没有唯一键，但是--alter里从现有字段中定义了一个唯一键，那工具会接受这个字段做为DELETE触发器使用的唯一键。
2. 不能执行表的RENAME操作
不能在--alter中使用RENAME操作，这类做可以直接在数据库上跑DML完成
3. 通过删除后重新添加列，列数据会丢失
不能通过DROP COLUMN然后ADD COLUMN来操作列的重命名，这回导致之前的列的数据丢失
4. 非空列又不指定默认值，我不知道往里头写啥
不能添加一列没有默认值且不允许有空的列
5. 外键约束，因为MySQL限制，约束名称变了
如果我们想删除外键，指定DROP FOREIGN KEY constraint_name，如果外键名字是constraint_name，需要在使用的时候在最前面加一个"“， 改成_constraint_name。 由于MySQL的限制，pt-online-schema-change在最终表里将索引或constraint的名字前面都加了个”"。
举例来说，如果我们原表有如下constraint:
CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)
现在想要删除的话，需要指定:
--alter "DROP FOREIGN KEY _fk_foo"
6. MySQL 5.0导入数据没使用共享锁，可能造成数据不一致(从原表读完，写入最终表前，被修改了)
在MySQL 5.0，pt-online-schema-change不会使用LOCK IN SHARE MODE，因为它可能导致slave错误，导致主从同步失败
Query caused different errors on master and slave. Error on master:
'Deadlock found when trying to get lock; try restarting transaction' (1213),
Error on slave: 'no error' (0). Default database: 'pt_osc'.
Query: 'INSERT INTO pt_osc.t (id, c) VALUES ('730', 'new row')'
这个错误发送在将一个MyISAM表转为InnoDB表的时候，因为MyISAM不支持事务。 MySQL 5.0上有小概率(5%)导致这个问题，MySQL 5.1之后就没有这个问题。
所以在MySQL 5.0下，不使用LOCK IN SHARE MODE，这种场景下可能导致数据的丢失，在转换完成后要验证数据是否完整
3.4 --alter-foreign-keys-method
当其他表依赖原表的ID做为外键字段的时候，修改原表的名字，会导致其他表的外键约束里的表面也被修改，导致外键约束里的表名不是最终表的表名。 所以需要特殊处理。
pt-online-schema-change提供了两种方式来完成外键约束的处理: rebuild_constraints、 drop_swap
rebuild_constraints
直接通过DML的alter table删除并重新添加用最终表的外键，这是推荐的方式，触发有外键约束的表太大以至于直接alter table会导致长时间锁表。 pt-online-schema-change通过原表到最终表拷贝数据的速率，已经外键表的行数来评估预计的修改时间，如果时间小于--chunk-time。 前面提到过，由于MySQL的限制重名，constraints名称多加了个"_"。
drop_swap
这种方式直接删除原表，然后并拷贝表重命名为原表，因此不存在修改外键的操作。 但带来的问题是，在drop table和rename table的中间，有一段时间(可能极短，但对在线业务可能已经让用户感知了)原表直接不存在， 而且如果中间发生错误或者无法将拷贝表重命名为原表的话，完全无法恢复。
这种方式会自动添加两个配置选项--no-swap-tables、--no-drop-old-table，查看对应选项了解更多。
--alter-foreign-keys-method还有一个可选值是auto，自动选择rebuild_constraints或drop_swap。
3.5 --[no]analyze-before-swap
在重命名原表和拷贝表前，先对拷贝表执行ANALYZE TABLE，以免拷贝表的统计数据有问题导致MySQL选择执行计划有问题，以至于本来一个很快的查询变成了全表扫描。
3.6 --ask-pass
弹出prompt让用户输入密码
3.7 --channel
当你有两个master时，一个master_a，一个master_b，并有一个slave，分别通过不同的channel， channel_master_a、channel_master_b从两个master同步数据。 如果这个时候你使用pt-table-sync，它无法知道从哪个master同步数据，因为使用show slave status返回两条记录， 这种场景下你需要通过--channel=channel_master_a指定通过master_a同步数据。
3.8 --check-interval
检查主从节点最大延迟--max-lag的时间间隔
3.9 --chunk-size
分片拷贝一个分片的行数，默认1000。 你也可以指定--chunk-time有pt-online-schema-change自动调整。
3.10 --chunk-time
默认值0.5，单位秒，pt-online-schema-change会统计每秒钟能拷贝的行数， 然后以这个行数*时间来定义本次拷贝的记录数，因为速度是动态变的，所以行数也是动态变的。
3.11 --config
指定配置文件，多个文件用逗号隔开，如果有这个选项，这个选项需要是所有配置选项的第一个。
3.12 --critical-load
服务器负责临界值，默认值是Threads_running=50，pt_online_schema_change会在执行前通过show global status查询变量值，如果值大于设置值的话，取消执行。 如果设置多个变量，通过逗号隔开，变量名和变量值之间用"="隔开。
3.13 --database
指定数据库名字
3.14 --default-engine
默认新表会使用和老表一样的engine，指定这个配置，新表将使用这个配置指定的存储引擎。
3.15 --[no]drop-old-table
默认在新表重命名为最终表后，原表会被删除，可以通过指定--nodrop-old-table yes来保留原表。
4. 数据库连接信息
| 选项 | 说明 |
|---|---|
| h | 指定host |
| P | 指定端口 |
| u | 指定登录用户 |
| p | 指定密码 |
| D | 指定数据库 |
| t | 要修改的表 |
| A | 指定编码 |
| F | 指定默认配置文件 |
| S | 使用socket文件来建立连接 |
5. 实例
5.1 安装Percona Toolkit
sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum search percona-toolkit
yum install percona-toolkit.x86_64
5.2 MySQL备份和恢复
备份
mysqldump -uhadoop -p'hujiang.123' -hdb3307 -P3307 storm_apps inf_user_did_ios_ct_ipad > inf_user_did_ios_ct_ipad.sql
恢复
mysql -h 192.168.36.92 -P 3306 -u'hadoop' -p'hujiang.123' storm_apps < inf_user_didi_ios_ct_ipad.sql
这里我们是从线上备份数据到测试环境，所以数据库连接参数不同
5.3 添加字段
# 只打印执行计划
pt-online-schema-change --alter "ADD COLUMN oaid varchar(64)" --progress time,10 --print --statistics --dry-run \
h=192.168.36.92,P=3306,u=hadoop,p='hujiang.123',D=storm_apps,t=inf_user_did_ios_ct_ipad
# 实际执行
pt-online-schema-change --alter "ADD COLUMN oaid varchar(64)" --progress time,10 --print --statistics --execute \
h=192.168.36.92,P=3306,u=hadoop,p='hujiang.123',D=storm_apps,t=inf_user_did_ios_ct_ipad
5.4 添加索引
pt-online-schema-change --alter "ADD INDEX idx_oaid (oaid)" --progress time,10 --print --statistics --execute \
h=192.168.36.92,P=3306,u=hadoop,p='hujiang.123',D=storm_apps,t=inf_user_did_ios_ct_ipad
5.5 删除索引
pt-online-schema-change --alter "DROP INDEX idx_oaid" --progress time,10 --print --statistics --execute \
h=192.168.36.92,P=3306,u=hadoop,p='hujiang.123',D=storm_apps,t=inf_user_did_ios_ct_ipad
5.6 重命名字段
这里有个问题，早期pt-online-schema-change不止RENAME，即使新版本支持，RENAME失败会导致数据丢失
pt-online-schema-change --nocheck-alter --alter "change oaid test_oaid varchar(64)" --progress time,10 --print --statistics --execute \
h=192.168.36.92,P=3306,u=hadoop,p='hujiang.123',D=storm_apps,t=inf_user_did_ios_ct_ipad
5.7 删除字段
pt-online-schema-change --alter "DROP COLUMN test_oaid" --progress time,10 --print --statistics --execute \
h=192.168.36.92,P=3306,u=hadoop,p='hujiang.123',D=storm_apps,t=inf_user_did_ios_ct_ipad
5.8 添加多个字段
pt-online-schema-change --alter "add column oaid varchar(255),
add column imei varchar(255), add column idfa varchar(255), add column anid varchar(255)" --progress time,10 --print --statistics --execute \
h=db3307,P=3306,u=hadoop,p='zzz',D=storm_apps,t=new_install_did
5.9 修改分区
pt-online-schema-change --alter "PARTITION BY KEY(app) PARTITIONS 16;" --progress time,10 --print --statistics --execute \
h=db3307,P=3306,u=hadoop,p='...',D=storm_apps,t=new_install_did
一开始我尝试用PARTITION BY HASH(app) PARTITIONS 16报错，是因为HASH函数只接收数字类型的。
和DBA聊下来，修改PARTITION存在丢数据的可能，尽量不用。
6. 坑
6.1 添加uniqe索引时，需要自己确保这个表在对应列上不存在重复
pt-online-schema-change采用的是INSERT IGNORE方式写入数据，写入的SQL是类似这样的:
INSERT INTO table xxxx (column1, column2) VALUES (value1, value2) ON DUPLICATE IGNORE
如果结果表在唯一键上有重复的话，会被默默的忽略。
参考文档
- https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html#cmdoption-pt-online-schema-change-dry-run
758
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
