How to implement Mysql scheduled tasks under Linux

Assumption: The stored procedure is executed every 10 minutes from 10 pm to 5 am every day.

Implementation:

The first is to use MySQL itself to formulate event timing tasks, which can be done with the help of Navicat For MySQL or other database development tools;
The second implementation method is to use Linux's scheduled tasks, which is implemented using the task management tool that comes with the Linux system.

1. Related commands for event timing tasks in Mysql,

1. Check whether event is enabled:

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

2. Start the scheduled task:

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

3. Create a scheduled task:

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 a stored procedure:

DROP PROCEDURE IF EXISTS PRO_ALARM; 
CREATE PROCEDURE PRO_ALARM() 
 BEGIN 
  DECLARE userId VARCHAR(32); 
  #This is used to handle the situation where the cursor reaches the last row 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 
       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'; 
  #Set a termination mark DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s = 1; 
  OPEN cursor_data; 
  #Get the record of the current cursor pointer, read a row of data and pass it to variables a, b 
  FETCH cursor_data 
  INTO userId; 
  #Start the loop and determine whether the cursor has reached the end as the loop condition WHILE s <> 1 DO 
   INSERT INTO EVENTS_NOTIFICATION VALUES (NULL, SYSDATE(), UNIX_TIMESTAMP(SYSDATE()), 
                          UNIX_TIMESTAMP(SYSDATE()), '00000', userId, '1', '0'); 
   #Read the next row of data FETCH cursor_data 
   INTO userId; 
  END WHILE; 
  #Close the cursor CLOSE cursor_data; 
 END;

2. Using Linux's scheduled tasks,

Linux scheduled task basic commands:

View scheduled tasks: crontab -l
Edit scheduled tasks: crontab -e

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

Or save use db_name;CALL PRO_ALARM(); into the SQL script and edit the scheduled task as follows:

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

Summarize

The above is my introduction on how to implement Mysql scheduled tasks under Linux. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!

You may also be interested in:

How to configure MySQL scheduled tasks (EVENT events) in detail
MySQL scheduled task implementation and usage examples
Analysis of the method of setting up scheduled tasks in mysql
Detailed explanation of mysql scheduled tasks (event events)
How to implement Mysql scheduled task backup data under Linux
MySQL scheduled task example tutorial
Analysis and solution of the reasons why MySQL scheduled tasks cannot be executed normally

<<: Explain TypeScript mapped types and better literal type inference

>>: Detailed explanation of Tomcat configuration and optimization solutions