I just recently used Event scheduler which was the major feature in MySQL 5.1 version. Its very much similar to the linux crontab functionality. MySQL Event is nothing but a bunch of statements which we can run on specific schedule. When you will create any event, its just a database object like table, view or stored procedure.You can also say that its like a trigger but trigger will be fired/executed on specific action and event will be fired/executed on specific time.
Some major properties of MySQL Event Scheduler:
- An event is uniquely identified by its name and the schema to which it is assigned.
- An event’s timing will be either one time or recurrent. A one-time event will execute one time only. A recurrent event will repeat its action on a regular interval. Schedule for a recurring event will be assigned a specific start day, time, end day, time, both, or neither.
- Event properties can be change by “ALTER EVENT” or update the mysql.event table. Here also, DEFINER and INVOKER are comes in the picture. Only DEFINER can change the properties.
- One important thing is there. “A user may include statements in an event’s action which require privileges that the user does not actually have“. I’m bit confused over here that is it the feature or security breach?
For enabling this feature, you have to set global parameter named “event_scheduler”. There are 3 modes to set this variable. 0 for OFF, 1 for ON and 2 for SUSPENDED. When the event_scheduler will be set to 2, it will be in suspended mode so in this case thread will be running on the server but it will not be able to run any event. MySQL will also create a separate thread for event scheduler. So, when you’ll enable it you will get extra thread as a daemon process in processlist.
To run the simple statement you can create even like this.
CREATE EVENT event_hourly
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
COMMENT ‘Clears out sessions table each hour.’
DELETE FROM site_activity.sessions;
To run complex queries you can create event like this.
CREATE EVENT event_daily
ON SCHEDULE EVERY 1 DAY
COMMENT ‘Saves number of queries which are running each hour’
INSERT INTO site_activity.queries(time, total) SELECT CURRENT_TIMESTAMP, COUNT(*) FROM information_schema.processlist;
You can also run any procedure by creating event on specific time like this.
CREATE EVENT event_call_calc_expense
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 30 DAY
You can get more details about events over here.
Reference: MySQL 5.1 Documentation