当前位置：首页 > news >正文

多个参数变更update_PTOSC在线DDL变更工具使用攻略

news 来源：原创 2024/5/18 17:38:21

pt-online-schema-change可以在不阻塞读写的情况下在线整理表结构、收集碎片、给大表加字段和索引，并且可以实时查看执行进度。

原理

pt-online-schema-change在工作过程时，首先会创建一张要修改的表的空表，在空表上执行alter语句。然后从原始表中copy数据行导入新表。拷贝完成后，会将原表移走，用新表代替原表，最后删除原始表。

数据是以一定的块大小从原表拷贝到临时表，在数据拷贝前，会在原表上创建delete触发器、update触发器、insert触发器，拷贝过程中在原表上进行的写操作都会通过触发器更新到临时表。

当完成数据拷贝过程后，会用rename table的方式交换原表和临时表的表名。

最后会删除触发器以及原表。

PT-OSC 工具的限制

原表上不能有触发器。
原表必须有主键。
如果原表有外键，需要使用--alter-foreign-keys-method指定特定值，否则工具不予执行。
要创建临时表，应该提前查看磁盘空间，如果磁盘空间不足会导致变更失败。

参数详解

--dry-run：创建和更改新表,但是不建立触发器，不拷贝数据，也不会替换原表。
--execute：这个参数的作用和前面工作原理的介绍的一样，会建立触发器，来保证最新变更的数据会影响至新表。注意：如果不加这个参数，这个工具会在执行一些检查后退出。这一举措是为了让使用充分了解了这个工具的原理，同时阅读了官方文档。
--alter：结构变更语句，可以指定多个更改，用逗号隔开。下面是一些条件限制，如果违反了这些限制，就会导致变更失败。
原表上必须有主键或者唯一索引。
不支持通过rename的方式给表重命名。
不支持索引重命名。，需要先drop在add。
子句中如果定义了add column并且定义了not null，必须制定default值，否则会失败。
如果要删除外键，使用工具的时候外键名要加下划线，比如 --alter “DROP FOREIGN KEY _fk_foo”。
--alter-foreign-keys-method：该参数定义了如何修改外键以便他们在新表中可以被引用。
比如要修改t1,t2有外键依赖于t1,t1_new是alter t1产生的临时表。这里的外键不是看t1上是否存在外键，而是作为子表的t2,在你rename t1时，t1不存在会导致t2的外键检测失败，从而不允许rename。
它支持两种不同的技术来实现这一点。
auto：自动选择实现方式，工具会优先使用rebuild_constraints方式。
rebuild_constraints：采用alter table的方式删除和新建外键，并重新添加引用新表的外键约束。优先选用此方式，除非子表太大，ALTER会花费很长时间。
drop_swap：禁用外键检查(FOREIGN_KEY_CHECKS=0)，然后删除原始表，重命名新表。这与通常我们认为的重命名不同，后者使用的是客户端无法检测到的原子重命名方式。这种方式更快，不会阻塞，但是风险更大。原因有二，首先，在删除原始表以及重命名新表之间的短时间内，对原表的查询会失败，其次，如果重命名失败会导致原表无法恢复。
--max-log：默认1S。每个chunks拷贝完成后，会查看check-slave-log指定的从库延迟信息，如果超过了max-log定义的值则暂停复制数据，知道延迟时间小于max-log定义的时间。
--chunk-time：在check-time执行的时间范围内，动态调整chunk-size的大小，以适应服务器性能的变化。设置为0，或者指定check-size的大小都可以禁止动态调整
--chunk-size：定义每次拷贝数据的大小，默认1000,可以添加K、M、G
--print：打印SQL语句到标准输出。

使用示例

1. 添加新列

pt-online-schema-change --user=root --password=root --alter "add column score int(3)" D=test,t=tuser --print --execute

2. 修改字段类型

pt-online-schema-change --user=root --password=root --alter "modify score varchar(10) DEFAULT NULL" D=test,t=tuser --print --execute --no-check-alter

3. 删除索引

pt-online-schema-change --user=root --password=root --alter "drop index name_age" D=test,t=tuser --print --execute

4. 增加索引

pt-online-schema-change --user=root --password=root --alter "add index idx_id_card(id_card)" D=test,t=tuser --print --execute

5. 修改存储引擎、碎片整理

pt-online-schema-change --user=root --password=root --alter "engine=innodb" D=test,t=tuser –execute

一些思考

在之前研究pt-osc的时候，思考过这么一个问题，我们知道在执行过程中会在原表上建立触发器，当有业务update原表时，触发器会在临时表上同样进行update，那如果此时原表上要update的数据还没有copy到临时表该怎么办呢？

后来了解到，在原表update时，临时表上采用的是replace into的方式调整数据，replace into和insert功能不太一样，replace into会先尝试插入数据到表中，如果发现表中已经有此行数据，则先删除再插入，否则直接插入。所以这也要求每张表必须有主键或者唯一索引，不然有可能会出现数据重复的情况。