mysql调度器线程_MySQL中的事件调度器EVENT

MySQL中的事件调度器，EVENT，也叫定时任务，类似于Unix crontab或Windows任务调度程序。

EVENT由其名称和所在的schema唯一标识。

EVENT根据计划执行特定操作。操作由SQL语句组成，语句可以是BEGIN…END语句块。EVENT可以是一次性的，也可以是重复性的。一次性EVENT只执行一次，周期性EVENT以固定的间隔重复其操作，并且可以为周期性EVENT指定开始日期和时间、结束日期和时间。(默认情况下，定期EVENT在创建后立即开始，并无限期地继续，直到它被禁用或删除。)

EVENT由一个特殊的事件调度器线程执行，用SHOW PROCESSLIST可以查看。

root@database-one 13:44: [gftest]> show variables like '%scheduler%';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| event_scheduler | OFF |

+-----------------+-------+

1 row in set (0.01 sec)

root@database-one 13:46: [gftest]> show processlist;

+--------+------+----------------------+-----------+---------+------+----------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+--------+------+----------------------+-----------+---------+------+----------+------------------+

......

+--------+------+----------------------+-----------+---------+------+----------+------------------+

245 rows in set (0.00 sec)

root@database-one 13:46: [gftest]> set global event_scheduler=1;

Query OK, 0 rows affected (0.00 sec)

root@database-one 13:47: [gftest]> show variables like '%scheduler%';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| event_scheduler | ON |

+-----------------+-------+

1 row in set (0.01 sec)

root@database-one 13:47: [gftest]> show processlist;

+--------+-----------------+----------------------+-----------+---------+------+------------------------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+--------+-----------------+----------------------+-----------+---------+------+------------------------+------------------+

......

| 121430 | event_scheduler | localhost | NULL | Daemon | 33 | Waiting on empty queue | NULL |

......

+--------+-----------------+----------------------+-----------+---------+------+------------------------+------------------+

246 rows in set (0.01 sec)

可以看到，默认情况下，MySQL的EVENT没有打开，通过设置event_scheduler参数来打开或者关闭EVENT。打开后就会多一个event_scheduler，这个就是事件调度器线程。

除了打开和关闭，还可以禁用，要禁用EVENT，请使用以下两种方法之一：

启动MySQL时用命令行参数

--event-scheduler=DISABLED

在MySQL配置文件中配置参数

event_scheduler=DISABLED

MySQL 5.7中创建EVENT的完整语法如下：

CREATE

[DEFINER = 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}

详细说明可以参考官网https://dev.mysql.com/doc/refman/5.7/en/create-event.html

我们通过一个实例来验证下。

1)创建一张表。

root@database-one 13:47: [gftest]> create table testevent(id int auto_increment primary key,create_time datetime);

Query OK, 0 rows affected (0.01 sec)

root@database-one 13:50: [gftest]> select * from testevent;

Empty set (0.00 sec)

2)创建一个EVENT，每3秒往表中插一条记录。

root@database-one 13:50: [gftest]> create event insert_date_testevent on schedule every 3 second do

-> insert into testevent(create_time) values(now());

Query OK, 0 rows affected (0.01 sec)

root@database-one 13:53: [gftest]> show events \G

*************************** 1. row ***************************

Db: gftest

Name: insert_date_testevent

Definer: root@%

Time zone: +08:00

Type: RECURRING

Execute at: NULL

Interval value: 3

Interval field: SECOND

Starts: 2020-03-26 13:53:10

Ends: NULL

Status: ENABLED

Originator: 1303306

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

1 row in set (0.00 sec)

3)过一会，去表中查询数据。

root@database-one 13:53: [gftest]> select * from testevent;

+----+---------------------+

| id | create_time |

+----+---------------------+

| 1 | 2020-03-26 13:53:10 |

| 2 | 2020-03-26 13:53:13 |

| 3 | 2020-03-26 13:53:16 |

| 4 | 2020-03-26 13:53:19 |

