Telegram Bot
MySQL Event Scheduler — Complete Guide
3 min read
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.
For closely related event scheduler topics, see MySQL Error #1227 — Event Scheduler, Enabling Permanently, Check Status & Enable Temporarily, and MySQL Event Scheduler — Complete Guide.
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=ONThen 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\GModifying 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:
- Check the Event Scheduler Status: Ensure the scheduler is enabled using:
- Check MySQL Error Logs: Look for errors in the MySQL error log:
- Verify Event Definitions: Use:
SHOW VARIABLES LIKE 'event_scheduler';sudo tail -f /var/log/mysql/error.logSHOW CREATE EVENT event_name\GPractical 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.
USD - United States Dollar
EUR - Euro
GBP - British Pound
CAD - Canadian Dollar
CHF - Swiss Franc
JPY - Japanese Yen
AUD - Australian Dollar
RUB - Russian Ruble
TRY - Turkish Lira
CNY - Chinese Yuan
INR - Indian Rupee
UAH - Ukrainian Hryvnia