Percona Toolkit系列之一:pt-online-schema-change

pt(Percona Toolkit)工具是由Percona公司开发的一个用perl语言编写的工具集,包含很多功能,例如在线更改数据表结构,校验主从数据,检查数据库状态,分析慢查询等。Percona Toolkit 是一组高级命令行工具，Percona 支持人员使用它来执行各种难以手动执行的 MySQL、MongoDB和系统任务。

下载地址为：https://www.percona.com/downloads/percona-toolkit/LATEST/

教程地址：https://docs.percona.com/percona-toolkit/

percona的网站国内有时打不开，可以访问国内的清华镜像

https://mirrors.cnnic.cn/percona/tools/yum/release/latest/RPMS/x86_64/

找到最新版本进行下载

一 PT的安装

1. 安装依赖包

yum install perl-DBI

yum install perl-DBD-MySQL

yum install perl-Time-HiRes

yum install perl-IO-Socket-SSL

yum install perl-TermReadKey

yum install perl-Digest-MD5

2. 安装 Percona Toolkit

rpm -ivh percona-toolkit-3.2.1-1.el7.x86_64.rpm

3. 测试是否安装成功

pt-query-digest --help

如果可以正常显示信息，则说明安装成功

二 PT工具的基本使用

下面我们开始学习应用于Mysql的工具的基本使用

1. pt-online-schema-change

这个工具是DBA经常会使用到的一个更改表的工具，在不阻塞读或写的情况下改变表的结构。

基本使用语法为：pt-online-schema-change [OPTIONS] DSN

下面列表列出部分options，更加详细的请使用pt-online-schema-change --help进行查看

OPTIONS：

类型备注：s=string, i=integer, f=float, h/H/a/A=comma-separated list, d=DSN, z=size, m=time

注意：--dry-run与--execute必须指定一个，二者相互排斥。

DSN为要操作的数据库和表。

DSN：

1.1 工作原理

1） 创建一个和你要执行 alter 操作的表一样的新表

2） 检查修改表的表结构，在新表上执行ALTER TABLE语句

3） 在原表上创建三个触发器分别对于INSERT UPDATE DELETE操作

4） 从原表拷贝数据到新表，在拷贝过程中，对原表的更新操作会写入到新表中

5） copy 完成以后，用rename table 新表代替原表。

6） 删除原表和触发器，完成表结构的修改。

1.2 工具限制

此工具也存在一些如下所示的限制：

1） 原表必须有主键或唯一索引，如果没有，将更改失败。

2） 如果线上的复制环境过滤器操作过于复杂，工具将无法工作。

3） 如果开启复制延迟检查，但主从延迟时，工具将暂停数据拷贝工作。

4） 如果开启主服务器负载检查，但主服务器负载较高时，工具将暂停操作。

5） 当表使用外键时，如果未使用--alter-foreign-keys-method参数，工具将无法执行。

1.3 工具工作分析

下面先通过几种情况，来分析一下它是如何工作的。

1.3.1 有主键PRIMARY KEY的情况：

目前，我们有数据库test，其中有一张表，结构如下：

