How to Schedule Jobs in MySQL

Galaxy Glossary

How do I schedule automated jobs directly in MySQL?

MySQL jobs are automated tasks created with CREATE EVENT that run on a defined schedule.

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

Description

Why use MySQL events instead of cron?

Events run inside MySQL, so they use the same connection pool, respect transactions, and stay in version-controlled SQL scripts. No external server access is required, keeping deployments portable.

How do I turn on the Event Scheduler?

Run SET GLOBAL event_scheduler = ON; (needs SUPER or SET_USER_ID privilege). To persist across restarts, add event_scheduler=ON in my.cnf.

What is the CREATE EVENT syntax?

Use CREATE EVENT with a schedule (AT, EVERY, or a combination). You can define a one-off or recurring job, its start time, interval, status, and body.

How do I schedule nightly cleanup of old orders?

Create an event that runs each night at 02:00 and deletes orders older than a year: see the example below for full code.

How can I modify or disable a job?

Use ALTER EVENT to change the schedule, body, or status. Disable with ALTER EVENT ... DISABLE; drop completely with DROP EVENT.

What are best practices?

Prefix event names with the schema (e.g., analytics.cleanup_old_orders), keep bodies idempotent, log rows affected, and test schedules on a staging database.

Common mistakes when scheduling jobs

See the list below for the two most frequent errors and how to fix them.

Why How to Schedule Jobs in MySQL is important

How to Schedule Jobs in MySQL Example Usage


CREATE EVENT IF NOT EXISTS nightly_stock_adjustment
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 HOUR
DO
  UPDATE Products p
  JOIN (
      SELECT product_id, SUM(quantity) qty
      FROM OrderItems oi
      JOIN Orders o ON o.id = oi.order_id
      WHERE o.order_date = CURDATE()
      GROUP BY product_id
  ) s ON s.product_id = p.id
  SET p.stock = p.stock - s.qty;

How to Schedule Jobs in MySQL Syntax


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

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

-- Example (ecommerce):
CREATE EVENT IF NOT EXISTS cleanup_old_orders
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00'
DO
  DELETE FROM Orders
  WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

Common Mistakes

Forgetting to enable the global event scheduler. The event will compile, but never fire. Fix by running SET GLOBAL event_scheduler = ON or editing my.cnf.
Using NOW() inside event bodies for date math. The timestamp is evaluated at compile time, not run time. Use CURDATE() or CURRENT_TIMESTAMP inside the DO block instead.

Frequently Asked Questions (FAQs)

Does CREATE EVENT affect replication?

Yes. Events are written to the binary log and executed on replicas if log_bin is enabled and the replica’s event scheduler is on.

Can I schedule a job to run every minute?

Absolutely. Use ON SCHEDULE EVERY 1 MINUTE. Keep the body lightweight to avoid overlapping executions.

How do I see all defined events?

Query INFORMATION_SCHEMA.EVENTS or run SHOW EVENTS FROM db_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