Percona Toolkit系列之二：pt-archiver

1. pt-archiver是一个主要用于对MySQL表数据进行归档和清除工具。它可以将数据归档到另一张表或者是一个文件中。pt-archiver在清除表数据的过程中并不会影响OLTP事务的查询性能。

它实现的功能包括：归档历史数据，在线删除大批量数据，数据导出和备份，数据远程归档，数据清理等。

基本使用语法为：pt-archiver [OPTIONS] --source DSN --where WHERE

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

OPTIONS：

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

注意以下几点：

--ignore 与--replace 互斥。

--txn-size 与--commit-each 互斥。

--low-priority-insert 与--delayed-insert 互斥。

--share-lock 与--for-update 互斥。

--analyze 与--optimize 互斥。

--no-ascend 与--no-delete 互斥。

至少需要指定一个--dest，--file 或--purge。

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

DSN：

2. 工作原理

1） 依据主键与Where，--limit等确定需要归档的数据

2） 归档第N（N>=1）部分数据至目标表或者文件

3） 依据--no-delete进行是否删除原表操作

4） 循环执行2.3步骤，直到无数据需要归档

3. 基本使用样例

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

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;

表t_user中有数据200000

另有数据库test_bak，其中有一张表t_user_bak，结构同t_user：

CREATE TABLE `t_user_bak` (

`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;

3.1 打印查询(--dry-run)

pt-archiver --source h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test,t=t_user --dest h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test_bak,t=t_user_bak --where 'nm like "55%"' --progress 50 --no-delete --limit=100 --statistics --commit-each --dry-run

首先，打印查询需要清除数据的执行语句，并确认数据是否准确。

从截图可以看到如下语句，需要在归档时执行：

SELECT /*!40001 SQL_NO_CACHE */ `id`,`nm`,`address`,`p` FROM `test`.`t_user` FORCE INDEX(`PRIMARY`) WHERE (nm like "55%") ORDER BY `id` LIMIT 100

SELECT /*!40001 SQL_NO_CACHE */ `id`,`nm`,`address`,`p` FROM `test`.`t_user` FORCE INDEX(`PRIMARY`) WHERE (nm like "55%") AND ((`id` > ?)) ORDER BY `id` LIMIT 100

INSERT INTO `test_bak`.`t_user_bak`(`id`,`nm`,`address`,`p`) VALUES (?,?,?,?)

3.2 归档,不删除原表数据（逐条事务归档）

pt-archiver --source h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test,t=t_user --dest h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test_bak,t=t_user_bak --where 'nm like "55%"' --progress 50 --no-delete --limit=100 --statistics

查看日志，见下图：

下面截图是执行mysqlbinlog后截取的一部分详细信息：

从上面的截图可以明确表明，数据是一条一事务提交的。

3.3 归档,不删除原表数据（逐批事务归档--commit-each）

pt-archiver --source h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test,t=t_user --dest h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test_bak,t=t_user_bak --where 'nm like "55%"' --progress 50 --no-delete --limit=100 --statistics --commit-each

查看日志，见下图：

从上面的日志截图可以明确表明，数据是以100（--limit）为一个事务进行提交的。

3.4 归档,不删除原表数据（逐批事务归档--txn-size）

下面让我们再使用--txn-size来看看是如何工作的

pt-archiver --source h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test,t=t_user --dest h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test_bak,t=t_user_bak --where 'nm like "550%"' --progress 50 --no-delete --limit=20 --statistics --txn-size=10

以上数据是以10（ --txn-size=10）为一个事务进行提交的

pt-archiver --source h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test,t=t_user --dest h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test_bak,t=t_user_bak --where 'nm like "550%"' --progress 50 --no-delete --limit=20 --statistics --txn-size=5

以上数据是以5（ --txn-size=5）为一个事务进行提交的

说明--txn-size控制一个事务内提交的数据行数

3.5 归档,不删除原表数据（--bulk-insert）

pt-archiver --source h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test,t=t_user --dest h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test_bak,t=t_user_bak --where 'nm like "550%"' --no-delete --limit=100 --statistics --bulk-insert

执行报告如下错误：

DBD::mysql::st execute failed: Loading local data is disabled; this must be enabled on both the client and server sides [for Statement "LOAD DATA LOCAL INFILE ? INTO TABLE `test_bak`.`t_user_bak`(`id`,`nm`,`address`,`p`)" with ParamValues: 0='/tmp/qMPmkDZ1mrpt-archiver'] at /bin/pt-archiver line 6875.

解决：需要SET GLOBAL local_infile=ON

再次执行，结果如下图：

查看日志，如下图：

查看binlog，如下图：

一个事务内提交，性能得到提升

3.6 归档到文件

pt-archiver --source h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test,t=t_user --where 'ID<100' --file '/root/mysqlbak/%Y-%m-%d-%D-%t.sql' --statistics

执行后，数据归档到文件/root/mysqlbak/2022-11-15-test-t_user.sql中，我们简单查看一下文件内容，如下所示：

从文件内容来看，缺少了列名，下面加上列名

pt-archiver --source h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test,t=t_user --where 'ID >= 120 and ID<130' --file '/root/mysqlbak/%Y-%m-%d-%D-%t.sql' --statistics --header

注意，如果导出的文件已经存在，则不会增加列名。

3.7 清除表数据（逐条事务--purge）

如果是仅需要删除数据，则无需指定--dest和file

pt-archiver --source h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test,t=t_user --where 'ID<300' --purge --statistics

如下图所示，删除了100行数据

binlog日志部分内容如下，显示了都是执行Delete操作

3.8 清除表数据（逐条事务--purge --bulk-delete）

pt-archiver --source h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test,t=t_user --where 'ID<350' --purge --bulk-delete --statistics --txn-size=100 --limit=200

查看日志，如下图：

3.9 清除表数据（--safe-auto-increment）

对于表的主键列是AUTO_INCREMENT列的情况，需要特别注意

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

CREATE TABLE `t_dept` (

`id` int NOT NULL AUTO_INCREMENT,

`deptnm` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,

`address` varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`)

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

