(转)pt-online-schema-change在线修改表结构

时间：2022-11-27 18:43:05

原文：http://www.ywnds.com/?p=4442

一、背景

MySQL大字段的DDL操作：加减字段、索引、修改字段属性等，在5.1之前都是非常耗时耗力的，特别是会对MySQL服务产生影响。在5.1之后随着Plugin Innodb的出现在线加索引的提高了很多，但是还会影响（时间缩短了），主要是在更改期间，会生成一个互斥锁，阻塞对整个表的所有操作。不过MySQL 5.6可以避免上面的情况，支持在线DDL操作了。但目前大部分在用的版本都是5.6之前的，所以DDL操作一直是运维人员头疼的事。那如何在不锁表的情况下安全快速地更新表结构？现在来说明下percona-toolkit的pt-online-schema-change（简称:OSC）的使用说明，可以很好的解决上述的问题。

在我们的以前做法中，为了不影响线上业务，我们一般采用：先在线下从库更改表结构，然后替换线上从库，这样一台台的修改，最后做一下主库切换，这个过程会耗费很长时间，并且在做主库切换时，风险也非常的大，我们怎样才能让时间更短，且能不阻塞读写情况下在线修改呢？早在2008年Shlomi Noach 就利用触发器的原理，开发了python版本oak-online-alter-table在线更改表结构脚本，最近，Percona公司在自己的percona-toolkit脚本集合中也发布了在线更改表结构的perl版本脚本pt-online-schema-change，Facebook公司也开发自己的在线更改表结构php版本脚本OnlineSchemaChange.php，而它们最底层的实现原理都为触发器。因为oak-online-alter-table不确定是否在被开发与支持，而OnlineSchemaChange.php的使用比较复杂，且有很多功能不支持如：表结构中如果有外键的情况，故在此我就Percona公司的脚本做详细的剖析。

二、OSC详细实现原理及过程

pt-online-schema-change在线更改表结构的实现核心有如下几个过程：（注：在跟改过程中涉及到三个表：原表、tmp_table即作为原表导数据的临时表，old_table在最后rename原表的结果表）

第一步：新建tmp_table，表结构同原表。

第二步：在tmp_table上更改表结构为需要的表结构。

第三步：在原表上创建三个触发器，以便后续原表上的操作同步到新表。

创建触发器，用于记录从拷贝数据开始之后，对源数据表继续进行数据修改的操作记录下来，用于数据拷贝结束后，执行这些操作，保证数据不会丢失。如果表中已经定义了触发器这个工具就不能工作了。我们可以看到这三个触发器分别对应于INSERT、UPDATE、DELETE三种操作：

1） pt_osc_test_online_table_del，DELETE操作，我们注意到DELETE IGNORE，当新有数据时，我们才进行操作，也就是说，当在后续导入过程中，如果删除的这个数据还未导入到新表，那么我们可以不在新表执行操作，因为在以后的导入过程中，原表中改行数据已经被删除，已经没有数据，那么他也就不会导入到新表中。

2） pt_osc_test_online_table_ins，INSERT操作，所有的INSERT INTO全部转换为REPLACE INTO，为了确保数据的一致性，当有新数据插入到原表时，如果触发器还未把原表数据未同步到新表，这条数据已经被导入到新表了，那么我们就可以利用replace into进行覆盖，这样数据也是一致的。

3） pt_osc_test_online_table_upd，UPDATE操作，所有的UPDATE也转换为REPLACE INTO，因为当新的数据的行还未同步到新表时，新表是不存在这条记录的，那么我们就只能插入该条数据，如果已经同步到新表了，那么也可以进行覆盖插入，所有数据与原表也是一致的。

我们也能看出上述的精髓也就这这几条replace into操作，正是因为这几条replace into才能保证数据的一致性。

第四步：拷贝原表数据到新表，数据量大时会根据主键进行分段chunk插入。

