0
0

MYSQL定时任务使用手册

   开发和管理数据库时，经常需要定时执行某些任务，比如每天备份数据库、每周统计报表等。MySQL提供了一个非常有用的工具，即事件调度器（Event Scheduler），可以帮助我们实现定时任务调度的功能。本文将介绍如何使用mysql的事件调度器实现定时任务调度，并提供相应的代码示例。注意MYSQL版本为5.1或以上才能使用此功能
1、开启事件调度器
1.1 在使用事件调度器之前，首先需要确认MySQL的事件调度器是否已经 开启。可以通过以下命令查看事件调度器的状态：
方法一
select @@event_scheduler;

--方法二
show variables like 'event_scheduler';

SHOW VARIABLES LIKE 'event_scheduler';

如果结果为Off，则需要手动开启事件调度器。可以通过以下命令开启：

1.2 开启定时调度命令，如下命令开启，如果服务器重启以后，此配置未失效，我们在配置文件 中修改，开启调度命令
-- 开启event事件：
-- 方法1
set GLOBAL event_scheduler=ON;
-- 方法2
set GLOBAL event_scheduler=1;

开启成功后，可以通过再次执行SHOW VARIABLES LIKE 'event_scheduler';命令来确认事件调度器已经开启。

1.3 (建议使用此方法)您也可以在MySQL配置文件（通常是my.cnf或my.ini）中设置：

[mysqld]
event_scheduler = ON

您也可以在MySQL配置文件（通常是my.cnf或my.ini）中设置：

[mysqld]
event_scheduler = ON

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
character-set-server=utf8      
default-storage-engine=innodb
port=50002 
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# 2024-08-20 
event_scheduler = ON

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
2、创建定时任务，使用事件调度器创建定时任务需要执行以下步骤：
2.1 创建一个事件调度器

使用CREATE EVENT语句可以创建一个事件调度器。具体语法如下：

CREATE EVENT event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'string']
DO event_body;

其中event_name是事件的名称，schedule是调度周期，event_body是事件的具体操作。

2.1.1 设定调度周期

调度周期可以根据实际需求设定。以下是常用的调度周期：

  • 每秒钟执行一次：EVERY 1 SECOND
  • 每分钟执行一次：EVERY 1 MINUTE
  • 每小时执行一次：EVERY 1 HOUR
  • 每天执行一次：EVERY 1 DAY
  • 每周执行一次：EVERY 1 WEEK
  • 每个月执行一次：EVERY 1 MONTH

可以根据需求选择合适的调度周期。

2.1.2 编写事件的具体操作
    在event_body部分编写具体的操作逻辑。可以使用SQL语句执行数据库操作，也可以调用存储过程或函数。

以下是一个示例的定时任务，每天凌晨3点备份数据库：

CREATE EVENT backup_event
ON SCHEDULE EVERY 1 DAY
STARTS '2022-01-01 03:00:00'
DO
BEGIN

1

2

3

4

5

6

DECLARE

backup_file 

VARCHAR

(255);

SET

backup_file := CONCAT(

'/var/backup/db_'

, DATE_FORMAT(NOW(), 

'%Y%m%d_%H%i%s'

), 

'.sql'

);

SET

@sql := CONCAT(

'mysqldump -hlocalhost -uroot -ppassword dbname > '

, backup_file);

PREPARE

stmt 

FROM

@sql;

EXECUTE

stmt;

DEALLOCATE

PREPARE

stmt;

END;

CREATE EVENT backup_event
ON SCHEDULE EVERY 1 DAY
STARTS '2022-01-01 03:00:00'
DO
BEGIN

DECLARE backup_file VARCHAR(255);

SET backup_file := CONCAT('/var/backup/db_', DATE_FORMAT(NOW(), '%Y%m%d_%H%i%s'), '.sql');

SET @sql := CONCAT('mysqldump -hlocalhost -uroot -ppassword dbname > ', backup_file);

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END;

以上代码创建了一个名为backup_event的事件调度器，设定调度周期为每天执行一次。在event_body部分，首先定义了一个变量backup_file，用于存放备份文件的路径。然后使用CONCAT函数拼接备份文件路径，再使用SET语句将备份命令赋值给@sql变量。最后，使用PREPARE和EXECUTE语句执行备份命令。

