热修改 MySQL 数据库 pt-online-schema-change 的使用详解

曾经终败给现在 2022-02-14 02:49 105阅读 0赞

innodb 在 ddl 的时候所执行的操作：

1. 按照原始表 (original\_table) 的表结构和 ddl 语句，新建一个不可见的临时表 (temporary\_table)

2. 在原表上面加上 WRITE LOCK 阻塞所有的更新操作 (insert、delete、update等操作)

3. 执行 insert into tmp\_table select \* from original\_table

4. rename original\_table 和 tmp\_table 最后 drop original\_table

5. 最后释放掉 write lock

「来自 2018 年的补充：目前 MySQL 自己也提供了 onlineddl 的工具，在数据量不大的时候还是非常好用的，直接指定 inplace 也可以轻松达到不锁表加字段的效果」。

通过以上的步骤我们可以很容易的发现，这样操作在表锁定的情况是只能查询，不能写入。为了解决这个问题所以 percona 公司推出了一个不会阻塞的工具 pt-online-schema-change。

这里不得不再次介绍一下 pt-online-schema-change 是怎么做到在不阻塞写入的情况下改动数据库的：

1. 首先创建一个和你要执行的 alter 操作的表一样的空的表结构。

2. 执行我们赋予的表结构的修改，然后 copy 原表中的数据到新表里面。

3. 在原表上创建一个触发器在数据 copy 的过程中，将原表的更新数据的操作全部更新到新的表中来。 这里特别注意一下，如果原表中已经定义了触发器那么工具就不能工作了，因为 pt 使用到了数据库的触发器。

4. copy 完成之后，用 rename table 新表代替原表，默认删除原表。

了解了原理之后，理解起来就似乎不那么困难了。感觉这些问题也并不是什么高大上的问题了。下面来看个?

![复制代码][copycode.gif]

pt-online-schema-change \
    　　　　　　　　　　　　 h=ip_address, u=user_name, D=database, t=table \
    　　　　　　　　　　　　 --set-vars \
    　　　　　　　　　　　　 --lock-wait-timeout=60 \
     　　　　　　　　　　　  --ask-pass \
    　　　　　　　　　　　　 --execute \
    　　　　　　　　　　　　 --alter "add column shop_id int(11) DEFAULT NULL "

![复制代码][copycode.gif]

lock-wait-timout 这个参数在 MySQL 之前的版本被默认设置成 1s 但是最新的版本会被默认设置成 60s 。一般 60s 的超时还是比较合理的。因为在挪动大量数据的时候，可能会耗时比较长。

以上就是大致语法，这里来介绍几个参数：

\--set-vars:

字符串类型

在链接到mysql之后立即设置mysql变量，这个变量会给展示这些设置和执行。

\--lock-wait-timeout：

类型int，默认值是1秒

设置一个session值为innodb\_lock\_wait\_timeout.这个选项帮助你防止一个长时间的锁等待，这种情况一般会发生在比如说数据拷贝的时候，变得非常慢。设置这个选项需要innodb的插件，所以要innodb引擎和mysql比较新。如果设置的这个值比需要的值大，而且这个工具不能设置为一个需要值的话，就会报出warning。反之如果工具不能设置这个值，但是这个值又比所需要的值小的话，就不会发生什么。

\--ask-for:

在连接数据库的时候提示输入密码。

\--execute参数

只有加了这个参数，才会去真正执行添加触发器拷贝数据等一系列操作。

其他想要了解更加相信的信息可以访问这个文档[https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html][https_www.percona.com_doc_percona-toolkit_2.1_pt-online-schema-change.html] 这是官方文档，基本上疑问都可以从这里得到解答。

Reference:

https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html  pt-online-schema-change 官方文档

[copycode.gif]: /images/20220214/2323499d5e38418396322d7b9d10471e.png
[https_www.percona.com_doc_percona-toolkit_2.1_pt-online-schema-change.html]: https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html