Percona-Toolkit中pt-archiver如何删除历史数据
发布时间：2022-01-04 09:27:24 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍Percona-Toolkit中pt-archiver如何删除历史数据，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！ pt-archiver是Percona-Toolkit工具集中的一个组件，是一个主要用于对MySQL表数据进行归档和清除工具。它可以将数据
|
这篇文章主要介绍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如何删除历史数据”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注亿速云行业资讯频道！ （编辑：云计算网_泰州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330476号