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

2018年3月28日09:11:33 发表评论 82 阅读

在线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:

结果,很不幸,我们的生产环境很(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:

结果在测试环境进行多次测试之后,应用到生产环境依然出现了阻碍,出现如下报错:

进一步看了下 help 参数:

 说明了 2 个问题:

1、gh-ost 对于有外键的表,修改之后外键约束将被删除;

2、ghost 也不支持有触发器的表(本以为 gh-ost 的工作原理不依赖触发器,应该就能兼容触发器,其实不然)。

三、最终解决

结合 2 个工具最终也无法完成所有修改之后,也只能硬着头皮再次尝试原始的 alter 语句,毕竟要开发花大力气去删除触发器、外键显然是不现实的...

在数据库执行 alter 之后,show processlist 立即发现大量 MDL 锁,一个是 alter 语句,另一批则是和表相关的 select 语句。

下意识认为是因为这些 select 阻塞了 alter 的执行,于是写了一个语句来 kill 这些 select 语句(已知清理 select 的影响):

结果并不奏效,才开始意识到 alter 这个语句其实一开始就没执行,而是阻塞等待的状态。那到底是什么阻塞 alter 语句了?查看 processlist 发现其他非 sleep 状态的连接都是在 alter 之后出现的,所以并不是造成阻塞的原因。继续 show processlist 看到一堆 sleep 状态连接,灵光一闪,联想到应该是有未完成提交的事务!

于是,使用如下步骤进行查看:

1、查看事务等待情况:

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

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

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

3、结果发现确实有 2 条未提交的事务,还是前 1 天的:

结合 start 时间、ip 地址以及 DB 名称,可以确定是后台 Twisted 接口启动时初始化的事务(真是坑啊!)

这次修改的是 ndb 这个库,因此尝试 kill 掉 trx_id 为 42B4A12DA 的事务对应的 process ID:

 

执行之后,就发现 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 修改表结构的一些经验分享内容,希望路过的大神如果有更好的方案能指点一二,不吝赐教。也希望此次我转载分享的内容对大家有所帮助,

  • 我的微信
  • 这是我的微信扫一扫
  • weinxin
  • 我的微信公众号
  • 我的微信公众号扫一扫
  • weinxin
avatar

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: