How to Set a Timer in MySQL

This guide covers how to automate SQL commands in MySQL. You’ll usually want to do this to schedule routine tasks like data backups, cleanup and reporting during off-peak hours.

What are MySQL Events?

MySQL's event scheduler is a built-in feature for executing tasks at scheduled times. It's similar to cron jobs in Unix/Linux systems.

Enabling the Event Scheduler

Before creating events, ensure the event scheduler is enabled:

SET GLOBAL event_scheduler = ON;

Alternatively, check its status:

SHOW VARIABLES LIKE 'event_scheduler';

How to Create a Basic Event in MySQL

You can create an event with the following syntax:

CREATE EVENT my_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
  UPDATE my_table SET my_column = my_value;

This event updates my_table one hour from the current time.

Advanced Scheduling

For more complex scheduling, you can use a couple clauses in the ON SCHEDULE section.

Repeating Events

To repeat an event, use the EVERY clause:

CREATE EVENT my_repeating_event
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
  INSERT INTO my_table (my_column) VALUES (my_value);

This will insert a new record into my_table every day.

Specifying Start and End Times

Control when an event starts and optionally when it ends:

CREATE EVENT my_timed_event
ON SCHEDULE EVERY 1 WEEK
STARTS '2023-01-01 00:00:00'
ENDS '2023-12-31 23:59:59'
DO
  CALL my_procedure();

This calls my_procedure weekly throughout 2023.

Managing Events

Viewing Existing Events

List all events:

SHOW EVENTS;

Altering Events

Modify an event's definition:

ALTER EVENT my_event
ON SCHEDULE EVERY 2 DAYS;

Deleting Events

Remove an event when it's no longer needed:

DROP EVENT IF EXISTS my_event;

Integrating with Basedash

Basedash is a solid way to streamline your MySQL workflows. Basedash gives you a fully-functional admin interface for your MySQL database in just a couple clicks. You can edit data, share SQL queries with your team and build collaborative dashboards.