详解MySQL用事件调度器Event Scheduler创建定时任务

下面是详解MySQL用事件调度器Event Scheduler创建定时任务的完整攻略：

什么是MySQL事件调度器

MySQL事件调度器(Event Scheduler)是MySQL数据库中的一种内置的定时任务调度器，可以自动执行存储过程、函数或SQL语句，并在指定的时间间隔内进行周期性的重复执行，实现了MySQL数据库的自动化管理和维护。我们可以利用MySQL事件调度器来实现定时备份、定时清理等任务，提高数据库的管理效率和安全性。

MySQL事件调度器的使用

开启事件调度器

要使用MySQL事件调度器，首先需要将其开启，可以通过以下命令查看当前事件调度器的状态：

SHOW VARIABLES LIKE 'event_scheduler';

如果返回值是‘ON’，表示事件调度器已经开启，如果返回值是‘OFF’，则需要将其开启，通过以下命令开启：

SET GLOBAL event_scheduler = ON;

创建事件调度器

使用事件调度器，需要创建一个事件，并且设置事件的执行时间、间隔时间和执行任务。以下是创建一个事件的语法：

CREATE EVENT <event_name>
ON SCHEDULE <schedule>
[ON COMPLETION [NOT] PRESERVE]
DO
<event_body>;

其中：

• <event_name> 为事件的名称，必填项；
• <schedule> 为事件的调度时间，可以是一个固定时间点、重复执行的时间间隔或者是手动执行命令，必填项。例如：AT '2021-05-20 20:00:00', EVERY 1 DAY, ON COMPLETION PRESERVE 等；
• [ON COMPLETION [NOT] PRESERVE] 为事件完成后是否保留的设置，可选项；
• <event_body> 为事件的执行任务，可以是SQL语句、存储过程或函数，必填项。

其中，事件的调度时间可以设置以下几种方式：

• AT 'time'：在指定时间点执行事件，如 AT '2021-05-20 20:00:00'；
• EVERY interval：在每个时间间隔执行事件，如 EVERY 1 DAY、EVERY 1 HOUR 等；
• STARTS time ENDS time：在指定的开始时间和结束时间内执行事件，如 STARTS '2021-05-20 20:00:00' ENDS '2021-05-20 23:00:00'；
• ON SCHEDULE expr：使用表达式来定义调度器，如 ON SCHEDULE expr 其中 expr 是一个时间表达式，里面可以自己定义时间规则。

查看事件调度器

在创建了事件调度器后，可以使用以下命令查看事件调度器的状态：

SHOW EVENTS;

删除事件调度器

如果不需要某个事件调度器了，可以使用以下命令删除事件调度器：

DROP EVENT <event_name>;

事件调度器示例

下面是两个简单的事件调度器示例。

示例1：每天凌晨定时备份数据库

假设我们需要每天凌晨2点定时备份数据库，我们可以创建一个名为‘backup_db’的事件调度器，具体步骤如下：

1. 创建备份脚本文件，例如：/usr/local/mysql/scripts/backup.sh，内容如下：

#!/bin/bash

# 备份MySQL数据库
/usr/bin/mysqldump -u root -p123456 dbname > /usr/local/mysql/backup/dbname_$(date +'%Y%m%d').sql

# 删除7天前的备份文件
find /usr/local/mysql/backup/ -name "*.sql" -type f -mtime +7 | xargs rm -f

1. 设置脚本文件可执行权限：

chmod +x /usr/local/mysql/scripts/backup.sh

1. 创建事件调度器：

CREATE EVENT backup_db ON SCHEDULE EVERY 1 DAY STARTS '2021-05-20 02:00:00'
ON COMPLETION PRESERVE
DO
BEGIN
  CALL sys_exec('/usr/local/mysql/scripts/backup.sh');
END

其中，sys_exec 是调用系统命令的函数，我们通过 CALL sys_exec() 执行了备份脚本文件。

1. 查看事件调度器的状态：

SHOW EVENTS;

示例2：每个小时自动清理过期记录

假设我们需要每个小时自动清理数据库中超过30天未登录的用户记录，我们可以创建一个名为‘delete_expired_user’的事件调度器，具体步骤如下：

1. 创建存储过程：

CREATE PROCEDURE delete_expired_users()
BEGIN
  DELETE FROM user WHERE last_login_time < DATE_SUB(NOW(), INTERVAL 30 DAY);
END

1. 创建事件调度器：

CREATE EVENT delete_expired_user ON SCHEDULE EVERY 1 HOUR
ON COMPLETION PRESERVE
DO
BEGIN
  CALL delete_expired_users();
END

其中，CALL delete_expired_users() 调用了上一步中创建的存储过程。

1. 查看事件调度器的状态：

SHOW EVENTS;

这样，我们就成功地创建了两个MySQL事件调度器，并实现了定时备份数据库和定时清理过期用户记录的功能。