MySQL任务调度工具EVENT-阿里云开发者社区

开发者社区> 数据库> 正文

MySQL任务调度工具EVENT

简介:

概述

MySQL的EVENT 是根据计划运行的任务,类似于Linux上的crontab 任务
主要具有以下属性

  • mysql中,定时事件的对象是全局唯一的
  • event根据时间表来执行操作,包含一个SQL操作,也可以使用begin,end 实现多个SQL的复合执行
  • event 的权限(创建修改和删除)进行控制赋予
  • 可以使用SQL语句设置或修改事件的许多属性。这些属性包括事件的名称,时间,持久性(即是否在其计划到期后保留),状态(启用或禁用),要执行的操作以及为其分配的架构

如何开启

查看系统变量,ON是表示已启动,OFF表示已停止(默认值),也可以为DISABLED,表示无法运行,不推荐使用这个参数值,状态没法在运行是进行修改

mysql> show variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec)

只要是开启了这个功能,就可以在看到此模块的线程,线程运行并执行所有定时任务

mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 454
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 45647
  State: Waiting for next activation
   Info: NULL

开启命令,如下,关闭也是同样

SET GLOBAL event_scheduler = ON;
SET @@GLOBAL.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@GLOBAL.event_scheduler = 1;

另外,因为event是全局变量,未指定全局来设置,会导致报错

mysql> set @@event_scheduler = OFF;
ERROR 1229 (HY000): Variable 'event_scheduler' is a GLOBAL variable and should be set with SET GLOBAL

权限赋予

EVENT 控制事件创建、修改和删除,权限由grant赋予,基本格式如下

mysql> GRANT EVENT ON test.* TO test;
Query OK, 0 rows affected (0.04 sec)

由于event是全局架构级别的权限,所以不能针对某个表进行赋权

mysql> GRANT EVENT ON test.test01 TO test;
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used

很显然的,包含在events里面的SQL语句,定时任务执行的用户一定需要有相关的权限,否则定时任务触发,但是任务执行也会失败,此时可以在mysql日志里可以看到信息

## 相关信息
创建event的基本语法

CREATE EVENT

CREATE EVENT [IF NOT EXISTS] event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE]
    [COMMENT 'comment']
    DO sql_statement;

schedule:
    AT timestamp [+ INTERVAL interval]
  | EVERY interval [STARTS timestamp] [ENDS timestamp]

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

系统表信息,会记录每个创建的定时任务

mysql> select * from information_schema.events\G;
*************************** 1. row ***************************
       EVENT_CATALOG: def            --永远是def
        EVENT_SCHEMA: test            --所在是的数据库名
          EVENT_NAME: event_test_insert            --event名
             DEFINER: root@%            --创建这个event的用户
           TIME_ZONE: SYSTEM            --调度时区,默认是SYSTEM
          EVENT_BODY: SQL                --主体语言
    EVENT_DEFINITION: INSERT INTO test.event_test VALUES (CURRENT_TIMESTAMP)                --需要执行的部分,就是DO 的部分内容
          EVENT_TYPE: RECURRING                --事件重复类型,一次(TRANSIENT)重复(REPEATING)
          EXECUTE_AT: NULL                --如果事件的计时由EVERY子句而不是AT子句确定(也就是说,事件类型为repeating),则此列的值为NULL。
      INTERVAL_VALUE: 1                --every的数字部分,对于一次性事件,此列为null
      INTERVAL_FIELD: SECOND                --every的单位部分,用于管理事件的时间,此处为秒
            SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION                    --在创建或更改事件并在其下执行事件时有效的SQL模式
              STARTS: 2020-03-19 13:51:52                --starts子句的事件
                ENDS: NULL                    --事件定义时包含了ENDS子句的循环事件
              STATUS: DISABLED                --ENABLE, DISABLE或SLAVESIDE_DISABLED 三个值之一,表示event的启用状态
       ON_COMPLETION: PRESERVE
             CREATED: 2020-03-19 13:51:52                --事件创建时间
        LAST_ALTERED: 2020-03-19 13:53:10                --最后修改时间
       LAST_EXECUTED: 2020-03-19 13:53:10                --最后一次执行时间
       EVENT_COMMENT:                     --备注
          ORIGINATOR: 0                --服务器id,默认为0
CHARACTER_SET_CLIENT: utf8                --字符
COLLATION_CONNECTION: utf8_general_ci            
  DATABASE_COLLATION: latin1_swedish_ci

除了查看系统表,可以有以下几种方式来查看已经创建的events,输出信息基本一致

SHOW EVENTS;
SHOW CREATE EVENT xxxx;
SELECT * FROM  MYSQL.EVENTS;

测试创建

创建一张测试表

mysql> CREATE TABLE kmtest(timeline varchar(20));
Query OK, 0 rows affected (0.02 sec)

定时循环任务

计划每一秒插入表中当前时间,如下

mysql> CREATE EVENT event_kmtest ON SCHEDULE EVERY 1 SECOND DO INSERT INTO test.kmtest VALUES (CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from kmtest limit 2;
+---------------------+
| timeline |
+---------------------+
| 2020-03-20 14:54:07 |
| 2020-03-20 14:54:08 |
+---------------------+
2 rows in set (0.00 sec)

停止当前的定时任务

mysql> ALTER EVENT event_kmtest ON COMPLETION PRESERVE DISABLE;
Query OK, 0 rows affected (0.00 sec)

如果不再需要,可以删除任务

mysql> drop event event_kmtest;
Query OK, 0 rows affected (0.00 sec)

定某一时刻执行

创建在某一时刻执行一次任务

mysql> CREATE EVENT event_kmtest ON SCHEDULE AT '2020-03-20 15:20:20' DO set @time= CURRENT_TIMESTAMP;INSERT INTO test.kmtest VALUES (CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

过了这个时间点再来查看

mysql> select * from kmtest;
+---------------------+
| timeline |
+---------------------+
| 2020-03-20 15:20:20|
+---------------------+
1 row in set (0.00 sec)

在等待执行的这段时间可以看到event的执行信息,到期执行完了就自动去除了,可以看到type是one time

mysql> show events\G;
*************************** 1. row ***************************
                  Db: test
                Name: event_kmtest
             Definer: root@%
           Time zone: SYSTEM
                Type: ONE TIME

多个SQL

一个events包含多个SQL,在使用的过程中,可能有时需要执行的SQL不仅仅是一个,有可能是多个,此时就需要使用定界符和begin end 来进行创建。例子如下:

delimiter |
CREATE EVENT event_kmtest ON SCHEDULE EVERY 1 SECOND 
 DO 
  begin
  set @time= CURRENT_TIMESTAMP;
  INSERT INTO test.kmtest VALUES (CURRENT_TIMESTAMP);
  end |
delimiter;

PS:语法是没错的,但是我在mysql的客户端命令行去执行时,会默认为换行输出,我也不知道啥原因,利用一些工具来执行是没问题的

image

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章