mysql schema 变更的一般方案
变更 mysql table schema 是使用 mysql 的过程中经常需要做的一件事，一般意义变更可以根据场景的不同做不同的操作。
直接 alter
如果数据量非常少，读写不频繁，可以直接对表进行 alter，由于数据少直接 alter 并会造成太长时间的锁表，一般情况都是能接受的
停机升级
对于数据量特别大的表，如果直接 alter 将导致线上不可用可以考虑停机修改，当然如果停机时间过长不能接受，只能做在线改了
不停机在线迁移
很多团队面临的业务都无法停机，在线变更 schema 也确实是很多业务团队都会面临的问题，我曾经做过一次 Mongodb 亿级拆分方案，大致思路是：
1、在应用程序中拦截所有的 update，insert，delete 操作
2、为数据一致性建立迁移缓冲区，临时保存迁移用户的所有业务数据
3、在业务代码中利用迁移缓冲区做一致性输出
4、离线迁移所有用户数据
最核心的需要解决的问题是拦截所有操作和保证用户数据在迁移过程中的一致性。
在 mysql 中进行在线 schema 变更其实也会面临这样的问题，但往往由于不同团队业务发展的不同，导致拦截所有的操作变得不可能，这就需要借助 mysql 自身的特点进行 schema 变更，思路就是：
1、建立临时表
2、原表建立更新触发器
3、迁移数据
利用 mysql 触发器 可以对原表的更改进行跟踪，以达到保证数据一致性的目的。
对于在线变更 mysql schema 已经有不同的团队给出了不同的解决方案。
Percona 出品，Perl 实现。
Facebook 出品，Python 实现。
GitHub 出品，Go 实现。
下面简单对三个工具的特性和使用进行了评测
pt-online-schema-change
https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html
特征
在线变更 mysql scheme，不阻塞表的 reads 和 writes
1 | pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor |
使用 pt 前需要注意和准备的
- 仔细阅读工具文档
- 审查工具已知的BUG
- 在非生产环境测试
- 备份生产环境数据并确认
pt 的工作方式和原理
pt-online-schema-change 模拟了 mysql 内部更改表的方式，但是 pt 是在原始表的副本上进行修改，这意味着原始表不会被 lock，client 依然可以继续读写原始表的数据，这样线上的业务不受影响。
pt 的工作方式大致流程如下：
1.创建一个需要修改的 table 的副本，即一张 empty table
2.根据你的需要修改这张表，
3.从原始表中复制数据到修改之后的 table
4.复制完成之后，用新表代替旧表，并删除旧表
pt 的复制以 small chunk 的形式执行（见—chunk-time），不同的大小的 chunk 会导致其执行的总时间是千差万别的，而且为了保证数据的一致性，pt 会在 origin table 创建 trigger 来保证对 origin table 的修改反映到 new table 中，所以必须保证原始表中不存在相同的触发器才能让 pt 正常工作。
pt 在数据 copy 完成之后，会使用 mysql 提供的原子操作 rename table 同时修改 origin table 和 new table，修改完成之后drop origin table。
如果 origin table 有外键 refer，将会增加使用 pt 的复杂度以及会有一定的风险，由于外键的存在 pt 将无法使用 atomic 的方式完成表的替换，必须把对应的外键更新完成才可以进行表的修改，pt 支持两种方式来完成这项任务（见—alter-foreign-keys-method)
使用 pt 的注意事项
外键带来的影响
最终的表和 origin table 有相同的 foreign keys 和 index，但是对象的名字可能稍有差别这样是为了避免冲突。
pt 的执行操作必须手动指定
安全起见，pt 默认不会执行表的修改，除非你指定了 —execute ，默认是未开启的状态，pt 提供了一些列的措施用来避免一些问题的出现和不必要的负载，包括自动检测 replicate 等：
- pt 将拒绝执行如果 origin table 没有 primary key 或 unique index —alter
- pt 将拒绝执行如果检测到 replicate filter
—[no]check-replication-filters - pt 将停止 copy 如果检测到 replicate 延迟过高
[—max-lag](https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html#cmdoption-pt-online-schema-change-max-lag) - pt 将停止 copy 或者终止操作如果检测到 server 的负载过高
[—max-load](https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html#cmdoption-pt-online-schema-change-max-load) and [—critical-load](https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html#cmdoption-pt-online-schema-change-critical-load) - pt 将设置 innodb_lock_wait_timeout=1 and (for MySQL 5.5 and newer) lock_wait_timeout=60 ，保证其操作过程尽量不破坏其他 transaction
[—set-vars](https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html#cmdoption-pt-online-schema-change-set-vars) - pt 将拒绝修改表如果有外键约束，除非指定外键的修改方法
[—alter-foreign-keys-method](https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html#cmdoption-pt-online-schema-change-alter-foreign-keys-method)
pt 的下载安装
1 | wget percona.com/get/percona-toolkit.tar.gz |
根据不同的平台下载和解压即可使用，再次强调使用前一定要在测试环境进行测试、生成环境一定要提前进行备份再去执行，要仔细研究他的文档，对各个参数了如指掌。
参考资料
- https://www.2cto.com/net/201612/573563.html
- https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html
OnlineSchemaChange
https://github.com/facebookincubator/OnlineSchemaChange
特征
OnlineSchemaChange 和 pt 的工作方式类似，不同的是基于 Python 而且已经在 Facebook 进行了实践，其最初的版本是 PHP 的，改成 Python 之后非常方便开发者对其内部原理进行了解和掌握。
正如 OnlineSchemaChange 文档所说，Facebook 对一致性的要求高于一切，所以 OnlineSchemaChange 会反复对数据的一致性进行校验，然后才会切换表。
工作方式和原理
OnlineSchemaChange 会分为如下几个阶段
OSC 所有的语句在 sql_log_bin = 0 模式下工作，他可以再 master 或者 replica 上执行，意味着你可以事先在 replica 执行，待验证没有问题之后切换 replica 为 master。
OSC 的工作分为几个阶段
1.init 阶段
在该阶段，首先做一些必备的检查确保所有需要修改的 schema 满足必要的条件，然后用新的 schema 创建一个 empty table 称之为 shadow table，同时创建一个 change table 用来记录异步 dump 和 load 的过程中 origin table 发生的改变叫 deltas table，deltas table 包含原始表的所有数据和一个自增序列用来跟踪变化的顺序，以及一个 int column 跟踪 dml 语句。
与此同时，insert 、update、delete 三个 trigger 会在 origin table 中创建用来跟踪数据对应的变化，同 pt 一样需要利用 trigger，所以 origin table 上不能有其他的 trigger。
比较特殊点的是 update trigger，如果 origin table 中的 PK column 没有改变，仅仅会在 deltas table 中记录更新后的值，如果 PK column 发生了变更，一条 delete 和 insert 语句将会同时记录，它们用来删除旧数据和插入新数据。
2.dump 阶段
一个 consistent snapshot 将打开用于对现有的表创建一个快照 stale view。使用 select NTO OUTFILE dump data 到磁盘以分块的形式，没有 使用 insert NTO … SELECT FROM 是为了避免锁表。
3.load 阶段
然后从 disk 上 load data 到 mysql 的 shadown table 使用 load data infile，load 完成之后删除这些 dump 的文件。
要确保磁盘空间是足够的，尤其是 origin table 是被压缩过的。
4.replay 阶段
在 dump 和 load 阶段会有很多 DML 发生在 origin table，change table 会记录这一些利用我们之前创建的 trigger，这些记录会不断 replay 到 shadow table 中，replay 会执行很多次，因为每次 replay 时又有 DML 发生了。
我们不会从 delta table 取数据只会获取变更的 id 和类型，数据本质 replay 时会去再查询。
使用限制和要求
- 必须要有主键或唯一性外键
- 不支持重命名 column
- origin table 不能有 trigger
- 不能有外键引用
- 只有一个 OSC 实例存在
- Running OSC for the same table on Master and Slave at the same time will break replication.
- OSC will stop replication on slave by default to avoid MDL lock chaining on slaves
- OSC is not able to run schema change together with RBR on any MySQL version other than Facebook’s MySQL. See more detail in
- 无法在非 Facebook Mysql 基于 RBR 模式运行 OSC
本地测试遇到的问题
1.osc 不支持非 mysql 版本的 binlog_format 为 row 的
查看 mysql 的 binlog 格式 show variables like '$binlog_format%';
2.mac 下报 UNABLE_TO_GET_DISK_SPACE
1 | raise OSCError('UNABLE_TO_GET_DISK_SPACE', {'path': path}) |
可能由于是 mac 系统无法获取磁盘空间，所以指定了一个目录用来存放 dump 出来的文件
3.无法使用 SELECT INTO OUTFILE 语句
1 | GENERIC_MYSQL_ERROR: MySQL Error during stage "running DDL on db 'blog'": [1290] The MySQL server is running with the --secure-file-priv option so it cannot execute this statement |
指定导出目录之后由于 mysql 的限制 无法使用 SELECT INTO OUTFILE 语句，查看当前的 show variables like '%secure_file_pri%'; 指定导出目录到新建的目录，如果没有自己设置一个再指定导出目录。
本地测试的一些指标
100 万数据，origin table 数据未发生改动
1 | INFO 2017-10-20 12:02:54.863 Time in dump: 19.043s |
100 万数据，origin table 数据全部发生了改变
1 | INFO 2017-10-20 13:59:07.838 Time in load: 244.596s |
参考资料
- https://github.com/facebookincubator/OnlineSchemaChange/wiki/How-OSC-works
- http://www.jianshu.com/p/bd9f38340e83
- http://blog.csdn.net/zhuwinmin/article/details/72875964
gh-ost 要求在集群模式下工作，操作复杂，等有空再更
