mysql 修改表结构 导致_MySQL在线DDL修改表结构的简单经验分享

一、故障背景

内部故障群反馈：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默认的工作模式，它会查看从库情况，