How to implement Mysql scheduled tasks under Linux

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

Recommend

MySQL 8.0.19 installation and configuration method graphic tutorial

This article records the installation and configu...

Function overloading in TypeScript

Table of contents 1. Function signature 2. Functi...

How to set PATH environment variable in Linux system (3 methods)

1. In Windows system, many software installations...

Docker-compose steps to configure the spring environment

Recently, I need to package the project for membe...

Detailed explanation of :key in VUE v-for

When key is not added to the v-for tag. <!DOCT...

Detailed explanation of the JVM series memory model

Table of contents 1. Memory model and runtime dat...

Detailed explanation of where the image pulled by docker is stored

20200804Addendum: The article may be incorrect. Y...

How to use custom tags in html

Custom tags can be used freely in XML files and HT...

Share 8 very useful CSS development tools

CSS3 Patterns Gallery This CSS3 pattern library s...

JS realizes automatic playback of timeline

Recently, I have implemented such an effect: clic...

How to query a record in Mysql in which page of paging

Preface In practice, we may encounter such a prob...

Detailed steps for developing WeChat mini-programs using Typescript

We don't need to elaborate too much on the ad...

A brief discussion on the understanding of TypeScript index signatures

Table of contents 1. What is an index signature? ...