文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

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

2024-04-02 19:55

关注

这篇文章主要介绍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 `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 `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 `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 `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如何删除历史数据”这篇文章的所有内容,感谢各位的阅读!希望分享的内容对大家有帮助,更多相关知识,欢迎关注亿速云行业资讯频道!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