应用场景
在mysql数据库运维过程中,总会碰到一些比较棘手的事情,历史数据归档绝对算的上一个。由于一些历史原因,有些业务表当初被设计成单表,而且没有分区,业务跑了一段时间,发现越来越慢了。一排查,发现这些单表的数据太多了,导致查询效率变低,这个时候,需要将一些业务用不到的历史数据归档,减少表的数据量,提升查询效率。
可是要丝滑的将这些历史数据进行归档,可不是一件容易的事情。注意是丝滑,不能停业务,不能对线上业务造成影响。
上面就是历史数据归档的需求,要解决上面的问题,percona-toolkits工具集里有一款工具pt-archiver,可以非常完美的解决你的需求
pt-archiver功能介绍
pt-archiver有以下几个功能
按照过滤条件,将线上数据导出成归档文件
按照过滤条件,清理线上过期的历史数据
按照过滤条件,清理过期数据,并把数据归档到本地归档表,或者远端归档服务器的历史表
pt-archiver使用限制
使用pt-archiver工具,只有一个限制,要归档的表,必须要有主键。
pt-archiver常用参数介绍
--where 'id<1000' 设置操作条件--limit 10000 每次取1000行数据给pt-archive处理--txn-size 1000 设置1000行为一个事务提交一次--progress 5000 每处理5000行输出一次处理信息--charset=UTF8 指定字符集为UTF8--no-delete 表示不删除原来的数据,注意:如果不指定此参数,所有处理完成后,都会清理原表中的数据--bulk-delete 批量删除source上的旧数据--bulk-insert 批量插入数据到dest主机 (看dest的general log发现它是通过在dest主机上LOAD DATA LOCAL INFILE插入数据的)--purge 删除source数据库的相关匹配记录
复制
pt-archiver使用场景模拟
线上库业务模拟
mysql> show create table sbtest1\G;*************************** 1. row *************************** Table: sbtest1Create Table: CREATE TABLE `sbtest1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`)) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 MAX_ROWS=10000001 row in set (0.02 sec)mysql> select count(*) from sbtest1;+----------+| count(*) |+----------+| 100000 |+----------+1 row in set (0.29 sec)
复制
历史库模拟
mysql> show create table arch_sbtest1\G;*************************** 1. row *************************** Table: arch_sbtest1Create Table: CREATE TABLE `arch_sbtest1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.02 sec)
复制
将历史数据导出到文件
将sbtest1历史数据导出到文件,并不删除原表记录,不加--no-delete,默认删除原表记录
[mysql@localhost backup]$ pt-archiver --source A=utf8,u=tony,p=tony,h=192.168.17.128,P=3308,D=sbtest,t=sbtest1 --file=/home/mysql/backup/%Y-%m-%d-%D.%t --where="id <10000" --no-delete --progress=100 --limit 100 --statisticsTIME ELAPSED COUNT2020-09-01T05:28:10 0 02020-09-01T05:28:10 0 1002020-09-01T05:28:10 0 2002020-09-01T05:28:10 0 3002020-09-01T05:28:10 0 4002020-09-01T05:28:10 0 5002020-09-01T05:28:10 0 600 .......2020-09-01T05:28:13 2 98002020-09-01T05:28:13 2 99002020-09-01T05:28:13 2 9999Started at 2020-09-01T05:28:10, ended at 2020-09-01T05:28:13Source: A=utf8,D=sbtest,P=3308,h=192.168.17.128,p=...,t=sbtest1,u=tonySELECT 9999INSERT 0DELETE 0Action Count Time Pctcommit 10000 1.1150 38.55select 101 0.6442 22.27print_file 9999 0.1509 5.22other 0 0.9827 33.97
复制
--limit 100,从上面的结果可以看出,每次从源库取出的数据是100条。
删除指定条件记录
按照过滤条件,删除过期的历史数据,在这里--bulk-delete,代表用批量删除的方法
[mysql@localhost backup]$ pt-archiver --source A=utf8mb4,u=tony,p=tony,h=192.168.17.128,P=3308,D=sbtest,t=sbtest1 --purge --where="id<=10000" --progress=500 --limit 1000 --txn-size 500 --bulk-delete --statisticsTIME ELAPSED COUNT2020-09-01T05:33:24 0 02020-09-01T05:33:24 0 5002020-09-01T05:33:24 0 10002020-09-01T05:33:24 0 15002020-09-01T05:33:24 0 20002020-09-01T05:33:24 0 25002020-09-01T05:33:24 0 30002020-09-01T05:33:24 0 35002020-09-01T05:33:24 0 40002020-09-01T05:33:24 0 45002020-09-01T05:33:24 0 50002020-09-01T05:33:24 0 55002020-09-01T05:33:24 0 60002020-09-01T05:33:24 0 65002020-09-01T05:33:25 0 70002020-09-01T05:33:25 0 75002020-09-01T05:33:25 1 80002020-09-01T05:33:25 1 85002020-09-01T05:33:25 1 90002020-09-01T05:33:25 1 95002020-09-01T05:33:25 1 100002020-09-01T05:33:25 1 10000Started at 2020-09-01T05:33:24, ended at 2020-09-01T05:33:25Source: A=utf8mb4,D=sbtest,P=3308,h=192.168.17.128,p=...,t=sbtest1,u=tonySELECT 10000INSERT 0DELETE 10000Action Count Time Pctbulk_deleting 10 0.8056 60.49commit 21 0.1089 8.18select 11 0.0567 4.26other 0 0.3606 27.08
复制
将历史数据迁移到远程数据库
将历史数据迁移到远程数据库,必须在目标数据库创建好表
[mysql@localhost backup]$ pt-archiver --source A=utf8,u=tony,p=tony,h=192.168.17.128,P=3308,D=sbtest,t=sbtest1 --dest A=utf8,u=root,p=root,h=172.17.0.3,P=3306,D=testdb,t=arch_sbtest1 --where="id<20000" --progress=500 --limit 1000 --txn-size 500 --bulk-delete --bulk-insert --statistics# A software update is available:TIME ELAPSED COUNT2020-09-01T05:37:51 0 02020-09-01T05:37:51 0 5002020-09-01T05:37:51 0 10002020-09-01T05:37:51 0 15002020-09-01T05:37:51 0 20002020-09-01T05:37:52 0 25002020-09-01T05:37:52 0 30002020-09-01T05:37:52 0 35002020-09-01T05:37:52 0 40002020-09-01T05:37:52 0 45002020-09-01T05:37:52 0 50002020-09-01T05:37:52 1 55002020-09-01T05:37:52 1 60002020-09-01T05:37:52 1 65002020-09-01T05:37:52 1 70002020-09-01T05:37:53 1 75002020-09-01T05:37:53 1 80002020-09-01T05:37:53 1 85002020-09-01T05:37:53 1 90002020-09-01T05:37:53 1 95002020-09-01T05:37:53 1 9999Started at 2020-09-01T05:37:51, ended at 2020-09-01T05:37:53Source: A=utf8,D=sbtest,P=3308,h=192.168.17.128,p=...,t=sbtest1,u=tonyDest: A=utf8,D=testdb,P=3306,h=172.17.0.3,p=...,t=arch_sbtest1,u=rootSELECT 9999INSERT 9999DELETE 9999Action Count Time Pctbulk_inserting 10 0.5509 28.50bulk_deleting 10 0.2252 11.65commit 40 0.1490 7.71select 11 0.0957 4.95print_bulkfile 9999 -0.0099 -0.51other 0 0.9222 47.70
复制
在历史库中查询归档表
mysql> select count(*) from arch_sbtest1;+----------+| count(*) |+----------+| 9999 |+----------+1 row in set (0.01 sec)
复制
可以看到已经归档到历史表了。
文章转载自落叶说Mysql数据库运维,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。
评论
相关阅读
【MySQL 30周年庆】MySQL 8.0 OCP考试限时免费!教你免费领考券
墨天轮小教习
2970次阅读
2025-04-25 18:53:11
MySQL 30 周年庆!MySQL 8.4 认证免费考!这次是认真的。。。
严少安
852次阅读
2025-04-25 15:30:58
【活动】分享你的压箱底干货文档,三篇解锁进阶奖励!
墨天轮编辑部
495次阅读
2025-04-17 17:02:24
MySQL 9.3 正式 GA,我却大失所望,新特性亮点与隐忧并存?
JiekeXu
435次阅读
2025-04-15 23:49:58
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
373次阅读
2025-04-15 14:48:05
MySQL 8.0 OCP 1Z0-908 考试解析指南(二)
JiekeXu
291次阅读
2025-04-30 17:37:37
记录MySQL数据库的一些奇怪的迁移需求!
陈举超
278次阅读
2025-04-15 15:27:53
SQL优化 - explain查看SQL执行计划(下)
金同学
274次阅读
2025-05-06 14:40:00
MySQL 8.0 OCP 1Z0-908 考试题解析指南
青年数据库学习互助会
257次阅读
2025-04-30 12:17:54
MySQL 30 周年庆!MySQL 8.4 认证免费考!这次是认真的。。。
数据库运维之道
247次阅读
2025-04-28 11:01:25