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

摘 要

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

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 的。

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

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

四、小结

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

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

关于Flashback的小测试(r10笔记第15天)

对于Oracle的Flashback来说,在11g里面有了一个很细微的变化,可以说是一个很不错的福利,那就是开启闪回不需要重启数据库至mount状态下,...

3414
来自专栏FreeBuf

新手指南:DVWA-1.9全级别教程之SQL Injection

目前,最新的DVWA已经更新到1.9版本(点击原文查看链接),而网上的教程大多停留在旧版本,且没有针对DVWA high级别的教程,因此萌发了一个撰写新手教程的...

5118
来自专栏数据和云

【循序渐进Oracle】Oracle的物理备份(上)

编辑手记:备份重于一切,我们必需知道,系统总是要崩溃的,没有有效的备份只是等哪一天死!今天你备份了吗?我们一起来回顾Oracle的物理备份,本文摘自《循序渐进O...

3478
来自专栏散尽浮华

mysqldump数据导出问题和客户端授权后连接失败问题

1,使用mysqldump时报错(1064),这个是因为mysqldump版本太低与当前数据库版本不一致导致的。 mysqldump: Couldn't exe...

2489
来自专栏杨建荣的学习笔记

配置dg broker的问题分析及修复(r6笔记第84天)

最近从同事那儿接手了一套新环境,备库因为服务器问题已经下架,重新配了一台服务器,所以需要搭一套备库,主库已经配置好了,而且同事已经把在主库把dg broker配...

3797
来自专栏Linyb极客之路

MySQL数据库“十宗罪”(十大经典错误案例)

Too many connections(连接数过多,导致连接不上数据库,业务无法正常进行)

1622
来自专栏杨建荣的学习笔记

巧用flashback database实现灵活的数据回滚(r5笔记第16天)

之前写了一篇博文分享了使用flashback database的特性来在测试环境中避免重复导入大批量的数据,造成时间和存储空间的浪费。http://blog.i...

3413
来自专栏xiaoheike

Tomcat生成的session持久化到MySQL

The default Tomcat default session storage mechanism uses temporary files. To sa...

1094
来自专栏北京马哥教育

没遇到过这十个MySQL 数据库经典错误,你一定不是个好工程师

笔者在刚开始学习数据库的时候,没少走弯路。经常会遇到各种稀奇古怪的 error 信息,遇到报错会很慌张,急需一个解决问题的办法。跟无头苍蝇一样,会不加思索地把错...

6348
来自专栏Netkiller

Linux 系统与数据库安全

目录 1. 帐号安全 1.1. Shell 安全 1.2. .history 文件 2. 临时文件安全 3. 其他安全问题 4. 防火墙配置 5. 数据库安全 ...

3735

扫码关注云+社区

领取腾讯云代金券