MySQL Event Scheduler与MHA

作者: mysia | 来源:发表于2020-03-10 18:39 被阅读0次

0 - 前言

事件调度器是在 MySQL 5.1 中新增的另一个特色功能，可以作为定时任务调度器，取代部分原先只能用操作系统任务调度器才能完成的定时功能。而且 MySQL 的事件调度器可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下就非常实用了。

事件调度器是定时触发执行的，在这个角度上也可以称作是”临时的触发器”。触发器只是针对某个表产生的事件执行一些语句，而事件调度器则是在某一个(间隔)时间执行一些语句。事件是由一个特定的线程来管理的，也就是所谓的”事件调度器”。启用事件调度器后，拥有 SUPER 权限的账户执行 SHOW PROCESSLIST 就可以看到这个线程了。通过设定全局变量event_scheduler 的值即可动态的控制事件调度器是否启用。

(root@(none))>select * from information_schema.processlist;
+--------+-----------------+------------------+------+------------------+---------+---------------------------------------------------------------+----------------------------------------------+
| ID     | USER            | HOST             | DB   | COMMAND          | TIME    | STATE                                                         | INFO                                         |
+--------+-----------------+------------------+------+------------------+---------+---------------------------------------------------------------+----------------------------------------------+
| 256578 | root            | localhost        | NULL | Query            |       0 | executing                                                     | select * from information_schema.processlist |
|     13 | repl            | 10.110.1.1:36837 | NULL | Binlog Dump GTID | 1206435 | Master has sent all binlog to slave; waiting for more updates | NULL                                         |
|     14 | repl            | 10.110.1.1:58374 | NULL | Binlog Dump GTID | 1206428 | Master has sent all binlog to slave; waiting for more updates | NULL                                         |
|      7 | event_scheduler | localhost        | NULL | Daemon           | 1206466 | Waiting on empty queue                                        | NULL                                         |
+--------+-----------------+------------------+------+------------------+---------+---------------------------------------------------------------+----------------------------------------------+
4 rows in set (0.00 sec)

1 - Event Scheduler与MHA

在MySQL 8.0之前，该参数是默认禁用的，如需使用，需要运行set global event_scheduler=on;，并在配置文件中添加event_scheduler = 1。在MySQL 8.0之后，该参数默认开启。

启用Event Scheduler，会影响MHA的正常使用。手工切换时，MHA会把该线程当作未完成的事务来处理，从而导致切换失败，具体信息如下：

'Time' => '1206439','Command' => 'Daemon','db' => undef,'Id' => '7','Info' => undef,'User' => 'event_scheduler','State' => 'Waiting on empty queue','Host' => 'localhost'

解决方式：

关闭Event Scheduler，定时任务（估计没人用）；
改造MHA，切换前检查未完成事务的时候将该线程过滤出去（成本高）；

2 - Event Scheduler相关

2.1 - 创建Event

CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO event_body;

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

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

语法说明：

DEFINER：指定可执行该定时器的MySQL账号，user的格式是’user_name’@’host_name’，CURRENT_USER或CURRENT_USER()，注意，单引号是需要在语句中输入的。如果不指定，默认是DEFINER = CURRENT_USER。
event_name：事件名称，最大64个字符，不区分大小写，MyEvent和myevent是一样的，命名规则和其他MySQL对象是一样的。
ON SCHEDULE schedule：下文详细说明。
[ON COMPLETION [NOT] PRESERVE]：可选，preserve是保持的意思，这里是说这个定时器第一次执行完成以后是否还需要保持，如果是NOT PRESERVE，该定时器只执行一次，完成后自动删除事件；没有NOT，该定时器会多次执行，可以理解为这个定时器是持久性的。默认是NOT PRESERVE。
[ENABLE | DISABLE | DISABLE ON SLAVE]：可选，是否启用该事件，ENABLE-启用，DISABLE-禁用，可使用alter event语句修改该状态。DISABLE ON SLAVE是指在主备复制的数据库服务器中，在备机上也创建该定时器，但是不执行。
COMMENT: 注释，必须用单引号括住。
DO event_body：事件要执行的SQL语句，可以是一个SQL，也可以是使用BEGIN和END的复合语句，和存储过程相同。

2.2 - Event的执行时间

ON SCHEDULE指定事件何时执行，执行的频率和执行的时间段，有AT和EVERY两种形式。

AT timestamp
AT timestamp用于只执行一次的事件。执行的时间由timestamp指定，timestamp必须包含完整的日期和时间，即年月日时分秒都要有。可以使用DATETIME或TIMESTAMP类型，或者可以转换成时间的值，例如“2020-03-10 00:00:00”。如果指定是时间是过去的时间，该事件不会执行，并生成警告。

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2020-03-10 17:48:21 |
+---------------------+
1 row in set (0.050 sec)

mysql> CREATE EVENT e_totals
    ->     ON SCHEDULE AT '2020-03-10 10:00:00'
    ->     DO INSERT INTO test.totals VALUES (NOW());
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1588
Message: Event execution time is in the past and ON COMPLETION  
         NOT PRESERVE is set. The event was dropped immediately 
         after creation.
1 row in set (0.01 sec)

这个只执行一次的事件，因为时间定义是过去的时间，所以不会执行，创建以后又被立即删除。可以使用CURRENT_TIMESTAMP指定执行时间，这样的话，事件创建成功会立即执行。

