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

How to use nginx as a load balancer for mysql

Note: The nginx version must be 1.9 or above. Whe...

MySQL 8.0 Window Function Introduction and Summary

Preface Before MySQL 8.0, it was quite painful to...

Good website copywriting and good user experience

Looking at a website is actually like evaluating a...

Tic-Tac-toe game implemented in pure CSS3

Operation effect: html <div class="tic-ta...

Perfect solution for theme switching based on Css Variable (recommended)

When receiving this requirement, Baidu found many...

How to use jsx syntax correctly in vue

Table of contents Preface Virtual DOM What is Vir...

Solution to the routing highlighting problem of Vue components

Preface Before, I used cache to highlight the rou...

MySQL uses variables to implement various sorting

Core code -- Below I will demonstrate the impleme...

How to install and deploy gitlab server on centos7

I am using centos 7 64bit system here. I have tri...

Detailed explanation of CSS image splicing technology (sprite image)

CSS image splicing technology 1. Image stitching ...

Tutorial on building nextcloud personal network disk with Docker

Table of contents 1. Introduction 2. Deployment E...

Automatic file synchronization between two Linux servers

When server B (172.17.166.11) is powered on or re...

How to Run a Command at a Specific Time in Linux

The other day I was using rsync to transfer a lar...

SQL implementation of LeetCode (184. The highest salary in the department)

[LeetCode] 184. Department Highest Salary The Emp...