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

这篇文章主要介绍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-each6、–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)
BEGINDECLARE i INT DEFAULT 0 ;
WHILE i 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=utf免费主机域名8 –purge –charset=utf8 –where “id
SELECT /*!40001 SQL_NO_CACHE */ `id`,`v_int`,`v_string`,`s_s免费主机域名tring` FROM `yoon`.`employees_ptarc` FORCE INDEX(`PRIMARY`) WHERE (id SELECT /*!40001 SQL_NO_CACHE */ `id`,`v_int`,`v_string`,`s_string` FROM `yoon`.`employees_ptarc` FORCE INDEX(`PRIMARY`) WHERE (id = ?)) ORDER BY `id` LIMIT 100
DELETE FROM `yoon`.`employees_ptarc` WHERE (((`id` >= ?))) AND (((`id`
生成的删除数据语句为：DELETE FROM `yoon`.`employees_ptarc` WHERE (((`id` >= ?))) AND (((`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 SELECT /*!40001 SQL_NO_CACHE */ `id`,`v_int`,`v_string`,`s_string` FROM `yoon`.`employees_ptarc` FORCE INDEX(`PRIMARY`) WHERE (id SELECT /*!40001 SQL_NO_CACHE */ `id`,`v_int`,`v_string`,`s_string` FROM `yoon`.`employees_ptarc` FORCE INDEX(`PRIMARY`) WHERE (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’)’,0root,localhost,79,608617,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110368’)’,0root,localhost,79,608618,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110369’)’,0root,localhost,79,608619,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110370’)’,0root,localhost,79,608620,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110371’)’,0root,localhost,79,608621,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110372’)’,0root,localhost,79,608622,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110373’)’,0root,localhost,79,608623,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110374’)’,0root,localhost,79,608624,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110375’)’,0root,localhost,79,608625,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110376’)’,0root,localhost,79,608626,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110377’)’,0root,localhost,79,608627,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110378’)’,0root,localhost,79,608628,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110379’)’,0root,localhost,79,608629,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110380’)’,0root,localhost,79,608630,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110381’)’,0root,localhost,79,608631,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110382’)’,0root,localhost,79,608632,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110383’)’,0root,localhost,79,608633,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110384’)’,0root,localhost,79,608634,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110385’)’,0root,localhost,79,608635,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110386’)’,0root,localhost,79,608636,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110387’)’,0root,localhost,79,608637,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110388’)’,0root,localhost,79,608638,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110389’)’,0root,localhost,79,608639,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110390’)’,0root,localhost,79,608640,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110391’)’,0root,localhost,79,608641,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110392’)’,0root,localhost,79,608642,QUERY,`yoon`,’DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ‘110393’)’,0以上是“Percona-Toolkit中pt-archiver如何删除历史数据”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注云技术行业资讯频道！

相关推荐: Linux系统下启动MySQL的命令

本篇内容介绍了“Linux系统下启动MySQL的命令”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！ 一、总结一下： 1.系统下启动MySQL的命令： /ect/…