How to Schedule an Event in MySQL – MySQL Event Scheduler

1 Flares 1 Flares ×

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
DO
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:

 

 ALTER myEvent
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:

 

SHOW 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.

Guru

+Ayodhyanath Guru holds a B.Tech degree in Electrical Engineering and has worked with various prestigious clients in the IT industry and presently working as a Software Engineer. He is a part time blogger and presently authors the Jafaloo.Com blog. Being a tech enthusiast Guru likes to surf the web and blogs about interesting technical topics like How-To guides, freewares, Tutorials, Software, Gadgets, web applications etc. Apart from blogging he likes coding in Java/J2EE and PHP.

You may also like...

1 Flares Twitter 0 Facebook 0 Google+ 1 LinkedIn 0 1 Flares ×