mysql数据库归档方法

1、使用分区，再利用分区交换技术能够很好地把指定分区中的数据移动到指定表中，这个需要在项目之处就进行此操作。

1、1 、创建一个新表，表结构和索引与旧表一模一样

2、2 、新建存储过程，查询30天的数据并归档进新数据库，然后把30天前的旧数据从旧表里删除

3、创建EVENT，每天晚上凌晨00：00定时执行上面的存储过程

4、备注：第一次执行存储过程的时候因为历史数据过大, 可能发生意外让该次执行没有成功。重新执行时会遇到报错ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction，应急解决方案如下：

1、执行show full processlist;查看所有MySQL线程。

2、执行SELECT * FROM information_schema.INNODB_TRX\G; 查看是否有错误线程，线程id在show full processlist;的结果中状态为sleep。

3、kill进程id。

另外写存储过程的时候可以控制事务的大小，比如说可以根据时间字段每次归档一天或者更小时间段的数据，这样就不会有大事务的问题，里面还可以加入日志表，每次归档操作的行为都写入日志表，以后查起来也一目了然。

1、使用percona-toolkit的pt-archiver工具来进行历史数据归档，支持删除和不删除元数据的选择。

pt-archiver使用的场景:

1、清理线上过期数据。

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

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

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

2、其它作用：

1、用于清理过期数据purge

$ pt-archiver --source h=10.99.73.9,P=3306,u=mha,p=123456,D=sbtest,t=sbtest \

--no-check-charset \

--where 'id<50000' \

--purge \

--limit=2 \

--statistics

注意： --source后的DSN之间不能空格出现，否则会出错。 --where条件的值，有字符串的，要用引号括起来。 --limit表示，每组一次删除多少条数据（注意：如果数据比较多时，也可以设置大一些，减少循环次数），最终的清理操作，还是通过Where pK=xx来处理的。

3、用于把数据导出文件，不用删除原表中数据

1、当历史数据进行归档后，这个时候就有需求了。当需要查看历史数据和现有表数据时有没有什么好的方法呢？其实可以使用union或union all来进行多表结果合并操作。

在数据库中，union和union all关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。

2、这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

而union all只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。 从效率上说，union all要比union快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用union all，如下：