How to Create Scheduled Events in MySQL
Oct 062016
you can start the scheduler from the MySQL command line:
SET GLOBAL event_scheduler = ON;
Creating an Event
We require a scheduled event which:
Copies posts from `blog` to `blog_archive` when the deleted flag is set to 1.
Copies the associated audit entries for those posts from `audit` to `audit_archive`.
Physically deletes archived posts from the `blog` table. Referential integrity has been defined with a foreign key so all associated audit entries for those posts will also be removed.
Assuming you have MySQL rights to create events, the basic syntax is:
CREATE EVENT `event_name`
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
DO BEGIN
-- event body
END;
The schedule can be assigned various settings, e.g.
Run once on a specific date/time:
AT ‘YYYY-MM-DD HH:MM.SS’
e.g. AT ‘2011-06-01 02:00.00’
Run once after a specific period has elapsed:
AT CURRENT_TIMESTAMP + INTERVAL n [HOUR|MONTH|WEEK|DAY|MINUTE]
e.g. AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
Run at specific intervals forever:
EVERY n [HOUR|MONTH|WEEK|DAY|MINUTE]
e.g. EVERY 1 DAY
Run at specific intervals during a specific period:
EVERY n [HOUR|MONTH|WEEK|DAY|MINUTE] STARTS date ENDS date
e.g. EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK ENDS ‘2012-01-01 00:00.00’
An event is normally dropped once its schedule has expired (ON COMPLETION NOT PRESERVE). Set ON COMPLETION PRESERVE to prevent that behavior. The MySQL CREATE EVENT Syntax documentation provides further details.
We can now define our event (remembering to set the DELIMITER first). We’ll set it to run every week starting on a Sunday morning:
DELIMITER $$
CREATE
EVENT `archive_blogs`
ON SCHEDULE EVERY 1 WEEK STARTS '2011-07-24 03:00:00'
DO BEGIN
-- copy deleted posts
INSERT INTO blog_archive (id, title, content)
SELECT id, title, content
FROM blog
WHERE deleted = 1;
-- copy associated audit records
INSERT INTO audit_archive (id, blog_id, changetype, changetime)
SELECT audit.id, audit.blog_id, audit.changetype, audit.changetime
FROM audit
JOIN blog ON audit.blog_id = blog.id
WHERE blog.deleted = 1;
-- remove deleted blogs and audit entries
DELETE FROM blog WHERE deleted = 1;
END */$$
DELIMITER ;
https://www.sitepoint.com/how-to-create-mysql-events/