在线DDL修改生产环境的大表一直是运维、DBA一个很头痛的问题，今日在别人博客看到一个有关这方便的文章，个人觉得写的还不错，希望很小给你看了本文博客分享一些相关经验，希望对还在头痛的同学能有所帮助。
内部故障群反馈：XX 系统卡住不可用了，请帮忙看看；
排查发现是有一个 alter 修改数据库的表结构的变更，出现了大量的 MDL 锁，导致服务不可用，最后通过 kill 掉这个 alter 恢复了服务。当然， 这个 alter 需求也就暂时搁置了。
业务需求的变更肯定还是要继续执行的，因此就有了各种尝试....
先尝试了 2 个已知的辅助工具：
pt-online-schema-change，简称 pt-osc，是 Percona 开发了一系列工具 Percona Toolkit 包的功能之一。
pt-osc 工具的工作流程：
pt-osc 工具的一些限制条件：
pt-osc 工具的执行 demo：
| 1 2 3 4 5 6 7 8 9 | 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 工具，还是非常给力的！
gh-ost 是 github 开源的一个 DDL 工具，即 gitHub,s Online Schema Transmogrifier/Transfigurator/Transformer/Thingy 的缩写，意思是 GitHub 的在线表定义转换器。上一篇文章已经简单分享了 github 自用的 gh-ost 工具【传送门】，这里再搬运一下 gh-ost 的三种工作模式和相关限制：
模式一：连上从库，在主库上修改
这是 gh-ost 默认的工作模式，它会查看从库情况，找到集群的主库并且连接上去。修改操作的具体步骤是：
如果主库的二进制日志格式是 Statement，就可以使用这种模式。但从库就必须配成启用二进制日志（log_bin, log_slave_updates），还要设成 Row 格式（binlog_format=ROW），实际上 gh-ost 会在从库上帮你做这些设置。事实上，即使把从库改成 Row 格式，这仍然是对主库侵入最少的工作模式。
模式二、直接在主库上修改
如果没有从库，或者不想在从库上操作，那直接用主库也是可以的。gh-ost 就会在主库上直接做所有的操作。仍然可以在上面查看主从复制延迟，限制如下：
模式三、在从库上修改和测试
这种模式会在从库上做修改。gh-ost 仍然会连上主库，但所有操作都是在从库上做的，不会对主库产生任何影响。在操作过程中，gh-ost 也会不时地暂停，以便从库的数据可以保持最新。
gh-ost 的执行 demo：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | ./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 |
结果在测试环境进行多次测试之后，应用到生产环境依然出现了阻碍，出现如下报错：
| 1 2 | 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 参数：
| 1 2 3 4 5 6 | -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 的影响）：
| 1 | 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、查看事务等待情况：
| 1 2 3 4 5 6 7 8 9 10 11 | 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 版本（我们生产环境版本）：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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，感兴趣的可以研究下）
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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 天的：
| 1 2 3 4 5 6 7 8 | 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：
| 1 | 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 修改表结构的一些经验分享内容，希望路过的大神如果有更好的方案能指点一二，不吝赐教。也希望此次我转载分享的内容对大家有所帮助，
