MySQL的EVENT调度器详解

查看event是否开启
mysql> show variables like 'event_%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+


mysql> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF               |
+-------------------+
1 row in set (0.00 sec)


开启event方法
1.配置文件开启
最好修改my.cnf配置文件，修改完配置文件需要重启才能生效
event_scheduler=1
2.命令行开启
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;   — 0代表关闭
SET @@global.event_scheduler = 1;




查看是否event线程存在
mysql> show processlist;
+-----+-----------------+-----------+-------+---------+--------+-----------------------------+------------------+
| Id  | User            | Host      | db    | Command | Time   | State                       | Info             |
+-----+-----------------+-----------+-------+---------+--------+-----------------------------+------------------+
| 103 | event_scheduler | localhost | NULL  | Daemon  |      3 | Waiting for next activation | NULL             |
+-----+-----------------+-----------+-------+---------+--------+-----------------------------+------------------+


创建一个event
create table test.tab(id int auto_increment primary key,name varchar(10));
insert into tab values(1,'a'),(2,'b'),(3,'c');
create event e_delete_tab on schedule every 30 second do delete from tab limit 1;


查看event情况
show events;
show events from db_name \G;
select * from mysql.event \G;
show status like '%event%';


语法
CREATE
    [DEFINER = { user | CURRENT_USER }]      --创建这个对象的用户，对于mysql这个意义不大，可以省略
    EVENT 
    [IF NOT EXISTS]
    event_name                               --定时任务的名称
    ON SCHEDULE schedule                     --定时任务执行的间隔，例如：every 30 second，every 30 day等
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]    --是否立即开启定时任务
    [COMMENT 'comment']                      --定时任务注释说明
    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}


event实例
1.创建带用户的event
create definer='root'@'localhost' 
event e_delete_tab 
on schedule every 30 second 
do delete from test.tab limit 1;
创建用户为'root'@'localhost'每隔30秒删除test.tab表一行数据
2.
create definer='root'@'localhost' 
event e_delete_tab 
on schedule at current_timestamp+interval 1 day 
do delete from test.tab limit 1;
一天后执行删除test.tab表一行数据
3.
create definer='root'@'localhost' 
event e_delete_tab 
on schedule at timestamp '2015-09-23 11:15:00'
do delete from test.tab limit 1;
在2015-09-23 11:15:00后执行删除test.tab表一行数据
4.
create definer='root'@'localhost' 
event e_delete_tab 
on schedule every 30 second
starts  '2015-09-23 11:15:00'+interval 1 day
do delete from test.tab limit 1;
在2015-09-23 11:15:00后每天每30秒执行一次删除test.tab表一行数据
5.
create definer='root'@'localhost' 
event e_delete_tab 
on schedule every 30 second
starts  current_timestamp+interval 1 day
do delete from test.tab limit 1;
在当前时间间隔1天后每30秒执行一次删除test.tab表一行数据
6.
create definer='root'@'localhost' 
event e_delete_tab 
on schedule every 30 second
starts  current_timestamp+interval 1 day
ends current_timestamp+interval 2 day
do delete from test.tab limit 1;
在当前时间间隔1天后每30秒执行一次删除test.tab表一行数据，在当前时间间隔2天后停止执行
7.
create definer='root'@'localhost' 
event e_delete_tab 
on schedule every 30 second 
on completion not preserve
do delete from test.tab limit 1;
每隔30秒执行一次,只执行一次不再执行，默认是not preserve
8.
create definer='root'@'localhost' 
event e_delete_tab 
on schedule every 30 second 
on completion preserve
do delete from test.tab limit 1;
每隔30秒执行一次，默认是not preserve
9.
create definer='root'@'localhost' 
event e_delete_tab 
on schedule every 30 second 
on completion not preserve
disable
do delete from test.tab limit 1;
创建event，不执行，默认为enable
10.
create definer='root'@'localhost' 
event e_delete_tab 
on schedule every 30 second 
comment 'This is a event,every 30 second delete one row!'
do delete from test.tab limit 1;
11.
create definer='root'@'localhost' 
event e_delete_tab 
on schedule every 30 second 
do call e_test();
每隔30秒，调用一次e_test存储过程


修改event
ALTER EVENT event_name
[ON SCHEDULE schedule]
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT 'comment']
[ENABLE | DISABLE]
[DO sql_statement]


1.
alter event e_delete_tab
on schedule every 40 second
do delete from test.tab limit 1;
修改event每隔40秒执行一次删除test.tab表一行数据
2.
alter event e_delete_tab
rename to e_delete_tab1;
rename
3.
alter event e_delete_tab disable;
临时关闭event
4.
alter event e_delete_tab enable;
开启event
5.
drop event e_delete_tab;
删除event




event创建、修改、删除权限
grant event on test.* to user@host;
event是数据库级别，所以不能赋权给表


查看用户是否有event权限
select User,Host,Event_priv from mysql.db;
select User,Host,Event_priv from mysql.user;
注意事项
1.
启动服务器时如果指定了--skip-grant-tables选项，则event_scheduler则自动被设置为DISABLED。命令行或配置文件的设置都会被覆盖。
2.
DISABLED：禁用事件调度器。SHOW PROCESSLIST命令的输出中看不到该线程。也不能通过设置event_scheduler为ON来启动该线程。如果要设置事件调度器为DISABLED，唯一的方法是在服务器启动时，使用如下选项：
--event-scheduler=DISABLED
或者在配置文件(/etc/my.cnf)的[mysqld]段中添加如下语句：
event_scheduler=DISABLED
不能在服务器运行时设置event_scheduler为DISABLED。通过如果在服务器启动时设置了event_scheduler为DISABLED，也不能动态改变event_scheduler的值为ON或OFF。