MySQL provides scheduler called events, events in MySQL like trigger that do some specific tasks written in the definition but unlike trigger, events are not called on some action but at some specific time on interval of time.

MySQL event runs constantly and wait for their time to execute. Before creating any scheduler to be run you need to turn the scheduler ON so that MySQL server enables the clock for it to monitor its action of time. There is a simple command to turn event scheduler ON / OFF.

To ON the scheduler:

SET GLOBAL event_scheduler = ON;
OR
SET GLOBAL event_scheduler = 1;

SET GLOBAL event_scheduler = OFF;
OR
SET GLOBAL event_scheduler = 0;

You can see all scheduler status in the PROCESSLIST command. For example:

mysql> SHOW PROCESSLIST\G
 Id: 897
   User: event_scheduler
   Host: 127.0.0.1
db: NULL
Command: Daemon
   Time: 12
State: Waiting on empty queue
   Info: NULL

Event Creation

Event is created using a simple Create event command although it has various options to set its time, name and definition including queries. The event name can be of maximum 64 characters in length. Event schedules for a MySQL Timestamp or for specific time interval. The time interval can be of YEAR, MONTH, WEEK, DAY, HOUR, MINUTE or SECOND.

All events are stored in event table in the mysql database

Basic syntax of an event is:

CREATE EVENT `My_event`
 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK
 DO
Delete from table where date < now()+ 3 WEEK;

My_event is the name of an event and it scheduled for current time and 3 week after that. It deletes the data from table according to date condition.

Alter an Event

To Alter an event we can use simple alter command with the modified definition for the specified event.

Basic syntax to alter event is:

ALTER EVENT `My_event`
  ON SCHEDULE EVERY 1 MONTH
  DO
  DELETE FROM table WHERE id > 2000;

To drop an event use simple DROP command:

    DROP EVENT My_event;

Or use If exists to avoid error in case if the event not exists:

    DROP EVENT IF EXISTS My_event;

Show Event

To show the event you can use the following command:

     SHOW EVENTS from database Name;