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

Understand the principles of MySQL persistence and rollback in one article

Table of contents redo log Why do we need to upda...

JavaScript to implement retractable secondary menu

The specific code for implementing the retractabl...

JavaScript implements draggable modal box

This article shares the specific code of JavaScri...

About the problem of dynamic splicing src image address of img in Vue

Let's take a look at the dynamic splicing of ...

Summary of Nginx load balancing methods

To understand load balancing, you must first unde...

How to use MySQL stress testing tools

1. MySQL's own stress testing tool - Mysqlsla...

Explain TypeScript mapped types and better literal type inference

Table of contents Overview Using mapped types to ...

Docker connects to a container through a port

Docker container connection 1. Network port mappi...

Summary of Mysql high performance optimization skills

Database Command Specification All database objec...

How to set up vscode remote connection to server docker container

Table of contents Pull the image Run the image (g...

Detailed explanation of mixins in Vue.js

Mixins provide distributed reusable functionality...

JavaScript data transmission between different pages (URL parameter acquisition)

On web pages, we often encounter this situation: ...

Detailed explanation of the solution to docker-compose being too slow

There is only one solution, that is to change the...

Vue implements login type switching

This article example shares the specific code of ...