- 一、故障背景
- 二、辅助工具
- 三、最终解决
- 四、小结
一、故障背景
内部故障群反馈：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：
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 工具，还是非常给力的！
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：
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 修改表结构的一些经验分享，希望路过的大神如果有更好的方案能指点一二，不吝赐教。