mysql> show variables like ‘%event_scheduler%’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| event_scheduler | OFF |
+—————–+——-+
>开启
mysql> set global event_scheduler=1;
mysql> exit
Bye
[root@linuxidc ~]# mysql -uroot -p123
mysql> show variables like ‘%event_scheduler%’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| event_scheduler | ON |
+—————–+——-+
注意：
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}
详细解析：
mysql> create event my_event
-> on schedule every 10 second
-> do update myschema.mytable set mycol = mycol + 1;
mysql> show events;
Empty set (0.02 sec)
mysql> create table t2(id int auto_increment primary key,t_time datetime);
mysql> delimiter $$
mysql> CREATE EVENT e_daily
-> ON SCHEDULE
-> EVERY 1 MINUTE
-> COMMENT ‘Saves total number of sessions then clears the table each day’
-> DO
-> BEGIN
-> INSERT INTO t2 values (null,current_timestamp);
-> END $$
mysql> delimiter ;
……过一段时间……
mysql> select * from t2;
+—-+———————+
| id | t_time |
+—-+———————+
| 1 | 2017-04-04 18:02:38 |
| 2 | 2017-04-04 18:03:38 |
| 3 | 2017-04-04 18:04:38 |
…………
3、查看新建的计划任务
mysql> select EVENT_NAME,LAST_EXECUTED from information_schema.EVENTS;
+————+———————+
| EVENT_NAME | LAST_EXECUTED |
+————+———————+
| e_daily | 2017-04-04 18:02:38 |
+————+———————+
mysql> show eventsG;
*************************** 1. row ***************************
Db: db1
Name: e_daily
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: MINUTE
Starts: 2017-04-04 18:02:38
Ends: NULL
Status: ENABLED
Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
[root@linuxidc ~]# tail -1 /var/log/mysqld.log
2017-04-04T08:01:16.311514Z 12 [Note] Event Scheduler: scheduler thread started with id 12
通过查看MySQL日志，查看执行情况 。
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语句可以修改事件的一个或多个属性，语法与create event语句完全相同，唯一不同的是可以对事件重命名，使用RENAME TO子句。
：http://www.linuxidc.com/Linux/2017-05/144014.htm
