MySQL在线DDL技术方案

1、概述

一、背景

目前，ERP系统上线过程中，主要包括代码分支合并、数据库脚本执行、程序发布和测试验证四大步。

随着公司业务发展，仓库下班的时间一再推迟，再加上新疆门店的即将开始营业，ERP系统上线的时间点由原来10点已推迟到凌晨12点以后，留给技术上线的窗口时间越来越短，受上线周期短的约束限制，每个周期迭代的功能点也不得不考虑此因素。

通过对上线过程的分析，二至五成的时间是在等MySQL脚本执行，受DDL锁表影响，每次升级不得不在营业结束后停机执行，如果能把这部分等待时间节省掉，可以减少大量上线过程中的等待时间。

除此之外，灰度发布环境想做到白天发版，也依赖MySQL脚本执行的前置，因此，MySQL支持在线DDL已变得十分重要。

二、主流方案与对比

2.1 主流方案与对比

大表的DDL的变更有两种解决方案，分别是使用Percona的pt-online-schema-change和gh-ost，在决定选型之前，两款工具的横向对比结果如下。

	PT-OSC	GH-OST
原理	1.创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构) 2.在新表执行alter table 语句 3.在原表中创建触发器3个触发器分别对应insert,update,delete操作 4.以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表 5.Rename 原表到old表中，在把临时表Rename为原表 6.如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理 7.默认最后将旧原表删除	1.在变更的服务器上 创建 ghost table( _tbname_gho like tbname) 2.更改 _tbname_gho 结构为新表结构 3.作为mysql的slave连接mysql server，并记录新增binlog event 4.交替执行: 应用新增events到 ghost table 和 复制老表的记录到 ghost table 5.table重命名(ghost table 替代 老表) 其中有2种常用用法： 1.连接从库，变更主库 - 默认方式，slave需要开启log-slave-update 2.连接主库，变更主库 - 必须ROW格式，带上参数–allow-on-master"
使用限制	1.原表必须要有主键或者唯一索引（不含NULL） 2.原表上不能有触发器存在 3.使用前需保证有足够的磁盘容量，因为复制原表需要一倍的空间 4.在阿里RDS 上使用需要增加参数no-version-check	1.原表必须要有主键或者唯一索引（不含NULL） 2.不支持外键 3.不支持触发器 4.不支持虚拟列 5.不支持 5.7 point类型的列 6. 5.7 JSON列不能是主键 7.不能存在另外一个table名字一样，只是大小写有区别 8.不支持多源复制 9.不支持M-M 双写 10.不支持FEDERATED engine
优点	1.执行速度快，业界使用比较广泛，较稳定	1.读binlog可以放在从库执行，减少主库的压力 2.不需要创建触发器，对原表没有改动
风险点	1.需要创建触发器，对原表有改动 2.涉及主键的更改需要review	1.当系统负载极高时，gh-ost有可能无法跟上binlog日志的处理（未测试过该场景） 2.限制比较多，见上文 3.涉及主键的更改需要review
重要参数说明	–max-load，默认threads_running=25,可以指定多个指标来限速,每个chunk拷贝完会检查，超过阀值会暂停复制。如果不指定该参数，工具会检查当前运行值并增加20% –critical-load,默认为threads_running=50,如果不指定，则工具检查当前运行值并当运行到200%是退出工具运行 –max-lag，默认1s，如果发现延迟大于该值，则暂停复制数据。 –check-interval,配合max-lag使用，检查从库超过延时后，该工具睡眠多久 –recursion-method，指定从库的发现机制,processlist,dsn,none 等 –chunk-time,默认0.5秒，拷贝数据行的时候为了保证0.5秒内拷贝完一个chunk，动态调整下一次chunk-size的大小 –[no]check-replication-filters，如果工具检查到服务器选项中有任何复制相关的筛选，工具会报错退出，默认为yes –chunk-size，指定块大小，默认1000行。	–max-load=Threads_running=25 表面如果在执行gh-ost的过程中出现Threads_running=25则暂停gh-ost的执行 –critical-load=Threads_running=60 表明执行过程中出现Threads_running达到60则终止gh-ost的执行 –chunk-size=1000 设置每次从原表copy到 ghost table的行数 –ok-to-drop-table 执行完之后删除原表 –allow-on-master 直连主库执行
运行命令实例	pt-online-schema-change --user=db_monitor --password=xxx --host=127.0.0.1 --port=xxx --alter “add COLUMN c2 varchar (120) not null default ‘’” D=sbtest,t=sbtest1 --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute	./gh-ost --assume-master-host=ip:port --master-user=db_monitor --master-password=xxx --user=db_monitor --password=yyy --host=10.xxx --port=port --alter=“ADD COLUMN c2 varchar(120)” --database=sbtest --table=“sbtest1” -execute --initially-drop-old-table --initially-drop-socket-file --initially-drop-ghost-table

2.2 性能测试对比

1、测试环境

16core CPU，2G buffer pool的测试实例，MySQL的5.7版本异步主从，2kw行记录，4.8GB 测试表大小。

2、测试结果

3、结果展示