How to Schedule an Event in MySQL – MySQL Event Scheduler
There are instances when you need to perform some database related tasks on schedule basis and to perform so you might be running a shell script using cron job or you might be running it manually. But do you know about MySQL Event Scheduler? Using MySQL event scheduler you can schedule any event/SQL command at a particular interval of time. So lets see how to schedule any event on MySQL database.
How to Schedule Event In MySQL (MySQL Event Scheduler)
Before scheduling any event you need to enable the MySQL event scheduler. Execute the below command in MySQL prompt to enable event scheduler.
Enabling the Event
set GLOBAL event_scheduler=ON; or set GLOBAL event_scheduler=1;
Creating the Event:
Now select the database for which you want to create the event. Now let’s say you have a table called example where one column is there called ‘last_modified’ (datetime). Now you want to schedule an event everyday where it will delete all data from the example table where last_modified is greater than equal to 20 days. So below is the command you need to execute in MySQL prompt:
CREATE EVENT myEvent
ON SCHEDULE EVERY 1 DAY
DELETE FROM example WHERE
last_modified >= DATE_SUB(NOW(), INTERVAL 20 DAY);
Modifying /Alter Event
If you want to modify the event run time simply execute the query using below syntax:
ON SCHEDULE EVERY 2 HOUR
STARTS TIMESTAMP + 5 HOUR;
The same way you can alter the do part as well.
Viewing All Events
You can list out all events by executing the below command, but before that select the database for which you want to list all the events:
Delete/Drop the Event
To delete or drop the MySQL event you can execute the query in below format:
drop event myEvent;
So this is how you can create and schedule an event in MySQL. Hope this tutorial was helpful for you, for any queries please leave your comments.