The MySQL Event Scheduler allows you to schedule and automate tasks in your MySQL database. These tasks, known as events, can run SQL statements at specific times or intervals without requiring manual intervention or external scripts.

Enabling the Event Scheduler

Before creating events, you must ensure that the MySQL Event Scheduler is enabled.

Check the Event Scheduler Status

Run the following query to check if the event scheduler is enabled:

SHOW VARIABLES LIKE 'event_scheduler';

Output:

  • ON: The scheduler is enabled.
  • OFF: The scheduler is disabled.

Enable the Event Scheduler Temporarily

If it is disabled, enable it for the current session:

SET GLOBAL event_scheduler = ON;

Enable the Event Scheduler Permanently

To ensure the Event Scheduler starts automatically with MySQL, edit the MySQL configuration file (my.cnf or my.ini) and add:

[mysqld]
event_scheduler=ON

Then restart MySQL:

sudo systemctl restart mysql

Creating Events

Events are created using the CREATE EVENT statement.

Syntax

CREATE EVENT event_name
ON SCHEDULE schedule
DO
    sql_statement;

Example: Creating a Simple Event

CREATE EVENT clean_logs
ON SCHEDULE EVERY 1 DAY
DO
  DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;

This example deletes rows older than 30 days from a table named logs.

Example: Running an Event Once

CREATE EVENT backup_event
ON SCHEDULE AT '2025-02-01 00:00:00'
DO
  INSERT INTO backups SELECT * FROM data;

Viewing Scheduled Events

To view all scheduled events in the current database:

SHOW EVENTS;

To view detailed information about an event:

SHOW CREATE EVENT event_name\G

Modifying Events

To update an existing event, use the ALTER EVENT statement.

Change the Schedule

ALTER EVENT clean_logs
ON SCHEDULE EVERY 2 DAY;

Disable or Enable an Event

Disable an event:

ALTER EVENT clean_logs DISABLE;

Enable an event:

ALTER EVENT clean_logs ENABLE;

Deleting Events

To delete an event, use the DROP EVENT statement.

Example:

DROP EVENT clean_logs;

Advanced Scheduling Options

  • Interval-Based Execution:
CREATE EVENT interval_event
ON SCHEDULE EVERY 10 MINUTE STARTS '2025-02-01 08:00:00' ENDS '2025-02-01 18:00:00'
DO
  UPDATE stats SET value = value + 1;
  • Multiple Timing Options: You can combine STARTS, ENDS, and EVERY for flexible scheduling.

Debugging and Logs

If events are not working as expected:

  1. Check the Event Scheduler Status: Ensure the scheduler is enabled using:
    2. SHOW VARIABLES LIKE 'event_scheduler';
  2. Check MySQL Error Logs: Look for errors in the MySQL error log:
    3. sudo tail -f /var/log/mysql/error.log
  3. Verify Event Definitions: Use:
    4. SHOW CREATE EVENT event_name\G

Practical Use Cases

1. Data Cleanup

Automatically delete old records to maintain database size.

2. Data Archiving

Regularly back up or archive data to another table.

3. Analytics Updates

Periodically calculate and store aggregated statistics.

4. Trigger Alerts

Run a query to send notifications when specific thresholds are met.

The MySQL Event Scheduler is a powerful feature for automating database tasks. By combining it with proper scheduling and SQL scripts, you can reduce manual interventions, improve performance, and maintain database hygiene.