如果事件执行的时间是未来的某个时间点，可以使用+ INTERVAL interval指定具体时间。interval有数字(quantity)和时间单位(Unit of time)两部分组成，例如：2分10秒后执行，应写为 + INTERVAL ‘2:10’ MINUTE_SECOND。可用的时间单位有很多，列表如下：

unit	说明
YEAR	年
QUARTER	季度
MONTH	月
DAY	天
HOUR	时
MINUTE	分
WEEK	周
SECOND	秒
YEAR_MONTH	年:月
DAY_HOUR	日:时
DAY_MINUTE	日:分
DAY_SECOND	日:秒
HOUR_MINUTE	时:分
HOUR_SECOND	时:秒
MINUTE_SECOND	分:秒

示例
这是一个最简单的示例，1小时后执行该事件：

CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;

Every interval
如果需要让事件定时执行，使用Every这种方式。注意EVERY后边的interval没有+ INTERVAL，时间格式和单位和上面的相同。

示例

EVERY 6 WEEK 每六周
EVERY 20 second 每20秒

EVERY后面可以跟可选的STARTS和ENDS，指定事件开始和结束时间，在这个时间段内，时间定时执行。STARTS和ENDS可同时指定，或者只指定STARTS，或两者都不指定。

示例

EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK 一周以后开始，每隔三个月
EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL ‘6:15’ HOUR_MINUTE 六个小时15分钟以后，每隔两周
EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK 30分钟以后开始，4周后结束，每隔12个小时

示例
这个示例是每小时执行一次指定操作：

CREATE EVENT e_hourly
    ON SCHEDULE
      EVERY 1 HOUR
    COMMENT 'Clears out sessions table each hour.'
    DO
      DELETE FROM site_activity.sessions;

以下是个稍微复杂点的例子，用到了复合语句：

delimiter $$

CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'Saves total number of sessions then clears the table each day'
    DO
      BEGIN
        INSERT INTO site_activity.totals (time, total)
          SELECT CURRENT_TIMESTAMP, COUNT(*)
            FROM site_activity.sessions;
        DELETE FROM site_activity.sessions;
      END $$

delimiter ;

存储过程中用到的变量、错误处理和流程控制（while，if……）语句在eventbody中都可以使用，有一点区别是，事件不能接收参数。如果需要，可以在事件中调用存储过程，通过存储过程传参数。

2.3 - 修改Event

ALTER
    [DEFINER = { user | CURRENT_USER }]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    [DO event_body]

这里的关键字含义和创建Event语句是一样的，很好理解，下面给出一些实例帮助大家理解(来自官方文档)。

原始的Event定义如下：

CREATE EVENT myevent
    ON SCHEDULE
      EVERY 6 HOUR
    COMMENT 'A sample comment.'
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;

下面的语句，将myevent从当前开始每6个小时执行，改为4个小时候每隔12个小时执行：

ALTER EVENT myevent
    ON SCHEDULE
      EVERY 12 HOUR
    STARTS CURRENT_TIMESTAMP + INTERVAL 4 HOUR;

在一个Alter Event语句中，可以修改Event的多个属性，下面的语句将myevent修改成1天后删除mytable表中的所有数据，只执行一次：

ALTER EVENT myevent
    ON SCHEDULE
      AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
    DO
      TRUNCATE TABLE myschema.mytable;

2.4 - 查看Event

MySQL的information_schema提供了访问数据库元数据的方式。元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。其中Events表中可以查看所有已定义的事件的具体信息，包括事件所属库名称、事件名称、事件类型、事件的完整定义、开始时间、INTERVAL定义、上一次执行时间等等。

mysql> SELECT * FROM EVENTS\G
*************************** 1. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: ^^^^^^ --数据库名称
          EVENT_NAME: ev_xxzh_tranhistory
             DEFINER: ^^^^^^^^^^^
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: BEGIN
    call sp_xxzh_transhistory();
END
          EVENT_TYPE: RECURRING
          EXECUTE_AT: NULL
      INTERVAL_VALUE: 1
      INTERVAL_FIELD: DAY
            SQL_MODE: NO_ENGINE_SUBSTITUTION
              STARTS: 2017-10-19 05:30:00
                ENDS: NULL
              STATUS: SLAVESIDE_DISABLED
       ON_COMPLETION: PRESERVE
             CREATED: 2017-10-19 18:00:26
        LAST_ALTERED: 2017-10-19 18:00:26
       LAST_EXECUTED: NULL
       EVENT_COMMENT: 
          ORIGINATOR: 110
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
  DATABASE_COLLATION: utf8_general_ci

Events表中的字段名称含义都很清晰，这里不再详细描述。

2.5 - 删除Event

删除事件的语句很简单：

drop event myevent；

2.6 - 查看调度器状态

show variables like "event_scheduler";

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

完。

参考资料：
https://blog.csdn.net/kelvin_yin/article/details/79128981

MySQL Event Scheduler与MHA

0 - 前言

1 - Event Scheduler与MHA

2 - Event Scheduler相关

2.1 - 创建Event

2.2 - Event的执行时间

2.3 - 修改Event

2.4 - 查看Event

2.5 - 删除Event

2.6 - 查看调度器状态

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

DBA