Schedule tasks in mysql using Event Scheduler

Event Scheduler in MySQL is like a task scheduler which performs database related tasks in MySQL. Event scheduler is available in MySQL version > MySQL 5.1.12.
To use event scheduler, first it should be set ON for your database server either in my.ini or my.cnf file. Even you can enable it on run time by using either of following:

SET GLOBAL event_scheduler = ON;

SET @@global.event_scheduler = ON;

SET GLOBAL event_scheduler = 1;

SET @@global.event_scheduler = 1;

A general syntax is as follows:

DELIMITER $$

-- SET GLOBAL event_scheduler = ON$$     -- required for event to execute but not create    

CREATE /*[DEFINER = { user | CURRENT_USER }]*/ EVENT `<<event_name>>`

ON SCHEDULE
/* uncomment the example below you want to use */

--  run once

   --  AT 'YYYY-MM-DD HH:MM.SS'/CURRENT_TIMESTAMP { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] }

--  run at intervals forever after creation

   -- EVERY 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...]

-- specified start time, end time and interval for execution
   /*EVERY 1  [HOUR|MONTH|WEEK|DAY|MINUTE|...]

   STARTS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1[HOUR|MONTH|WEEK|DAY|MINUTE|...] }

   ENDS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] } */

DO
BEGIN
    COMMENT 'any sql_statements'
END$$

DELIMITER ;


An example of Event:


SQL>CREATE EVENT e_hourly
    ON SCHEDULE
      EVERY 1 HOUR
    COMMENT 'Clears out sessions table each hour.'
    DO
      DELETE FROM site_activity.sessions;
 
 
 
To check any running event or details of event, following query will be used:


mysql> SHOW EVENTS\G

Some more examples of Events:

CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'will run every day'
    DO
      BEGIN
        INSERT INTO site_activity.totals (time, total)
          SELECT CURRENT_TIMESTAMP, COUNT(*)
            FROM site_activity.sessions;
        DELETE FROM site_activity.sessions;
      END |

delimiter ;