Mysql事件调度器(Event Scheduler)类似于定时器,可以在某一个时间点执行一个SQL语句或一个语句块(BEGIN ... END);或者每隔固定间隔重复执行。类似于Linux下的crontab,或Windows下的Task Scheduler。
一、事件调度器的配置
所有事件都是在事件调度线程(event scheduler thread)里面执行的;当提及事件调度器时,通常指该线程。如果用户有PROCESS权限,可以通过SHOW PROCESSLIST命令来查看该线程及其状态:
mysql> show processlist;
+-----+-----------------+-----------+------+---------+------+-----------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------+------+---------+------+-----------------------------------+------------------+
| 1 | system user | | | Daemon | 0 | Waiting for event from ndbcluster | NULL |
| 3 | root | localhost | test | Query | 0 | NULL | show processlist |
| 203 | event_scheduler | localhost | NULL | Daemon | 2 | Waiting for next activation | NULL |
+-----+-----------------+-----------+------+---------+------+-----------------------------------+------------------+
3 rows in set (0.00 sec)
系统变量event_scheduler决定事件调度器是否启用。它有三个值:
OFF:停止事件调度器。事件调度线程停止运行,同时也不会显示在SHOW PROCESSLIST的输出中。所有的调度时间也不会被执行。OFF是event_scheduler的默认值。
当事件调度器停止后,可以通过设置event_scheduler为ON来启动它。
ON:启动时间调度器。事件调度线程开始运行,并执行所有的调度事件。此时可以通过SHOW PROCESSLIST命令的输出中看到该线程及其状态。
通过设置event_scheduler为OFF可以停止该线程。
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。
event_scheduler的值也支持数字形式:1为ON;0为OFF。如下四条语句均可启动事件调度线程:
set global event_scheduler = on
set @@global.event_scheduler = on
set global event_scheduler = 1
set @@global.event_scheduler = 1
同理,如下四条语句也可以停止时间调度线程:
SET GLOBAL event_scheduler = OFF
SET @@global.event_scheduler = OFF
SET GLOBAL event_scheduler = 0
SET @@global.event_scheduler = 0
设置时必须使用global关键字,否则会出错:
mysql< SET @@event_scheduler = OFF;
ERROR 1229 (HY000): Variable 'event_scheduler' is a GLOBAL variable and should be set with SET GLOBAL
启动服务器时如果指定了--skip-grant-tables选项,则event_scheduler则自动被设置为DISABLED。命令行或配置文件的设置都会被覆盖。
二、事件信息
所有定义的时间可以通过如下方法查看:
1、查询mysql数据库的event表:
mysql> select * from mysql.event \G
*************************** 1. row ***************************
db: test
name: evt_insert
body: insert into account values(NULL, 100.00)
definer: root@localhost
execute_at: NULL
interval_value: 10
interval_field: SECOND
created: 2013-06-13 14:07:54
modified: 2013-06-13 14:07:54
last_executed: 2013-06-13 19:29:44
starts: 2013-06-13 18:07:54
ends: NULL
status: ENABLED
on_completion: DROP
sql_mode:
comment:
originator: 1
time_zone: SYSTEM
character_set_client: utf8
collation_connection: utf8_general_ci
db_collation: utf8_general_ci
body_utf8: insert into account values(NULL, 100.00)
1 row in set (0.01 sec)
2、查询information_schema数据库的events表:
mysql> select * from information_schema.events \G
*************************** 1. row ***************************
EVENT_CATALOG: def
EVENT_SCHEMA: test
EVENT_NAME: evt_insert
DEFINER: root@localhost
TIME_ZONE: SYSTEM
EVENT_BODY: SQL
EVENT_DEFINITION: insert into account values(NULL, 100.00)
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
INTERVAL_VALUE: 10
INTERVAL_FIELD: SECOND
SQL_MODE:
STARTS: 2013-06-13 14:07:54
ENDS: NULL
STATUS: ENABLED
ON_COMPLETION: NOT PRESERVE
CREATED: 2013-06-13 14:07:54
LAST_ALTERED: 2013-06-13 14:07:54
LAST_EXECUTED: 2013-06-13 15:30:14
EVENT_COMMENT:
ORIGINATOR: 1
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
1 row in set (0.01 sec)
3、使用show create event命令可以查看事件创建信息
mysql> show create event evt_insert \G
*************************** 1. row ***************************
Event: evt_insert
sql_mode:
time_zone: SYSTEM
Create Event: CREATE DEFINER=`root`@`localhost` EVENT `evt_insert` ON SCHEDULE EVERY 10 SECOND STARTS '2013-06-13 14:07:54' ON COMPLETION NOT PRESERVE ENABLE DO insert into account values(NULL, 100.00)
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
4、使用show events命令。其语法格式如下:
SHOW EVENTS [{FROM | IN} schema_name] [LIKE 'pattern' | WHERE expr]
可以查看某一个数据库中的所有事件,或者符合某一pattern的事件。
mysql> show events \G
*************************** 1. row ***************************
Db: test
Name: evt_insert
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 10
Interval field: SECOND
Starts: 2013-06-13 14:07:54
Ends: NULL
Status: ENABLED
Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
三、事件调度器的权限
事件的创建、修改、删除需要event权限。通过grant语句可以将某一个数据库的event权限赋予某一用户,如:
GRANT EVENT ON myschema.* TO jon@ghidora;
如果要赋予用户所有数据库的event权限,可以:
GRANT EVENT ON *.* TO jon@ghidora;
event权限属于数据库级别,因此不能将该权限用户表级别,否则报错:
mysql> GRANT EVENT ON myschema.mytable TO jon@ghidora;
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used
事件调度器在执行事件的语句块时,使用的是其定义者的权限。如果定义事件时使用了insert语句,但是该定义者并没有insert权限,则该事件执行时会报错,同时其insert失败。
查看information_schema.events表时会发现事件存在并且其状态为enabled,但其last_executed列为NULL
mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='e_store_ts' AND EVENT_SCHEMA='myschema'\G
*************************** 1. row ***************************
EVENT_CATALOG: NULL
EVENT_SCHEMA: myschema
EVENT_NAME: e_store_ts
DEFINER: jon@ghidora
EVENT_BODY: SQL
EVENT_DEFINITION: INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP())
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
INTERVAL_VALUE: 5
INTERVAL_FIELD: SECOND
SQL_MODE: NULL
STARTS: 0000-00-00 00:00:00
ENDS: 0000-00-00 00:00:00
STATUS: ENABLED
ON_COMPLETION: NOT PRESERVE
CREATED: 2006-02-09 22:36:06
LAST_ALTERED: 2006-02-09 22:36:06
LAST_EXECUTED: NULL
EVENT_COMMENT:
1 row in set (0.00 sec)
通过revoke语句类收回用户的event权限,如:
REVOKE EVENT ON myschema.* FROM jon@ghidora;
虽然收回了用户的event权限,但用户已经定义的事件并不会被删除或disable。通过如果删除一个用户,或者重命名一个用户,那么该用户定义的时间也不会自动删除,或者更改其定义者为新用户名。
用户的event权限存储在mysql.user和mysql.db两张表的event_priv字段中。event_priv字段的值为Y或N。N为默认值。只有当用户具有全局event权限(即grant event on *.*)时,mysql.user.event_priv字段值才为Y。对于数据库级别的event权限,grant语句会在mysql.db表中增加一条记录,并将其event_priv字段设为Y。用户不需要自己手动修改者两张表,因为grant和revoke语句会执行相同的操作。
有5个状态变量可以查看与event相关的操作:
Com_create_event:服务器启动后执行create event语句的次数
Com_alter_event:服务器启动后执行alter event语句的次数
Com_drop_event:服务器启动后执行drop event语句的次数
Com_show_create_event:服务器启动后执行show create event语句的次数
Com_show_events:服务器启动后执行show events语句的次数
可以通过如下语句来查看与event相关的所有状态变量:
SHOW STATUS LIKE '%event%';
mysql> show status like '%event%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| Com_alter_event | 0 |
| Com_create_event | 1 |
| Com_drop_event | 0 |
| Com_show_binlog_events | 0 |
| Com_show_create_event | 2 |
| Com_show_events | 3 |
| Com_show_relaylog_events | 0 |
| Ndb_api_event_data_count_injector | 1 |
| Ndb_api_event_nondata_count_injector | 0 |
| Ndb_api_event_bytes_count_injector | 204 |
| Ndb_api_event_data_count | 1 |
| Ndb_api_event_nondata_count | 0 |
| Ndb_api_event_bytes_count | 204 |
+--------------------------------------+-------+
13 rows in set (0.00 sec)
四、语法
1、create 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 '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}
最基本的create event只需要三个部分:
1. create event关键字以及一个event名称
2. on schedule子句
3. do子句
例如:
create event evt_insert
on schedule every 10 second
do update myschema.mytable set mycol = mycol + 1;
对于语句的详细解释:
definer:说明该event的用户。服务器在执行该事件时,使用该用户来检查权限。默认用户为当前用户,即definer = current_user。如果明确指明了definer,则必须遵循如下规则:
1.如果没有super权限,唯一允许的值就是自己当前用户,而不能设置为其他用户。
2.如果具有super权限,则可以指定任意存在的用户。如果指定的用户不存在,则事件在执行时会报错。
if not exists:如果事件已经存在,则不会创建,也不会报错
on schedule子句指定何时执行该事件,以及如何执行该事件
1. at timestamp用于创建单次执行的事件。timestamp执行了事件执行的时间,必须包括日期和事件两部分,或者是一个datetime类型的表达式,如current_timestamp。如果指定的时间是过去的时间,则会产生一个warning。
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2006-02-10 23:59:01 |
+---------------------+
1 row in set (0.04 sec)
mysql > CREATE EVENT e_totals
-> ON SCHEDULE AT '2006-02-10 23:59:00'
-> DO INSERT INTO test.totals VALUES (NOW());
Query OK, 0 rows affected, 1 warning (0.00 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.
如果要指定将来某个时间间隔,如从现在开始的两个半小时之后,则可以使用interval关键字: + INTERVAL '2:30' HOUR_MINUTE。interval关键字可以进行组合,如上例可以这样:+ INTERVAL 2 HOUR + INTERVAL 30 MINUTE。
2. every子句用于创建重复执行的事件。如果每分钟执行一次,则可以:EVERY 1 MINUTE。every子句可以指定一个开始事件和结束时间,通过STARTS和ENDS关键字来表示,具体语法与前面类似。例如:
EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK。
通常情况下,如果一个事件过期已过期,则会被立即删除。通过on completion preserve子句可以保留已过期的时间。ON COMPLETION NOT PRESERVE为默认值。
默认情况下,事件一旦创建后就立即开始执行。可以通过disable关键字来禁用该事件。
comment子句用于给事件添加注释。
do子句用于指示事件需要执行的操作,可以是一条语句,也可以是被begin...end包括的语句块。如果语句块中包含sql语句,则可能需要修改delimiter。语句块中可以包含任意的控制结构,如:
delimiter //
CREATE EVENT e
ON SCHEDULE
EVERY 5 SECOND
DO
BEGIN
DECLARE v INTEGER;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
SET v = 0;
WHILE v < 5 DO
INSERT INTO t1 VALUES (0);
UPDATE t2 SET s1 = s1 + 1;
SET v = v + 1;
END WHILE;
END //
delimiter ;
也可以在语句块中调用存储过程,如:
CREATE EVENT e_call_myproc
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO CALL myproc(5, 27);
2、alter 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 'comment']
[DO event_body]
alter event语句可以修改事件的一个或多个属性,DEFINER/ON SCHEDULE/ON COMPLETION PRESERVER/ENABLE/COMMENT/DO子句的语法与create event语句完全相同,唯一不同的是可以对事件重命名,使用RENAME TO子句,如:
ALTER EVENT OLDDB.MYEVENT RENAME TO NEWDB.MYEVENT;
3、drop event语法
DROP EVENT [IF EXISTS] event_name
该语句删除一个定义的事件