MySQL Event Scheduler
MySQL Events are tasks that run according to a schedule. When you
create an event, you create a named database object that contains one or more
SQL statements ready to be executed, beginning and end, at one or more
regular intervals of date and time. Similar to the Task Scheduler
in Windows
or crontab
in UNIX.
Features and properties
- An event is uniquely identified by its name and the schema to which it is assigned.
- Perform a specific action according to a schedule one time or recurrent.
- Import and save files from GitHub®, Dropbox®, Google Drive®, and
One Drive®. - Drag and drop markdown and HTML files into Dillinger.
- Export documents as Markdown, HTML, and PDF.
First of all, you have to validate the status of the Event Scheduler status. A special
event scheduler thread executes events. When running, users can see the event scheduler
thread and its current state if the users have the process privilege in the output of
SHOW PROCESSLIST
.
Example
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 1
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 2. row ***************************
Id: 2
User: event_scheduler
Host: localhost
db: NULL
Command: Daemon
Time: 3
State: Waiting for next activation
Info: NULL
2 rows in set (0.00 sec)
If the event scheduler is not enabled, set the event_scheduler system
variable to enable and start it:
SET GLOBAL event_scheduler = ON;
SET @@GLOBAL.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@GLOBAL.event_scheduler = 1;
Similary, set the event_scheduler system variable to disable or turn it
off:
SET GLOBAL event_scheduler = OFF;
SET @@GLOBAL.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@GLOBAL.event_scheduler = 0;
Event syntax
The CREATE EVENT statement creates a new event. This is the basic syntax of the
CREATE EVENT statement:
CREATE EVENT [IF NOT EXIST] event_name
ON SCHEDULE schedule
DO
event_body
If the event is a one-time event, use:
AT timestamp [+ INTERVAL]
If the event is a recurring event, use:
EVERY interval STARTS timestamp [+INTERVAL] ENDS timestamp [+INTERVAL]
Example:
CREATE EVENT [IF NOT EXIST] test_event
ON SCHEDULE EVERY 10 SECONDS
DO
INSERT INTO database VALUES (now());
Results:
1 2021-02-23 15:44:05
2 2021-02-23 15:44:15
3 2021-02-23 15:44:25
4 2021-02-23 15:44:35
5 2021-02-23 15:44:45
6 2021-02-23 15:44:55
7 2021-02-23 15:45:05
8 2021-02-23 15:45:15
9 2021-02-23 15:45:25
10 2021-02-23 15:45:35
Grant privileges
To allow a user to create, modify, or delete events, you need to
provide privileges:
GRANT EVENT ON (schema) TO (user)
For example, the following command grants permission for the schema
myschema to an user called lin@tolvar.
GRANT EVENT ON myuschema TO lin@tolvar
If you use an asterisk (*), you grant permissions to all schemas.
Updated 11 months ago