史上最好用的Mysql历史数据归档工具

我不是女神ヾ 2022-11-07 04:25 406阅读 0赞

# 应用场景 #

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

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

上面就是历史数据归档的需求，要解决上面的问题，percona-toolkits工具集里有一款工具pt-archiver，可以非常完美的解决你的需求  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTA3OTc3_size_16_color_FFFFFF_t_70]

# 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 sbtest1\G;
    *************************** 1. row ***************************
           Table: sbtest1
    Create 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=1000000
    1 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_sbtest1
    Create 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=utf8
    1 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 --statistics
    
    TIME                ELAPSED   COUNT
    2020-09-01T05:28:10       0       0
    2020-09-01T05:28:10       0     100
    2020-09-01T05:28:10       0     200
    2020-09-01T05:28:10       0     300
    2020-09-01T05:28:10       0     400
    2020-09-01T05:28:10       0     500
    2020-09-01T05:28:10       0     600
     .......
    2020-09-01T05:28:13       2    9800
    2020-09-01T05:28:13       2    9900
    2020-09-01T05:28:13       2    9999
    Started at 2020-09-01T05:28:10, ended at 2020-09-01T05:28:13
    Source: A=utf8,D=sbtest,P=3308,h=192.168.17.128,p=...,t=sbtest1,u=tony
    SELECT 9999
    INSERT 0
    DELETE 0
    Action          Count       Time        Pct
    commit          10000     1.1150      38.55
    select            101     0.6442      22.27
    print_file       9999     0.1509       5.22
    other               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 --statistics
    TIME                ELAPSED   COUNT
    2020-09-01T05:33:24       0       0
    2020-09-01T05:33:24       0     500
    2020-09-01T05:33:24       0    1000
    2020-09-01T05:33:24       0    1500
    2020-09-01T05:33:24       0    2000
    2020-09-01T05:33:24       0    2500
    2020-09-01T05:33:24       0    3000
    2020-09-01T05:33:24       0    3500
    2020-09-01T05:33:24       0    4000
    2020-09-01T05:33:24       0    4500
    2020-09-01T05:33:24       0    5000
    2020-09-01T05:33:24       0    5500
    2020-09-01T05:33:24       0    6000
    2020-09-01T05:33:24       0    6500
    2020-09-01T05:33:25       0    7000
    2020-09-01T05:33:25       0    7500
    2020-09-01T05:33:25       1    8000
    2020-09-01T05:33:25       1    8500
    2020-09-01T05:33:25       1    9000
    2020-09-01T05:33:25       1    9500
    2020-09-01T05:33:25       1   10000
    2020-09-01T05:33:25       1   10000
    Started at 2020-09-01T05:33:24, ended at 2020-09-01T05:33:25
    Source: A=utf8mb4,D=sbtest,P=3308,h=192.168.17.128,p=...,t=sbtest1,u=tony
    SELECT 10000
    INSERT 0
    DELETE 10000
    Action             Count       Time        Pct
    bulk_deleting         10     0.8056      60.49
    commit                21     0.1089       8.18
    select                11     0.0567       4.26
    other                  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   COUNT
    2020-09-01T05:37:51       0       0
    2020-09-01T05:37:51       0     500
    2020-09-01T05:37:51       0    1000
    2020-09-01T05:37:51       0    1500
    2020-09-01T05:37:51       0    2000
    2020-09-01T05:37:52       0    2500
    2020-09-01T05:37:52       0    3000
    2020-09-01T05:37:52       0    3500
    2020-09-01T05:37:52       0    4000
    2020-09-01T05:37:52       0    4500
    2020-09-01T05:37:52       0    5000
    2020-09-01T05:37:52       1    5500
    2020-09-01T05:37:52       1    6000
    2020-09-01T05:37:52       1    6500
    2020-09-01T05:37:52       1    7000
    2020-09-01T05:37:53       1    7500
    2020-09-01T05:37:53       1    8000
    2020-09-01T05:37:53       1    8500
    2020-09-01T05:37:53       1    9000
    2020-09-01T05:37:53       1    9500
    2020-09-01T05:37:53       1    9999
    Started at 2020-09-01T05:37:51, ended at 2020-09-01T05:37:53
    Source: A=utf8,D=sbtest,P=3308,h=192.168.17.128,p=...,t=sbtest1,u=tony
    Dest:   A=utf8,D=testdb,P=3306,h=172.17.0.3,p=...,t=arch_sbtest1,u=root
    SELECT 9999
    INSERT 9999
    DELETE 9999
    Action              Count       Time        Pct
    bulk_inserting         10     0.5509      28.50
    bulk_deleting          10     0.2252      11.65
    commit                 40     0.1490       7.71
    select                 11     0.0957       4.95
    print_bulkfile       9999    -0.0099      -0.51
    other                   0     0.9222      47.70

在历史库中查询归档表

mysql> select count(*) from arch_sbtest1;
    +----------+
    | count(*) |
    +----------+
    |     9999 |
    +----------+
    1 row in set (0.01 sec)

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTA3OTc3_size_16_color_FFFFFF_t_70]: /images/20221023/1735960b58e84e739eddf15c98f11402.png