如何在 Percona-Toolkit 中删除 pt-archiver 中的历史数据

pt-archiver 是 Percona-Toolkit 工具集中的一个组件。它是一个主要用于归档和清除MySQL表数据的工具。它可以将数据归档到另一个表或文件。 pt-archiver在清表数据的过程中不会影响OLTP事务的查询性能。对于数据归档，可以归档到另一台服务器上的另一个表，也可以归档到文件。可以使用 LOAD DATA INFILE 将数据加载到该文件中。这个功能其实类似于表的历史数据的增量。删除。

对于在线历史数据的归档，我直接使用mysqldump来恢复。不过，要删除归档后的历史数据，使用pt-archiver感觉比编写shell脚本方便得多。

pt-archiver --help参数说明主要介绍可用于删除历史数据的参数。

1. --progress 每行打印进度信息

2. --limit 限制select返回的行数

3. --sleep 指定select语句睡眠时间

4. --txn-size 指定提交事务的行数

5. --bulk-delete 使用单个 DELETE 语句批量删除每个行块。该语句删除块的第一行和最后一行之间的每一行，暗示 --commit-each

6。 --dry-run 打印查询并退出，不执行任何操作

创建新表employees_ptarc test:

CREATE TABLE `employees_ptarc` (
`id` int(11 ) NOT NULL AUTO_INCRMENT、
`v_int` int(11) 默认 NULL、
`v_string` varchar(50) 默认 NULL、
`s_string` char(20) NOT NULL、
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

通过存储过程创建数据：

delimi$$
CREATE PROCEDURE i_employees_ptarc (IN row_num INT)
BEGIN

DECLARE i INT DEFAULT 0 ;
当我 < row_num DO
插入员工_ptarc (v_int, v_string, s_string)
值
(
floor(1 + rand() * 1000000),
/>substring(
MD5(RAND()),
1,
floor(1 + rand() * 20)
),
substring(MD5(RAND) ()), 1, 20)
) ;
设置 i = i + 1 ;
结束
同时； END$$

分隔符；

call i_employees_ptarc(400000);

# pt-archiver --source h=localhost,P=7777,u=root ,p='Asd .123@#',D=yoon,t=employees_ptarc,A=utf8 --purge --charset=utf8 --where "id < 400000" --progress=100 --txn-size=100 --limit=100 --bulk-delete --statistics --dry-run

SELECT /*!40001 SQL_NO_CACHE */ `id`,`v_int`,`v_string`,`s_string` FROM ` yoon`.`employees_ptarc ` FORCE INDEX(`PRIMARY`) WHERE (id < 400000) AND (`id` < '400000') ORDER BY `id` LIMIT 100
SELECT /*!40001 SQL_NO_CACHE */ `id `,`v_int`,`v_string`, `s_string` 来自 `yoon`.`employees_ptarc` FORCE INDEX(`PRIMARY`) WHERE (id < 400000) AND (`id` < '400000') AND ((`id` >= ?)) ORDER BY `id` LIMIT 100
从 `yoon` 中删除.`employees_ptarc` WHERE (((`id` >= ?))) AND (((`id` <= ?))) AND (id < 400000) LIMIT 100

生成的删除数据语句是： DELETE FROM `yoon`.`employees_ptarc` WHERE (((`id` >= ?))) AND (((`id` <= ?))) AND ( id < 400000) LIMIT 100 如果不是核心业务历史数据，这个操作是没有问题的，但是对于核心库删除历史数据来说，更理想的删除语句是：delete from table where id=?

去掉pt-archiver的参数bulk-delete，改为txn-size，并添加sleep来控制，这样就不会影响DB了，如下：

# pt-archiver --source h=localhost,P=7777,u=root,p='Asd.123@#',D=yoon,t=employees_ptarc --purge --charset=utf8 --其中“id <= 400000”--progress= 200 --limit=200 --sleep=1 --txn-size=200 --statistics --dry-run

SELECT / *!40001 SQL_NO_CACHE */`id`,`v_int`,`v_string`,`s_string` FROM `yoon`.`employees_ptarc` FORCE INDEX(` PRIMARY`) WHERE (id <= 600000) AND (`id` < '400000') ORDER BY ` id` LIMIT 100
SELECT /*!40001 SQL_NO_CACHE */ `id`,`v_int`,`v_string`,`s_string` FROM `yoon`.`employees_ptarc` FORCE INDEX(`PRIMARY`) WHERE (id < = 600000) AND (`id` < '400000') AND ((`id` >= ?)) ORDER BY `id` LIMIT 100
从 `yoon`.`employees_ptarc` WHERE (`id` = ?)

