MySQL Events or scheduled events are tasks that execute according to a specified schedule. They are stored in the database and executed at one or more intervals.

For example, you can create an event that optimizes a table by backfilling data from another table the database that runs at 1:00 AM every day.

MySQL Events are also known as “temporal triggers” because they are triggered by time, not by DML events like normal triggers. MySQL events are similar to a cronjob on Linux or a task scheduler on Windows. MySQL Event Scheduler manages the schedule and execution of Events. MySQL Events can be very useful in many cases such as optimizing database tables, cleaning up logs, archiving data, or generating complex reports during off-peak time.

Run SHOW PROCESSLIST to make sure that event_scheduler User shows up as an indication that you can create an event.

SHOW PROCESSLIST;

Show event details

You can use show events query to show details of events. You can also use the INFORMATION_SCHEMA.EVENTS table to do the same.

SELECT * 
FROM INFORMATION_SCHEMA.EVENTS 
WHERE EVENT_SCHEMA = 'database_name';

SELECT 
      EVENT_SCHEMA, EVENT_NAME, CREATED, LAST_EXECUTED, DEFINER, TIME_ZONE, EVENT_DEFINITION,
      EVENT_TYPE, INTERVAL_VALUE, INTERVAL_FIELD, STARTS, ENDS, STATUS, ON_COMPLETION, EVENT_COMMENT
FROM INFORMATION_SCHEMA.EVENTS 
WHERE EVENT_SCHEMA = 'database_name';

SHOW EVENTS FROM database_name;

Turn on and off the event scheduler

-- turn on event_scheduler
SET GLOBAL event_scheduler = ON;

-- turn off event_scheduler
SET GLOBAL event_scheduler = OFF;

Create an event

CREATE EVENT  requires the EVENT privilege for the schema in which the event is to be created. If the DEFINER clause is present, the privileges required depend on the user value. An event is associated with a schema. If no schema is indicated as part of event_name, the default (current) schema is assumed.

// This event executes once—one hour following its creation
CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.sometable SET somecol = somecol + 1;

Recurring event with end time

With ENDS the recurring event will stop its execution at that time.

CREATE EVENT IF NOT EXISTS E_MAX_FIRE
ON 
   SCHEDULE EVERY 1 MINUTE
   STARTS CURRENT_TIMESTAMP
   ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
   INSERT INTO activity(event_name, event_fired_at)
   VALUES('max-event',NOW());

Recurring event with stored procedure

Note that with no ENDS clause the recurring event will continue to execute at EVERY 1 MINUTE forever.

delimiter |

CREATE EVENT E_MIN_FIRE
    ON SCHEDULE
      EVERY 1 MINUTE
      STARTS CURRENT_TIMESTAMP
    COMMENT 'Saves total number of sessions then clears the table each day'
    DO
      BEGIN
        INSERT INTO activity(event_name, event_fired_at)
          VALUES('min-event',NOW());
      END |

delimiter ;