Getting Started With Mysql Event Scheduler


Introduction

Hi guys, my last article I shown you “How to generate database oriented Form using Cygnite simple command”. Today I would like to write about MySql Event Scheduler. MySql Event Scheduler very useful concept if you are dealing with large scale application like email system, online shopping, or may be activity history management application. Where every day thousands of data getting stored into the database which create a heavy load into your database and slow down your application. You may no longer required existing activity history or want to archive data, this is where Mysql Event scheduler come to play. Event useful to create backup, delete unnecessary rows from database so on. Let me show you how to start with Mysql scheduler.

Creating a Table:

Let us create a sample table activity_history to store each and every activity of user in the application. You may compare with similar stuffs at facebook where each and every user activity displays at right top corner.


CREATE TABLE activity_history 
(
id INT(11) AUTO_INCREMENT,
user_id INT(11),,
description INT(11),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);


Enabling Event Scheduler:

Before creating or scheduling event you need enable event by executing below command.


SET GLOBAL event_scheduler = ON;
              or
SET GLOBAL event_scheduler = 1;


Similarly you can also turn off the event as below.



SET GLOBAL event_scheduler = OFF;


Confirming Event

After enabling you may confirm your event by



SHOW PROCESSLISTG


Creating Event:



CREATE EVENT delete_activity
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM activity_history WHERE created_at <= DATE_SUB(NOW(), INTERVAL 15 DAY) ;


Above event will run every day and delete 15 days old activity history from the table.

Altering Event:

Suppose you want to alter the old event you can do as below.



ALTER EVENT delete_activity
    ON SCHEDULE EVERY 1 MONTH
    STARTS '2014-07-01 01:00:00'


If you would like to rename Event



ALTER EVENT delete_activity RENAME TO delete_activity_event;


Displaying Mysql Events:

Below command will display all the events available into your database.



SHOW EVENTS;


Deleting Event:

You may delete unnecessary event by executing.



DROP EVENT delete_activity_event;


Conclusion:

Hope this article useful. If so please don’t forget to give a small like or share with friends, also leave your comments below.

Have a nice day. Keep visiting for latest posts.

Follow Us On Facebook Open Source Web Developers by Appsntech facebook group Twitter Open Source Web Developers by Appsntech twitter group Google+ Open Source Web Developers by Appsntech Google group Linkedin Open Source Web Developers by Appsntech, LinkedIn group
Copyright @2011-2015 appsntech.com. All rights reserved. Powered By- Sanjoy Dey Productions