查看审核日志：

root,localhost,79,608616 ,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110367\')',0

root,localhost,79,608617,QUERY,`yoon`,'从 `yoon`.`employees_ptarc` WHERE (`id` = \'110368\ ')',0

root,localhost,79,608618,QUERY,`yoon`,' 从 `yoon`.`employees_ptarc` WHERE (`id` = \'110369\')' 中删除, 0

root,localhost,79,608619,QUERY,`yoon`,'从 `yoon `.`employees_ptarc` WHERE (`id` = \'110370\')' 中删除',0

root,localhost,79,608620,QUERY,`yoon`,'从 `yoon` 中删除。`employees_ptarc` WHERE (` id` = \'110371\')',0

root,localhost,79,608621,QUERY,`yoon`,'从`yoon`删除。`employees_ptarc` WHERE (`id` = \'110372\') ',0

root,localhost,79,608622,QUERY,`yoon`,'从 `yoon` 中删除。`employees_ptarc` WHERE (`id` = \'110373\' )',0< /p>

root,localhost,79,608623,QUERY,`yoon`,'从 `yoon` 中删除。`employees_ptarc` WHERE (`id` = \'110374\')',0

root, localhost,79,608625,QUERY,`yoon`,'从`yoon`删除。`employees_ptarc` WHERE (`id` = \'110376\')',0

root,localhost,79, 608626,查询,`yoon`,'从 `yoon`.`employees_ptarc` WHERE (`id` = \'110377\')' 中删除',0

root,localhost,79 ,608627,QUERY, `yoon`,'从 `yoon`.`employees_ptarc` WHERE (`id` = \'110378\')' 中删除',0

root,localhost,79,608628,QUERY,`yoon`, '从 `yoon`.`employees_ptarc` WHERE (`id` = \'110379\')' 中删除',0

root,localhost,79,608629,QUERY,`yoon`,'从 ` 中删除yoon`.`employees_ptarc` WHERE (`id` = \'110380\')',0

root,localhost,79,608630,QUERY,`yoon`,'从 `yoon`.`employees_ptarc` WHERE (`id` = \'110381 \' 中删除)',0

root,localhost,79,608631,QUERY,`yoon`,'从 `yoon` 中删除。`employees_ptarc` WHERE (`id` = \'110382\')',0

root,localhost,79,608632,QUERY,`yoon`,'从`yoon`删除。`employees_ptarc` WHERE (`id` = \'110383\')',0

root,localhost,79,608633,QUERY,`yoon`,'从 `yoon`.`employees_ptarc` WHERE (`id` = \'110384\')' 中删除',0

root ,localhost,79,608634,QUERY,`yoon`,'从 `yoon`.`employees_ptarc` 删除 (`id` = \'110385\')',0

root,localhost ,79 ,608635,QUERY,`yoon`,'从 `yoon`.`employees_ptarc` WHERE (`id` = \'110386\')' 中删除',0

root,localhost,79,608636,QUERY ,`yoon`,'从 `yoon `.`employees_ptarc` WHERE (`id` = \'110387\')' 中删除',0

root,localhost,79,608637,QUERY,`yoon` ,'从 `yoon`.`employees_ptarc ` WHERE (`id` = \'110388\')' 中删除',0

root,localhost,79,608638,QUERY,`yoon`,'从`yoon`.`employees_ptarc` WHERE (`id` = \'110389\')',0

root,localhost,79,608639,QUERY,`yoon`,'从 `yoon`.`employees_ptarc` 中删除 (`id` = \ '110390\')',0

root,localhost,79,608640,QUERY,`yoon`,'从 `yoon`.`employees_ptarc` WHERE (`id` = \'110391\' 中删除)',0

root,localhost,79,608641,QUERY,`yoon`,'从 `yoon` 中删除。`employees_ptarc` WHERE (`id` = \'110392\')',0

root,localhost,79,608642,QUERY,`yoon`,'从`yoon`删除。`employees_ptarc` WHERE (`id` = \'110393\')',0

以上是《Perco如何删除na-Toolkit中pt-archiver中的历史数据》本文的全部内容，感谢您的阅读！希望分享的内容对大家有所帮助。更多相关知识，欢迎关注行业资讯频道！