数据的拷贝是基于小块(根据chunk-time参数指定)进行的，而且根据主键或者索引进行选择，所以对整体服务器性能影响较小。它是通过一些查询（基本为主键、唯一键值）分批把数据导入到新的表中，在导入前，我们能通过参数 --chunk-size 对每次导入行数进行控制，已减少对原表的锁定时间，并且在导入时，我们能通过 --sleep 参数控制，在每个chunk导入后与下一次chunk导入开始前sleep一会，sleep时间越长，对于磁盘IO的冲击就越小。

第五步：交换表，其通过一个RENAME TABLE同时处理两个表，实现原子操作。

其通过一个RENAME TABLE同时处理两个表，实现原子操作。在rename过程，其实我们还是会导致写入读取堵塞的，所以从严格意思上说，我们的OSC也不是对线上环境没有一点影响，但由于rename操作只是一个修改名字的过程，也只会修改一些表的信息，基本是瞬间结束，故对线上影响不太大。

第六步：清理以上过程中的old表，触发器。

总结

以上即为整个Percona OSC的过程，我们看到精华部分就触发器那一块，不过还有很多细节未介绍，如：外键、记录binlog等等，由于环境的复杂性，此工具还是有很多风险，如以下几个方面问题或者需要规避的一些问题：

1）此工具不是原子操作，如果某一点失败，不仅仅会留下很多中间过程的垃圾文件，而这些文件很难完全清理，并且如果有这些文件存在，那么就不能在次执行OSC操作。

2）在执行时，尽量避免有这个表的批量更新、锁表、优化表的操作，我们能想象的到，如果有锁表、优化表那么OSC是否还能正常执行？

3）如果存在主从结构，那么尽量在从库先执行。因为如果在主库执行完毕后再到从库执行，我们能想象，主库字段多，此时数据同步到从库，会不会有问题呢？

4）必须有单一列的主键或者单一唯一键，这样我们在insert select * from分片时，是不是能更好的处理量呢？

5）不要有外键，尽管脚本经过严格测试，但是是否还有bug，也未知，表的外键是不是会带来更多的问题呢？

6）在执行之前，我们是不是要对磁盘容量进行评估呢？因为OSC会使用表的一倍以上空间。

以上列到的，只是部分问题，我想如果需要在线进行实施，还需要经过严格的测试，但是它的实现为我们提供了一个很好的在线更改表结构方法，我相信只要我们能很好的规避他的弊端，它会给我们带来很大的帮助，对于MySQL 5.6之前的数据库版本，在MySQL 5.6版本开始已经支持Online DDL操作了。

三、OSC使用语法

具体安装可以参考：Percona Toolkit工具集介绍

 

常用选项说明：

--user，-u

#连接的用户名

--password，-p

#连接的密码

--database，-D

#连接的数据库

--port，-P

#连接数据库的端口

--host，-h

#连接的主机地址

--socket，-S

#连接的套接字文件

--ask-pass

#隐式输入连接MySQL的密码

--charset

#指定修改的字符集

--defaults-file，-F

#读取配置文件

--alter

#结构变更语句，不需要alter table关键字，可以指定多个更改，用逗号分隔。如下场景，需要注意：

1）不能用RENAME来重命名表。

2）列不能通过先删除，再添加的方式进行重命名，这会将数据拷贝到新列。

3）如果加入的列非空而且没有默认值，则工具会失败，其不会为你设置一个默认值，必须显示指定。

4）删除外键(drop foreign key constrain_name)时，需要指定名称_constraint_name，而不是原始的constraint_name。如：CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)，需要指定：–alter “DROP FOREIGN KEY _fk_foo”

--alter-foreign-keys-method

#如何把外键引用到新表？需要特殊处理带有外键约束的表，以保证它们可以应用到新表。当重命名表的时候，外键关系会带到重命名后的表上。该工具有两种方法，可以自动找到子表，并修改约束关系。

1）auto：在rebuild_constraints和drop_swap两种处理方式中选择一个。

2）rebuild_constraints：使用ALTER TABLE语句先删除外键约束，然后再添加。如果子表很大的话，会导致长时间的阻塞。

3）drop_swap： 执行FOREIGN_KEY_CHECKS=0，禁止外键约束，删除原表，再重命名新表。这种方式很快，也不会产生阻塞，但是有风险：

