一 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的进行状态修改不影响从库的状态。
