MySQL Event Scheduler – The Complete Guide

When working with databases, there are often situations where you need to automate repetitive tasks like cleaning up old records, generating daily reports, or archiving data. Instead of relying on external cron jobs or scripts, MySQL provides an in-built feature called the Event Scheduler to handle such automation.

In this blog, we’ll explore what the Event Scheduler is, how it works, and how you can use it in real projects.

✅ What is the MySQL Event Scheduler?

The MySQL Event Scheduler is a mechanism that allows you to schedule and run SQL statements automatically at a specific time or interval.
Think of it as a cron job inside MySQL.

• Introduced in MySQL 5.1.6+.
• Useful for recurring tasks without using OS-level schedulers.
• Controlled by the global system variable:

SHOW VARIABLES LIKE 'event_scheduler';

Possible values:

• OFF → Disabled (default in many MySQL versions).
• ON → Enabled.
• DISABLED → Permanently disabled (cannot be turned on without restart).

⚙️ How to Enable Event Scheduler

You can enable the scheduler in two ways:

1. Temporarily (session-based)

SET GLOBAL event_scheduler = ON;

2. Permanently (in MySQL config file)

Edit my.cnf or my.ini:

[mysqld]
event_scheduler=ON

Then restart MySQL.

📝 Syntax of Creating an Event

CREATE EVENT event_name
ON SCHEDULE schedule_time
DO
   sql_statement;

Components:

1. event_name → Unique name for the event.
2. ON SCHEDULE → Defines when to execute.
   • AT 'timestamp' → Run once at a specific time.
   • EVERY interval → Run repeatedly (daily, hourly, etc.).
3. DO → Defines what SQL to run.

📌 Example 1 – One-time Event

Suppose you want to delete all logs older than 30 days at midnight:

CREATE EVENT clear_old_logs
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO
  DELETE FROM logs WHERE log_date < NOW() - INTERVAL 30 DAY;

This will execute once after 1 day.

📌 Example 2 – Recurring Event

Let’s say you want to delete old session data every day at 1 AM:

CREATE EVENT delete_old_sessions
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP(CURRENT_DATE, '01:00:00')
DO
  DELETE FROM sessions WHERE last_active < NOW() - INTERVAL 7 DAY;

This event will run daily at 1 AM.

🔍 Managing Events

Show Events

SHOW EVENTS;

View Event Details

SHOW CREATE EVENT event_name\G

Alter an Event

ALTER EVENT delete_old_sessions
ON SCHEDULE EVERY 12 HOUR;

Drop (Delete) an Event

DROP EVENT delete_old_sessions;

Enable/Disable Event

ALTER EVENT event_name ENABLE;
ALTER EVENT event_name DISABLE;

🎯 Practical Use Cases

1. Data Cleanup – Remove old logs, temporary files, expired tokens.
2. Data Archiving – Move records older than 1 year to an archive table.
3. Reports – Generate daily/monthly sales reports automatically.
4. Notifications – Update tables for reminders or alerts.
5. Performance – Optimize tables during off-peak hours.

⚠️ Things to Keep in Mind

• The event_scheduler must be ON; otherwise, events won’t run.
• Events are stored in the mysql.event system table.
• Events run with the privileges of the definer user.
• Heavy queries inside events can affect performance.
• Good practice: Use events for DB-related automation only, and cron jobs for external tasks.

🏆 Conclusion

The MySQL Event Scheduler is a powerful but often overlooked feature that can save you from writing complex external scripts. Whether it’s cleaning up old data, scheduling backups, or generating reports, you can rely on MySQL itself to handle recurring jobs.