2.2 创建任务的实例
CREATE TABLE t_mysql_job (
  id INT AUTO_INCREMENT PRIMARY KEY,
  job_name VARCHAR(50) NOT NULL,
  job_description VARCHAR(100),
  job_status INT DEFAULT 0,
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE EVENT job_10_minute_test
ON SCHEDULE EVERY 10 MINUTE
-- 从什么时候开始计时
STARTS SYSDATE()
DO
-- 在这里编写定时任务的逻辑代码
INSERT INTO t_mysql_job (job_name, job_description) VALUES ('Daily Job', 'This is a daily job');
2.2.1从当前时间延迟指定时间，每隔10分钟执行一次
 以下**SQL**用于创建一个循环事件，每个

EVERY 10 MINUTE

表示该事件每一分钟就执行一次，

CURRENT_TIMESTAMP + INTERVAL 5 MINUTE

表示从当前时间开始延迟五分钟再执行。

create EVENT job_p_hr_attendance_daily
ON SCHEDULE
EVERY 10 MINUTE STARTS CURRENT_TIMESTAMP + INTERVAL 10 MINUTE
ON COMPLETION PRESERVE
DO  call  p_hr_attendance_daily
;
2.2.2 指定某个时间开始，每隔5分钟执行一次
CREATE EVENT DelayedSchedulingEvent
ON SCHEDULE
EVERY 5 MINUTE STARTS '2024-03-22 10:10:00'
ON COMPLETION PRESERVE
DO INSERT INTO `template_backend`.`message` (`message`, `message_date`) values ('指定时间2024-03-22 10:10:00后,每隔1分钟执行', now());
3、管理定时任务

使用CREATE EVENT语句创建了定时任务后，可以通过以下命令进行管理：

  • 查看所有事件调度器：SHOW EVENTS;
  • 查看指定事件调度器的信息：SHOW EVENT event_name;
  • 启用事件调度器：ALTER EVENT event_name ENABLE;
  • 禁用事件调度器：ALTER EVENT event_name DISABLE;
  • 修改事件调度器的执行时间：ALTER EVENT event_name ON SCHEDULE AT 'date_time';

通过管理命令，可以方便地查看和管理定时任务。

3.1 查看执行事件，使用命令 show events,显示如下重要的信息

DB：表示用作与哪个库，Name是事件名称。
Definer：表示哪个用户权限执行。
Type：ONE TIME表示只执行一次。
status：ENABLE表示当前事件为可用状态。

show events
4 定时任务查询（帐号要查询此表的权限）
4.1 执行以下命令来查询事件日志表：
SELECT * FROM mysql.event;

复制代码

  1. 这将列出所有已经创建的事件及其相关信息，如事件名称、状态、创建时间、执行时间等。
  2. 如果想查看特定事件的执行历史，可以通过事件名称来筛选：
4.2 按事件名称来查询，执行记录

--查询执行情况
SELECT * FROM mysql.event  ;
--查询执行情况
SELECT * FROM mysql.event WHERE name = 'your_event_name';
--查询执行情况
SELECT * FROM information_schema.EVENTS ;

如果不是管理员帐号，切换到管理员下，执行如下命令，给予如下权限，以后便可以查询event的权限

grant select, insert,update,delete  on mysql.event   to 'mes'@'%';
实际案例代码

参考代码：

-- 创建事件调度器
CREATE EVENT backup_event
ON SCHEDULE EVERY 1 DAY
STARTS '2022-01-01 03:00:00'
DO
BEGIN

1

2

3

4

5

6

DECLARE

backup_file 

VARCHAR

(255);

SET

backup_file := CONCAT(

'/var/backup/db_'

, DATE_FORMAT(NOW(), 

'%Y%m%d_%H%i%s'

), 

'.sql'

);

SET

@sql := CONCAT(

'mysqldump -hlocalhost -uroot -ppassword dbname > '

, backup_file);

PREPARE

stmt 

FROM

@sql;

EXECUTE

stmt;

DEALLOCATE

PREPARE

stmt;

END;

-- 查看所有事件调度器
SHOW EVENTS;

-- 查看指定事件调度器的信息
SHOW EVENT backup_event;

-- 启用事件调度器
ALTER EVENT backup_event ENABLE;

-- 禁用事件调度器
ALTER EVENT backup_event DISABLE;

-- 修改事件调度器的执行时间
ALTER EVENT backup_event ON SCHEDULE AT '2023-01-01 03:00:00';

CREATE DEFINER=`root`@`%` PROCEDURE `yuxiu_mes_prd`.`p_hr_attendance_daily`()
BEGIN
    
    DECLARE finished INTEGER DEFAULT 0;  
    DECLARE v_dept_code VARCHAR(100); 
    DECLARE v_dept_name VARCHAR(100);  
    DECLARE v_onjob_qty int;
    DECLARE v_onjob_qty_sum int default  0;  
    DECLARE v_attendance_actual_qty_sum int  default  0;   
    -- 计算在职人数
    DECLARE cur CURSOR FOR select
                            dept_code,
                            dept_name,
                            COUNT(person_num) qty
                        from
                            md_person
                        where
                            status = '10'
                        group by
                            dept_code,
                            dept_name;
    -- 声明NOT FOUND的处理程序  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;  
   
    OPEN cur; -- 打开游标 
    
    execute_do: LOOP  
        FETCH cur INTO v_dept_code, v_dept_name,v_onjob_qty; -- 从游标中获取数据  
        IF finished THEN   
            LEAVE execute_do; -- 如果已经到达结果集的末尾，则退出循环  
        END IF;  
        -- 在这里可以对获取到的数据进行处理，比如打印出来 
        set v_onjob_qty_sum=v_onjob_qty_sum+v_onjob_qty; 
        INSERT
            INTO
            hr_attendance_daily(attendance_date,
            dept_code,
            dept_name,
            onjob_qty,
            attendance_required_qty,
            update_by,
            update_time)
    VALUES 
        (current_date,
        v_dept_code,
        v_dept_name,
        v_onjob_qty,    
        v_onjob_qty,
        'p_hr_attendance_daily',
        now()) ON DUPLICATE KEY
        UPDATE
            onjob_qty = v_onjob_qty,
            attendance_required_qty = v_onjob_qty,        
            update_by = 'p_hr_attendance_daily',
            update_time = now()
            ;  
    END LOOP execute_do;  
   -- 更新出勤率
   update hr_attendance_daily set  attendance_rate=attendance_actual_qty/onjob_qty where attendance_date =CURRENT_DATE() ;                
 
  -- 计算出勤人数总数
  select  SUM(attendance_actual_qty) into  v_attendance_actual_qty_sum from hr_attendance_daily 
  where attendance_date =CURRENT_DATE() and dept_code<>'all' ;
 
 -- 计算所有出勤率
         INSERT
            INTO
            hr_attendance_daily(attendance_date,
            dept_code,
            dept_name,
            onjob_qty,
            attendance_required_qty,
            attendance_actual_qty,
            attendance_rate,
            update_by,
            update_time)
      VALUES 
        (current_date,
        'all',
        'all',
        v_onjob_qty_sum,    
        v_onjob_qty_sum,
        v_attendance_actual_qty_sum,
        v_attendance_actual_qty_sum/v_onjob_qty_sum,
        'p_hr_attendance_daily',
        now()) ON DUPLICATE KEY
        UPDATE
            onjob_qty = v_onjob_qty_sum,
            attendance_required_qty = v_onjob_qty_sum,
            attendance_actual_qty=v_attendance_actual_qty_sum,
            attendance_rate=v_attendance_actual_qty_sum/v_onjob_qty_sum,
            update_by = 'p_hr_attendance_daily',
            update_time = now()
            ;  
  
 
END

执行查看JOB执行的情况 

--执行查看任务
select * from mysql.event  ;
select * from information_schema.events ;
show variables like 'event_scheduler';
标签： oracle 数据库
本文转载自: https://blog.csdn.net/yqj234/article/details/141302672
版权归原作者 yqj234 所有， 如有侵权，请联系我们删除。
登录后发布评论

“MYSQL定时任务使用手册”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

信息系统与互联网中的安全、隐私及伦理问题

3分钟解决Ubuntu22.04没有声音输出设备

Docker下载途径

Vue3 与 D3.js 集成：实现数据可视化和动态图表的交互效果

【Kafka】Kafka源码解析之producer过程解读

设备的双按钮安全启动控制，你知道吗？

打造网页版Ubuntu环境：群晖NAS部署docker-webtop与远程访问指南

文章导航