pt-online-schema-change在线改表工具开发部分-优快云博客

本文介绍了pt-online-schema-change工具的使用方法及注意事项，包括如何在线修改MySQL表结构而不中断服务，以及在使用过程中可能遇到的问题和解决方案。

pt-online-schema-change在线改表工具

开发部分

pt-online-schema-change

pt-online-schema-change 模仿MySQL修改内部表的方法,不过它是通过创建新表并复制数据.也就是说,原始的表不会被锁,客户端可以持续的读写数据.

根据原表创建一个新的空表,并按需求修改.然后从原表向新表复制数据.当复制完成时,挪走原始表,并用新表代替.默认情况下,会把原表删除.

数据复制过程是一小块一小块的复制,也可以通过--chunk-time参数调整块的大小.这个参数的工作原理与pt-table-check中的类似.

在复制数据过程中,任何数据的改动都会在新表中体现出来,因为这个工具会在原表中创建触发器,在原表更新的数据,在新表也进行了更新.如果在原表已经定义过触发器,那么这个工具就不能工作了.

当工具把数据全部复制到新表后,它会自动执行RENAME TABLE操作,同时修改原表和新表.这个操作完成后,会自动删除原表.

外键会让工具操作起来更负载并且会增加额外的风险.有外键约束的情况下,自动重命名原表会让外键失效.所以,在表结构修改完成后,该工具必须重新更新外键.这个工具有两种方法可以完成这个操作.你可以看文档的--alter-foreign-keys-method参数.

外键也会导致一些副作用,修改完成的表会出现相同的外键索引(除非在ALTER语句中指定了不同的外键),但是这些外键的名字不会一样,避免冲突.

为了安全,这个工具只有在指定--execute参数时才会修改表.默认情况下,是不加--execute参数的.该工具支持多种措施,防止产生高负载或其他问题,比如,自动检查从库,连接从库,可能会使用下列方法进行安全检查:

如果检测到启用了”复制过滤器”,该工具不会执行操作.见--[no]check-replication-filters参数

如果检测到复制出现延迟,该工具会暂停复制数据.见--max-lag 参数

如果检测到服务器负载过高,该工具会暂停或者停止.见--max-load和--critical-load 参数

该工具设置 innodb_lock_wait_timeout=1 and (for MySQL 5.5 and newer) lock_wait_timeout=60,因此它尽可能的减小锁争用,避免影响其他事务.这两个MySQL变量值可以通过 --set-ars参数设置.(innodb_lock_wait_timeout,在准备执行一次事务时,如果innodb等待了50秒(默认值)后还没有获得所申请的数据锁,innodb就将回滚这次事务. lock_wait_timeout尝试获取元数据锁的等待时间(默认值是一年),原数据锁是指访问表,触发器等对象而产生的锁.)

如果修改的表存在外键约束,并且没有指定--alter-foreign-keys-method参数,那么该工具不会执行操作.

该工具不能修改 Percona XtraDB Cluster中各节点的MyISAM表

---------Percona XtraDB Cluster

pt-online-schema-change 可以在Percona XtraDB Cluster (PXC) 5.5.28-23.7 或者更高的版本使用.但是它有两个限制:必须是InnoDB引擎的表,并且wsrep_OSU_method 要设置为TOI,否则会报错退出.

---------OUTPUT

该工具是动态打印信息到STDOUT(标准输出)的,因此可以看到它正在做的事情.在数据复制阶段,它把进度信息打印到STDERR(标准错误输出)中.可以指定--print参数,得到更多的信息.

如果--statistics被指定,在最后完成时,会生成一个如下的报告:

# Event Count

# ====== =====

# INSERT 1

完整的工作流程

4826E0E469934201AF74EAF9AFC824BE

参数说明

--alter

type: string

结构修改,不带ALTER TABLE关键字.你可以执行多个表的修改操作,在它们之间用逗号分割.关于ALTER TABLE语法,请参考MySQL手册.

该参数有以下局限性,如果被触发,会导致这个工具执行失败:

不能指定rename语句.

不能用删除列添加新列的方法重命名.该工具将不能复制原表中此列的数据到新列.

如果你添加一个没有默认值新列并且属性设置为NOT NULL,该工具将不能执行,它不会给你指定一个默认值.

