Unknown to many, the MySQL instance has the ability to schedule events. This feature can be useful for scheduling activities like maintenance and batch processes. To get started the user in MySQL creating the event requires the privileges to create events.
1. The following will GRANT user jsmith the ability to create events on all DBs in MySQL.
GRANT event privileges on all DBs to user jsmith
2. GRANT event privileges to all tables in DB myschema.
GRANT EVENT ON myschema.* TO jsmith;
3. Enter the following code to create a scheduled event which runs every 1 hour starting at 2014-01-07 13:00:00.
CREATE EVENT evntTruncate -- event name ON SCHEDULE EVERY '1' HOUR -- run every 1 hour STARTS '2014-01-07 13:00:00' -- should be in the future DO TRUNCATE TABLE City; -- SQL statement to execute
4. Run the following query to verify that the event has been scheduled.
SELECT * FROM information_schema.events;