MySQL Event Scheduler

MySQL Event Scheduler allows you to automate repetitive database tasks effortlessly. This tutorial covers the essentials of MySQL Event Scheduler, including how to create, modify, and delete events with examples.

What is MySQL Event Scheduler?

The Event Scheduler is a built-in MySQL database management system feature that allows you to execute SQL statements at specified intervals. You can use it to automate database operations, such as purging records, clearing logs, or generating reports, without needing external cron jobs or scheduled tasks.

How to Enable MySQL Event Scheduler?

MySQL Event Scheduler is disabled by default for security reasons. To enable it, you must set the event_scheduler system variable to ON. You can do this by running the following command:

SET GLOBAL event_scheduler = ON;

To check its status:

SHOW VARIABLES LIKE 'event_scheduler';

How to Create an Event?

To create an event, you can use the CREATE EVENT statement. Here is the syntax:

CREATE EVENT [event_name]
ON SCHEDULE [schedule]
DO [sql_statement];

Here, the event_name is the name of the event, the schedule is the schedule on which the event should run, and the sql_statement is the SQL statement that should be executed when the event runs.

For scheduling, you have two main options: use the AT clause for a one-time event or employ the EVERY clause for recurring.

Create a Basic Event

Here’s how to create a simple event that deletes records older than 30 days from a table named logs.

-- Run Once at a Specific Date and Time

CREATE EVENT purge_logs
ON SCHEDULE AT '2023-09-15 12:00:00'
DO
DELETE FROM logs WHERE timestamp_column < NOW();


-- Run Every Day

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

Create a Complex Event

Sometimes, you may need to create events that contain multiple SQL statements, or you may also need to call stored procedures. For example, you can move old logs from the logs table to the logs_archive table and then delete them from the logs table using the following events.

CREATE EVENT purge_logs
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- Handle the error
    ROLLBACK;
  END;

  START TRANSACTION;

  -- Archive the old records first
  INSERT INTO logs_archive (id, message, timestamp_column)
  SELECT id, message, timestamp_column FROM logs WHERE timestamp_column < NOW() - INTERVAL 30 DAY;

  -- Delete old records from the logs table
  DELETE FROM logs WHERE timestamp_column < NOW() - INTERVAL 30 DAY;

  COMMIT;
END;

Alter an Event

To modify an existing event:

ALTER EVENT purge_logs
ON SCHEDULE EVERY 2 DAYS;

Show Active Events

To view all active events, run:

SHOW EVENTS;

Disable a Specific Event

To turn off an event without deleting it:

ALTER EVENT purge_logs DISABLE;

Delete an Event

To permanently remove an event:

DROP EVENT IF EXISTS purge_logs;

Conclusion

This tutorial has covered the fundamentals of MySQL Event Scheduler, including how to create events with real-world examples. Learning to use MySQL Event Scheduler can significantly enhance your database management skills and productivity. Automating repetitive tasks lets you focus on more critical aspects of your work and let MySQL take care of the rest.