表中最大自增列值为152，假设后续未对此自增列做任何其它修改，包括未做手动修改AUTO_INCREMENT的操作，下面做如下数据清理，清理最后2条数据：

在正式清除最后2条数据之前，我们先观察：

pt-archiver --source h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test,t=t_dept --where 'ID>150' --purge --bulk-delete --statistics --txn-size=100 --limit=200 --dry-run

输出如下：

SELECT /*!40001 SQL_NO_CACHE */ `id`,`deptnm`,`address` FROM `test`.`t_dept` FORCE INDEX(`PRIMARY`) WHERE (ID>150) AND (`id` < '152') ORDER BY `id` LIMIT 200

SELECT /*!40001 SQL_NO_CACHE */ `id`,`deptnm`,`address` FROM `test`.`t_dept` FORCE INDEX(`PRIMARY`) WHERE (ID>150) AND (`id` < '152') AND ((`id` >= ?)) ORDER BY `id` LIMIT 200

DELETE FROM `test`.`t_dept` WHERE (((`id` >= ?))) AND (((`id` <= ?))) AND (ID>150) LIMIT 200

发现删除范围实际上为150与152之间，则只删除1条。我们运行一下清除，看看是否只删除了1条：

pt-archiver --source h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test,t=t_dept --where 'ID>150' --purge --bulk-delete --statistics --txn-size=100 --limit=200

从上面的输出结果表明，的确仅仅删除了1条。

检查数据表内容

mysql> select count(*) from t_dept where id>150;

+----------+

| count(*) |

+----------+

| 1 |

+----------+

1 row in set (0.01 sec)

mysql> select max(id) from t_dept where id>150;

+---------+

| max(id) |

+---------+

| 152 |

+---------+

1 row in set (0.00 sec)

也表明的确仅仅删除了1条，最大值id=152被保留了下来，这是因为什么呢？

避免AUTO_INCREMENT属性的值在数据库重启后还可以重用

那么如果确认此行的确是需要删除的，请使用--nosafe-auto-increment

继续如下执行：

pt-archiver --source h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test,t=t_dept --where 'ID>150' --purge --bulk-delete --statistics --txn-size=100 --limit=200 --nosafe-auto-increment

从上面的输出结果表明，又删除了1条。

检查数据表内容

mysql> select * from t_dept where id>150;

Empty set (0.00 sec)

这次将前面保留的id=152的数据也清除了。

3.10 清除表数据（无主键，无唯一键）

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

CREATE TABLE `t_my` (

`id` int NOT NULL,

`nm` varchar(10) DEFAULT NULL,

`address` varchar(20) DEFAULT NULL

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

执行如下命令清除数据：

pt-archiver --source h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test,t=t_my --where 'ID<300' --purge --statistics

报告错误：

Cannot find an ascendable index in table at /usr/bin/pt-archiver line 3260.

从错误上看，好像是缺失了升序的索引，那么如果我们增加一个升序索引，是否就可以正常工作了呢？

如下增加一个普通索引：

ALTER TABLE `t_my` ADD INDEX `idx_nm` (`nm`) ;

再次清除数据：

执行成功，也成功删除了数据。

回过头来，我们查看一下

pt-archiver --source h=192.168.246.137,u=root,p=Ygzlpaul*2022,D=test,t=t_my --where 'ID<300' --purge --statistics --dry-run

显示如下：

SELECT /*!40001 SQL_NO_CACHE */ `id`,`nm`,`address` FROM `test`.`t_my` FORCE INDEX(`idx_nm`) WHERE (ID<300) ORDER BY `nm` LIMIT 1

SELECT /*!40001 SQL_NO_CACHE */ `id`,`nm`,`address` FROM `test`.`t_my` FORCE INDEX(`idx_nm`) WHERE (ID<300) AND (((? IS NULL OR `nm` >= ?))) ORDER BY `nm` LIMIT 1

DELETE FROM `test`.`t_my` WHERE (`id` = ? AND ((? IS NULL AND `nm` IS NULL) OR (`nm` = ?)) AND ((? IS NULL AND `address` IS NULL) OR (`address` = ?))) LIMIT 1

总结，归档的表最好有主键，可以提升归档性能，也支持仅有普通索引的情况。

注意：以上所用软件的各个版本为：

Mysql：8.0.31

pt-archiver： 3.2.1