MySQL Event Scheduler

MySQL 5.1 Event Scheduler, Conceptually, this is similar to the idea of the UNIX crontab (also known as a “cron job”) or the Windows Task Scheduler.

What is “Event Scheduler” event is an object whose statements are executed in response to the passage of a specified time interval. An event performs a specific action according to a schedule. An event’s timing can be either on-time or recurrent. A one-time event executes one time only. A recurrent event repeats its action at a regular time interval.

Events are executed by a special event scheduler thread; when we refer to the Event Scheduler, we actually refer to this thread. When running, the event scheduler thread and its current state can be seen by users having the PROCESS privilege in the output of SHOW PROCESSLIST

* MySQL 5.1.12 and later, an event are uniquely identified by its name and the schema to which it is assigned.

Let’s see how to create Event in MySQL 5.1:

Step I: You need in Install MySQL 5.1

Download MySQL 5.1 from URL http://dev.mysql.com/downloads/mysql/5.1.html#winx64 For windows

Follow simple Installer step according to Installation.doc;

Step II: Create database in MySQL Server.

I did the following necessary steps To Run the Scheduler:

(i) Goto Start>All programs>MySQL>MySQL 5.1 server> MySQL command Line Client

(ii) mysql> SET GLOBAL EVENT_SCHEDULER=ON;

(iii ) Create event writing simple statement on MySQL Command Line
mysql > CREATE EVENT purge_Data
ON SCHEDULE AT ‘2008-07-20 23: 59:00’
BEGIN
INSERT INTO archive_news_article select *from news_article where pubdate <>

and then press enter, Above event scheduler execute on ‘2008-07-20 23: 59:00’ only one time and purge data from news_article table to archive_news_article table. Let me Explain the recurrent event scheduler with an Example Let’s apply on same schema if we want to purge starts from a particular Date Time in every month end .

(iii) mysql> SET GLOBAL EVENT_SCHEDULER=ON;

(iii ) Create event writing simple statement on MySQL Command Line

mysql > CREATE EVENT purge_everymonth
ON SCHEDULE EVERY 1 MONTHSTARTS '2008-07-29 23:00:00' + interval 0 minute
DO
BEGIN
INSERT INTO archive_news_article select *from news_article where pubdate <>
END ;

and then press enter, Above event scheduler execute star t on ‘2008-07-20 23: 59:00’ and purge data from news_article table to archive_news_article table and then it execute every 1 month. At the 23: 59:00’ .

When a scheduled event is no longer wanted or needed, it can be deleted from the server by its definer using the DROP EVENT statement. Again go to Mysql command line:

Mysql> Drop Event purge_everymonth;

Important
It is possible to set the Event Scheduler to DISABLED only at server startup. If event_scheduler is ON or OFF, you cannot set it to DISABLED at runtime. Also, if the Event Scheduler is set to DISABLED at startup, you cannot change the value of event_scheduler at runtime.

For more details please visit http://dev.mysql.com/doc/refman/5.1/en/events.html

Happy Reading....:-)