The event scheduler is another feature added in MySQL5.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.
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.
The Code is as follows:
(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 //
The Code is as follows:
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.
The Code is as follows:
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:
The Code is as follows:
(Root: localhost :) test> alter event 'slave _ monitor' ON
Completion preserve disable;
(Root: localhost :) test> alter event 'slave _ monitor' ON
Completion preserve enable;