CREATE TABLE `t_user` (

`id` int NOT NULL,

`nm` varchar(10) DEFAULT NULL,

`address` varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

现在，让我们执行如下命令，目的是增加一个字段nickname varchar(20)

pt-online-schema-change --user=root --password=Ygzlpaul*2022 --host=192.168.246.135 --alter "add column nickname varchar(20)" D=test,t=t_user --execute

提示执行成功了，我们先看看是否真的成功了呢？

查看表结构，发现的确执行成功：

接下来，我们看看，通过执行上述命令，数据库究竟做了什么吧

查看一下，目前所有的日志有哪些

SHOW MASTER LOGS;

目前就一个日志文件（这里是为了方便简单理解，做了Reset操作）

现在，执行SHOW BINLOG EVENTS IN 'binlog.000001'; 内容如下图所示：

为方便理解，以下内容，我将仅仅列出End_log_pos及其Info两个列的信息：

1）

End_log_pos: 539

Info: use `test`; CREATE TABLE `test`.`_t_user_new` (

`id` int NOT NULL,

`nm` varchar(10) DEFAULT NULL,

`address` varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /* xid=177 */

解释：建立了一张新表，结构与t_user完全相同

2）

End_log_pos: 768

Info: use `test`; ALTER TABLE `test`.`_t_user_new` add column nickname varchar(20) /* xid=178 */

解释：对新表做Alter，新增列nickname varchar(20)

3）

End_log_pos: 1133

Info: use `test`; CREATE DEFINER=`root`@`%` TRIGGER `pt_osc_test_t_user_del` AFTER DELETE ON `test`.`t_user` FOR EACH ROW DELETE IGNORE FROM `test`.`_t_user_new` WHERE `test`.`_t_user_new`.`id` <=> OLD.`id` /* xid=190 */

解释：在表t_user增加了一个AFTER DELETE 的触发器。请特别注意，连接字段用的是id，用的是主键列，这或许也就解释了，为什么使用pt-online-schema-change的时候，需要表中存在 PRIMARY KEY 或 UNIQUE INDEX的原因。

4）

End_log_pos: 1638

Info: use `test`; CREATE DEFINER=`root`@`%` TRIGGER `pt_osc_test_t_user_upd` AFTER UPDATE ON `test`.`t_user` FOR EACH ROW BEGIN DELETE IGNORE FROM `test`.`_t_user_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `test`.`_t_user_new`.`id` <=> OLD.`id`;REPLACE INTO `test`.`_t_user_new` (`id`, `nm`, `address`) VALUES (NEW.`id`, NEW.`nm`, NEW.`address`);END /* xid=191 */

解释：在表t_user增加了一个AFTER UPDATE 的触发器。

5）

End_log_pos: 2019

Info: use `test`; CREATE DEFINER=`root`@`%` TRIGGER `pt_osc_test_t_user_ins` AFTER INSERT ON `test`.`t_user` FOR EACH ROW REPLACE INTO `test`.`_t_user_new` (`id`, `nm`, `address`) VALUES (NEW.`id`, NEW.`nm`, NEW.`address`) /* xid=192 */

解释：在表t_user增加了一个AFTER INSERT 的触发器。

6）

End_log_pos: 2173

Info: BEGIN

7）

End_log_pos: 2244

Info: table_id: 134 (test._t_user_new)

8）

End_log_pos: 2324

Info: table_id: 134 flags: STMT_END_F

9）

End_log_pos: 2355

Info: COMMIT /* xid=195 */

解释：以上6到9，开启一个事务，进行拷贝数据。对于里面的详细信息，稍候，我们使用mysqlbinlog再来查看一下，这里暂时先跳过。

10）

End_log_pos: 2566

Info: use `test`; ANALYZE TABLE `test`.`_t_user_new` /* pt-online-schema-change */

解释：分析新表

11）

End_log_pos: 2824

Info: use `test`; RENAME TABLE `test`.`t_user` TO `test`.`_t_user_old`, `test`.`_t_user_new` TO `test`.`t_user` /* xid=203 */

解释：重命名原先的目标表名为OLD，同时，修改新建的表名为目标表名。

12）

End_log_pos: 3047

Info: use `test`; DROP TABLE IF EXISTS `_t_user_old` /* generated by server */ /* xid=204 */

解释：删除原表

13）

End_log_pos: 3248

Info: use `test`; DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t_user_del`

解释：删除AFTER DELETE 的触发器

14）

End_log_pos: 3449

Info: use `test`; DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t_user_upd`

解释：删除AFTER UPDATE 的触发器

15）

End_log_pos: 3650

Info: use `test`; DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t_user_ins`

解释：删除AFTER INSERT 的触发器

上面6）到7）具体日志记录了什么？

现在，就让我们使用mysqlbinlog再查看一下前面涉及到数据的部分的日志是如何的？

mysqlbinlog --base64-output=DECODE-ROWS -v --start-position=2173 --stop-position=2355 /var/lib/mysql/binlog.000001

从上面的截图可以看到记录了一系列insert语句（binlog_format=ROW）。

总结：从上面的日志分析，我们也可以得出其工作原理，及其执行步骤究竟是怎么样的。

1.3.2 无主键，有唯一索引UNIQUE KEY的情况：一个字段构成唯一索引：

目前，我们有数据库test，其中有一张表t_prj，结构如下：

CREATE TABLE `t_prj` (

`prjid` int NOT NULL,

`prjname` varchar(20) DEFAULT NULL,

`prjad` varchar(20) DEFAULT NULL,

UNIQUE KEY `uidx_id` (`prjid`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

我们仍然是增加一个字段，看看与有主键（PRIMARY KEY）有什么相同或者不同之处。

pt-online-schema-change --user=root --ask-pass --host=192.168.246.135 --alter "add column prjph tinyint" D=test,t=t_prj --execute

注：此次我们采用--ask-pass，需要输入密码，而不再以明文形式提供

我们跳过检查表结构环节，直接执行SHOW BINLOG EVENTS IN 'binlog.000001'; 查看日志内容，如下图所示：

仔细对比查看，发现与前面有主键（PRIMARY KEY）的执行步骤一致，这里就不再详细解释，唯一需要注意的地方就是，触发器的where条件引用的是唯一索引字段：`prjid`

对于上面的例子，唯一索引是一个字段构成的，那么，假设，我们更改为2个或2个以上字段构成唯一索引呢？

1.3.3 无主键，有唯一索引UNIQUE KEY的情况：三个字段构成唯一索引：

目前，我们有数据库test，其中有一张表t_pfs，结构如下：

CREATE TABLE `t_pfs` (

`pid` smallint NOT NULL,

`fid` smallint NOT NULL,

`sid` smallint NOT NULL,

`note` varchar(20) DEFAULT NULL,

UNIQUE KEY `uidx` (`pid`,`fid`,`sid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

继续增加一个字段，看看与有唯一索引UNIQUE KEY的情况：一个字段构成唯一索引有什么不同之处。

pt-online-schema-change --user=root --ask-pass --host=192.168.246.135 --alter "add column prjph tinyint" D=test,t=t_pfs --execute

仔细对比，发现触发器的where条件引用的是构成唯一索引字段的所有字段，

我截取其中的部分信息如下：

`test`.`_t_pfs_new`.`pid` <=> OLD.`pid` AND `test`.`_t_pfs_new`.`fid` <=> OLD.`fid` AND `test`.`_t_pfs_new`.`sid` <=> OLD.`sid`

1.3.4 有主键，也有唯一索引

那如果主键，唯一索引都有，是使用主键字段做where？还是唯一索引字段做where呢?

目前，我们有数据库test，其中有一张表t_prjo，结构如下：

CREATE TABLE `t_prjo` (

`id` int NOT NULL,

`prjid` int NOT NULL,

`prjname` varchar(20) DEFAULT NULL,

`prjad` varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `uidx_id` (`prjid`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

继续增加一个字段

pt-online-schema-change --user=root --ask-pass --host=192.168.246.135 --alter "add column prjph tinyint" D=test,t=t_prjo --execute

仔细对比，发现，触发器的where条件引用的是构成主键的字段，

我截取其中的部分信息如下：

`test`.`_t_prjo_new`.`id` <=> OLD.`id` /* xid=201 */

这里也就说明，主键优先级高于唯一索引，优先构成Where的条件。

1.3.5 没有主键，没有唯一索引

我们接下来看看，既没有主键，也没有唯一索引的情况下，工具的表现是如何的。

目前，我们有数据库test，其中有一张表t_nm，结构如下：

CREATE TABLE `t_nm` (

`nm` varchar(10) DEFAULT NULL,

`address` varchar(20) DEFAULT NULL,

`pw` tinyint DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

继续增加一个字段，看看没有主键，没有唯一索引的情况会发生什么。

pt-online-schema-change --user=root --ask-pass --host=192.168.246.135 --alter "add column prjph tinyint" D=test,t=t_nm --execute

结果，发现执行失败，有如下提示信息：

The new table `test`.`_t_nm_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.

Please check you have at least on UNIQUE and NOT NULLABLE index.

提示信息很明确，执行pt-online-schema-change，需要表最少有一个不能NULL的唯一键存在。

1.3.6 无主键，有多个唯一索引UNIQUE KEY的情况：

这里，我们测试一下如果存在多个唯一索引的情况，看看where条件是用的什么？

目前，我们有数据库test，其中有一张表t_nm，结构如下：

CREATE TABLE `t_pfs_dt` (

`pid` smallint NOT NULL,

`fid` smallint NOT NULL,

`sid` smallint NOT NULL,

`note` varchar(20) DEFAULT NULL,

UNIQUE KEY `uidx_1` (`pid`),

UNIQUE KEY `uidx_2` (`fid`),

UNIQUE KEY `uidx_3` (`sid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

这里增加一个字段，同时删除一个字段:

pt-online-schema-change --user=root --ask-pass --host=192.168.246.135 --alter "add column prjph tinyint default 0,drop column note" D=test,t=t_pfs_dt --execute

仔细对比，发现触发器的where条件引用的是构成唯一索引uidx_1的字段pid，

我截取其中的部分信息如下：

`test`.`_t_pfs_dt_new`.`pid` <=> OLD.`pid`

假如我们将唯一索引做如下调整

CREATE TABLE `t_pfs_dt_a` (

`pid` smallint NOT NULL,

`fid` smallint NOT NULL,

`sid` smallint NOT NULL,

`note` varchar(20) DEFAULT NULL,

UNIQUE KEY `uidx_2` (`pid`), -- 由原来的uidx_1改为uidx_2

UNIQUE KEY `uidx_1` (`fid`), -- 由原来的uidx_2改为uidx_1

UNIQUE KEY `uidx_3` (`sid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

再次执行上述修改命令

pt-online-schema-change --user=root --ask-pass --host=192.168.246.135 --alter "add column prjph tinyint default 0,drop column note" D=test,t=t_pfs_dt_a --execute

再查看日志，发现触发器的where条件引用的是构成唯一索引uidx_1的字段fid：

`test`.`_t_pfs_dt_a_new`.`fid` <=> OLD.`fid`

大胆猜测，应该是多个唯一索引的情况下，会选择索引排序排第一的唯一索引字段做为Where字段。大家如果有兴趣，也可以试试。

1.4 基本使用样例

1.4.1 新增列

pt-online-schema-change --user=root --ask-pass --host=192.168.246.135 --alter "add column prjph tinyint" D=test,t=t_pfs --execute

1.4.2 删除列

pt-online-schema-change --user=root --ask-pass --host=192.168.246.135 --alter "drop column nickname" D=test,t=t_user --execute

1.4.3 新增列（有缺省值），并删除列等多个操作

pt-online-schema-change --user=root --ask-pass --host=192.168.246.135 --alter "add column prjph tinyint default 0,drop column note" D=test,t=t_pfs_dt_a --execute

1.4.4 增加索引

pt-online-schema-change --user=root --ask-pass --host=192.168.246.135 --alter "add key idx_nm(nm)" D=test,t=t_user --execute

1.4.5 增加索引，并新增列

pt-online-schema-change --user=root --ask-pass --host=192.168.246.135 --alter "add column prjph tinyint default 0,add key idx_nm(nm)" D=test,t=t_user --execute

1.4.6 修改字段长度，类型

pt-online-schema-change --user=root --ask-pass --host=192.168.246.135 --alter "modify column prjph bigint,modify column note varchar(15)" D=test,t=t_pfs--execute

1.4.7 修改表引擎

pt-online-schema-change --user=root --ask-pass --host=192.168.246.135 --alter "ENGINE=InnoDB" D=test,t=t_my--execute

1.4.8 删除外键

test库下存在如下表：

CREATE TABLE `t_goods` (

`goodid` int NOT NULL,

`goodname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,

PRIMARY KEY (`goodid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `t_goods_dt` (

`goodsid` int DEFAULT NULL,

`typeid` tinyint DEFAULT NULL,

`id` int NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`),

KEY `fk_goodsid` (`goodsid`),

CONSTRAINT `fk_goodsid` FOREIGN KEY (`goodsid`) REFERENCES `t_goods` (`goodid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

pt-online-schema-change --user=root --ask-pass --host=192.168.246.135 --alter "DROP FOREIGN KEY _fk_goodsid" D=test,t=t_goods_dt--execute

注意：删除外键约束时，外键约束名称必须添加前导下划线 _