> 技术杂谈 > MySQL在线DDL修改表结构的简单经验分享

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

技术杂谈 张戈 2019-10-07 15:39:34 91429 阅读 0 评论

简介 内部故障群反馈:XX系统卡住不可用了,请帮忙看看; 排查发现是有一个alter修改数据库的表结构的变更,出现了大量的MDL锁,

一、故障背景

内部故障群反馈:XX系统卡住不可用了,请帮忙看看;

排查发现是有一个alter修改数据库的表结构的变更,出现了大量的MDL锁,导致服务不可用,最后通过kill掉这个alter恢复了服务。当然, 这个alter需求也就暂时搁置了。

业务需求的变更肯定还是要继续执行的,因此就有了各种尝试….


二、辅助工具

先尝试了2个已知的辅助工具:


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工具,还是非常给力的!

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 out2018-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-keysDANGER! 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-checksset 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、查看事务等待情况:

SELECTr.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_queryFROMinformation_schema.innodb_lock_waits wINNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_idINNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

2、查看当前进行中的事务:

5.5版本(我们生产环境版本):

SELECTa.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.INFOFROMinformation_schema.INNODB_TRX aLEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.idWHEREb.COMMAND = 'Sleep';

:5.6版本(5.6原生支持在线DDL,感兴趣的可以研究下)

SELECTa.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_TEXTFROMinformation_schema.INNODB_TRX aLEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.idAND b.COMMAND = 'Sleep'LEFT JOIN PERFORMANCE_SCHEMA.threads c ON b.id = c.PROCESSLIST_IDLEFT 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修改表结构的一些经验分享,希望路过的大神如果有更好的方案能指点一二,不吝赐教。