一、建立存储过程
临时将半年前的数据备份到历史数据表
CREATE PROCEDURE `proc_backupdata_his`()
BEGIN
INSERT INTO MCM_BASE.STATISTIC_HOST_HIS
SELECT * FROM MCM_BASE.STATISTIC_HOST T
WHERE date_format(T.STATISTIC_TIME,'%Y-%m-%d') <= date_format(DATE_SUB(curdate(), INTERVAL 6 MONTH),'%Y-%m-%d')
AND NOT EXISTS (SELECT 1 FROM MCM_BASE.STATISTIC_HOST_HIS B WHERE T.ID=B.ID);
COMMIT;
DELETE FROM MCM_BASE.STATISTIC_HOST T
WHERE date_format(T.STATISTIC_TIME,'%Y-%m-%d') <= date_format(DATE_SUB(curdate(), INTERVAL 6 MONTH),'%Y-%m-%d')
AND EXISTS (SELECT 1 FROM MCM_BASE.STATISTIC_HOST_HIS B WHERE B.ID = T.ID);
COMMIT;
END
二、建立定时任务
显示定时任务并设置,查看定时策略
show variables like '%event_sche%';
set global event_scheduler=1;
设置定时任务开始(如果策略显示ON,则不需要执行)
set global event_scheduler=1;
设置mysql 重启有效,所以需要写入配置文件中
[mysqld]
event_scheduler=ON //这一行加入mysqld标签下
创建mysql定时任务
create event proc_backupdata_his
ON SCHEDULE EVERY 1 day STARTS '2023-04-15 00:00:05'
ON COMPLETION NOT PRESERVE ENABLE
DO CALL proc_backupdata_his();
查看定时任务
SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;
开启 或 关闭 指定的定时任务 second_event 为定时任务名称
alter event second_event on completion preserve enable;//开启定时任务
alter event second_event on completion preserve disable;//关闭定时任务
删除事件 或者 停止事件 status 为事件名字
# 停止事件
ALTER EVENT status DISABLE
# 删除事件
DROP EVENT status