MySQL 大表数据定期归档

数据库有一张表数据量很大,真正WEB项目只用到一个月内的数据,因此把一个月前的旧数据定期归档。


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

create table table_new like table_old;


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

delimiter $
create procedure sp()
begin
insert into tb_new select * from table_old where rectime < NOW()  -  INTERVAL 30 DAY;
delete from db_smc.table_old where rectime < NOW() - INTERVAL 30 DAY;
end


3 - 创建EVENT,每天晚上凌晨00:00定时执行上面的存储过程

create event if not exists event_temp 
on schedule every 1 day
on completion preserve
do call sp();


备注:

第一次执行存储过程的时候因为历史数据过大, 可能发生意外让该次执行没有成功。重新执行时会遇到报错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


内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:http://sulao.cn/post/366.html

我要评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。