热修改mysql数据库（生产环境中增加字段、索引不发生锁表）-CSDN博客

首先不得不在该篇里面梳理一个数据库热增加删除字段表的工具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

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

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

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

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

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

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

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

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

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

--set-vars:

type: string; default: wait_timeout=10000

Set these MySQL variables. Immediately after connecting to MySQL, this string will be appended to SET and executed.

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

--lock-wait-timeout：

type: int; default: 1

Set the session value of innodb_lock_wait_timeout. This option helps guard against long lock waits if the data-copy queries become slow for some reason. Setting this option dynamically requires the InnoDB plugin, so this works only on newer InnoDB and MySQL versions. If the setting’s current value is greater than the specified value, and the tool cannot set the value as desired, then it prints a warning. If the tool cannot set the value but the current value is less than or equal to the desired value, there is no error.

类型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.cnblogs.com/Mr-Echo/p/9435333.html