MySQL任务调度工具EVENT

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

概述

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

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
13天前
|
关系型数据库 MySQL
elasticsearch对比mysql以及使用工具同步mysql数据全量增量
elasticsearch对比mysql以及使用工具同步mysql数据全量增量
28 0
|
13天前
|
关系型数据库 MySQL 数据库
rds迁移数据迁移工具选择
rds迁移数据迁移工具选择
88 3
|
6月前
|
关系型数据库 MySQL 数据库
Navica工具把远程MySQL导入到本地MySQL数据库
Navica工具把远程MySQL导入到本地MySQL数据库
|
13天前
|
SQL 关系型数据库 MySQL
MySQL——MySQL的图形化界面工具安装与使用
MySQL——MySQL的图形化界面工具安装与使用
173 0
|
13天前
|
关系型数据库 MySQL 数据库
rds安装数据库客户端工具
安装阿里云RDS的数据库客户端涉及在本地安装对应类型(如MySQL、PostgreSQL)的客户端工具。对于MySQL,可选择MySQL Command-Line Client或图形化工具如Navicat,安装后输入RDS实例的连接参数进行连接。对于PostgreSQL,可以使用`psql`命令行工具或图形化客户端如PgAdmin。首先从阿里云控制台获取连接信息,然后按照官方文档安装客户端,最后配置客户端连接以确保遵循安全指引。
129 1
|
7月前
|
网络协议 关系型数据库 MySQL
【Linux环境】Centos7.x 安装MySQL,使用Navicat等远程连接工具报2003-Can‘t connect to MySQL server on(10060 Unknown err)
【Linux环境】Centos7.x 安装MySQL,使用Navicat等远程连接工具报2003-Can‘t connect to MySQL server on(10060 Unknown err)
259 0
|
13天前
|
存储 安全 关系型数据库
MySQL中使用percona-xtrabackup工具 三种备份及恢复 (超详细教程)
MySQL中使用percona-xtrabackup工具 三种备份及恢复 (超详细教程)
|
13天前
|
SQL 关系型数据库 MySQL
【MySQL-3】图形化界面工具DataGrip安装&配置&使用
【MySQL-3】图形化界面工具DataGrip安装&配置&使用
|
13天前
|
SQL 关系型数据库 MySQL
postgresql|数据库|MySQL数据库向postgresql数据库迁移的工具pgloader的部署和初步使用
postgresql|数据库|MySQL数据库向postgresql数据库迁移的工具pgloader的部署和初步使用
133 0
|
7月前
|
SQL 监控 关系型数据库
Mysql主从同步报错解决:Error executing row event: Table zabbix.history-..
Mysql主从同步报错解决:Error executing row event: Table zabbix.history-..