I. Overview
The event Scheduler is another feature added in MySQL 5.1 that can be used as a timer Task Scheduler to replace some of the timed work > energy that can only be done with the operating system Task Scheduler. For example, the Crontabe in Linux can only be executed once per minute, while MySQL's event scheduler implements one task per second, which is useful in situations where the real time is > high.
The event scheduler is executed at timed intervals, which can also be referred to as "temporary triggers." Triggers execute statements only for events that are generated by a table, whereas the event scheduler executes some statements at one (interval) time. Events are managed by a specific thread, known as the event scheduler. When the event scheduler is enabled, an account with SUPER permissions executes show processlist to see the thread. You can dynamically control whether the event scheduler is enabled by setting the value of the global variable Event_scheduler.
Copy Code code 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 above, the owner of the thread is Event_scheduler.
second, the application case
This case uses the Event Scheduler feature to invoke a stored procedure every second to determine if the SLAVE is running properly, to ignore the 0 errors, and then restart SLAVE if the SLAVE is found to be turned off.
Create a stored procedure first
Delimiter//
Copy Code code 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 a statement such as show SLAVE STATUS cannot be invoked in a stored procedure, the exact copy error message and error code cannot be obtained, and no further processing of SLAVE stops is possible.
Next, create a task
Copy Code code as follows:
CREATE EVENT IF not EXISTS ' Slave_monitor '
On SCHEDULE EVERY 5 SECOND
On completion PRESERVE
Todo
Call Slave_monitor ();
A task is created, executed every 5 seconds, and remains the task after the task is finished, not deleted. Of course, the task in this case will not end unless it is manually disabled.
If you want to temporarily close a task in motion, execute the ALTER EVENT statement:
Copy Code code as follows:
(root:localhost:) test> alter event ' Slave_monitor ' on
Completion PRESERVE DISABLE;
(root:localhost:) test> alter event ' Slave_monitor ' on
Completion PRESERVE ENABLE;
