PT-Online-Schema-Change 详解

pt-online-schema-change

英文描述是: ALTER tables without locking them,即在线改表。

工具下载地址

https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

使用场景

在 mysql 5.6 版本以前，修改表结构如添加索引、修改列，需要锁表，期间不能写入，对于一个有几千万数据的大表来说，这个简直不能接收，对于分秒必争的互联网时代，服务中断很长时间的后果是很严重的。

升级Mysql 到5.6 版本后，情况会好转，支持online-DDL, 但是实际在Alter表的时候，还会有可能锁表。

pt-online-schema-change是Percona-toolkit一员， 支持在不锁表的情况下，在线改表。

PT-OSC 工作流程

1. 创建一个和要执行 alter 操作的表一样的新的空表，后缀默认是new。

2. 在新表执行alter table 语句，因为是空表，执行速度很快。

3. 在原表中创建触发器3个触发器分别对应insert,update,delete操作。

4. 以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表，注意这里是Replace操作。

5. 表明替换 将原表名table修改为 tableold, 将tablenew 表明修改为原表名tabl

6. 如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理。

7. 默认最后将旧原表删除。

使用和常见命令

使用方法：

pt-online-schema-change [OPTIONS] DSN

options是对应的选项 可以在DSN里面指定数据库和表名。

例如，在数据库test中，表名是subscriber,加一列,列名是： name

pt-online-schema-change --alter "ADD COLUMN name VARCHAR(64) DEFAULT ''" D=test,t=subscriber

工具输出:

”--print”, 选项，打印详细输出信息。 “--statistics” ，选项，打印统计信息。 如：

# Event Count

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

# INSERT 50

常见选项：

1. --dry-run

创建并修改新表，但不会建触发器，拷贝数据。

2.--execute

如果我们测试好后，需要真正的执行alter操作，则需要指定这个选项，如果不指定，工具会默认只做一些检查然后退出。

3.-h xxx -P xxx -u xxx -p xxx: host, port

数据库IP，端口，用户名，密码

4.--alter

实际执行的alter语句， 多个更改用逗号分隔。 以下事项需要注意：

绝大部分情况下表上需要有主键或唯一索引，因为工具在运行当中为了保证新表也是最新的，需要旧表上创建 DELETE和UPDATE 触发器，同步到新表的时候有主键会更快。个别情况是，当alter操作就是在c1列上建立主键时，DELETE触发器将基于c1列。

子句不支持 rename 去给表重命名。

alter命令原表就不支持给索引重命名，需要先drop再add，在pt-osc也一样。(mysql 5.7 支持 RENAME INDEX oldindexname TO newindexname)

但给字段重命名，千万不要drop-add，整列数据会丢失，使用change col1 col1_new type constraint（保持类型和约束一致，否则相当于修改 column type，不能online）

子句如果是add column并且定义了not null，那么必须指定default值，否则会失败。

如果要删除外键（名 fk_foo），使用工具的时候外键名要加下划线，比如--alter "DROP FOREIGN KEYfkfoo"

5.D=xxx , t=xxx 指定数据库和表

风险

1. 默认如果检测到有复制过滤会拒绝改表，修改参数为--[no]check-replication-filters

2. 默认如果检测到主从复制延迟会自动停止数据拷贝，调节参数为--max-lag

3. 默认如果检测到服务器负载过重会停止或中断操作，调节参数为--max-load和--critical-load

4. 默认会设置锁等待超时时间为1s来避免干扰其他事务的进行，调节参数为--lock-wait-timeout

5. 默认如果检测到外键冲突后会拒绝改表，调节参数为--alter-foreign-keys-method

6. 该工具不能在PXC（Percona XtraDB Cluster）集群中对myisam表进行改表操作

建议操作方法

1. 先执行--dry-run 检查系统支持情况。

2. 执行 --execute, 但是，设置如下选项：

--no-drop-old-table

--no-drop-new-table

--no-swap-tables

--no-drop-triggers

3. 切换服务器，手动删除触发器，改表名。

欢迎关注转发"小强聊架构"。