MySQL Events

MySQL events, commonly known as scheduled events, are the tasks that are executed according to a specified schedule.

MySQL events are similar to cron jobs on Linux or task schedulers on Windows, providing a tool to automate recurring tasks within the MySQL database server.

For example, you can create an event to optimize all tables in a database, scheduling it to run at 1:00 AM every Sunday.

Events are also known as “temporal triggers” because they are triggered by time, not by the changes made to tables like triggers.

To schedule and execute events, MySQL uses an event scheduler that continuously monitors events and ensures their timely execution.

MySQL event lifecycle

Here’s the typical lifecycle of an event:

• Creation – MySQL allows you to create an event using the CREATE EVENT statement. Like other database objects, MySQL stores events in the event scheduler.
• Activation – after defining an event, you need to explicitly activate it using the ALTER EVENT ... ENABLE statement.
• Modification – You can modify an event using the ALTER EVENT statement to change attributes such as the schedule or the SQL statements to be executed.
• Deactivation – The stop the event, you can deactivate it using the ALTER EVENT ... DISABLE statement.
• Removal – If an event is no longer in use, you can remove it by using the DROP EVENT statement.

MySQL event scheduler configuration

MySQL uses a special thread called an event scheduler thread to execute all scheduled events. You can view the status of the event scheduler thread by executing the SHOW PROCESSLIST command:

SHOW PROCESSLIST;Code language: SQL (Structured Query Language) (sql)

Output:

+----+-----------------+-----------------+------+---------+------+-----------------------------+------------------+
| Id | User            | Host            | db   | Command | Time | State                       | Info             |
+----+-----------------+-----------------+------+---------+------+-----------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL | Daemon  |    0 | Waiting for next activation | NULL             |
|  9 | root            | localhost:53911 | mydb | Query   |    0 | init                        | SHOW PROCESSLIST |
+----+-----------------+-----------------+------+---------+------+-----------------------------+------------------+
2 rows in set (0.00 sec)Code language: PHP (php)

The output shows that the event scheduler is currently running.

If the event scheduler is not enabled, you can set the event_scheduler system variable to ON to enable and start it:

SET GLOBAL event_scheduler = ON;Code language: PHP (php)

To disable and stop the event scheduler thread, you can set event_scheduler system variable to OFF:

SET GLOBAL event_scheduler = OFF;Code language: PHP (php)

Use cases of MySQL events

In practice, you’ll find events useful in the following cases:

Data backup

Events can be used to automate regular data backups to ensure the safety and recoverability of critical data.

Data purging

Events allow you to schedule tasks to automatically remove outdated data, optimizing database performance.

Reporting

Events allow you to generate periodic reports or statistical analyses during off-peak hours.

Maintenance tasks

Events allow automating routine maintenance tasks such as index rebuilding or table optimization to keep the database running efficiently.