Event Scheduler in Mysql


Mysql Event scheduler is used to schedule events which can be triggered periodically or only 1 time. It is supported in version MySQL 5.1.6 and above. It is similar to Cron jobs in UNIX which run periodically to perform operations which need to be done daily,monthly etc.

By default, Event scheduler is off. You have to start it explicitly. To do so, you can run this query.

---------------------------------------------------------------------------------------------
SET GLOBAL event_scheduler = ON;
---------------------------------------------------------------------------------------------



To turn off, event scheduler in mysql,

---------------------------------------------------------------------------------------------
SET GLOBAL event_scheduler = OFF;
---------------------------------------------------------------------------------------------

This event scheduling can be used reporting functionality. You can generate reports on hourly,monthly,daily basis.

Create Event To Trigger Once

---------------------------------------------------------------------------------------------
DELIMITER $$
CREATE
EVENT `change_username5`
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO BEGIN
select * from user INTO OUTFILE "C:\\NewF\\nev.xls";
END;
$$
---------------------------------------------------------------------------------------------

So, above event triggers after 1 minute from system time and store the data of user table into excel file.

Now, different time stamps you can specify,

(1) CURRENT_TIMESTAMP
(2) CURRENT_TIMESTAMP + n [HOUR|MONTH|WEEK|DAY|MINUTE]

Create Event To Trigger Periodically

This event triggers at every hour everyday.

---------------------------------------------------------------------------------------------
DELIMITER $$
CREATE
EVENT `change_username5`
ON SCHEDULE EVERY 1 HOUR
DO BEGIN
select * from user INTO OUTFILE "C:\\NewF\\nev.xls";
END;
$$
---------------------------------------------------------------------------------------------

but suppose if you want to run the trigger every hour but only for few days then, below will help,

This will run every hour but for only 5 days.

---------------------------------------------------------------------------------------------
DELIMITER $$
CREATE
EVENT `change_username`
ON SCHEDULE EVERY 1 HOUR
Starts CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO BEGIN
select * from user INTO OUTFILE "C:\\NewF\\nev.xls";
END;
$$
---------------------------------------------------------------------------------------------

Check All Events

To check all the events, created you can fire below query,

---------------------------------------------------------------------------------------------
show events;
---------------------------------------------------------------------------------------------




ALTER EVENTS

You can also alter the name of event or its timings.

(1) Alter Event name

---------------------------------------------------------------------------------------------
ALTER EVENT oldeventname RENAME TO neweventname;
---------------------------------------------------------------------------------------------

(2) Alter triggering timings

---------------------------------------------------------------------------------------------
DELIMITER $$
ALTER
EVENT `change_username5`
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO BEGIN
select * from user INTO OUTFILE "C:\\NewF\\new.xls";
END;
$$
---------------------------------------------------------------------------------------------

(3) Alter queries inside events
    
---------------------------------------------------------------------------------------------
DELIMITER $$
ALTER
EVENT `change_username5`
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO BEGIN
update user set username='nev' where user_id = 100;
END;
$$
---------------------------------------------------------------------------------------------

DELETE EVENTS

---------------------------------------------------------------------------------------------
Drop event  change_username5;
---------------------------------------------------------------------------------------------

Share on Google Plus

About Nevil Patel (Admin)

Nevil Patel is the founder of Techno Nutty, Blogger and Tech enthusistic.
    Blogger Comment
    Facebook Comment

2 comments :

  1. hi navil first of all thanks for your wonderful artical i have a question and that is is there any way to excute the mysql event

    ReplyDelete
    Replies
    1. You just have to create event and set time as mentioned in article. It will be executed automatically as per timings.

      Delete