Event Scheduler Scheduler:mysql

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.

(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 //
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

CREATE EVENT IF NOT EXISTS `Slave_Monitor`
ON SCHEDULE EVERY 5 SECOND
ON COMPLETION PRESERVE
DO
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:

(root:localhost:)test> alter event `Slave_Monitor` ON
COMPLETION PRESERVE DISABLE;
(root:localhost:)test> alter event `Slave_Monitor` ON
COMPLETION PRESERVE ENABLE;