3.1 在删除原表和重命名新表的短时间内，表是不存在的，程序会返回错误。

3.2 如果重命名表出现错误，也不能回滚了，因为原表已经被删除。

4）none： 类似”drop_swap”的处理方式，但是它不删除原表，并且外键关系会随着重命名转到老表上面。

--[no]check-alter

#默认yes，语法解析。配合 --dry-run 和 --print 一起运行，来检查是否有问题（change column，drop primary key）。

--max-lag

#默认1s，每个chunk拷贝完成后，会查看所有复制Slave的延迟情况。要是延迟大于该值，则暂停复制数据，直到所有从的滞后小于这个值，使用Seconds_Behind_Master。如果有任何从滞后超过此选项的值，则该工具将睡眠 --check-interval 指定的时间，再检查。如果从被停止，将会永远等待，直到从开始同步，并且延迟小于该值。如果指定 --check-slave-lag ，该工具只检查该服务器的延迟，而不是所有服务器。

--check-slave-lag

#指定一个从库的DSN连接地址，如果从库超过 --max-lag 参数设置的值，就会暂停操作。

--recursion-method = none

#默认是show processlist，发现从的方法，也可以是host，但需要在从上指定report_host，通过show slave hosts来找到，可以指定none来不检查Slave的主从延迟。

--check-interval

#默认是1。 --max-lag 检查的睡眠时间。

--[no]check-plan

#默认yes，检查查询执行计划的安全性。

--[no]check-replication-filters

#默认yes，如果工具检测到服务器选项中有任何复制相关的筛选，如指定binlog_ignore_db和replicate_do_db此类。发现有这样的筛选，工具会报错且退出。因为如果更新的表Master上存在，而Slave上不存在，会导致复制的失败。使用 --no-check-replication-filters 选项来禁用该检查。

--[no]swap-tables

#默认yes，交换原始表和新表，除非你禁止 --[no]drop-old-table 。

--[no]drop-triggers

#默认yes，删除原表上的触发器。 --no-drop-triggers 会强制开启 --no-drop-old-table ，即不删除触发器就会强制不删除原表。

--new-table-name

#复制创建新表的名称，默认%T_new。

--[no]drop-new-table

#默认yes。删除新表，如果复制组织表失败。

--[no]drop-old-table

#默认yes。复制数据完成重命名之后，删除原表。如果有错误则会保留原表。

--max-load

#默认为Threads_running=25。每个chunk拷贝完后，会检查SHOW GLOBAL STATUS的内容，检查指标是否超过了指定的阈值。如果超过，则先暂停。这里可以用逗号分隔，指定多个条件，每个条件格式：status指标=MAX_VALUE或者status指标:MAX_VALUE。如果不指定MAX_VALUE，那么工具会这只其为当前值的120%。

--critical-load

#默认为Threads_running=50。用法基本与 --max-load 类似，如果不指定MAX_VALUE，那么工具会这只其为当前值的200%。如果超过指定值，则工具直接退出，而不是暂停。

--default-engine

#默认情况下，新的表与原始表是相同的存储引擎，所以如果原来的表使用InnoDB的，那么新表将使用InnoDB的。在涉及复制某些情况下，很可能主从的存储引擎不一样。使用该选项会默认使用默认的存储引擎。

--set-vars

#设置MySQL变量，多个用逗号分割。默认该工具设置的是：wait_timeout=10000、innodb_lock_wait_timeout=1、lock_wait_timeout=60。

--chunk-size-limit

#当需要复制的块远大于设置的chunk-size大小，就不复制，默认值是4.0，一个没有主键或唯一索引的表，块大小就是不确定的。

--chunk-time

#在chunk-time执行的时间内，动态调整chunk-size的大小，以适应服务器性能的变化，该参数设置为0。或者指定chunk-size，都可以禁止动态调整。

--chunk-size

#指定块的大小，默认是1000行，可以添加k,M,G后缀。这个块的大小要尽量与 --chunk-time 匹配，如果明确指定这个选项，那么每个块就会指定行数的大小。

