How to Use MySQL Database Events

When I first started managing production MySQL databases, I kept running into the same problem: I needed certain tasks to happen automatically at scheduled times — cleaning up stale sessions, archiving old logs, refreshing summary tables — without relying on an external cron job and a script that had to open its own connection every time. That’s when I discovered MySQL’s built-in Event Scheduler, and it changed the way I think about database automation. In this guide, I’ll walk you through everything I’ve learned about MySQL Events, from the absolute basics to advanced production patterns.

What Are MySQL Database Events?

A MySQL Event is a scheduled task — essentially a stored program that the MySQL server itself executes at a specified time or on a recurring interval. Think of it as a cron job that lives inside your database engine rather than on your operating system. Events are managed by a special background thread called the Event Scheduler.

I like to describe it this way: stored procedures are code you call manually (or from your application), while events are code MySQL calls for you, on a schedule you define.

Why Use Events Instead of OS-Level Cron?

I’ve used both approaches extensively, and here’s how I decide which to use:

If the task is purely SQL — deleting old rows, updating summary tables, rotating flags — I reach for an Event. If it needs to touch the filesystem, call an API, or coordinate across services, I use cron or a proper job scheduler.

MySQL Architecture Primer: Where Events Fit In

Before diving into syntax, it helps to understand where the Event Scheduler sits within MySQL’s architecture.

graph TD
    A[Client Applications] --> B[Connection Layer]
    B --> C[SQL Layer - Parser, Optimizer, Cache]
    C --> D[Storage Engine Layer - InnoDB, MyISAM, etc.]
    D --> E[(Physical Data Files)]
    F[Event Scheduler Thread] --> C
    F -.->|reads schedule from| G[mysql.event system table]

The Event Scheduler runs as a separate background thread within mysqld. It periodically checks the mysql.event system table, and when an event’s execution time arrives, it spawns a worker thread that executes the event’s body just like any other SQL statement — going through the same SQL layer and storage engine as a normal query.

This matters because events consume real server resources. A heavy event running against InnoDB will still take out locks, generate undo logs, and write to the redo log exactly like a query from your application would.

Enabling the Event Scheduler

By default, the Event Scheduler is often disabled. I always check its status first:

SHOW VARIABLES LIKE 'event_scheduler';

Output:

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| event_scheduler  | OFF   |
+------------------+-------+

To enable it for the current session (temporary, resets on restart):

SET GLOBAL event_scheduler = ON;

For a permanent setting, I add this to my.cnf:

[mysqld]
event_scheduler = ON

I verify the scheduler is running by checking the process list:

SHOW PROCESSLIST;

You should see a row with User = event_scheduler and Command = Daemon.

Creating Your First Event

Let’s say I want to delete session records older than 30 days, every day at 2 AM. Here’s the full syntax:

CREATE EVENT cleanup_old_sessions
ON SCHEDULE EVERY 1 DAY
STARTS '2026-08-01 02:00:00'
DO
  DELETE FROM sessions WHERE created_at < NOW() - INTERVAL 30 DAY;

Breaking this down:

• ON SCHEDULE EVERY 1 DAY — this is a recurring event.
• STARTS — the first execution timestamp; MySQL calculates all future runs relative to this.
• DO — the action, which can be a single statement or a BEGIN ... END block.

One-Time Events

Sometimes I only need a task to run once — for example, disabling a promotional flag after a campaign ends:

CREATE EVENT disable_promo
ON SCHEDULE AT '2026-09-01 00:00:00'
DO
  UPDATE promotions SET active = 0 WHERE promo_code = 'SUMMER26';

By default, one-time events are dropped automatically after they run, unless you add ON COMPLETION PRESERVE.

Multi-Statement Events

For more complex logic, I wrap statements in BEGIN ... END:

DELIMITER $$

CREATE EVENT nightly_maintenance
ON SCHEDULE EVERY 1 DAY
STARTS '2026-08-01 01:00:00'
DO
BEGIN
  DELETE FROM audit_log WHERE created_at < NOW() - INTERVAL 90 DAY;
  UPDATE product_stats
    SET total_sales = (SELECT SUM(quantity) FROM orders WHERE orders.product_id = product_stats.product_id);
  INSERT INTO maintenance_log (run_at, status) VALUES (NOW(), 'completed');
