Procedure of the MySQL event Scheduler

The following article mainly introduces the MySQL event scheduler (eventsched). The event scheduler we tested this time was executed in the MySQL environment, and it adds another related function, it can be used as a new scheduled task scheduler. This replaces some timer functions that can only be completed by the operating system task scheduler. I. Overview

The following article mainly introduces the MySQL Event Scheduler. The Event Scheduler we tested this time is performed in the MySQL 5.1 environment, and it adds another related function, it can be used as a new scheduled task scheduler. This replaces some timer functions that can only be completed by the operating system task scheduler. I. Overview

The following article mainly introduces the MySQL Event Scheduler. The Event Scheduler we tested this time is performed in the MySQL 5.1 environment, and it adds another related function, it can be used as a new scheduled task scheduler.

This replaces 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 one task per 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 MySQL event scheduler is enabled, the SUPER account executes 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

Implement the MySQL event scheduler. In this case, the storage process is called every second to determine whether the SLAVE is running normally. If the SLAVE is disabled and zero errors are ignored, then restart 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; 
 
 

The above content is an introduction to the MySQL event scheduler. I hope you will get some benefits.