MySQL任务调度工具EVENT

2021-10-08 05:20:30

概述

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的客户端命令行去执行时，会默认为换行输出，我也不知道啥原因，利用一些工具来执行是没问题的

MySQL任务调度工具EVENT

上一篇：MySQL与 PostgreSQL 数据库功能对比


下一篇：PostgreSQL 流复制的主备切换