mysql5.095下载_史上最好用的Mysql历史数据归档工具-CSDN博客

本文链接：https://blog.csdn.net/weixin_36384501/article/details/113113823

在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)

可以看到已经归档到历史表了。