我为什么不建议使用event_scheduler

前言

原标题《MySQL8.0的参数event_scheduler默认是ON，请注意一些坑》，于 2019-01-23 首发在我已弃用的博客上。之前的文章内容组织得不够好，不好读懂，现在我重写了一遍，目前没有发现有人和我持一样的观点，但我观点没有改变过，欢迎讨论。

event_scheduler 是什么？

参数: event_scheduler
是 MySQL 定时器的开关，MySQL 5.7 中默认值是 off，MySQL 8.0 中默认值是 on，它类似于 Windows 操作系统的定时任务的概念，指定某个时间点执行一次定时事件(event)任务，或者每隔一段时间循环执行定时事件任务。

这个东西有企业在用么？

我看了几个企业的开发规范，都没有提及需要禁用 event 功能，所以 event 功能是允许开发人员使用的。（不过，我相信 java 开发人员更喜欢用 java 定时器。）也许是因为 MySQL5.7 默认就不启用 event_scheduler 功能嘛，默认就无法用 event，开发规范就不提及这东西啦。况且我们还可以通过用户权限来控制开发人员是否可以使用 event。所以从我手上的几家公司的开发规范里，并不可以实际看出大家有没有使用 event_scheduler 这个功能。

由于 MySQL8.0 默认是开启 event_scheduler 功能了，所以我认为我们还是有必要讨论一下 event 到底能不能用，有没有坑？

我能想到的坑——主从复制的坑

首先这个事情并没有实际发生在我生产环境，是一种脑海中想到的情况，于是去做了实验。

准备环境

两个 MySQL8.0 数据库实例

#配置文件不显式地指定默认值:
[root@192-168-199-131 3308]# cat my.cnf |grep event_scheduler

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.29    |
+-----------+
1 row in set (0.00 sec)

mysql> show variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.12 sec)

MySQL8.0 的 event_scheduler 默认值确实是 ON

下面我带大家做两个实验

实验一：在已有的主从环境下，建立一个重复插入的event。

步骤1

#主库上执行
mysql> show variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.12 sec)

create database fander;
use fander;
CREATE EVENT IF NOT EXISTS test
ON SCHEDULE EVERY 1 SECOND
ON COMPLETION PRESERVE
DO insert into fander.test values (1);

#这个event表示每一秒都往fander库的test表插入一行值为1的数据。

mysql> show create event test\G
*************************** 1. row ***************************
               Event: test
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`admin`@`localhost` EVENT `test` ON SCHEDULE EVERY 1 SECOND STARTS '2022-07-02 16:20:59' ON COMPLETION PRESERVE ENABLE DO insert into fander.test values (1)
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> show tables;
Empty set (0.01 sec)
#我先不建立测试重复插入的 test 表。

步骤2

#从库上执行
mysql> show variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.12 sec)
mysql> use fander;
mysql>  show create event test\G
*************************** 1. row ***************************
               Event: test
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`admin`@`localhost` EVENT `test` ON SCHEDULE EVERY 1 SECOND STARTS '2022-07-02 16:20:59' ON COMPLETION PRESERVE DISABLE ON SLAVE DO insert into fander.test values (1)
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

我现在还没有创建 test 表，所以这个 event 没有可操作对象是不能执行成功的。error log 里有如下记录：

[root@192-168-199-131 3308]# tail -4 mysql-error.log 
2022-07-02T16:26:28.924690+08:00 339 [ERROR] [MY-010045] [Server] Event Scheduler: [admin@localhost][fander.test] Table 'fander.test' doesn't exist
2022-07-02T16:26:28.924721+08:00 339 [Note] [MY-010046] [Server] Event Scheduler: [admin@localhost].[fander.test] event execution failed.
2022-07-02T16:26:29.928068+08:00 340 [ERROR] [MY-010045] [Server] Event Scheduler: [admin@localhost][fander.test] Table 'fander.test' doesn't exist
2022-07-02T16:26:29.928098+08:00 340 [Note] [MY-010046] [Server] Event Scheduler: [admin@localhost].[fander.test] event execution failed.

在创建 test 表前 我们讨论一下这个问题：
主从环境的 event_scheduler 都是开启的。这意味这从库有可能会重复写入数据。一部分数据来源于主库的 event 的循环插入的复制，一部分数据来源于从库自身的 event 的循环插入。是吗？

答案是否定的。

原因是:主从库的 Create Event 语句是不一样。

我们回去看看 “步骤1” 和 “步骤2”，看show create event test\G
输出结果中的黄底字部分。在create event 时，event 在主库创建后，复制到从库上是 disable 的状态！所以 MySQL 在设计之初就考虑了这个问题。event 在创建后，复制到从库之后的结构默认是 disable 的，无法执行，从而从库不会发生 event 的写入操作而导致数据不一致！

我们进一步测试验证，是不是确实如此。

#主库上执行
mysql> create table test (a int);
Query OK, 0 rows affected (0.04 sec)
mysql> select count(1) from test;
+----------+
| count(1) |
+----------+
|       52 |
+----------+
1 row in set (0.01 sec)

#从库上执行
mysql> select count(1) from test;
+----------+
| count(1) |
+----------+
|       52 |
+----------+
1 row in set (0.01 sec)

注意，也可以用对比 gtid 的方法查看从库，发现从库确实没有自身数据写入。

