[MySQL5.1 experience] MySQL event scheduler (eventsched)

Translator: ye Jinrong (Email :), source: imysql.cn I. Overview The Event scheduler is another feature added in MySQL5.1 and can be used as a scheduled task scheduler, this replaces some timer functions that can only be completed by the operating system task scheduler. For example, crontabe in Linux can be executed only once per minute.

Http://imysql.cn I. Summary The Event scheduler is another feature added in MySQL 5.1, can be used as a scheduled task scheduler, this replaces some timer functions that can only be completed by the operating system task scheduler. For example, crontabe in Linux can be executed only once per minute.

Ye Jinrong (Email:), source: http://imysql.cn

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
"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. An event is composed of a specific
Thread management, 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. By setting global variables
The value of event_scheduler dynamically controls whether the event scheduler is enabled.

(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 article does not discuss the detailed syntax of the event scheduler. For more information about the syntax, see Chapter 20th of the MySQL manual.

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;