PerconaToolkit系列之二ptarchiver

　　1. pt-archiver是一个主要用于对MySQL表数据进行归档和清除工具。它可以将数据归档到另一张表或者是一个文件中。pt-archiver在清除表数据的过程中并不会影响OLTP事务的查询性能。
　　它实现的功能包括：归档历史数据，在线删除大批量数据，数据导出和备份，数据远程归档，数据清理等。
　　基本使用语法为：pt-archiver [OPTIONS] --source DSN --where WHERE
　　下面列表列出部分options，更加详细的请使用pt-archiver  --help进行查看
　　OPTIONS：
　　OPTIONS
　　短操作符
　　类型
　　默认值
　　简要说明
　　--ask-pass
　　连接 MySQL 时提示输入密码。
　　--bulk-delete
　　批量删除source上的旧数据。
　　--bulk-insert
　　使用 LOAD DATA INFILE 插入
　　--channel
　　s
　　使用复制通道连接到服务器时使用的通道名称。
　　--charset
　　-A
　　s
　　默认字符集。
　　--[no]check-charset
　　Yes
　　确保连接和表字符集相同。
　　--[no]check-columns
　　Yes确保--source 并--dest 具有相同的列。
　　--check-interval
　　m1s
　　如果--check-slave-lag 给出，这定义了工具每次暂停的时间。 它发现Slave落后的时间。此检查每 100 行执行一次
　　--check-slave-lag
　　s
　　暂停归档，直到指定的 DSN 的从延迟小于--max-lag。可以多次指定此选项以检查多个从。
　　--columns
　　-c
　　a
　　要存档的列的逗号分隔列表。
　　--commit-each
　　提交每组提取和存档的行
　　--database
　　-D
　　s
　　连接到这个数据库。
　　--delayed-insert
　　将 DELAYED 修饰符添加到 INSERT 语句。
　　将 DELAYED 修饰符添加到 INSERT 或 REPLACE 语句。
　　--dest
　　d
　　DSN 指定要归档到的表
　　--dry-run
　　打印查询并退出而不做任何事情。 --file
　　s
　　要归档到的文件，用类似DATE_FORMAT()的格式。
　　％d 一个月中的天，数字（01 ..31 ）
　　％H 小时（00 ..23 ）
　　％I 分钟，数字（00 ..59 ）
　　％m 月份，数字（01 ..12 ）
　　％s 秒（00 ..59 )
　　%Y 年份，数字，四位数
　　%D 数据库名
　　%t 表名
　　--for-update
　　将 FOR UPDATE 修饰符添加到 SELECT 语句。
　　--header
　　在 顶部打印列标题--file。
　　--high-priority-select
　　将 HIGH_PRIORITY 修饰符添加到 SELECT 语句。
　　--host
　　-h
　　s
　　连接到主机。
　　--ignore
　　将 IGNORE 用于 INSERT 语句。
　　--limit
　　i
　　1
　　每个语句要获取和存档的行数。 --max-lag
　　m1s
　　如果从设备--check-slave-lag 滞后，则暂停归档。
　　--no-delete
　　不要删除归档的行。
　　--output-format
　　s
　　用于--file 指定输出格式。
　　--password
　　-p
　　连接时使用的密码。如果密码包含逗号，它们必须用反斜杠转义
　　--port
　　-P
　　i
　　用于连接的端口号。
　　--progress
　　i
　　每 X 行打印一次进度信息。
　　--purge
　　清除而不是归档
　　--[no]safe-auto-increment
　　yes不要归档最大 AUTO_INCREMENT 的行
　　--slave-user
　　s
　　设置用于连接到Slave的用户。 --slave-password
　　s
　　设置用于连接到从站的密码。它可以与 –slave-user 一起使用，并且所有从站上的用户密码必须相同。 --set-vars
　　a
　　个以逗号分隔的 variable=value 对列表中设置 MySQL 变量。
　　--socket
　　-S
　　用于连接的套接字文件。 --source
　　d
　　DSN 指定要从中存档的表（必需）。这个参数是一个 DSN。有关语法，请参阅 DSN 选项。
　　--statistics
　　收集和打印计时统计信息。 --txn-size
　　i
　　1
　　指定每个事务的大小（以行数计）。零完全禁用事务。
　　--user
　　-u
　　如果不是当前用户，则用于登录的用户。
　　--where
　　s
　　WHERE 子句来限制要存档的行（必需）。
　　类型备注：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：
　　DSN
　　简要说明
　　a
　　执行查询时要使用的数据库。
　　A
　　默认字符集。
　　b
　　如果为 true，则使用 SQL_LOG_BIN 禁用 binlog。
　　D
　　数据库。
　　F
　　仅从给定文件中读取默认选项
　　h
　　连接到主机。
　　i
　　要使用的索引。
　　L
　　显式启用 LOAD DATA LOCAL INFILE。
　　m
　　插件模块名称。
　　p
　　连接时使用的密码。如果密码包含逗号，它们必须用反斜杠转义
　　P
　　用于连接的端口号。
　　S
　　用于连接的套接字文件。
　　t
　　表。
　　u
　　如果不是当前用户，则用于登录的用户。
　　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