MySQL在线DDL修改表结构的简单经验分享
摘 要
在线DDL修改生产环境的大表一直是运维、DBA一个很头痛的问题，本文分享一些相关经验，希望对还在头痛的同学能有所帮助，当然更希望路过的大神，如果有更靠谱的方案能够指点一二，不吝赐教。
一、故障背景
内部故障群反馈：XX 系统卡住不可用了，请帮忙看看；
排查发现是有一个 alter 修改数据库的表结构的变更，出现了大量的 MDL 锁，导致服务不可用，最后通过 kill 掉这个 alter 恢复了服务。当然， 这个 alter 需求也就暂时搁置了。
业务需求的变更肯定还是要继续执行的，因此就有了各种尝试....
二、辅助工具
先尝试了 2 个已知的辅助工具：
1、pt-online-schema-change
pt-online-schema-change，简称 pt-osc，是 Percona 开发了一系列工具 Percona Toolkit 包的功能之一。
pt-osc 工具的工作流程：
- 检查更改表是否有主键或唯一索引，是否有触发器
- 检查修改表的表结构，创建一个临时表，在新表上执行 ALTER TABLE 语句
- 在源表上创建三个触发器分别对于 INSERT UPDATE DELETE 操作
- 从源表拷贝数据到临时表，在拷贝过程中，对源表的更新操作会写入到新建表中
- 将临时表和源表 rename（需要元数据修改锁，需要短时间锁表）
- 删除源表和触发器，完成表结构的修改。
pt-osc 工具的一些限制条件：
- 数据库不能有触发器，否则无法使用
- 源表必须有主键或唯一索引，如果没有工具将停止工作
- 如果线上的复制环境过滤器操作过于复杂，工具将无法工作
- 如果开启复制延迟检查，但主从延迟时，工具将暂停数据拷贝工作
- 如果开启主服务器负载检查，但主服务器负载较高时，工具将暂停操作
- 但表使用外键时，如果未使用--alter-foreign-keys-method 参数，工具将无法执行
- 只支持 Innodb 存储引擎表，且要求服务器上有该表 1 倍以上的空闲空间。
pt-osc 工具的执行 demo：
pt-online-schema-change \
--user=mysql \
--password=xxxxxx \
--host=192.189.1.100 \
--alter "add column DiskSequence varchar(256) default '';" \
D=database_name,t=table_name \
--alter-foreign-keys-method=auto \
--nocheck-replication-filters \
--execute #不加这个选项则表示仅测试，不执行结果，很不幸，我们的生产环境很（keng）古（B）董，大量使用触发器，导致 PT 工具无法使用。当然，在一些没有触发器的 DB 上，已经成功应用 pt-osc 工具，还是非常给力的！
2、gh-ost
gh-ost 是 github 开源的一个 DDL 工具，即 gitHub,s Online Schema Transmogrifier/Transfigurator/Transformer/Thingy 的缩写，意思是 GitHub 的在线表定义转换器。上一篇文章已经简单分享了 github 自用的 gh-ost 工具【传送门】，这里再搬运一下 gh-ost 的三种工作模式和相关限制：
模式一：连上从库，在主库上修改
这是 gh-ost 默认的工作模式，它会查看从库情况，找到集群的主库并且连接上去。修改操作的具体步骤是：
- 在主库上读写行数据；
- 在从库上读取二进制日志事件，将变更应用到主库上；
- 在从库上查看表格式、字段、主键、总行数等；
- 在从库上读取 gh-ost 内部事件日志（比如心跳）；
- 在主库上完成表切换；
如果主库的二进制日志格式是 Statement，就可以使用这种模式。但从库就必须配成启用二进制日志（log_bin, log_slave_updates），还要设成 Row 格式（binlog_format=ROW），实际上 gh-ost 会在从库上帮你做这些设置。事实上，即使把从库改成 Row 格式，这仍然是对主库侵入最少的工作模式。
模式二、直接在主库上修改
如果没有从库，或者不想在从库上操作，那直接用主库也是可以的。gh-ost 就会在主库上直接做所有的操作。仍然可以在上面查看主从复制延迟，限制如下：
- 主库必须产生 Row 格式的二进制日志；
- 启动 gh-ost 时必须用--allow-on-master 选项来开启这种模式；
模式三、在从库上修改和测试
这种模式会在从库上做修改。gh-ost 仍然会连上主库，但所有操作都是在从库上做的，不会对主库产生任何影响。在操作过程中，gh-ost 也会不时地暂停，以便从库的数据可以保持最新。
- --migrate-on-replica 选项让 gh-ost 直接在从库上修改表。最终的切换过程也是在从库正常复制的状态下完成的。
- --test-on-replica 表明操作只是为了测试目的。在进行最终的切换操作之前，复制会被停止。原始表和临时表会相互切换，再切换回来，最终相当于原始表没被动过。主从复制暂停的状态下，你可以检查和对比这两张表中的数据。
gh-ost 的执行 demo：
./gh-ost \
--ok-to-drop-table \
--initially-drop-ghost-table \
--initially-drop-socket-file \
--host="192.168.1.1" \
--port=3306 \
--user="root" \
--password=""\
--database="test_db" \
--table="test_table" \
--verbose \
--alter="add column test_field varchar(256) default '';" \
--panic-flag-file=/tmp/ghost.panic.flag \
--allow-on-master \
--throttle-flag-file /tmp/1.log \
--execute结果在测试环境进行多次测试之后，应用到生产环境依然出现了阻碍，出现如下报错：
2018-03-21 08:20:21 FATAL 2018-03-21 08:20:21 ERROR Found 7 parent-side foreign keys on `ndb`.`net_device`. Parent-side foreign keys are not supported. Bailing out
2018-03-21 08:22:48 ERROR Found triggers on `ndb`.`net_device_parts`. Triggers are not supported at this time. Bailing out进一步看了下 help 参数：
-discard-foreign-keys
DANGER! This flag will migrate a table that has foreign keys and will NOT create foreign keys on the ghost table, thus your altered table will have NO foreign keys
. This is useful for intentional dropping of foreign keys
-skip-foreign-key-checks
set to 'true' when you know for certain there are no foreign keys on your table, and wish to skip the time it takes for gh-ost to verify that说明了 2 个问题：
1、gh-ost 对于有外键的表，修改之后外键约束将被删除；
2、ghost 也不支持有触发器的表（本以为 gh-ost 的工作原理不依赖触发器，应该就能兼容触发器，其实不然）。
三、最终解决
结合 2 个工具最终也无法完成所有修改之后，也只能硬着头皮再次尝试原始的 alter 语句，毕竟要开发花大力气去删除触发器、外键显然是不现实的...
在数据库执行 alter 之后，show processlist 立即发现大量 MDL 锁，一个是 alter 语句，另一批则是和表相关的 select 语句。
下意识认为是因为这些 select 阻塞了 alter 的执行，于是写了一个语句来 kill 这些 select 语句（已知清理 select 的影响）：
mysql -h192.168.1.00 -umysql -pxxx -e "show processlist" | grep 'Waiting for table metadata lock'|grep select | awk '{print $1}' | xargs -i% mysql -h192.168.1.100 -umysql -pxxx -e "kill %"结果并不奏效，才开始意识到 alter 这个语句其实一开始就没执行，而是阻塞等待的状态。那到底是什么阻塞 alter 语句了？查看 processlist 发现其他非 sleep 状态的连接都是在 alter 之后出现的，所以并不是造成阻塞的原因。继续 show processlist 看到一堆 sleep 状态连接，灵光一闪，联想到应该是有未完成提交的事务！
于是，使用如下步骤进行查看：
1、查看事务等待情况：
SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM
information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;2、查看当前进行中的事务：
5.5 版本（我们生产环境版本）：
SELECT
a.trx_id,
a.trx_state,
a.trx_started,
a.trx_query,
b.ID,
b. USER,
b. HOST,
b.DB,
b.COMMAND,
b.TIME,
b.STATE,
b.INFO
FROM
information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id
WHERE
b.COMMAND = 'Sleep';附：5.6 版本（5.6 原生支持在线 DDL，感兴趣的可以研究下）
SELECT
a.trx_id,
a.trx_state,
a.trx_started,
a.trx_query,
b.ID,
b.USER,
b.DB,
b.COMMAND,
b.TIME,
b.STATE,
b.INFO,
c.PROCESSLIST_USER,
c.PROCESSLIST_HOST,
c.PROCESSLIST_DB,
d.SQL_TEXT
FROM
information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id
AND b.COMMAND = 'Sleep'
LEFT JOIN PERFORMANCE_SCHEMA.threads c ON b.id = c.PROCESSLIST_ID
LEFT JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;3、结果发现确实有 2 条未提交的事务，还是前 1 天的：
MySQL [(none)]> SELECT a.trx_id, a.trx_state,a.trx_started,a.trx_query,b.ID,b. USER,b. HOST,b.DB,b.COMMAND,b.TIME,b.STATE,b.INFO FROMinformation_schema.INNODB_TRX a LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id WHEREb.COMMAND = 'Sleep';
+-----------+-----------+---------------------+-----------+-----------+------------+--------------------+-------+---------+------+-------+------+
| trx_id | trx_state | trx_started | trx_query | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+-----------+-----------+---------------------+-----------+-----------+------------+--------------------+-------+---------+------+-------+------+
| 42B4A12D5 | RUNNING | 2018-03-20 13:28:44 | NULL | 469281880 | mysql | xxx.xx.xx.xx:33194 | iprms | Sleep | 259 | | NULL |
| 42B4A12DA | RUNNING | 2018-03-20 13:28:44 | NULL | 469281878 | mysql | xxx.xx.xx.xx:33191 | ndb | Sleep | 259 | | NULL |
+-----------+-----------+---------------------+-----------+-----------+------------+--------------------+-------+---------+------+-------+------+
6 rows in set (0.01 sec)结合 start 时间、ip 地址以及 DB 名称，可以确定是后台 Twisted 接口启动时初始化的事务（真是坑啊！）
这次修改的是 ndb 这个库，因此尝试 kill 掉 trx_id 为 42B4A12DA 的事务对应的 process ID：
kill 469281878;执行之后，就发现 alter 语句已经进入 Query 状态了，不出几分钟就已经修改完成了！
花絮周边：后面偶然看到了内部同事分享的文章中有这样一段描述，也就不难解释为啥我们的 Twisted 程序启动就初始化了一个事务了：
使用 python 操作 mysql 的时候，使用了其 pymysql 模块，Python 的 pymysql 模块默认是会设置 autocommit=0 的。
让我们来对比一下其他同样使用 python 访问的正常连接请求，再断开前都会手动的 commit。
找到原因后有思考了下，是不是可以在建连后就设置 autocommit=1 呢？这样对于之后新变更的 SQL 就不要再考虑到手动 commit 的事情了，可以通过在初始化连接池的时候，对每一个连接进行设置，即
四、小结
对于 MySQL 在线 DDL 修改大表，gh-ost 和 pt-osc 都是很不错的选择，前提是不能有坑爹的触发器和外键！当然，实在是遇上了也没什么办法，只能硬扛！很多时候，你会非常郁闷，明明数据库没什么负载，当前也没什么活动线程，但是执行 alter 语句就会出现大量 MDL 锁，且 alter 语句本身也是锁住的状态，基本是因为有未完成提交的事务，评估确定风险可控之后，将这些未提交的事务 kill 掉，就可以完成 alter 操作了。
以上，就是我在 MySQL 在线 DDL 修改表结构的一些经验分享，希望路过的大神如果有更好的方案能指点一二，不吝赐教。