Guide to MySQL Event Scheduler Scheduled Tasks

The MySQL event scheduler is a powerful built-in feature that enables database administrators and developers to automate repetitive database tasks directly within the database server. This article explores how the event scheduler works, how to enable it, and the step-by-step process of creating, managing, and monitoring scheduled tasks without relying on external system utilities like cron jobs.

What is the MySQL Event Scheduler?

The MySQL event scheduler is a background process that constantly monitors and executes tasks according to a defined schedule. An “event” in MySQL is a named database object containing one or more SQL statements to be executed at specific times or intervals.

Because these events run directly within the database engine, they eliminate the need for external scripting languages (like Python or PHP) or operating system schedulers (like Linux Cron or Windows Task Scheduler) to trigger routine database maintenance.

Enabling the Event Scheduler

Before you can use scheduled events, you must ensure the event scheduler thread is running.

To check the current status of the event scheduler, run this SQL command:

SHOW VARIABLES LIKE 'event_scheduler';

If it is turned off, you can enable it dynamically using:

SET GLOBAL event_scheduler = ON;

To ensure the event scheduler remains active even after a database restart, add the following line to your MySQL configuration file (my.cnf or my.ini) under the [mysqld] section:

event_scheduler=ON

Creating Scheduled Events

MySQL allows you to create two types of events: one-time events (which execute once at a specific date and time) and recurring events (which execute repeatedly at a specified interval).

1. One-Time Event Example

This event runs exactly one hour after it is created to archive old logs:

CREATE EVENT archive_old_logs
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
  INSERT INTO archive_table SELECT * FROM logs_table WHERE log_date < NOW() - INTERVAL 30 DAY;

2. Recurring Event Example

This event runs every day, starting immediately, to clear out expired user sessions:

CREATE EVENT clear_expired_sessions
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
  DELETE FROM user_sessions WHERE expiry_date < NOW();

Managing and Modifying Events

Once events are created, you can view, modify, or delete them using standard SQL commands.

Viewing Active Events

To see a list of all events defined in the current database:

SHOW EVENTS;

For more detailed information, you can query the system catalog:

SELECT * FROM information_schema.EVENTS WHERE EVENT_SCHEMA = 'your_database_name';

Altering an Event

If you need to change the schedule or the action of an existing event, use the ALTER EVENT statement. For example, to temporarily disable an event:

ALTER EVENT clear_expired_sessions DISABLE;

To turn it back on:

ALTER EVENT clear_expired_sessions ENABLE;

Deleting an Event

When an event is no longer needed, you can permanently remove it using the DROP EVENT command:

DROP EVENT IF EXISTS clear_expired_sessions;

Key Benefits of Using Database-Level Scheduling