数据库

MySQL在线DDL修改表结构的简单经验分享

Jager · 3月23日 · 2018年 · 3088次已读

一、故障背景

内部故障群反馈: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的。
MySQL在线DDL修改表结构的简单经验分享

让我们来对比一下其他同样使用python访问的正常连接请求,再断开前都会手动的commit。

MySQL在线DDL修改表结构的简单经验分享

 找到原因后有思考了下,是不是可以在建连后就设置autocommit=1呢?这样对于之后新变更的SQL就不要再考虑到手动commit的事情了,可以通过在初始化连接池的时候,对每一个连接进行设置,即

MySQL在线DDL修改表结构的简单经验分享

四、小结

对于MySQL在线DDL修改大表,gh-ost和pt-osc都是很不错的选择,前提是不能有坑爹的触发器和外键!当然,实在是遇上了也没什么办法,只能硬扛!很多时候,你会非常郁闷,明明数据库没什么负载,当前也没什么活动线程,但是执行alter语句就会出现大量MDL锁,且alter语句本身也是锁住的状态,基本是因为有未完成提交的事务,评估确定风险可控之后,将这些未提交的事务kill掉,就可以完成alter操作了。

以上,就是我在MySQL在线DDL修改表结构的一些经验分享,希望路过的大神如果有更好的方案能指点一二,不吝赐教。

7 条回应
  1. Zero2018-3-23 · 22:59

    沙发,嘿嘿

  2. 米扑博客2018-3-24 · 9:05

    实用技术贴,非常赞 😉

  3. 明月登楼2018-3-24 · 17:27

    好吧,没有看懂!好高深的样子!

  4. 鸟叔2018-3-28 · 10:35

    纯技术文章,看不懂

  5. 新闻头条2018-3-31 · 2:16

    文章不错非常喜欢

  6. 趣知识2018-4-6 · 17:57

    学习学习

  7. 葛一速2018-6-12 · 19:48

    实践出真理,佩服了