mysql 存储过程+定时任务实现数据迁移

矫情吗；* 2024-03-30 14:55 81阅读 0赞

需求：  
系统中用户浏览信息记录已经实现分库分表记录数据（2个库 每个库100张表），用户量比较大，每天都会产生很多的记录信息，系统默认显示用户最近一个月的浏览信息记录，要求将超过一个月的记录信息记录到历史表中，历史表的设计和默认信息存储表相同分库分表。  
  
实现：利用mysql的存储过程和定时任务来完成。 考虑到系统已经在运行，数据库中已经存在很大量的数据了，徐拿着用存储过程和定时任务来做数据迁移。

步骤：  
1.在使用之前必须确保 event\_scheduler已开启  
查看mysql数据库是否开始  
执行 SHOW VARIABLES LIKE 'event\_scheduler';  
或 SELECT @@event\_scheduler;  
或SHOW PROCESSLIST;  
  
开启：  
执行： SET GLOBAL event\_scheduler=1; 或者 SET GLOBAL event\_scheduler = ON;  
也可以在配置文件my.cnf 中加入 event\_acheduler=1  
也可以在启动命令上加上 “--event\_scheduler =1”

2.创建素具迁移的存储过程

DELIMITER $$  
USE \`yst\_uic\_service\`$$  
  
DROP PROCEDURE IF EXISTS \`Sync\_uchome\_seen\_history\`$$  
  
CREATE DEFINER=\`root\`@\`localhost\` PROCEDURE \`Sync\_uchome\_seen\_history\`(IN tabSize INT)  
BEGIN  
DECLARE i INT DEFAULT 0;  
  
\#\# 获取需同步数据的时间节点（上个月）  
SET @upmonth= DATE\_ADD(NOW(), INTERVAL -1 MONTH);  
WHILE i < tabSize DO  
SET @sqlstr=CONCAT('INSERT INTO \`uchome\_history\_',i,'\`(\`icon\`,\`uid\`,\`epgId\`,\`detailsId\`,\`score\`,\`titleData\`,\`datePoint\`,\`dateLine\`,\`objtype\`,\`templateId\`,\`times\`,\`watchTime\`)  
SELECT \`icon\`,\`uid\`,\`epgId\`,\`detailsId\`,\`score\`,\`titleData\`,\`datePoint\`,\`dateLine\`,\`objtype\`,\`templateId\`,\`times\`,\`watchTime\` FROM \`uchome\_seen\_',i,'\` WHERE \`dateLine\`<=?');  
  
SET @delsqlstr=CONCAT('delete from \`uchome\_seen\_',i,'\` WHERE \`dateLine\`<=? ');  
\#执行数据迁移  
PREPARE \_stmt FROM @sqlstr;  
EXECUTE \_stmt USING @upmonth;  
DEALLOCATE PREPARE \_stmt;  
\#执行迁移后的数据删除  
PREPARE \_stdel FROM @delsqlstr;  
EXECUTE \_stdel USING @upmonth;  
DEALLOCATE PREPARE \_stdel;  
SET i := i+1;  
END WHILE;  
END$$  
  
DELIMITER ;

3.创建定时任务

DELIMITER $$  
  
ALTER DEFINER=\`root\`@\`localhost\` EVENT \`Sync\_uchome\_seen\_history\`  
  
ON SCHEDULE EVERY 1 DAY  
  
STARTS '2015-06-29 01:30:00'  
  
ON COMPLETION NOT PRESERVE ENABLE  
  
DO CALL\`Sync\_uchome\_seen\_history\`(10)$$  
  
DELIMITER ;

4.关闭和开启任务时间  
  
临时关闭： ALTER EVENT Sync\_uchome\_seen\_history ON COMPLETION PRESERVE DISABLE;  
关闭任务 ： ALTER EVENT Sync\_uchome\_seen\_history DISABLE;

临时开启: ALTER EVENT Sync\_uchome\_seen\_history ON COMPLETION PRESERVE ENABLE;  
开启： ALTER EVENT Sync\_uchome\_seen\_history ENABLE;

5.查看数据库中事件  
show events；  
或者 select \* from mysql.event;

其他与定时任务相关的内容：

1.指定时间完成某项任务：  
  
创建事件2天后清理某张数据表：  
ON SCHEDULE AT CURRENT\_TIMESTAMP + INTERVAL 2 DAY  
DO TRUNCATE TABLE shop.aaa;

指定时间点执行任务  
ON SCHEDULEAT TIMESTAMP '2015-07-01 23:59:59'  
DO TRUNCATE TABLE shop.aaa;

定时每天执行，指定时间点结束  
（每天执行，2天后结束）  
ON SCHEDULE EVERY 1 DAY  
ENDS CURRENT\_TIMESTAMP+ INTERVAL 2 DAY  
DO TRUNCATE TABLE shop.aaa;

指定时间开始，指定时间结束  
（2天后开始执行，每天执行一次，2个月后停止）  
ON SCHEDULE EVERY 1 DAY  
STARTS CURRENT\_TIMESTAMP+ INTERVAL 2DAY  
ENDS CURRENT\_TIMESTAMP+ INTERVAL 2 month  
DO TRUNCATE TABLE shop.aaa;

创建删除事件的事件  
ON SCHEDULE ATTIMESTAMP '2015-08-01 23:59:59'  
DO DROP EVENT IF EXISTS e\_test;