What is Event Scheduler in MySQL: Explanation and Example

The Event Scheduler in MySQL is a feature that allows you to run SQL commands automatically at scheduled times or intervals. It works like a built-in timer that executes tasks such as maintenance or data updates without manual intervention.

How It Works

The MySQL Event Scheduler acts like an automatic alarm clock inside the database. You set up an event with a specific SQL command and tell MySQL when to run it, either once at a certain time or repeatedly at intervals.

Think of it as programming your coffee machine to start brewing at 7 AM every day without you pressing any buttons. Similarly, the Event Scheduler runs your SQL tasks on time, helping keep your database updated or cleaned without you needing to do it manually.

When enabled, the scheduler checks for events that need to run and executes their SQL code in the background. This helps automate repetitive tasks like deleting old records, updating summary tables, or sending notifications.

Example

This example creates an event that deletes rows older than 30 days from a table named logs every day at midnight.

sql

CREATE EVENT IF NOT EXISTS delete_old_logs
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY
DO
  DELETE FROM logs WHERE log_date < NOW() - INTERVAL 30 DAY;

Output

Query OK, 0 rows affected (0.01 sec)

When to Use

Use the Event Scheduler when you want to automate repetitive database tasks without relying on external scripts or manual work. It is ideal for:

• Cleaning up old or temporary data regularly
• Updating summary or aggregate tables on a schedule
• Sending periodic reports or notifications from the database
• Running maintenance tasks like optimizing tables

This helps keep your database efficient and reduces the chance of forgetting important routine tasks.

Key Points

• The Event Scheduler must be enabled with SET GLOBAL event_scheduler = ON;.
• Events can be one-time or recurring.
• Events run inside the MySQL server without external tools.
• Useful for automating maintenance and data management tasks.

Key Takeaways

The Event Scheduler automates SQL tasks by running them on a set schedule inside MySQL.

Enable it with SET GLOBAL event_scheduler = ON; before creating events.

Events can run once or repeatedly at defined intervals.

Use it to automate cleanup, updates, and maintenance without external scripts.