mysql not in语句_最好用的Mysql历史数据归档工具pt-archiver

应用场景

在mysql数据库运维过程中，总会碰到一些比较棘手的事情，历史数据归档绝对算的上一个。由于一些历史原因，有些业务表当初被设计成单表，而且没有分区，业务跑了一段时间，发现越来越慢了。一排查，发现这些单表的数据太多了，导致查询效率变低，这个时候，需要将一些业务用不到的历史数据归档，减少表的数据量，提升查询效率。

可是要丝滑的将这些历史数据进行归档，可不是一件容易的事情。注意是丝滑，不能停业务，不能对线上业务造成影响。

上面就是历史数据归档的需求，要解决上面的问题，percona-toolkits工具集里有一款工具pt-archiver，可以非常完美的解决你的需求

pt-archiver功能介绍

pt-archiver有以下几个功能
1.按照过滤条件，将线上数据导出成归档文件
2.按照过滤条件，清理线上过期的历史数据
3.按照过滤条件，清理过期数据，并把数据归档到本地归档表，或者远端归档服务器的历史表。

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 sbtest1G;*************************** 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_sbtest1G;*************************** 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)

将历史数据导出到文件

将MySQL数据中的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    3000....2020-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    2000....2020-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)

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

这个技能你get了吧。