实现mysql定时任务的方法(二)

本文介绍如何使用MySQL Event Scheduler实现定时任务,包括开启事件调度器、创建存储过程及事件,并记录调度历史。同时对比了与crontab的不同。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

前言:在项目中碰到需要实行mysql定时任务,在查询资料和自己实验后暂时发现有两种可行的方法,本文先介绍第二种方法——利用mysql的event实现定时任务功能。

Event Scheduler简介

事件(event)是MySQL在相应的时刻调用的过程式数据库对象,它由一个特定的线程来管理的,也就是所谓的事件调度器(event scheduler)。而事件调度器是在MySQLv5.1.6中新增的一个功能,它相当于一个定时器,可以在指定的时间点执行一条SQL语句或一个语句块,也可以用于在固定间隔重复执行。

具体实现步骤

1.确认mysql事件调度器是否开启:

mysql> show global variables like '%event%';
+---------------------------------------------------+-------+
| Variable_name                                     | Value |
+---------------------------------------------------+-------+
| event_scheduler                                   | OFF   |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size      | 10    |
+---------------------------------------------------+-------+
3 rows in set (0.00 sec)

2.如果显示为OFF则可以通过以下方式开启:

set global event_scheduler=on;

需要注意的是这种开启方式在mysql服务重启后,又会重置为OFF,所以我们可以在mysql配置文件(Linux系统路径为/etc/my.cnf,windows系统一般是安装目录下面的my-default.ini文件)添加如下一行来保证默认开启事件调度器:

event_scheduler=1

3.创建event要调用的存储过程message_clear,该存储过程主要完成的清除history.message表的数据:

delimiter //
drop procedure if exists history.message_clear//
create procedure history.message_clear()
begin
truncate table history.message;
end//
delimiter ;

4.创建事件message_clear_event,该event每隔30天就会去调用存储过程message_clear:

delimiter //
drop event if exists history.message_clear_event;
create event history.message_clear_event
on schedule every 30 day starts NOW()
on completion preserve disable
do call history.message_clear();//
delimiter ;

5.开启事件message_clear_event:

alter event message_clear_eventon completion preserve enable;

6.查看创建事件的信息:

mysql> select * from mysql.event\G
*************************** 1. row ***************************
                  db: mnf
                name: message_clear_event
                body: call history.message_clear()
             definer: root@localhost
          execute_at: NULL
      interval_value: 30
      interval_field: DAY
             created: 2017-09-21 16:03:29
            modified: 2017-09-21 16:03:29
       last_executed: NULL
              starts: 2017-09-21 08:03:29
                ends: NULL
              status: ENABLED
       on_completion: PRESERVE
            sql_mode:
             comment:
          originator: 1
           time_zone: SYSTEM
character_set_client: utf8
collation_connection: utf8_general_ci
        db_collation: latin1_swedish_ci
           body_utf8: call history.message_clear()

事件调度记录

从上面查询event信息来看,我们只能知道这个event什么时候执行过,但是并不知道是否执行成功。为了记录事件调度的历史,可以额外创建一个event history表来作记录:

create table `history`.`event_history` (  
  `dbname` varchar(128) not null default '',  
  `eventname` varchar(128) not null default '',  
  `starttime` datetime not null default '0000-00-00 00:00:00',  
  `endtime` datetime default null,  
  `issuccess` int(11) default null,  
  `duration` int(11) default null,  
  `errormessage` varchar(512) default null,  
  `randno` int(11) default null,  
  primary key (`dbname`,`eventname`,`starttime`),  
  key `ix_endtime` (`endtime`),  
  key `ix_starttime_randno` (`starttime`,`randno`)  
) engine=innodb default charset=utf8; 

接着将event修改成如下:

delimiter //  
create definer=`root`@`localhost` event `message_clear_event` on schedule   
#修改以下调度信息  
every 30 day starts now() on completion preserve disable do   
begin  
    declare r_code char(5) default '00000';  
    declare r_msg text;  
    declare v_error integer;  
    declare v_starttime datetime default now();  
    declare v_randno integer default floor(rand()*100001);  

    insert into history.event_history (dbname,eventname,starttime,randno)   
    #修改下面的作业名(该作业的名称)  
    values(database(),'message_clear_event', v_starttime,v_randno);    

    begin  
        #异常处理段  
        declare continue handler for sqlexception    
        begin  
            set  v_error = 1;  
            get diagnostics condition 1 r_code = returned_sqlstate , r_msg = message_text;  
        end;  

        #此处为实际调用的用户程序过程  
        call history.message_clear()  
    end;  

    update history.event_history set endtime=now(),issuccess=isnull(v_error),duration=timestampdiff(second,starttime,now()), errormessage=concat('error=',r_code,', message=',r_msg),randno=null where starttime=v_starttime and randno=v_randno;  

end//  
delimiter ;  

通过往event_history表插入数据,我们能知道每次event什么时候执行,执行了多久以及是否有错误。但是需要注意的是get diagnostics这个功能只有5.6以后的版本才能使用,之前的版本我们可以通过其他方式来设置sql异常处理,具体可以参考: MySQL存储过程中的错误处理

主从库开启定时器注意事项

在另外一篇文章MySQL定时器Events中看到有如下注意事项:“对于我们线上环境来说,使用event时,注意在主库上开启定时器,从库上关闭定时器,event触发所有操作均会记录binlog进行主从同步,从库上开启定时器很可能造成卡库。切换主库后之后记得将新主库上的定时器打开。”
虽然目前在实际开发中还未遇到这种情况,但是先记录在这里以防以后遇到。

两种实现mysql定时任务方法对比

在前一篇文章实现mysql定时任务的方法(一)介绍了实现mysql定时任务的另一种方式,在使用过两种方法之后有如下对比结果:

  1. crontab方式任务调度周期只能精准到分钟,而event方式可以精准到秒;
  2. crontab方式可以通过直接运行crontab文件的方式调用任务,而event方式不能手动调用,只能定时触发;
  3. crontab方式不能执行太过复杂的存储过程,只能执行比较简单的数据库定时任务,而event方式不再依赖外部命令,能直接使用数据库本身提供的功能;
  4. crontab方式能提供的日志记录比较简单,而event可以较详细的记录每次任务调度时的错误,缺点是需要额外建一个表。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值