Creating scheduled events in MySQL

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;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s