MySQL Event Scheduler for Automated Tasks

The MySQL Event Scheduler is a built-in task scheduler that executes SQL statements or stored procedures at predetermined intervals. Think of it as a database-level cron — ideal for maintenance tasks, data aggregation, cleanup jobs, and partition management without external dependencies.

Enabling the Event Scheduler

-- Check if the scheduler is running
SHOW VARIABLES LIKE 'event_scheduler';

-- Enable it (survives restart if set in config)
SET GLOBAL event_scheduler = ON;

-- Make permanent in my.cnf
-- [mysqld]
-- event_scheduler = ON

Creating Events

One-Time Event

CREATE EVENT cleanup_expired_sessions
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
    DELETE FROM sessions WHERE expires_at < NOW();

Recurring Event

-- Run every day at midnight
CREATE EVENT daily_stats_rollup
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY
COMMENT 'Aggregate daily statistics'
DO
BEGIN
    INSERT INTO daily_stats (stat_date, total_orders, total_revenue)
    SELECT
        CURDATE() - INTERVAL 1 DAY,
        COUNT(*),
        SUM(total)
    FROM orders
    WHERE DATE(created_at) = CURDATE() - INTERVAL 1 DAY;

    -- Clean old detailed records
    DELETE FROM order_events
    WHERE created_at < NOW() - INTERVAL 90 DAY;
END;

Complex Multi-Statement Event

DELIMITER //
CREATE EVENT weekly_maintenance
ON SCHEDULE EVERY 1 WEEK
STARTS '2025-01-06 03:00:00'  -- Next Monday at 3 AM
COMMENT 'Weekly database maintenance'
DO
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl VARCHAR(255);
    DECLARE cur CURSOR FOR
        SELECT table_name FROM information_schema.tables
        WHERE table_schema = DATABASE() AND engine = 'InnoDB';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Optimize all InnoDB tables
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO tbl;
        IF done THEN LEAVE read_loop; END IF;
        SET @sql = CONCAT('OPTIMIZE TABLE `', tbl, '`');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;

    -- Update table statistics
    ANALYZE TABLE orders, users, products;
END//
DELIMITER ;

Managing Events

-- List all events
SHOW EVENTS;

-- Show event details
SHOW CREATE EVENT daily_stats_rollup;

-- Alter an event schedule
ALTER EVENT daily_stats_rollup
ON SCHEDULE EVERY 6 HOUR;

-- Temporarily disable an event
ALTER EVENT daily_stats_rollup DISABLE;

-- Re-enable
ALTER EVENT daily_stats_rollup ENABLE;

-- Drop an event
DROP EVENT IF EXISTS daily_stats_rollup;

-- View event execution history (MySQL 8.0+)
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE '%event%';

Practical Use Cases

Partition Management

DELIMITER //
CREATE EVENT manage_partitions
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY
DO
BEGIN
    -- Create next month partition
    SET @next_month = DATE_FORMAT(NOW() + INTERVAL 1 MONTH, '%Y%m');
    SET @partition_name = CONCAT('p', @next_month);
    SET @partition_value = DATE_FORMAT(NOW() + INTERVAL 2 MONTH, '%Y-%m-01');

    SET @sql = CONCAT('ALTER TABLE events ADD PARTITION (PARTITION ',
        @partition_name, ' VALUES LESS THAN (\'', @partition_value, '\'))');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Drop partitions older than 1 year
    SET @old_month = DATE_FORMAT(NOW() - INTERVAL 12 MONTH, '%Y%m');
    SET @old_partition = CONCAT('p', @old_month);

    SET @drop_sql = CONCAT('ALTER TABLE events DROP PARTITION ', @old_partition);
    PREPARE stmt2 FROM @drop_sql;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;
END//
DELIMITER ;

Data Archival

CREATE EVENT archive_old_logs
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 2 HOUR
DO
BEGIN
    -- Move old logs to archive table
    INSERT INTO logs_archive
    SELECT * FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;

    -- Delete archived records
    DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY LIMIT 100000;
END;

Cache Refresh

CREATE EVENT refresh_materialized_view
ON SCHEDULE EVERY 15 MINUTE
DO
BEGIN
    TRUNCATE TABLE mv_product_stats;
    INSERT INTO mv_product_stats
    SELECT
        p.id, p.name,
        COUNT(oi.id) AS total_sold,
        SUM(oi.quantity * oi.price) AS total_revenue,
        AVG(r.rating) AS avg_rating,
        NOW() AS refreshed_at
    FROM products p
    LEFT JOIN order_items oi ON oi.product_id = p.id
    LEFT JOIN reviews r ON r.product_id = p.id
    GROUP BY p.id, p.name;
END;

Error Handling

DELIMITER //
CREATE EVENT safe_cleanup
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        INSERT INTO event_errors (event_name, error_time, error_message)
        VALUES ('safe_cleanup', NOW(), 'Cleanup failed');
    END;

    DELETE FROM temp_data WHERE expires_at < NOW();
    DELETE FROM rate_limits WHERE window_start < NOW() - INTERVAL 1 DAY;
END//
DELIMITER ;

Best Practices

• Always add COMMENT to events describing their purpose
• Use STARTS to control exactly when recurring events begin
• Add error handlers to prevent silent failures
• Use LIMIT in DELETE statements within events to avoid long-running transactions
• Monitor event execution through the Performance Schema
• Test events manually before scheduling them by running the event body as a standalone query
• Keep event execution time short — long-running events can block other scheduled events
• Use events for database-centric tasks; use system cron for tasks involving external systems