发布于 由admin

MySQL编程进阶之：事件调度器与`SET GLOBAL event_scheduler=ON`：开启调度器的最佳实践。

各位观众老爷，晚上好！我是你们的老朋友，今天咱们来聊聊MySQL里一个挺有意思的东西：事件调度器 (Event Scheduler)。 别看名字挺高大上，其实就是个定时任务管理器，能让你在MySQL里安排一些定期执行的任务，比如每天凌晨备份数据库、每周清理一下日志啥的。

一、 啥是MySQL事件调度器？

简单来说，MySQL事件调度器就是一个内置的守护进程，它会按照你设定的时间表，自动执行你定义的SQL语句。你可以把它想象成一个内置的cron，只不过它是在MySQL服务器内部运行，直接操作数据库。

二、 为啥要用事件调度器？

  • 自动化运维： 可以自动执行一些例行维护任务，比如数据备份、数据清理、统计报表生成等等，解放你的双手。
  • 定时任务： 实现一些需要在特定时间执行的任务，比如每日/每周/每月的数据汇总、定时更新缓存等等。
  • 简化复杂逻辑： 可以将复杂的业务逻辑拆分成多个小的事件，分别在不同的时间执行，使代码更清晰易懂。
  • 避免外部依赖： 不需要依赖外部的定时任务工具（比如cron），直接在MySQL内部实现定时任务，减少了部署和维护的复杂度。

三、 SET GLOBAL event_scheduler=ON：开启调度器的正确姿势

要使用事件调度器，首先要确保它已经开启。默认情况下，事件调度器可能是关闭的。开启的方式很简单，就是执行这条命令：

SET GLOBAL event_scheduler = ON;

但是！这里面藏着一些小坑，需要注意：

  • 权限问题： 必须拥有SUPER权限才能执行这个命令。如果你没有SUPER权限，可以尝试联系你的数据库管理员，让他帮你开启。

  • 重启失效： 这种方式开启的事件调度器，在MySQL服务器重启后会失效。也就是说，下次重启后，你还需要再执行一次SET GLOBAL event_scheduler = ON;。 这肯定不方便！

  • 正确的开启姿势： 为了避免重启失效的问题，你需要修改MySQL的配置文件 (my.cnf 或 my.ini)，在 [mysqld] 部分添加或修改以下配置：

    [mysqld]
event_scheduler = ON

    然后重启MySQL服务器。这样，事件调度器就会随着服务器启动而自动启动了。

  • 检查状态： 开启之后，可以用这个命令检查事件调度器是否正常运行：

    SHOW VARIABLES LIKE 'event_scheduler';

    如果 Value 是 ON，就说明已经成功开启了。

四、 创建事件：CREATE EVENT 语法详解

开启了事件调度器，接下来就要创建事件了。创建事件的核心语句是 CREATE EVENT。它的语法比较灵活，但是掌握了几个关键点，就能轻松上手。

CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
DO event_body;
  • event_name: 事件的名字，必须是唯一的。
  • schedule: 事件的执行时间表，可以是单次执行，也可以是周期性执行。
  • event_body: 事件要执行的SQL语句。

4.1 schedule 的几种写法：

  • 单次执行：

    AT timestamp [+ interval]
    • timestamp：指定事件执行的具体时间点，比如 2023-10-27 22:00:00。
    • interval：从当前时间开始，经过一段时间后执行，比如 CURRENT_TIMESTAMP + INTERVAL 1 DAY (一天后执行)。

    示例：

    CREATE EVENT my_event_once
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO
  UPDATE my_table SET my_column = '单次执行';

  • 周期性执行：

    EVERY interval
[STARTS timestamp [+ interval]]
[ENDS timestamp [+ interval]]
    • interval：指定事件执行的间隔，比如 1 DAY (每天)，1 HOUR (每小时)，30 MINUTE (每30分钟) 等等。
    • STARTS timestamp [+ interval]：指定事件开始执行的时间点，可选。如果省略，则从创建事件后立即开始执行。
    • ENDS timestamp [+ interval]：指定事件结束执行的时间点，可选。如果省略，则事件会一直执行下去。

    示例：

    CREATE EVENT my_event_daily
ON SCHEDULE EVERY 1 DAY
STARTS '2023-10-28 00:00:00'
ENDS '2023-10-31 00:00:00'
DO
  INSERT INTO my_log (message) VALUES ('每天执行一次');
    CREATE EVENT my_event_hourly
ON SCHEDULE EVERY 1 HOUR
DO
  UPDATE my_statistics SET count = count + 1;

  • 更复杂的周期性执行 (使用 EVERY 和 STARTS 组合):

    CREATE EVENT my_event_weekly
ON SCHEDULE EVERY 1 WEEK
STARTS CURRENT_TIMESTAMP + INTERVAL (7 - DAYOFWEEK(CURRENT_DATE)) DAY + INTERVAL 1 DAY + INTERVAL 10 HOUR
DO
  -- 备份数据库
  SYSTEM 'mysqldump -u root -p密码 数据库名 > /tmp/backup.sql';

    这个例子会在每个星期日的10点执行。 这段代码使用了 DAYOFWEEK() 函数来计算距离下一个星期日还有多少天，然后用 INTERVAL 将开始时间设置为下一个星期日的10点。注意这里使用了SYSTEM函数，需要开启log_bin_trust_function_creators，并且赋予SUPER权限。

4.2 event_body 的写法：

