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

由于周五公司团建的关系所以此篇推迟了抱歉。

首先不得不在该篇里面梳理一个数据库热增加删除字段表的工具 pt-online-schema-change 这个工具在前面我的博文 《关于utf8mb4的学习了解笔记》里面有提到过，他是一个 online 的 ddl (data definition language) 工具。由于 MySQL 的ddl 语句在执行的时候会锁表，在数据量大的情况下锁表就会严重影响正常的数据写入。

既然都说到这里了，也总结一下我在网上查到的 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 新表代替原表，默认删除原表。

 

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

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 "

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 这是官方文档，基本上疑问都可以从这里得到解答。

 

--------------------------------------分割线----------------------------------------

pt-online-schema-change \
    u=dever,h=127.0.0.1,D=user,t=user,P=16033\
    --set-vars lock_wait_timeout=3 \
    --ask-pass \
    --execute \
    --charset=utf8mb4 \
    --no-check-replication-filters \
    --no-version-check \
    --alter "add column is_cxy20_user tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否创新院2020年学员'";

 

 

Reference:

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