pt工具的使用（7） pt-archiver的使用
一、描述
pt-archiver 是归档表的工具，在 sql 中写入高效归档和清除作业。 目标是在不对 OLTP 查询产生太大影响的情况下，将旧数据从表中蚕食掉，从而实现低影响、仅向前的作业。 您可以将数据插入到另一个表中，而不必在同一台服务器上。 您也可以将其写入适合 LOAD DATA INFILE 格式的文件。 或者你两者都不能做，在这种情况下它只是一个增量删除。
二、实践
针对不同参数做了一些实验，来帮助了解参数
1.官方示例
# 标准用法
pt-archiver [OPTIONS] --source DSN --where WHERE# 将表从oltp实例归档到olap的实例中
pt-archiver --source h=oltp_server,D=test,t=tbl --dest h=olap_server --file '/var/log/archive/%Y-%m-%d-%D.%t' --where "1=1" --limit 1000 --commit-each# 从子表删除孤立行
pt-archiver --source h=host,D=db,t=child --purge --where 'NOT EXISTS(SELECT * FROM parent WHERE col=child.col)'
2.具体测试
2.1 归档到同一实例上的不同表
./pt-archiver --source h=127.0.0.1,P=3307,u=rpl_test,D=mts_rpl_test2,t=mts_test3 --charset=utf8mb4 --ask-pass --dest h=127.0.0.1,P=3307,u=rpl_test,D=mts_rpl_test2,t=mts_test3_arch --ask-pass --where "id<135887" --limit 100 --commit-each
# 因为--dest会从--source继承相同的指，所以上面的命令也可以用下面的写法来写，注意，表必须是已经存在的表
./pt-archiver --source h=127.0.0.1,P=3307,u=rpl_test,D=mts_rpl_test2,t=mts_test3 --charset=utf8mb4 --ask-pass --dest t=mts_test3_arch --where "id<135887" --limit 100 --commit-each
# 利用配置文件来进行登录
./pt-archiver --source F=/etc/my2.cnf,u=root,D=mts_rpl_test2,t=mts_test3 --charset=utf8mb4 --ask-pass --dest t=mts_test3_arch --where "id<135887" --limit 100 --commit-each
# 利用socket文件来进行登录,如果使用单机多实例部署的时候采用这种方法要尤其注意dest的真实实例./pt-archiver --source u=root,D=mts_rpl_test2,t=mts_test3 -S /data/mysql_data2/mysql.sock --charset=utf8mb4 --ask-pass --dest t=mts_test3_arch --where "id<135887" --limit 100 --commit-each
2.2 归档到文件
# 输出到文件，因为文件没有utf8mb4这种编码，所以这里直接设置了no-check-charset
./pt-archiver --source b,h=127.0.0.1,P=3307,u=rpl_test,D=mts_rpl_test2,t=mts_test3 --ask-pass --file '/data/it_tool/archiver/%Y-%m-%d-%D.txt' --no-check-charset --where "id<135887" --limit 100 --commit-each
2.3 只删除不归档
# 只删除，不归档
./pt-archiver --source h=127.0.0.1,P=3307,u=rpl_test,D=mts_rpl_test2,t=mts_test3 --charset=utf8mb4 --ask-pass --purge --where "id<136887" --limit 100 --commit-each
2.4 不写入binlog中
# b=true指定归档操作不写入binlog中，因为b的copy是false,所以source和dest都需要指定,可以在通用日志中看到有如下操作SET SQL_LOG_BIN=0
./pt-archiver --source b=true,h=127.0.0.1,P=3307,u=rpl_test,D=mts_rpl_test2,t=mts_test3 --charset=utf8mb4 --ask-pass --dest b=true,t=mts_test3_arch --where "id<137998" --limit 100 --commit-each
2.5 通过通用日志看执行原理
# 基本环境检测和设置，包括设置字符集，sql_mode,关闭自动提交，查询最大id等，
2023-04-12T09:23:40.510296Z 28 Query show variables like '%general%'
2023-04-12T09:24:12.562739Z 40 Connect rpl_test@127.0.0.1 on mts_rpl_test2 using TCP/IP
2023-04-12T09:24:12.562940Z 40 Query set autocommit=0
2023-04-12T09:24:12.563548Z 40 Query /*!40101 SET NAMES "utf8mb4"*/
2023-04-12T09:24:12.563695Z 40 Query SHOW VARIABLES LIKE 'wait\\_timeout'
2023-04-12T09:24:12.565120Z 40 Query SET SESSION wait_timeout=10000
2023-04-12T09:24:12.565245Z 40 Query SELECT @@SQL_MODE
2023-04-12T09:24:12.565337Z 40 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'*/
2023-04-12T09:24:12.565430Z 40 Query SELECT VERSION()
2023-04-12T09:24:12.565564Z 40 Query SHOW VARIABLES LIKE 'character_set_server'
2023-04-12T09:24:12.566813Z 40 Query SELECT version()
2023-04-12T09:24:12.567026Z 40 Query SHOW VARIABLES LIKE 'version%'
2023-04-12T09:24:12.568246Z 40 Query SHOW ENGINES
2023-04-12T09:24:12.568528Z 40 Query SHOW VARIABLES LIKE 'innodb_version'
2023-04-12T09:24:12.569788Z 40 Query show variables like 'innodb_rollback_on_timeout'
2023-04-12T09:24:12.570783Z 40 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2023-04-12T09:24:12.570906Z 40 Query USE `mts_rpl_test2`
2023-04-12T09:24:12.571014Z 40 Query SHOW CREATE TABLE `mts_rpl_test2`.`mts_test3`
2023-04-12T09:24:12.571152Z 40 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2023-04-12T09:24:12.571430Z 40 Query SELECT CONCAT(/*!40100 @@session.character_set_connection, */ "")
2023-04-12T09:24:16.494268Z 41 Connect rpl_test@127.0.0.1 on mts_rpl_test2 using TCP/IP
2023-04-12T09:24:16.494437Z 41 Query set autocommit=0
2023-04-12T09:24:16.494571Z 41 Query /*!40101 SET NAMES "utf8mb4"*/
2023-04-12T09:24:16.494700Z 41 Query SHOW VARIABLES LIKE 'wait\\_timeout'
2023-04-12T09:24:16.496211Z 41 Query SET SESSION wait_timeout=10000
2023-04-12T09:24:16.496320Z 41 Query SELECT @@SQL_MODE
2023-04-12T09:24:16.496443Z 41 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'*/
2023-04-12T09:24:16.496606Z 41 Query SELECT VERSION()
2023-04-12T09:24:16.496714Z 41 Query SHOW VARIABLES LIKE 'character_set_server'
2023-04-12T09:24:16.497890Z 41 Query SELECT version()
2023-04-12T09:24:16.498032Z 41 Query SHOW VARIABLES LIKE 'version%'
2023-04-12T09:24:16.499070Z 41 Query SHOW ENGINES
2023-04-12T09:24:16.499392Z 41 Query SHOW VARIABLES LIKE 'innodb_version'
2023-04-12T09:24:16.500537Z 41 Query show variables like 'innodb_rollback_on_timeout'
2023-04-12T09:24:16.501484Z 41 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2023-04-12T09:24:16.501583Z 41 Query USE `mts_rpl_test2`
2023-04-12T09:24:16.501665Z 41 Query SHOW CREATE TABLE `mts_rpl_test2`.`mts_test3_arch`
2023-04-12T09:24:16.501849Z 41 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2023-04-12T09:24:16.502141Z 41 Query SELECT CONCAT(/*!40100 @@session.character_set_connection, */ "")
2023-04-12T09:24:16.502295Z 40 Query SHOW VARIABLES LIKE 'wsrep_on'
2023-04-12T09:24:16.503568Z 40 Query SHOW VARIABLES LIKE 'wsrep_on'
2023-04-12T09:24:16.504761Z 40 Query SHOW VARIABLES LIKE 'version%'
2023-04-12T09:24:16.505724Z 40 Query SHOW ENGINES
2023-04-12T09:24:16.506017Z 40 Query SHOW VARIABLES LIKE 'innodb_version'
2023-04-12T09:24:16.507188Z 40 Query SELECT MAX(`id`) FROM `mts_rpl_test2`.`mts_test3`
2023-04-12T09:24:16.507580Z 40 Query SELECT CONCAT(@@hostname, @@port)
2023-04-12T09:24:16.507709Z 41 Query SELECT CONCAT(@@hostname, @@port)# 确定数据范围，插入新表，归档旧表，重复这个操作达到limit的条数，然后commit
2023-04-12T09:24:16.507988Z 40 Query SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`email` FROM `mts_rpl_test2`.`mts_test3` FORCE INDEX(`PRIMARY`) WHERE (id<135887) AND (`id` < '2000001') ORDER BY `id` LIMIT 100
2023-04-12T09:24:16.521222Z 41 Query INSERT INTO `mts_rpl_test2`.`mts_test3_arch`(`id`,`name`,`email`) VALUES ('134887','asd134886','233@qq.com')
2023-04-12T09:24:16.521535Z 40 Query DELETE FROM `mts_rpl_test2`.`mts_test3` WHERE (`id` = '134887')# 简单的心跳检测
2023-04-12T09:24:16.521787Z 40 Query SELECT 'pt-archiver keepalive'# 提交查询并退出，commit是每达到一次limit的条数就进行一次commit，这两个commit是分别提交insert和delete的
2023-04-12T09:24:16.805639Z 41 Query commit
2023-04-12T09:24:16.805790Z 40 Query commit
2023-04-12T09:24:16.805977Z 40 Query SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`email` FROM `mts_rpl_test2`.`mts_test3` FORCE INDEX(`PRIMARY`) WHERE (id<135887) AND (`id` < '2000001') AND ((`id` >= '135886')) ORDER BY `id` LIMIT 100
2023-04-12T09:24:16.806178Z 41 Query commit
2023-04-12T09:24:16.806236Z 40 Query commit
2023-04-12T09:24:16.806312Z 40 Quit
2023-04-12T09:24:16.806393Z 41 Quit
三、参数详解
| 参数 | 意义 |
|---|---|
| --source | 制动归档的表，“i”指定索引，默认使用主键。“a”和“b”选项允许您控制语句如何流经二进制日志。 如果指定“b”选项，二进制日志记录将在指定的连接上被禁用。 如果您指定“a”选项，连接将使用指定的数据库，您可以使用它来防止从服务器使用 --replicate-ignore-db 选项执行二进制日志事件。 这两个选项可以用作实现相同目标的不同方法：将数据从主服务器上归档，但将其保留在从服务器上。 例如，您可以在主服务器上运行清除作业，并使用您选择的方法防止它在从服务器上发生。 |
| --dest | 此项指定一个表，pt-archiver 将插入从 --source 归档的行。 它使用与 --source 相同的 key=val 参数格式。 大多数缺失值默认为与 --source 相同的值，因此您不必重复 --source 和 --dest 中相同的选项。 使用 --help 选项查看从 --source 复制了哪些值。 |
| --where | 指定 WHERE 子句以限制归档哪些行 |
| --commit-each --txn-size |
--commit-each提交每组获取和归档的行,与--limit配合使用 --txn-size指定每次事务提交的行数 这两个参数是互斥的 |
| --limit | 每个语句要获取和归档的行数。默认为一行 |
| --[no]check-charset | 是否检查字符集，默认检查 |
| --file | 归档到文件中，%D Database name；%t Table name，时间的格式化如例子中所描述，与--output-format结合使用可以指定输出的内容是dump(使用制表符作为分隔符)还是csv（使用逗号作为分隔符），与--header配合使用指定是否打印字段名字在第一行 |
| --sleep --sleep-coef |
--sleep 两次提取中间的休眠时间，默认不休眠 --sleep-coef 指定sleep时间为最后一次 SELECT 时间的多少倍。 |
| --purge | 只清除，不归档，最好用--primary-key-only 指定表的主键列。 这将防止无缘无故地从服务器获取所有列。 |
| --ignore --replace |
指定归档的时候insert的方式，这两个参数是互斥的 |
| --pause-file=s | 当此参数指定的文件存在时，执行将暂停 |
| --pid=s | 创建给定的 PID 文件。如果 PID 文件已存在且其中包含的 PID 与当前 PID 不同，则该工具将不会启动。但是，如果 PID 文件存在并且其中包含的 PID 不再运行，则该工具将使用当前 PID 覆盖 PID 文件。工具退出时，PID 文件会自动删除 |
| --plugin=s | 用作通用插件的 Perl 模块名称。 |
| --low-priority-insert
--delayed-insert |
两者都是低优先级别插入，两个参数互斥，但是delay将在之后被mysql废除 |
| --share-lock --for-update |
指定加读锁还是写锁，这两个参数是互斥的 |
| --analyze --optimize |
–analyze=ds,为d则在dest上使用analyze,为s则在source上使用analyze,ds则表示两者都执行 –optimize表示执行optimize,使用方式与analyze一直，两个参数互斥 |
| --no-ascend --no-delete |
--no-ascend不使用升序索引优化 --no-delete 不删除数据，两个参数是互斥的 |
| –check-interval=m | 定义归档每次暂停多长时间 |
| --check-slave-lag=s | 暂停归档，直到此副本的滞后小于–max-lag |
| --max-lag=m | 暂停校验和，直到所有副本的滞后小于此值 |
| --local | 不要将 OPTIMIZE 或 ANALYZE 查询写入 binlog。 |
| --channel | 指定复制通道 |
| --ascend-first | 仅升序第一个索引列 |
| --buffer | 指定–file时，仅在事务提交的时候刷新到磁盘 |
| --bulk-delete | 批量删除 |
| --[no]bulk-delete-limit | 是否开启批量删除限制，delete … limit |
| --bulk-insert | 通过load-data批量插入 |
| --columns=a | 归档指定的字段，逗号分隔 |
| --dry-run | 试运行，不做任何处理 |
| --max-flow-ctl | 试用于pxc集群的类max-lag参数 |
| --progress | 指定多少行打印一次进度信息 |
| --slave-user=s | 从库用户 |
| --slave-password=s | 从库密码 |
| --set-vars | 设置执行时的mysql参数 |
| --quick-delete | 给delete加quick修饰符 |
| --quiet | 不输出任何信息，包括statistics信息 |
| --retries | 遇到超时或死锁的重试次数 |
| --run-time | 指定运行时间，s=seconds, m=minutes, h=hours, d=days; 如果不指定用的是s,例子：–run-time 600s |
| --config=A | 个逗号分隔的配置文件列表；如果指定，这必须是命令行上的第一个选项 |
| --statistics | 收集和打印时间统计数据 |
| --sentinel | 默认文件是/tmp/pt-archiver-sentinel，该文件存在则退出归档 |
| --stop | 通过创建sentine文件来停止归档 |
官方文档：参数和使用方式