event_body 里面可以放任何有效的SQL语句，包括 SELECT、INSERT、UPDATE、DELETE、CALL (调用存储过程) 等等。 如果要执行多条SQL语句，需要用 BEGIN...END 块包起来。

示例：

CREATE EVENT my_event_multiple_statements
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
  INSERT INTO my_log (message) VALUES ('事件开始执行');
  UPDATE my_table SET my_column = '每日更新';
  INSERT INTO my_log (message) VALUES ('事件执行完毕');
END;

五、 事件的状态：ENABLE、DISABLE 和 ALTER EVENT

  • ENABLE: 启用事件。事件创建后，默认是启用的。
  • DISABLE: 禁用事件。禁用后，事件不会再执行，直到被重新启用。
  • ALTER EVENT: 修改事件。可以修改事件的执行时间表、执行的SQL语句、状态等等。

示例：

-- 禁用事件
ALTER EVENT my_event_daily DISABLE;

-- 启用事件
ALTER EVENT my_event_daily ENABLE;

-- 修改事件的执行时间表
ALTER EVENT my_event_daily
ON SCHEDULE EVERY 2 DAY;

-- 修改事件执行的SQL语句
ALTER EVENT my_event_daily
DO
  UPDATE my_table SET my_column = '每两天更新一次';

六、 查看事件：SHOW EVENTS

可以使用 SHOW EVENTS 语句来查看当前数据库中定义的所有事件。

SHOW EVENTS FROM your_database_name;

这条语句会显示事件的名称、执行时间表、状态等等信息。

也可以通过查询 information_schema.EVENTS 表来获取更详细的事件信息：

SELECT * FROM information_schema.EVENTS WHERE EVENT_SCHEMA = 'your_database_name';

七、 删除事件：DROP EVENT

不再需要的事件，可以使用 DROP EVENT 语句删除。

DROP EVENT [IF EXISTS] event_name;

加上 IF EXISTS 可以避免事件不存在时报错。

八、 实例演示：数据库备份

下面我们来演示一个实际的例子：每天凌晨3点备份数据库。

  1. 创建备份脚本：

    首先，创建一个备份数据库的脚本文件 (backup.sh)。

    #!/bin/bash

# 数据库信息
DB_USER="root"
DB_PASS="你的密码"
DB_NAME="你的数据库名"
BACKUP_DIR="/tmp/backup"

# 创建备份目录
mkdir -p $BACKUP_DIR

# 生成备份文件名
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_$(date +%Y%m%d).sql"

# 执行备份
mysqldump -u$DB_USER -p"$DB_PASS" $DB_NAME > $BACKUP_FILE

# 压缩备份文件(可选)
gzip $BACKUP_FILE

echo "Database backup completed: $BACKUP_FILE.gz"

    记得给脚本文件添加执行权限：

    chmod +x backup.sh

  2. 创建事件：

    然后，在MySQL中创建一个事件，每天凌晨3点执行备份脚本。

    CREATE EVENT backup_database_daily
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 3 HOUR
DO
  SYSTEM '/path/to/your/backup.sh';

    把 /path/to/your/backup.sh 替换成你实际的脚本文件路径。

    注意： 为了让 SYSTEM 命令能够执行外部脚本，你需要确保MySQL服务器有执行该脚本的权限。 并且需要开启log_bin_trust_function_creators，并赋予SUPER权限。

九、 最佳实践和注意事项

  • 错误处理： 事件执行过程中如果发生错误，MySQL不会自动记录错误日志。你需要自己在事件体中添加错误处理机制，比如使用 TRY...CATCH 块 (MySQL 8.0 之后支持) 或者将错误信息写入日志表。
  • 权限控制： 事件的创建者需要拥有足够的权限才能执行事件体中的SQL语句。
  • 资源消耗： 事件的执行会消耗服务器资源，特别是当事件体中的SQL语句比较复杂时。需要合理安排事件的执行时间，避免在高峰期执行资源消耗过大的事件。
  • 并发问题： 如果多个事件同时修改同一张表，可能会导致并发问题。需要使用事务或者锁来保证数据的一致性。
  • 避免死循环： 编写事件体时要小心，避免出现死循环。比如，在一个事件中更新一张表，然后触发另一个事件，而另一个事件又更新同一张表，这样就可能导致死循环。
  • 监控： 监控事件的执行情况，及时发现和解决问题。可以定期查看事件的执行日志，或者编写监控脚本来检查事件是否按计划执行。
  • 测试： 在生产环境中使用事件之前，一定要先在测试环境进行充分的测试，确保事件能够正常工作。
  • 备份： 定期备份事件的定义，以便在发生意外情况时能够快速恢复。可以使用 SHOW CREATE EVENT 语句来获取事件的定义，然后保存到文件中。
  • 开启 log_bin_trust_function_creators: 如果事件体中使用了自定义函数，需要开启 log_bin_trust_function_creators 参数。
  • 使用存储过程: 将复杂的逻辑封装到存储过程中，然后在事件中调用存储过程，可以提高代码的可维护性和可重用性。

十、 总结

MySQL事件调度器是一个非常实用的工具，可以帮助我们实现自动化运维和定时任务。 掌握了 SET GLOBAL event_scheduler=ON 的正确开启姿势、CREATE EVENT 的语法、以及一些最佳实践和注意事项，就能轻松驾驭这个强大的工具。 记住，要先在测试环境进行充分的测试，然后再在生产环境中使用。

希望今天的讲座对大家有所帮助！ 感谢各位的观看，下次再见！

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注