删除外键约束,需要指定一个外键的名字,但是,这个名字并不是外键的名字,而是一个区别于外键的名字.这是MySQL的限制.在创建新表的时候, pt-online-schema-change会创建一个以下划线开头的外键.比如

CONSTRAINT `_fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)

然后,必须这样指定

--alter "DROP FOREIGN KEY _fk_foo"

在MySQL 5.0 share_mode 模式中不能使用。这是mysql的一个bug 详细信息可以看：https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html

--alter-foreign-keys-method

如何把外键引用到新表?需要特殊处理带有外键约束的表,以保证它们可以应用到新表.当重命名表的时候,外键关系会带到重命名后的表上.

该工具有两种方法,可以自动找到子表,并修改约束关系.

auto, 在rebuild_constraints和drop_swap两种处理方式中选择一个.

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

drop_swap, 执行FOREIGN_KEY_CHECKS=0,禁止外键约束,删除原表,再重命名新表.

这种方式很快,也不会产生阻塞,但是有风险:

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

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

none, 类似"drop_swap"的处理方式,但是它不删除原表,并且外键关系会随着重命名转到老表上面.使用SHOW ENGINE INNODB STATUS;命令会发现如下错误信息

Trying to add to index `idx_fk_staff_id` tuple:

DATA TUPLE: 2 fields;

0: len 1; hex 05; asc ;;

1: len 4; hex 80000001; asc ;;

But the parent table `sakila`.`staff_old`

or its .ibd file does not currently exist!

7E6012858F8B46759DB162F46E059215

--ask-pass

隐式输入连接MySQL的密码

--port

-P，连接数据库的端口

--socket:

-S，连接的套接字文件

--charset

设置字符集,相当于用客户端执行"SET NAMES UTF8"命令

--[no]check-alter

解析并检查alter指定的命令:

在以前的版本,使用CHANGE COLUMN命令会导致数据丢失,现在的版本虽然改进,但是在执行前,还应该使用 --dry-run 和 --print 查看一下详细的操作情况.

DROP PRIMARY KEY, 执行该命令的话,会发出警告.

--check-interval

检查间隔,默认是1秒.请看--max-lag参数.

--[no]check-plan

为了安全,检查查询的执行计划.默认情况下,这个工具在执行查询之前会先EXPLAIN,以获取一次少量的数据,如果是不好的EXPLAIN,那么会获取一次大量的数据.

这个工具会多次执行EXPALIN,如果EXPLAIN不同的结果,那么就会认为这个查询是不安全的.

--[no]check-replication-filters

检查MySQL的复制过滤器,如果存在就报错退出.

如:binlog_ignore_db 和 replicate_do_db

--check-slave-lag

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

--chunk-index

为chunk指定一个索引(使用FORCE INDEX语法).

默认情况下,工具会自动选择一个合适的索引.如果指定的索引不存在,该工具会自动选择一个合适的.

--chunk-index-columns

选择使用具有n列的索引,多用于复合索引.

--chunk-size

指定块的大小,默认是1000行,可以添加k,M,G后缀.这个块的大小要尽量与--chunk-time匹配.

如果明确指定这个选项,那么每个块就会指定行数的大小.

--chunk-size-limit

当需要复制的块远大于设置的chunk-size大小,就不复制.默认值是4.0

一个没有主键或唯一索引的表,块大小就是不确定的.

--chunk-time

在chunk-time执行的时间内,动态调整chunk-size的大小,以适应服务器性能的变化.

该参数设置为0,或者指定chunk-size,都可以禁止动态调整.

--config

执行配置文件,必须在命令行的第一个参数位置.

--critical-load

最大负荷类型:数组;默认值:Threads_running = 50

检查全局状态后,如果负载太高就中止。选择接受一个以逗号分隔的MySQL状态变量和阈值。一个可选的= MAX_VALUE(或:MAX_VALUE)可以按照每个变量。如果不是,该工具确定一个阈值通过检查当前值在启动时,翻倍。

--database

选择数据库

--table

表明

--defaults-file

-F，读取配置文件

--[no]check-replication

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

--new-table

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

--check-slave-lag : 暂停数据复制直到从库的延迟小于 --max-lag（默认值为1）

#####################################################################################################################################################################

发现问题：

在执行在线改表操作的时候出现报错，报错如下：

BC6CDFC246A344C5B7A81280D636EEE9