mysql自动任务—日志备份

一、建立日志备份存储过程

drop procedure if exists pro_baklog;

CREATE PROCEDURE pro_baklog(in days int)
 BEGIN
    DECLARE t_error INTEGER DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; 
       START TRANSACTION;
     -- 开始执行事务 
     -- 1、向日志历史表中插入 XX 天以前日志
     insert into platform_log_history
     (    log_type,
          operator_id,
          opt_time,
          opt_class,
          client_ip,
          client_type,
          module_id,
          biz_function,
          log_content,
          operation_result,
          remark,
          dataowner_id,
          product_id,
          tenant_id,
          creator_id,
          gmt_create,
          modifier_id,
          gmt_modified  )
         SELECT log_type,
          operator_id,
          opt_time,
          opt_class,
          client_ip,
          client_type,
          module_id,
          biz_function,
          log_content,
          operation_result,
          remark,
          dataowner_id,
          product_id,
          tenant_id,
          creator_id,
          gmt_create,
          modifier_id,
          gmt_modified
          FROM platform_log 
          where DATE_SUB(CURDATE(), INTERVAL days DAY) >= date(opt_time);
					
         -- 2、删除日志表中 XX天以前日志
         delete from platform_log
         where DATE_SUB(CURDATE(), INTERVAL days DAY) >= date(opt_time); 
				 
        IF t_error = 1 THEN
            ROLLBACK;
        ELSE
            COMMIT;
        END IF; 
 END;

二、建立数据库任务事件

DROP EVENT IF EXISTS eve_baklog ;

CREATE EVENT eve_baklog
ON SCHEDULE EVERY 1 DAY 
STARTS CONCAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY),' 00:00:00')
DO call pro_baklog(30); 

关于事件的详细用法参考：
https://www.cnblogs.com/javahr/p/9664203.html