END$$

DELIMITER ;

I always use DELIMITER when writing multi-statement events so that the semicolons inside the block don’t prematurely terminate the CREATE EVENT statement.

Scheduling Options in Depth

MySQL gives you fine-grained control over recurrence:

-- Every 15 minutes
ON SCHEDULE EVERY 15 MINUTE

-- Every week, ending on a specific date
ON SCHEDULE EVERY 1 WEEK
STARTS '2026-08-01 00:00:00'
ENDS '2026-12-31 00:00:00'

-- Every 6 hours starting immediately
ON SCHEDULE EVERY 6 HOUR
STARTS CURRENT_TIMESTAMP

I’ve found the ENDS clause particularly useful for time-boxed campaigns or temporary data migrations — I don’t have to remember to come back and drop the event manually.

Managing Events

Viewing Existing Events

SHOW EVENTS;

Or for more detail via INFORMATION_SCHEMA:

SELECT EVENT_NAME, STATUS, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, LAST_EXECUTED
FROM INFORMATION_SCHEMA.EVENTS
WHERE EVENT_SCHEMA = 'my_database';

Altering an Event

I frequently need to pause or reschedule an event rather than dropping and recreating it:

-- Disable temporarily
ALTER EVENT cleanup_old_sessions DISABLE;

-- Re-enable
ALTER EVENT cleanup_old_sessions ENABLE;

-- Change the schedule
ALTER EVENT cleanup_old_sessions
ON SCHEDULE EVERY 12 HOUR;

Dropping an Event

DROP EVENT IF EXISTS cleanup_old_sessions;

Real-World DBA Workflows

Scenario 1: Rolling Summary Tables

In one project, I maintained a daily_revenue_summary table to avoid running expensive aggregate queries on the raw orders table for dashboard rendering. An event handled the refresh:

CREATE EVENT refresh_daily_revenue
ON SCHEDULE EVERY 1 HOUR
DO
  REPLACE INTO daily_revenue_summary (report_date, total_revenue, order_count)
  SELECT DATE(created_at), SUM(total_amount), COUNT(*)
  FROM orders
  WHERE created_at >= CURDATE()
  GROUP BY DATE(created_at);

Scenario 2: Partition Maintenance

For time-partitioned tables, I’ve used events to automatically drop old partitions:

CREATE EVENT drop_old_partitions
ON SCHEDULE EVERY 1 WEEK
DO
  ALTER TABLE logs DROP PARTITION p_2025_01;

In practice, I generate this dynamically using a stored procedure with prepared statements, since partition names change over time.

Scenario 3: Token and Cache Expiry

CREATE EVENT purge_expired_tokens
ON SCHEDULE EVERY 10 MINUTE
DO
  DELETE FROM auth_tokens WHERE expires_at < NOW();

This keeps the auth_tokens table lean without requiring my application layer to handle cleanup logic.

Performance and Optimization Considerations

A few lessons I’ve learned the hard way:

1. Events run with the definer’s privileges. If the user who created the event is dropped or has revoked privileges, the event silently fails. I always use a dedicated maintenance account.
2. Long-running events can pile up. If an event takes longer than its interval to run, MySQL will simply start the next execution when the previous one is scheduled, potentially overlapping. I add safeguards using a status flag table for long jobs.
3. Indexing matters just as much for events as for queries. A DELETE inside an event still needs a proper index on the filtering column, or it’ll do a full table scan and lock more rows than necessary.
4. Batch large deletes. Deleting millions of rows in one event body can cause long transactions and replication lag. I batch using LIMIT:

CREATE EVENT batch_cleanup
ON SCHEDULE EVERY 5 MINUTE
DO
  DELETE FROM audit_log WHERE created_at < NOW() - INTERVAL 90 DAY LIMIT 5000;

5. Check event_scheduler after a server restart. Since the setting isn’t always persisted unless it’s in the config file, I always double check SHOW VARIABLES LIKE 'event_scheduler'; after maintenance windows.

Security Considerations

