MySQL Event Scheduler: Automated Database Maintenance for WordPress

MySQL’s built-in Event Scheduler lets you run SQL statements on a schedule directly inside the database — useful for purging old WordPress transients, archiving log tables, or aggregating statistics without a cron job on the OS.

Problem: WordPress relies on WP-Cron for recurring maintenance tasks — deleting expired transients, cleaning up revisions — but WP-Cron only fires on page load and stops working entirely on low-traffic sites.

Solution: Use the MySQL Event Scheduler as a server-level alternative for purely database-focused maintenance. Enable it with SET GLOBAL event_scheduler = ON, then create a recurring event with CREATE EVENT that runs a DELETE or OPTIMIZE TABLE statement on a schedule, independent of web traffic.

The examples below enable the Event Scheduler, create a recurring event that cleans expired transients from a WordPress database, and show how to inspect, alter, and drop events.

-- 1. Enable the scheduler (must be run as a user with SUPER or SYSTEM_VARIABLES_ADMIN)
SET GLOBAL event_scheduler = ON;

-- Persist across restarts: add to my.cnf / my.ini
-- [mysqld]
-- event_scheduler = ON

-- 2. Create a recurring event: purge expired transients every hour
DELIMITER $$

CREATE EVENT IF NOT EXISTS purge_expired_transients
ON SCHEDULE EVERY 1 HOUR
STARTS NOW()
DO
BEGIN
    DELETE FROM wp_options
    WHERE option_name LIKE '\_transient\_%'
      AND option_name NOT LIKE '\_transient\_timeout\_%';

    DELETE o FROM wp_options AS o
    INNER JOIN wp_options AS t
        ON t.option_name = CONCAT('_transient_timeout_', SUBSTRING(o.option_name, 12))
    WHERE o.option_name LIKE '\_transient\_%'
      AND t.option_value < UNIX_TIMESTAMP();
END$$

DELIMITER ;

Inspect and manage events:

-- List all events in the current database
SELECT event_name, status, interval_value, interval_field, last_executed, next_execution
FROM information_schema.EVENTS
WHERE event_schema = DATABASE();

-- Disable an event temporarily
ALTER EVENT purge_expired_transients DISABLE;

-- Re-enable
ALTER EVENT purge_expired_transients ENABLE;

-- Change schedule to run every 6 hours
ALTER EVENT purge_expired_transients
ON SCHEDULE EVERY 6 HOUR;

-- Run once at a specific time then auto-drop
CREATE EVENT one_time_recount
ON SCHEDULE AT '2024-05-01 03:00:00'
ON COMPLETION NOT PRESERVE
DO UPDATE wp_term_taxonomy tt
   SET count = (SELECT COUNT(*) FROM wp_term_relationships tr WHERE tr.term_taxonomy_id = tt.term_taxonomy_id);

-- Drop permanently
DROP EVENT IF EXISTS purge_expired_transients;

NOTE: The user that owns the event needs the EVENT privilege on the database. Events run with the privileges of their definer, so use a dedicated low-privilege MySQL user for maintenance tasks rather than root.