--[no]check-plan

#默认yes。为了安全，查查询的执行计划，默认情况下，这个工具在执行查询之前会先EXPLAIN，以获取一次少量的数据。如果是不好的EXPLAIN，那么会获取一次大量的数据，这个工具会多次执行EXPALIN，如果EXPLAIN不同的结果，那么就会认为这个查询是不安全的。

--statistics

#打印出内部事件的数目，可以看到复制数据插入的数目。

--dry-run

#创建和修改新表，但不会创建触发器、复制数据、和替换原表。并不真正执行，可以看到生成的执行语句，了解其执行步骤与细节。 --dry-run 与 --execute 必须指定一个，二者相互排斥。和–print配合最佳。

--execute

#确定修改表，则指定该参数会真正开始执行。 --dry-run与--execute 必须指定一个，二者相互排斥。

--print

#打印SQL语句到标准输出。指定此选项可以让你看到该工具所执行的语句，和 --dry-run 配合最佳。

--progress

#复制数据的时候打印进度报告，二部分组成：第一部分是百分比，第二部分是时间。

--quiet，-q

#不把信息标准输出。

四、OSC使用实战

测试一：简单使用

第一步：创建一个测试表，表引擎为myisam

PS：使用OSC时，原表需要一个主键或唯一索引，因为删除的触发器需要，否则数据不会被复制。上面已经说明了该工具的实现方式，下面来执行看是否有效。

第二步：更改存储引擎查看OSC实现方式和原理

 

如果表没有主键或唯一键，会出现如下这一句：

第三步：使用–execute正式修改表的存储引擎（DDL操作）

 

上面指定了 --execute ，输出的信息里面也表示原表已经被修改成功并且记录了很详细的操作信息，查看表的引擎是否已经被修改：

测试二：主从环境

在主从环境下，修改表结构时尽量在从库先执行，因为如果先在主库执行完毕后再到从库执行，我们能想象，主库字段多，数据同步到从库，会不会有问题呢，要考虑？

另外在主从环境下，如果添加一个字段需要加上 --no-check-replication-filters 选项，语句如下。

如果不加 --no-check-replication-filters 选项的话，那么就会报错的，因为默认会检查主从。除了add column，也可以modify column，drop column，对于change column则需要指定： --no-check-alter 参数。

测试三：主外键表的基本操作

 

执行错误退出，提示需要指定： --alter-foreign-keys-method 参数来操作有外键的表。要是没有外键而加了参数的话会出现：

No foreign keys reference `aaa`.`xx`; ignoring –alter-foreign-keys-method。

 

对可靠性要求不高可以用auto模式更新，要是可靠性要求高则需要用rebuild_constraints模式。即：

总结

1）上面的测试都是把原表删除了，要是不删除原表则： --no-drop-old-table ，这样会让原表（_test_binlog_old）保留。

2）要是在线上环境上添加字段，但又不想影响到服务，可以用参数： --max-load 去执行该工具，默认是Threads_running=25，即当前有这么多线程在运行的时候就暂停数据的复制，等少于该值则继续复制数据到新表：

3）当业务量较大时，修改操作会等待没有数据修改后，执行最后的rename操作。因此，在修改表结构时，应该尽量选择在业务相对空闲时，至少修改表上的数据操作较低时，执行较为妥当。

4）如果对外键表操作时，四种外键操作类型需要根据表的数据量和可靠程度，进行选择。处于可靠性的原因，尽量使用rebuild_constraints类型，如果没有可靠性要求，可以使用auto类型。

5）由于可能存在一定的风险，在操作之前，建议对数据表进行备份，可以使得操作更安全、可靠。

使用该工具的前提是处理的表需要有主键或则唯一索引。当处理有外键的表时，需要加 --alter-foreign-keys-method 参数，值可以根据情况设置。当是主从环境，不在乎从的延迟，则需要加 --recursion-method=none 参数。当需要尽可能的对服务产生小的影响，则需要加上 --max-load 参数。

标签：

相关文章