| 5 | 2020-03-26 13:53:22 |

| 6 | 2020-03-26 13:53:25 |

| 7 | 2020-03-26 13:53:28 |

| 8 | 2020-03-26 13:53:31 |

| 9 | 2020-03-26 13:53:34 |

| 10 | 2020-03-26 13:53:37 |

| 11 | 2020-03-26 13:53:40 |

| 12 | 2020-03-26 13:53:43 |

| 13 | 2020-03-26 13:53:46 |

| 14 | 2020-03-26 13:53:49 |

| 15 | 2020-03-26 13:53:52 |

| 16 | 2020-03-26 13:53:55 |

+----+---------------------+

16 rows in set (0.00 sec)

从表里数据可以看到，创建的插数定时任务已经在正常运行了。

EVENT的详细信息除了用show event命令，还可以从mysql.event或information_schema.events中查询，也可以用show create event命令查看。

root@database-one 00:09: [gftest]> select * from mysql.event \G

*************************** 1. row ***************************

db: gftest

name: insert_date_testevent

body: insert into testevent(create_time) values(now())

definer: root@%

execute_at: NULL

interval_value: 3

interval_field: SECOND

created: 2020-03-26 13:53:10

modified: 2020-03-26 13:53:10

last_executed: 2020-03-26 16:09:37

starts: 2020-03-26 05:53:10

ends: NULL

status: ENABLED

on_completion: DROP

sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

comment:

originator: 1303306

time_zone: +08:00

character_set_client: utf8

collation_connection: utf8_general_ci

db_collation: utf8_general_ci

body_utf8: insert into testevent(create_time) values(now())

1 row in set (0.00 sec)

root@database-one 00:09: [gftest]> select * from information_schema.events \G

*************************** 1. row ***************************

EVENT_CATALOG: def

EVENT_SCHEMA: gftest

EVENT_NAME: insert_date_testevent

DEFINER: root@%

TIME_ZONE: +08:00

EVENT_BODY: SQL

EVENT_DEFINITION: insert into testevent(create_time) values(now())

EVENT_TYPE: RECURRING

EXECUTE_AT: NULL

INTERVAL_VALUE: 3

INTERVAL_FIELD: SECOND

SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

STARTS: 2020-03-26 13:53:10

ENDS: NULL

STATUS: ENABLED

ON_COMPLETION: NOT PRESERVE

CREATED: 2020-03-26 13:53:10

LAST_ALTERED: 2020-03-26 13:53:10

LAST_EXECUTED: 2020-03-27 00:10:22

EVENT_COMMENT:

ORIGINATOR: 1303306

CHARACTER_SET_CLIENT: utf8

COLLATION_CONNECTION: utf8_general_ci

DATABASE_COLLATION: utf8_general_ci

1 row in set (0.02 sec)

root@database-one 00:10: [gftest]> show create event insert_date_testevent \G

*************************** 1. row ***************************

Event: insert_date_testevent

sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

time_zone: +08:00

Create Event: CREATE DEFINER=`root`@`%` EVENT `insert_date_testevent` ON SCHEDULE EVERY 3 SECOND STARTS '2020-03-26 13:53:10' ON COMPLETION NOT PRESERVE ENABLE DO insert into testevent(create_time) values(now())

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

1 row in set (0.00 sec)

event常用命令

把事件修改为为每2分钟运行一次：

ALTER EVENT insert_date_testevent

ON SCHEDULE EVERY 2 MINUTE;

使用DISABLE关键字来禁用某个事件：

ALTER EVENT insert_date_testevent DISABLE;

使用ENABLE关键字来启用事件：

ALTER EVENT insert_date_testevent ENABLE;

使用ALTER EVENT重命名现有事件：

ALTER EVENT insert_date_testevent RENAME TO test_event;

使用RENAME TO子句将事件从一个数据库移动到另一个数据库中：

ALTER EVENT mydb.test_event

RENAME TO mydb_2.test_event_05;