• 写在前面

调研一件工具之前，在心里一定要明白，自己要实现的是什么功能，带着自己的问题，去找寻答案才能学到新的知识点！

• 需求背景

1）线上数据库有一类ip表，用来记录每天用户对网站的访问记录情况，然后将其按照时间段整合到一起，做数据分析工作，整合的时间不同，最后形成的数据表大小亦是不同，小点千万级，大点可能亿级，现有需求将半年前的用户记录删除，如何安全删除？

2）系统做了升级，现要将原来的数据迁移至新服务器上同名数据库中，如何保证安全迁移，而不造成导入时形成大量I/O，导致系统负载过高，用户、研发等部门可以正常使用，特别是在业务高峰期间？

• pt-archiver简介

数据库归档和清理工具，可以实现的功能有

1、清理线上过期数据

2、清理过期数据，并把数据归档到本地归档表中，或者远端归档服务器

3、两张表之间的数据不完全相同，希望合并。此时加--ignore --replace选项，可以轻松实现

4、导出线上数据，到线下数据作处理

根据我的需求，调研此工具可以实现所需功能，这也正是本文始点：带着问题找答案，但作者只用到了1、2的功能，至于3、4的功能在这里只做一个记录，待之后有需求后，再做使用

• 需求实现

1.1直接删除

DELETE FROM `ip_limit_20170701_bak` WHERE id>1000

最简单的方式，但如果数据表很大，可能造成大量的磁盘I/O、系统负载等，如果还存在slave，还会造成严重的从库延迟等问题

1.2按照索引条件批量删除

DELETE FROM `ip_limit_20170701_bak` WHERE id>1000 LIMIT 10000

较安全的删除方式，可以保证磁盘I/O、系统负载、从库延迟等处于可控范围内，但数据量巨大，手动操作较为繁琐

1.3使用工具删除

[root@backup home]# pt-archiver --version   [pt工具请自行安装]

pt-archiver 3.0.10

[root@backup ~]# time

pt-archiver --source u=root,p=123456,S=/tmp/mysql.sock,P=3306,D=test,t=ip_limit_20170701_bak --no-check-charset --progress 10000 --where 'id BETWEEN 1000 AND 494731' --limit 10000 --txn-size 1000 --bulk-delete --sleep 5 --purge --statistics

解释：删除test库，ip_limit_20170701_bak表数据，不做字符集检查，删除条件是 id BETWEEN 1000 AND 494731，每次取出10000行进行处理，每处理1000行则进行一次提交，每次处理10000行后显示处理信息，每完成一次处理sleep 5s

2.1mysqldump方式

第一种：将源数据库dump成sql文件，传输到目标服务器，再进行数据导入

第二种：直接在目标服务器dump源数据库的sql，再进行数据导入

但，无论采用哪种方式数据迁移，数据表越大，在数据传输的东西，都会对带宽造成极大的消耗，在查看流量监控图的时候，可以清楚的看到毛尖；再者，在数据导入的时候，势必会造成巨大的I/O消耗，系统负载也会飙升，对数据库也会造成巨大的冲击

2.2使用工具删除

迁移数据时保留源数据：

[root@backup ~]#time pt-archiver --source u=root,p=123456,S=/tmp/mysql.sock,P=3306,D=test,t=ip_limit_20170701 --dest u=root,p=root,h=192.168.32.199,P=3306,D=ceshi,t=ip_limit_20170701 --charset=utf8 --progress 10000 --where 'id>=1' --limit 10000 --txn-size 1000 --sleep 5 --no-delete --bulk-insert --statistics

迁移数据时不保留源数据：

[root@backup ~]#time pt-archiver --source u=root,p=123456,S=/tmp/mysql.sock,P=3306,D=test,t=ip_limit_20170701 --dest u=root,p=root,h=192.168.32.199,P=3306,D=ceshi,t=ip_limit_20170701 --charset=utf8 --progress 10000 --where 'id>=1' --limit 10000 --txn-size 1000 --sleep 5  --bulk-delete --statistics

• 常用参数

--source=d                 DSN specifying the table to archive from (required)    目标节点

--where=s                  WHERE clause to limit which rows to archive (required)

--purge                    Purge instead of archiving; allows omitting --file and --dest    删除source数据库的相关匹配记录

--progress=i               Print progress information every X rows    每处理多少行显示一次信息

--limit=i                  Number of rows to fetch and archive per statement (default 1)    每次取出多少行处理

--[no]check-charset        Ensure connection and table character sets are the same (default yes)    不检查字符集

--txn-size=i               Number of rows per transaction (default 1)    每多少行提交一次

--bulk-delete              Delete each chunk with a single statement (implies --commit-each)    并行删除

--statistics               Collect and print timing statistics    结束后输出统计信息

• 最后一条数据不删除/不迁移BUG问题

平滑删除、迁移数据时，最后一条数据都不会被删除/迁移，已被证明为pt-archiver BUG，需要修改下pt-archiver代码

[root@backup ~]# vim /usr/bin/pt-archiver

原代码：

6401       $first_sql .= " AND ($col < " . $q->quote_val($val) . ")";

修改后：

6401       $first_sql .= " AND ($col <= " . $q->quote_val($val) . ")";

这些都是作者在工作中遇到的实际工作后引发的一些思考，并做的相关实践，对于其中理解不到位或者根本就是错误之处，望请下方留言，不胜感激！