Home > Uncategorized > Scheduled tasks aka EVENTs in Mysql server

Scheduled tasks aka EVENTs in Mysql server

The bad:
Since a scheduled event is executed under the context for which the event-scheduler thread is running, a user can create a scheduled event that is out of the user’s context.
However, a user must have been GRANTed the EVENT privilege in order to create or modify a scheduled event.
So, review your GRANTs.

Start the event scheduler thread:
Starting in version 5.1.6 of mysql server scheduled events are featured and secure.
You can configure the event-scheduler thread to run (see `show processlist`) by modifying the `/etc/my.cnf` file as follows:

[mysqld]
event_scheduler=ON

Restart mysqld:

service mysqld restart

Example syntax:
This is an example of CREATE EVENT syntax that is related to flow-inspector.

This event runs once a day, starting today, at midnight, deleting some records that are older than one month.

use flowinspector;
DELIMITER |
CREATE EVENT `flowinspector_dump`
ON SCHEDULE
EVERY 1 DAY
STARTS date_format(now(), '%Y-%m-%d 00:00:00')
ON COMPLETION NOT PRESERVE
ENABLE
DO BEGIN
set @a=unix_timestamp(date_sub(now(),interval 1 month));
delete from flowinspector.flows_600 where bucket < @a;
delete from flowinspector.flows_aggr_600 where bucket < @a;
delete from flowinspector.index_nodes_600 where bucket < @a;
delete from flowinspector.index_ports_600 where bucket < @a;
END;
|
DELIMITER ;
Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: