MySQL 事件调度器

用户1278550

发布于 2018-08-09 14:49:03

1.5K0

发布于 2018-08-09 14:49:03

文章被收录于专栏：idbaidba

一 event 介绍

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

在使用这个功能之前必须确保event_scheduler已开启,可执行

SET GLOBAL event_scheduler = 1;

或

SET GLOBAL event_scheduler = on;

设置成功之后,执行show proceslist;

 11:56:09> show variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.01 sec)
11:56:30> SET GLOBAL event_scheduler = 1;
Query OK, 0 rows affected (0.03 sec)
11:56:41> show variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.01 sec)
11:59:57> show processlist;
+--------+-----------------+-----------------+------+---------+------+------------------------+------------------+
| Id     | User            | Host            | db   | Command | Time | State                  | Info             |
+--------+-----------------+-----------------+------+---------+------+------------------------+------------------+
| 165933 | root            | localhost:34672 | NULL | Query   |    0 | NULL                   | show processlist |
| 165934 | event_scheduler | localhost       | NULL | Daemon  |  204 | Waiting on empty queue | NULL             |
+--------+-----------------+-----------------+------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)

进程中会出现一个event_scheduler的用户用于执行调度事件。

二创建事件

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}

1) 创建一个任务每隔两秒向表插入数据

create event e_insert on schedule every 2 sencond do insert into test.evnt values (now());

2) 10天后清空evnt表：

create event e_10d_truncate_evnt on schedule at current_timestamp + interal 10 day do truncate table yang.evnt;

3) 2013年4月5日12点整清空evnt表：

create event eevnt1 on schedule at timestamp '2013-04-05 12:00:00' do truncate table yang.evnt;

4) 2天后开启每天定时清空evnt表,一年后停止执行

create event e_x on schedule every 1 day starts current_timestamp + interval 2 day ends current_timestamp + interval 1 year do truncate table yang.evnt

[on completion [not] preserve] 默认是on completion not preserve即计划任务执行完毕后自动drop该事件；on completion preserve则不会drop掉。

5) 每天定时清空evnt表(只执行一次,任务完成后就终止该事件)：

create event e_evnt_3 on schedule every 1 day on completion not preserve do truncate table yang.evnt

[enable | disenable]可是设置该事件创建后状态是否开启或关闭,默认为enable。 [commet 'commet']可以给该事件加上注释。

三修改事件

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]

1) 关闭事件

alter event e_evnt disenable;

2) 开启事件

alter event e_evnt enable;

3) 将每天清空evnt表改为5天清空一次:

alter event e_evnt on schedule every 5 day;

四删除事件

drop event [IF EXISTS] event_name

五主从复制对event的影响

对于主从架构的数据库要注意将从库的任务调度关闭，以防止在主库执行了之后，从库又重复在备库进行调度evnet。

test  07:31:16 >create event  e_insert  
    -> on schedule every 2 second
    -> do insert into test.evnt values (now());
Query OK, 0 rows affected (0.01 sec)

test  07:33:13 > SELECT EVENT_SCHEMA,EVENT_NAME,
EVENT_DEFINITION,
INTERVAL_FIELD,STATUS,LAST_EXECUTED 
FROM information_schema.EVENTS\G
*************************** 1. row ******************
    EVENT_SCHEMA: test
      EVENT_NAME: e_insert
EVENT_DEFINITION: insert into test.evnt values (now())
  INTERVAL_FIELD: SECOND
          STATUS: ENABLED
   LAST_EXECUTED: NULL

在从库上查看

test [RO]  07:33:21 > SELECT EVENT_SCHEMA,EVENT_NAME,
-> EVENT_DEFINITION,
    -> INTERVAL_FIELD,STATUS,LAST_EXECUTED
    -> FROM information_schema.EVENTS\G
*************************** 1. row *******************
    EVENT_SCHEMA: test
      EVENT_NAME: e_insert
EVENT_DEFINITION: insert into test.evnt values (now())
  INTERVAL_FIELD: SECOND
          STATUS: SLAVESIDE_DISABLED
   LAST_EXECUTED: NULL
1 row in set (0.00 sec)

新创建的event在master上的状态是ENABLED，在slave上的状态是SLAVESIDE_DISABLED。

修改event的状态

test [RW] 07:50:30 >SELECT EVENT_SCHEMA,EVENT_NAME,
-> EVENT_DEFINITION,
    -> INTERVAL_FIELD,STATUS,LAST_EXECUTED
    -> FROM information_schema.EVENTS\G
*************************** 1. row *********************** 
    EVENT_SCHEMA: test
      EVENT_NAME: e_insert
EVENT_DEFINITION: insert into test.evnt(dt) values (now())
  INTERVAL_FIELD: SECOND
          STATUS: DISABLED
   LAST_EXECUTED: 2018-06-07 19:41:51
1 row in set (0.00 sec)
(none) [RW]  07:51:00 >use test;
Database changed
test [RW]  07:51:02 >alter event e_insert enable;
Query OK, 0 rows affected (0.00 sec)
test [RW] 07:51:04 >SELECT EVENT_SCHEMA,EVENT_NAME,
-> EVENT_DEFINITION,
    -> INTERVAL_FIELD,STATUS,LAST_EXECUTED
    -> FROM information_schema.EVENTS\G
*************************** 1. row ***********************
    EVENT_SCHEMA: test
      EVENT_NAME: e_insert
EVENT_DEFINITION: insert into test.evnt(dt) values (now())
  INTERVAL_FIELD: SECOND
          STATUS: ENABLED
   LAST_EXECUTED: 2018-06-07 19:41:51
1 row in set (0.00 sec)

查看从库中event的状态

test [RO] 07:51:07 > SELECT EVENT_SCHEMA,EVENT_NAME,
-> EVENT_DEFINITION,
    -> INTERVAL_FIELD,STATUS,LAST_EXECUTED
    -> FROM information_schema.EVENTS\G
*************************** 1. row ***********************
    EVENT_SCHEMA: test
      EVENT_NAME: e_insert
EVENT_DEFINITION: insert into test.evnt(dt) values (now())
  INTERVAL_FIELD: SECOND
          STATUS: SLAVESIDE_DISABLED
   LAST_EXECUTED: NULL
1 row in set (0.00 sec)

可以看出，在主库上修改event的status状态不会改变slave上面的状态。

总结一下 主从对event_scheduler的影响:

在主库上新建event，在slave上event的状态为SLAVESIDE_DISABLED没有影响。
通过xtrabackup恢复出来的从库，如果有events那么需要在slave上把event_scheduler设置为off，并且检查 events的status状态,如果是enable，则需要执行: ALTER EVENT e_insert DISABLE ON SLAVE;
当主从发生切换时，需要人工(或者有配套的机制)来维护event的状态改为: 新主库 set global event_scheduler=on; alter event e_insert enable; 新从库 set global event_scheduler=off; alter event e_insert disable on slave;
在主库上对event的进行状态修改不影响从库的状态。