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;
To OFF the scheduler:
SET GLOBAL event_scheduler = OFF; OR SET GLOBAL event_scheduler = 0;
You can check the value of event_scheduler in MySQL server as:
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
Note: Events can’t be altered, dropped or created by another event.
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;
Drop an Event
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;
To show the event you can use the following command:
SHOW EVENTS from database Name;