结论是，在已有的主从环境下，建立一个重复插入的 event，event 不会在从库上开启，不会这方面的数据重复写入，从而保证了数据的一致性。这种情况下 event 的开启没有带来坑。

但创建 event 后，主和从的 event 结构居然是不一致的！MySQL 这设计我感觉有点怪，也是这个怪事，导致了后面的两个坑。

实验二：在已有一个重复插入的 event 的主库上，扩展建立一个从库。

# 主库
mysql> show variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.12 sec)

mysql> show create event test\G
*************************** 1. row ***************************
               Event: test
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`admin`@`localhost` EVENT `test` ON SCHEDULE EVERY 1 SECOND STARTS '2022-07-02 16:20:59' ON COMPLETION PRESERVE ENABLE DO insert into fander.test values (1)
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

由于是做实验，我把主库关库使用物理冷备的方法建立从库。(具体步骤不是本文的内容)

# 物理备份扩展出来的从库
mysql> show variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.12 sec)

mysql> CHANGE MASTER TO
    ->   MASTER_HOST='localhost',
    ->   MASTER_USER='rpl_user',
    ->   MASTER_PASSWORD='password',
    ->   MASTER_PORT=3308,
    ->   master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> use fander
Database changed
mysql> show create event test\G
*************************** 1. row ***************************
               Event: test
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `test` ON SCHEDULE EVERY 1 SECOND STARTS '2022-07-02 17:02:19' ON COMPLETION PRESERVE ENABLE DO insert into fander.test values (1)
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

可以发现主从库的 Create Event 语句这时是一模一样了。下面接着测试。

#主库:
mysql>  select count(1) from test;
+----------+
| count(1) |
+----------+
|       14 |
+----------+
1 row in set (0.00 sec)

#从库:
mysql>  select count(1) from test;
+----------+
| count(1) |
+----------+
|       33 |
+----------+
1 row in set (0.00 sec)

mysql> show slave status\G
...
          Retrieved_Gtid_Set: 07b92486-64b0-11e8-b4cf-000c29c71881:501-561
            Executed_Gtid_Set: 07b92486-64b0-11e8-b4cf-000c29c71881:1-561,
59613f3a-1ee0-11e9-a9e7-000c29259487:1-13
...

以上测试用的是物理冷备。我测试在用逻辑备份主库，扩展从库后，情况一样。

在从库发生了数据插入了！

其实不用测试，想想就知道了。用主库的备份扩展从库，这个备份恢复后 show create event test\G
肯定和主库一模一样的。在他们建立复制关系之前他们就是各自独立的关系，大家都会跑 event_scheduler 里的 event，所以他们在建立复制关系之前，其实 gtid 就已经不一致了。

建立复制之前，gtid 已经不一致了，所以这个坑我就会及时发现，对吗？不！如果我这个定时插入的 event 不是每秒插入，而是定时，例如每天凌晨 0 点才插入一条，当时 gtid 并不会马上不一致，你能发现么？还有其他情况，例如没有走 gtid 的复制呢，你能发现吗？

在建立复制关系之后，这个扩展的从库 show create event test\G
 的 event 结构不会发生变化，event 结构是一致的，区别于"实验一"，"实验一"的 event 是主库建立 event 后，复制到从库后结构从 enable 变成 disable 的。

结论是:
坑1——在已有一个重复插入的 event 的主库上，利用主库的备份扩展建立一个从库，会因为从库也有启用了这个 event schedule，导致从库重复执行 insert 语句。等于计划任务重新执行了两次！一次来源于主从复制，一次来源于自身插入。数据会造成不一致！

如何避免？

建议:

1. 规范 my.cnf 模板，event_scheduler 默认设置为 off，而不是采用官方默认值 on。
2. 从库建议开启 read_only=1 或者 super_read_only=1，严格防止写入。

扩展，还有另外一种情况！

如果日常备份时备份的是文章"实验一"情况的从库，而不是备份主库，"只在从库上备份" 这个才是大家日常备份的常态吧，那么如果整个集群发生灾难，在用从库备份恢复出整个集群后，event 默认是 disable 的，是跑不了的。具体见下面: (黄底字)

[root@localhost ~]# cat all2.sql |grep "EVERY 1 SECOND"
/*!50106 CREATE*/ /*!50117 DEFINER=`root`@`localhost`*/ /*!50106 EVENT `test` ON SCHEDULE EVERY 1 SECOND STARTS '2022-07-02 17:02:19' ON COMPLETION PRESERVE DISABLE ON SLAVE DO insert into fander.test values (1) */ ;;

这个就更坑了，也就是你还需要人手修改 event 为 ENABLE 让其可以跑，基本没有人会关注这点，导致 event 没跑的后果可能就是丢失数据。

坑2——使用从库的备份恢复集群，有可能导致你的 event 没运作。

最后的建议

1. 最好不要在数据库跑 event_scheduler ，数据库是用于存放数据的，而不是跑定时任务跑逻辑的，这些需求应该放在应用层，例如 java 定时器。
2. 如果你确实还需要开启 event_scheduler ，那么我建议在 my.cnf  默认关闭，每次维护时(例如重启后)动态开启，确保只在主库上开启，这个逻辑可以做到高可用软件上，让高可用软件自动保证主库 event_scheduler=on，从库 super_read_only=on。

mysql> set global event_scheduler=on;

1. 请记住，我加粗红字提及的两个坑，避免之。