How to Schedule Queries in MySQL

Galaxy Glossary

How do I schedule recurring SQL queries inside MySQL without cron?

CREATE EVENT lets MySQL run SQL statements automatically on a defined schedule, eliminating external cron jobs.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

How do I enable the MySQL Event Scheduler?

Run SET GLOBAL event_scheduler = ON; as a privileged user or start the server with --event-scheduler=ON. Persist the setting in my.cnf for reboots. Verify with SELECT @@event_scheduler;.

What permissions are required?

Grant the EVENT privilege: GRANT EVENT ON *.* TO 'analyst'@'%';. Without it, CREATE EVENT and ALTER EVENT fail.

What is the basic syntax for CREATE EVENT?

CREATE EVENT [IF NOT EXISTS] name ON SCHEDULE schedule DO statement;. The schedule can be AT a timestamp or EVERY interval, with optional STARTS / ENDS.

How can I schedule a daily order-revenue summary?

The event below inserts yesterday’s revenue into order_summaries every night at 00:05.

CREATE EVENT IF NOT EXISTS ev_daily_revenue ON SCHEDULE EVERY 1 DAY STARTS CURRENT_DATE + INTERVAL 5 MINUTE DO INSERT INTO order_summaries(report_date,total_revenue) SELECT CURRENT_DATE - INTERVAL 1 DAY, SUM(total_amount) FROM Orders WHERE order_date = CURRENT_DATE - INTERVAL 1 DAY;

How do I modify or disable an event?

Change the frequency: ALTER EVENT ev_daily_revenue ON SCHEDULE EVERY 1 HOUR;. Pause execution: ALTER EVENT ev_daily_revenue DISABLE;. Remove permanently with DROP EVENT ev_daily_revenue;.

What are best practices?

Name events descriptively, wrap complex logic in stored procedures, store definitions in version control, log results to a table, and test schedules in staging first.

Why How to Schedule Queries in MySQL is important

How to Schedule Queries in MySQL Example Usage


-- Nightly snapshot of new customers
CREATE EVENT ev_new_customers
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 10 MINUTE
DO
INSERT INTO customer_audit(id,name,email,snapshot_date)
SELECT id,name,email,CURRENT_DATE
FROM   Customers
WHERE  created_at >= CURRENT_DATE - INTERVAL 1 DAY;

How to Schedule Queries in MySQL Syntax


CREATE EVENT [IF NOT EXISTS] event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'text']
    DO sql_statement;

schedule:
    AT timestamp [+ INTERVAL interval]
  | EVERY interval
      [STARTS timestamp [+ INTERVAL interval]]
      [ENDS   timestamp [+ INTERVAL interval]]

interval:
    quantity {YEAR|QUARTER|MONTH|DAY|HOUR|MINUTE|SECOND}

Example (ecommerce):
CREATE EVENT ev_low_stock_alert
ON SCHEDULE EVERY 4 HOUR
DO
UPDATE Products
SET    stock_warning_sent = 1
WHERE  stock < 10 AND stock_warning_sent = 0;

Common Mistakes

Mistake: Forgetting to enable <code>event_scheduler</code>. Without it, events never run. Fix: <code>SET GLOBAL event_scheduler = ON;</code> and add <code>event_scheduler=ON</code> to <code>my.cnf</code>.
Mistake: Using server time zone assumptions. Events run in the server’s time zone, not session time zone. Fix: Set <code>default_time_zone</code> at startup or specify absolute UTC timestamps in schedules.

Frequently Asked Questions (FAQs)

FAQ

Does CREATE EVENT affect replication?

Events are written to the binary log, so they replicate by default. Use ALTER EVENT ... DISABLE ON SLAVE to stop execution on replicas.

Can I schedule multiple statements?

Yes. Place the logic in a stored procedure and call that procedure from DO CALL proc_name();.

How do I see all scheduled events?

Query information_schema.EVENTS or run SHOW EVENTS FROM database_name;.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie