中国OCM之家(OCMH)联合创始人。曾先后就职于日本ARUZE公司，微软中国，中国铁道科学研究院和空中网。拥有十年以上数据库开发与运维经验，曾经参与过企业级内存数据库的开发(日本)；原铁道部某大型数据中心的设计，实施，交付，运维与管理；数据库优化器的测试开发工作；国内知名互联网公司的数据库运维工作。目前致力于开源数据库在互联网行业的研发与应用，同时关注NOSQL数据库技术。分享过的企业包括：中国移动，国家电网，海尔集团，港铁集团，光大银行，北京银行，中国铁路总公司，中国铁道科学研究院，顺丰科技，圆通快递和动批网等。目前在海量数据学院分享MySQL课程。
今天为大家介绍MySQL实用运维工具套件percona-toolkit中的pt-archiver工具，pt-archiver工具可以做什么呢？很简单，归档历史数据或者说冷热数据分离。
当线上核心系统的数据量越来越大的时候，DBA往往会想到为其瘦身，瘦身的好处不言而喻，比如防止数据库膨胀过大、保持数据库效率和提高备份恢复的效率等等。瘦身的实质就是将表中的历史数据进行转移归档，比如将一年以前数据或者三个月以前的数据抽取到历史数据库、数据仓库中，之后再将已经归档完毕的数据从线上库中删除，如图所示：
pt-archiver如何帮助我们完成这项工作呢，让我们一起来往下看。
使用篇
假设我们在3311端口有MySQL实例是线上系统，其中employees库中有表salaries。我们希望将salaries表中的历史数据抽取到历史数据库（端口号3314的MySQL实例）中，之后将salaries表中的历史数据清理到，从而达到数据抽取的目的。
表结构如下：
如果有的DBA喜欢自己开发存储过程来进行数据归档，不妨让我们先看一下如果交给pt-archiver，会怎么样。
首先，抽取数据。
pt-archiver --source h=127.0.0.1,P=3311,u=root,
D=employees,t=salaries --no-delete
--dest h=127.0.0.1,P=3314,D=history,t=salaries
--where "from_date<'1995-01-01'"
--share-lock
--statistics --progress 1000
--limit 1000 --txn-size 1000
参数解释如下：
source : 源数据库连接信息。(DSN模式)
h 主机地址
P 主机端口
u 用户名
p 密码
D 数据库
t 表名
no-delete代表只是抽取数据，不删除数据；如果我们希望抽取之后再删除数据，则使用purge选项。
dest : 目标数据库连接信息。(DSN模式)
h 主机地址
P 主机端口
u 用户名
p 密码
D 数据库
t 表名
where表示对于employees数据库下salaries表的抽取过滤条件，我们可以在这里设置历史数据的过滤条件。
share-lock表示在抽取过程中需将源表（线上表）变为只读状态。鉴于有时候抽取数据多为历史数据，所以一般业务不会修改这些数据，允许的话我们不加这个选项也没有问题。当然，我们还可以指定为for-update,这样就可以以独占的方式来进行抽取了。
statistics的意思是打印出归档过程的统计信息。
progress代表打印导出过程中的信息，当前时间，当前一共耗费多少时间，当前fetch数据行数。
limit：如果是limit 1000，代表每次fecth多少行数据，类似游标获取的处理，默认为1。
txn-size：如果是txn-size 1000，代表每个事物提交的数据行数是1000，即批量提交。
我们可以看到抽取后的结果如下：
接下来，我们确认了抽取数据的正确性后，便可以删除源表的历史数据了（线上表），命令如下：
pt-archiver --source h=127.0.0.1,P=3311,u=root,
D=employees,t=salaries --purge
--where "from_date<'1995-01-01'"
--statistics --progress 1000
--limit 1000 --txn-size 1000
这样，我们就达到了为数据库瘦身的目的。
另外，我们还可以把历史数据归档到数据文件中去，归档文件或者将文件导入其他数据库，比如hbase或者mongodb或者oracle。命令如下：
pt-archiver --source h=127.0.0.1,P=3311,u=root,
D=employees,t=salaries --no-delete
--where "from_date<'1996-01-01'"
--file '/tmp/%Y-%m-%d-%D.%t'
--statistics --progress 1000
--limit 1000 --txn-size 1000
原理篇
其实pt-archiver的原理比较简单，请看下图：
上图是源数据库抽取的流程，pt-archiver会关闭自动提交，之后按照主键获取数据集，数据集的数量符合limit参数指定的数量。这样我们就完成源数据库的抽取工作。
如果指定了purge参数，则代表需要将提取的数据删除。删除的方式有两种：逐行删除和批量删除。如果我们指定了bulk-delete参数则代表批量删除。每删除txn-size参数指定的行数，则进行提交。批量删除和逐行删除的流程如下图：
至于目标库（历史库）的导入流程，如下图：
图中，pt-archiver会判断到底是否设置了bulk-insert，如果设置了bulk-insert则采用临时文件导入的方式（LOAD DATA LOCL INFILE xxxx INTO TABLE xxx），否则，采用逐行插入（insert）的方式。
性能篇
pt-archiver虽然帮我们解决了繁琐的日常归档问题，但是效率上确是差强人意。下面总结几个选项可以帮助我们提高归档性能：
1.批量数据删除和导入，我们可以开启bulk-delete和bulk-insert来进行批量的删除和插入。一般开启这个选项，速度可能会提高8倍以上（普遍情况）。（不过批量删除的bulk-delete我们一定要小心，如果系统繁忙的话可能会影响并发能力）
2.将limit选项调大，例如10,000，可以提提高抽取的数据量大小。
3.将txn-size选项调大，例如10,000,将逐句提交转化为批量提交（包括源数据库删除和目标数据库的插入）。
4.数据库层面可以调节的参数有：
（1）在历史数据库或数据仓库中插入历史数据库的时候，考虑将innodb_flush_log_at_trx_commit改为0，降低刷盘频率。
（2）在历史数据库或数据仓库中插入历史数据库的时候，考虑关闭二进制日志和将sync_binlog参数改为0或一个比较大的值。
限制篇
说到限制，其实除了速度，我比较不习惯一点，就是当source指定的源数据库（线上库）向dest目标库（历史库）做数据归档的时候，我们无法在目标库上添加自己需要的列，比如我想在历史库上增加一列“本行数据的归档时间”。在pt-archvier工具中就无法做到，也就是源库的表和目标库的表必须保持一致。所以，如果我们想做类似历史拉链表这样的结构时候，还需要自己在历史库中做一些转化了。
最后，感谢percona公司开发了pt工具，祝你一天开心愉快！
PT工具历史相关文章：
Percona toolkit（PT工具）大满足-之MySQL潜行者pt-stalk
Percona toolkit（PT工具）大满足-之MySQL杀手pt-kill（双十一特别版）
Percona toolkit（PT工具）大满足-之MySQL收割者pt-table-checksum/sync
Percona toolkit（PT工具）大满足-之英雄归来pt-osc在MySQL5.5-5.7的故事（上集）
Percona toolkit（PT工具）大满足-之英雄归来pt-osc在MySQL5.5-5.7的故事（下集）
