厦门免费网站建设,深圳软件科技有限公司,阿里云服务器 网站,有没有专门做外包销售的公司开发和管理数据库时#xff0c;经常需要定时执行某些任务#xff0c;比如每天备份数据库、每周统计报表等。MySQL提供了一个非常有用的工具#xff0c;即事件调度器#xff08;Event Scheduler#xff09;#xff0c;可以帮助我们实现定时任务调度的功能。本文将介绍如何… 开发和管理数据库时经常需要定时执行某些任务比如每天备份数据库、每周统计报表等。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_schedulerON;
-- 方法2
set GLOBAL event_scheduler1;
开启成功后可以通过再次执行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-serverutf8
default-storage-engineinnodb
port50002
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-links0log-error/var/log/mysqld.log
pid-file/var/run/mysqld/mysqld.pid2、创建定时任务使用事件调度器创建定时任务需要执行以下步骤
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
BEGINDECLARE 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表示哪个用户权限执行。 TypeONE TIME表示只执行一次。 statusENABLE表示当前事件为可用状态。
show events 4 定时任务查询帐号要查询此表的权限
4.1 执行以下命令来查询事件日志表
SELECT * FROM mysql.event;复制代码 这将列出所有已经创建的事件及其相关信息如事件名称、状态、创建时间、执行时间等。 如果想查看特定事件的执行历史可以通过事件名称来筛选
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 DEFINERroot% PROCEDURE yuxiu_mes_prd.p_hr_attendance_daily()
BEGINDECLARE 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 selectdept_code,dept_name,COUNT(person_num) qtyfrommd_personwherestatus 10group bydept_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_sumv_onjob_qty_sumv_onjob_qty; INSERTINTOhr_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 KEYUPDATEonjob_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_rateattendance_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_codeall ;-- 计算所有出勤率INSERTINTOhr_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 KEYUPDATEonjob_qty v_onjob_qty_sum,attendance_required_qty v_onjob_qty_sum,attendance_actual_qtyv_attendance_actual_qty_sum,attendance_ratev_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;
