MySQL event Scheduler)

The event scheduler is another feature added in MySQL 5.1. It can be used as a scheduled task scheduler to replace some timer functions that can only be completed by the operating system task scheduler.

　　I. Overview

The event scheduler is another feature added in MySQL 5.1. It can be used as a scheduled task scheduler to replace some timer tasks that can only be completed by the operating system task scheduler>. For example, crontabe in Linux can be executed only once per minute, while the MySQL event scheduler can execute a task every second, this is very practical in some environments that require higher real-time performance.

The event scheduler is scheduled to trigger execution, which can also be called a "temporary trigger ". The trigger only executes some statements for the events generated by a table, while the event scheduler executes some statements at a certain interval. Events are managed by a specific thread, that is, the so-called "event scheduler ". After the event scheduler is enabled, an account with super permissions can execute show processlist to view the thread. You can dynamically control whether the event scheduler is enabled by setting the global variable event_scheduler.

 

(Root: localhost :) test> set global event_scheduler = on; (Root: localhost :) test> show processlist \ G * *************************** 4. row *************************** ID: 46147 User: event_scheduler HOST: localhost DB: NULL Command: Daemon Time: 1 State: waiting on empty queue Info: NULL

As shown above, the thread owner is event_scheduler.

　　II. Application Cases

This case uses the feature of event scheduler to call a stored procedure every second to determine whether the slave is running normally. If the slave is disabled, ignore the zero error and restart the slave.

First, create a stored procedure

 

Delimiter // Create procedure 'slave _ monitor '() Begin Select variable_value into @ slave_status From information_schema.global_status Where variable_name = 'slave _ running '; If ('on '! = @ Slave_status) then Set global SQL _slave_skip_counter = 0; Slave start; End if; End ;// Delimiter;

Because the statement like show slave status cannot be called in the stored procedure, the exact copy error message and Error Code cannot be obtained, and the various conditions of slave stop cannot be further processed.

Next, create a task.

 

Create event if not exists 'slave _ monitor' On schedule every 5 second On completion preserve Do Call slave_monitor ();

A task is created and executed every five seconds. After the task is completed, the task is retained instead of deleted. Of course, the task in this example will not end unless it is manually disabled.

If you want to temporarily close a task during running, execute the alter event statement:

 

(Root: localhost :) test> alter event 'slave _ monitor' on Completion preserve disable; (Root: localhost :) test> alter event 'slave _ monitor' on Completion preserve enable;