MySQL’s Event Scheduler can feel like a black box, but it’s actually just a thread that periodically wakes up to check for scheduled events.
Let’s watch it in action. Imagine you want to clean up old log entries every night.
-- First, make sure the event scheduler is enabled.
SET GLOBAL event_scheduler = ON;
-- Now, create the event.
CREATE EVENT cleanup_logs
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
DELETE FROM application_logs WHERE log_timestamp < NOW() - INTERVAL 7 DAY;
-- You can see it in the information_schema.
SELECT EVENT_NAME, STATUS, NEXT_RUN, EVENT_DEFINITION
FROM information_schema.events
WHERE EVENT_NAME = 'cleanup_logs';
This cleanup_logs event will execute its DELETE statement once a day, starting an hour from now. The information_schema.events table is your window into what the scheduler is thinking.
The core problem the Event Scheduler solves is the need for cron-like job execution directly within your database. Instead of an external process polling MySQL, MySQL itself can manage and execute scheduled tasks. This is particularly useful for database maintenance, data aggregation, or triggering actions based on time.
Internally, the Event Scheduler is a single thread within the MySQL server process. It continuously loops, checking the information_schema.events table for events whose NEXT_RUN timestamp has passed. When it finds one, it executes the EVENT_DEFINITION SQL. After execution, it recalculates the NEXT_RUN based on the event’s SCHEDULE and updates the information_schema table.
Controlling events involves several key parameters and syntax elements:
ON SCHEDULE: Defines the timing.EVERY 1 HOUR,EVERY 2 DAY,AT '2023-10-27 03:00:00'.STARTS: When the schedule should begin.CURRENT_TIMESTAMPor a specific datetime.ENDS: When the schedule should stop. Optional, defaults to never.DO: The SQL statement(s) to execute. Can be a single statement or aBEGIN...ENDblock for multiple statements.ENABLE/DISABLE: Control whether an event will run without dropping it.ALTER EVENT: Modify existing events.
Consider this event that runs a stored procedure:
CREATE EVENT nightly_report
ON SCHEDULE EVERY 1 DAY
STARTS '2023-10-27 02:00:00'
DO
CALL generate_daily_sales_report();
The DO clause can contain multiple statements if enclosed in BEGIN and END.
CREATE EVENT complex_cleanup
ON SCHEDULE EVERY 3 HOUR
DO
BEGIN
DELETE FROM temp_table WHERE created_at < NOW() - INTERVAL 12 HOUR;
INSERT INTO audit_log (message, timestamp) VALUES ('Temp table cleaned', NOW());
END;
The event_scheduler system variable must be ON for any events to run. You can set this globally or per-session, but for persistent scheduling, GLOBAL is the way to go. If it’s OFF, the scheduler thread is dormant.
-- Check status
SHOW VARIABLES LIKE 'event_scheduler';
-- Turn on (if not already)
SET GLOBAL event_scheduler = ON;
You can also drop events if they’re no longer needed.
DROP EVENT IF EXISTS old_event_name;
A common pitfall is assuming events run exactly on time. The scheduler thread checks periodically, so an event scheduled for 03:00:00 might actually execute a few milliseconds or even seconds later, depending on server load and the scheduler’s internal polling interval. This is usually fine for maintenance tasks but critical for high-precision timing.
When you define an event with ON SCHEDULE EVERY N unit, the STARTS clause dictates the first execution. Subsequent executions are then calculated based on that initial run time. This means if you have an event set to run EVERY 1 DAY and STARTS at 2023-10-27 03:00:00, and the server is down at that exact moment, the event will attempt to run as soon as the server is back up and the scheduler is active, effectively "catching up." However, if the NEXT_RUN time is missed entirely (e.g., the server was down for days), the event might not run again until the next scheduled interval after the NEXT_RUN time has passed, depending on the DO_COMPENSATE setting (which isn’t directly exposed but is part of the internal logic).
The next concept you’ll likely encounter is handling event failures and monitoring their execution status beyond just NEXT_RUN.