How to Realize Mysql Timing Task under Linux

Assumption: Execute stored procedures regularly every 10 minutes from 10 pm to 5 am every day.

Implementation method:

The first is to use Mysql itself to implement and formulate event timing tasks, which can be done with Navicat For Mysql or other database development tools;
The second implementation method is to use the timing task of linux, which is realized by using the task management tool of Linux system.

1. The relevant commands of event timing task in Mysql,

1. Check whether event is enabled:

SELECT @@event_scheduler; 
 Or 
SHOW VARIABLES LIKE 'event%'; 

2. Start the timed task:

set GLOBAL event_scheduler = 1; 
 Or 
SET GLOBAL event_scheduler = ON; 

3. Establish timed tasks:

DROP EVENT IF EXISTS JOB_ALARM;
CREATE EVENT JOB_ALARM
 ON SCHEDULE EVERY 10 MINUTE
DO
 BEGIN
  if(date_format(current_time(),'%H')>22 || date_format(current_time(),'%H')<5) THEN
   CALL PRO_ALARM();
  END IF;
END

4. Create stored procedures:

DROP PROCEDURE IF EXISTS PRO_ALARM; 
CREATE PROCEDURE PRO_ALARM() 
 BEGIN 
  DECLARE userId VARCHAR(32); 
  # This is used to handle the cursor reaching the end 1 The situation of the line  
  DECLARE s INT DEFAULT 0; 
  # Declare cursor cursor_name ( cursor_name Is a multi-row result set)  
  DECLARE cursor_data CURSOR FOR 
   SELECT tmp.USER_ID 
   FROM ( 
       SELECT 
        e.USER_ID, 
        MAX(e.TIME_GMT) TIME_GMT 
       FROM EVENTS e 
       GROUP BY e.USER_ID 
       HAVING MAX(e.TIME_GMT) < UNIX_TIMESTAMP() - 60 * 30 
          AND MAX(e.TIME_GMT) > UNIX_TIMESTAMP() - 60 * 60 * 24) tmp 
    INNER JOIN EVENTS t ON tmp.USER_ID = t.USER_ID 
                   AND tmp.TIME_GMT = t.TIME_GMT 
   WHERE TYPE_ID != '34001'; 
  # Settings 1 Termination tags  
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s = 1; 
  OPEN cursor_data; 
  # Gets the record of the current pointer of the cursor, reads the 1 Row data and pass it to variables a,b 
  FETCH cursor_data 
  INTO userId; 
  # Start the loop and determine whether the cursor has reached the last as a loop condition  
  WHILE s <> 1 DO 
   INSERT INTO EVENTS_NOTIFICATION VALUES (NULL, SYSDATE(), UNIX_TIMESTAMP(SYSDATE()), 
                          UNIX_TIMESTAMP(SYSDATE()), '00000', userId, '1', '0'); 
   # Read under 1 Row data  
   FETCH cursor_data 
   INTO userId; 
  END WHILE; 
  # Close the cursor  
  CLOSE cursor_data; 
 END; 

2. Using the timing task of linux,

linux Timing Task Basic Command:

View Timing Tasks: crontab-l
Edit Timing Tasks: crontab-e

*/10 22-23, 0-5 * * * mysql-u username-p password-e "use db_name; CALL PRO_ALARM (); "

Or use db_name; CALL PRO_ALARM (); In the sql script, edit the timing task as follows:

*/10 22-23, 0-5 * * * mysql-u username-p password < /application/Job_mysql.sql

Summarize