• Events execute with the SQL security context of their DEFINER. I avoid using root as the definer and instead create a scoped maintenance_user with only the privileges needed (e.g., DELETE, UPDATE on specific tables).
• The EVENT privilege itself should be granted sparingly:

GRANT EVENT ON my_database.* TO 'maintenance_user'@'localhost';

• I audit events regularly using INFORMATION_SCHEMA.EVENTS, since a forgotten event silently deleting data can be a nasty surprise months later.

Troubleshooting Common Issues

I check the error log (SHOW VARIABLES LIKE 'log_error';) whenever an event seems to be silently failing — MySQL logs event execution errors there.

Common Mistakes I See with MySQL Events

A few things worth watching for before relying on events in production:

1. Assuming the Event Scheduler is on by default. In most default installations it’s OFF, and I’ve seen teams write perfectly correct event definitions that simply never ran because nobody checked this setting after a fresh server build.
2. Not persisting the setting across restarts. Enabling it with SET GLOBAL event_scheduler = ON; only lasts until the next restart unless it’s also set in my.cnf. I always do both.
3. Writing an event with no error handling. If a statement inside the event body fails, the whole event execution can silently abort partway through, especially in a multi-statement BEGIN...END block. I add explicit logging (as shown in the maintenance example) so failures are visible somewhere other than the error log.
4. Ignoring overlapping executions on long-running events. If an event’s body regularly takes longer than its scheduled interval, executions can start piling up, competing for locks. I monitor LAST_EXECUTED and adjust the interval or add a “still running” guard flag.
5. Using an overly-privileged definer account. Creating events as root works, but it means the scheduled task always runs with full server privileges — I always create a scoped maintenance account instead, limiting the blast radius if something in the event body goes wrong.

Interview Questions on MySQL Events

1. What is the MySQL Event Scheduler, and how does it differ from a cron job? It’s an in-database thread that executes scheduled SQL tasks; unlike cron, it requires no external OS-level configuration and travels with the database schema.
2. How do you check if the Event Scheduler is enabled? SHOW VARIABLES LIKE 'event_scheduler';
3. What happens to a one-time event after it executes? It’s automatically dropped unless created with ON COMPLETION PRESERVE.
4. Under whose privileges does an event execute? The DEFINER‘s privileges at the time the event was created.
5. How would you troubleshoot an event that isn’t running? Check the scheduler status, verify the definer’s privileges, inspect INFORMATION_SCHEMA.EVENTS for status/errors, and check the MySQL error log.

Frequently Asked Questions

Q: Does the Event Scheduler work the same way in MySQL and MariaDB? A: The core syntax is nearly identical since MariaDB forked from MySQL, but always check version-specific documentation for edge cases in scheduling syntax.

Q: Can events call stored procedures? A: Yes — and I often prefer this pattern, since it keeps the event definition simple and puts the actual logic in a testable, reusable procedure.

Q: Do events work with replication? A: Events execute on the source (master) server, and their resulting data changes replicate normally to replicas. Replicas do not independently run the event’s schedule.

Q: What’s the maximum precision for event scheduling? A: MySQL supports intervals down to seconds (EVERY 30 SECOND), though I rarely go below a minute for production workloads.

Q: Can I see event execution history? A: MySQL doesn’t keep a built-in execution log beyond LAST_EXECUTED in INFORMATION_SCHEMA.EVENTS. For detailed history, I log manually into a table from within the event body, as shown in the maintenance example above.

Summary and Key Takeaways

MySQL Events give you a lightweight, portable way to automate recurring database maintenance directly inside the server — no external scheduler required. Over the years, I’ve relied on them for cleanup jobs, summary table refreshes, token expiry, and partition maintenance.

Key takeaways:

• Always confirm event_scheduler is ON, both at runtime and in your config file.
• Use a dedicated, least-privilege definer account for security.
• Batch large operations to avoid replication lag and long transactions.
• Monitor events through INFORMATION_SCHEMA.EVENTS and your error log.
• Reserve events for in-database logic; use external schedulers for cross-system orchestration.

References

• MySQL Reference Manual: Using the Event Scheduler
• MySQL Reference Manual: CREATE EVENT Syntax
• MySQL Reference Manual: Event Scheduler Status