Percona

共计 5227 个字符，预计需要花费 14 分钟才能阅读完成。

这篇文章主要介绍 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 测试：

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

通过存储过程造数据：

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

DECLARE i INT DEFAULT 0 ; 
WHILE i row_num DO 
INSERT INTO employees_ptarc (v_int, v_string, s_string) 
VALUES 
( 
floor(1 + rand() * 1000000), 
substring( 
MD5(RAND()), 
1, 
floor(1 + rand() * 20) 
), 
substring(MD5(RAND()), 1, 20) 
) ; 
SET i = i + 1 ; 
END 
WHILE ; END$$

delimiter ;

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` FROM `yoon`.`employees_ptarc` FORCE INDEX(`PRIMARY`) WHERE (id 400000) AND (`id` 400000) AND ((`id` = ?)) ORDER BY `id` LIMIT 100 
DELETE FROM `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 –where 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 
DELETE FROM `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`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110368\) ,0

root,localhost,79,608618,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110369\) ,0

root,localhost,79,608619,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110370\) ,0

root,localhost,79,608620,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110371\) ,0

root,localhost,79,608621,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110372\) ,0

root,localhost,79,608622,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110373\) ,0

root,localhost,79,608623,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110374\) ,0

root,localhost,79,608624,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110375\) ,0

root,localhost,79,608625,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110376\) ,0

root,localhost,79,608626,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110377\) ,0

root,localhost,79,608627,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110378\) ,0

root,localhost,79,608628,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110379\) ,0

root,localhost,79,608629,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110380\) ,0

root,localhost,79,608630,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110381\) ,0

root,localhost,79,608631,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110382\) ,0

root,localhost,79,608632,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110383\) ,0

root,localhost,79,608633,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110384\) ,0

root,localhost,79,608634,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110385\) ,0

root,localhost,79,608635,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110386\) ,0

root,localhost,79,608636,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110387\) ,0

root,localhost,79,608637,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110388\) ,0

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

root,localhost,79,608639,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110390\) ,0

root,localhost,79,608640,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110391\) ,0

root,localhost,79,608641,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110392\) ,0

root,localhost,79,608642,QUERY,`yoon`, DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \ 110393\) ,0

以上是“Percona-Toolkit 中 pt-archiver 如何删除历史数据”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注丸趣 TV 行业资讯频道！