如何保存mysql中的表_如何对MySQL中的大表进行数据归档

使用MySQL的过程，经常会遇到一个问题，比如说某张”log”表，用于保存某种记录，随着时间的不断的累积数据，但是只有最新的一段时间的数据是有用的；这个时候会遇到性能和容量的瓶颈，需要将表中的历史数据进行归档。

下面描述一种典型的做法：

比如说表结构如下：

CREATE TABLE `history` (

`id` int(11) NOT NULL,

`value` text,

`addtime` timestamp default current_timestamp,

PRIMARY KEY (`id`),

index idx_addtime(`addtime`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

这张表中保存有2012年2013年两年的数据，现在需要将2012年的数据备份归档起来，但是2013年年初的数据还需要被查询，因此不能简单的进行如下的动作：

create table history_tmp like history;

rename table history to history_2012,history_tmp to history;

需要在新表中保留2013年年初的数据，可以参照下面的流程进行：

create table history_tmp like history;

maxid=select max(id) from history;

minid=select id from history where addtime>"2013-01-01 00:00" order by addtime asc limit 1;

last=0;

set autocommit=1;

for(i=minid;i

{

insert into history_tmp select * from history where id>=last and id

last=i;

}

begin;

lock table history_tmp write,history write;

maxid=select max(id) from history;

insert into history_tmp select * from history where id>=last and id<=maxid;

alter table history rename to history_2012;

alter table history_tmp rename to history;

unlock tables;

commit;

说明:

使用alter table xx rename to xx，而不是rename是因为mysql的一个bug， bug地址，直接rename会出现”ERROR 1192 (HY000): Can’t execute the given command because you have active locked tables or an active transaction”错误.

需要使用lock history write来防止新的写入。

这个方式是假设这个表在有插入和查询操作，如果有update、delete操作可以通过类似OSC的算法使用trigger来实现。

不能直接使用insert select where id>minid这种方式，因为这样会导致slave的延迟，而且迟迟不能提交的事务会导致undo log无法purge。