MySQL8.0的参数event_scheduler默认是ON，请注意一些坑

event_scheduler是什么？

event_scheduler是什么MySQL定时器的开关，类似于windows操作系统的定时任务的概念，指定某个时间点执行一次定时任务，或者每隔一段时间循环执行定时任务。

这个东西有企业在用么？

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

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

首先这个事情并没有实际发生在我生产环境，是人为想出来的一种场景。

准备:
两个MySQL8.0数据库实例
mysql3308
mysql33081

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

MySQL8.0的默认值即为:
mysql> show variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.12 sec)

我给大家做了以下的测试。

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

主库上执行
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=`root`@`localhost` EVENT `test` ON SCHEDULE EVERY 1 SECOND STARTS '2019-01-23 14:55: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)

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

从库上执行
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=`root`@`localhost` EVENT `test` ON SCHEDULE EVERY 1 SECOND STARTS '2019-01-23 14:55:19' 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)

有没有发现问题？

主从环境的event_scheduler都是开启的。这意味这从库有可能会重复写入数据。一部分数据来源于主库的event的循环插入的复制，一部分数据来源于从库自身的event的循环插入。
主从库的Create Event语句不一样？
在create event时，event在主库创建后，复制到从库上是disable的状态！所以MySQL在设计之初就考虑了这个问题。event在从库是disable的，无法执行，从而从库不会发生写入操作而导致数据不一致！

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

主库上执行
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的主库上，扩展建立一个从库。

主库:
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=`root`@`localhost` EVENT `test` ON SCHEDULE EVERY 1 SECOND STARTS '2019-01-23 14:55: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.06 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 '2019-01-23 14:55: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
...

以上测试用的是物理冷备。我测试在用逻辑备份主库，扩展从库后，情况一样。
结论是，在已有一个重复插入的event的主库上，扩展建立一个从库，会因为从库也有这个event schedule，导致从库重复执行insert语句。等于计划任务重新执行了两次！数据会造成不一致！

如何避免？

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

扩展

如果日常备份时备份的是文章"一"情况的从库，那么恢复数据库后，event默认是disable的，是跑不了的。具体见下面:

[root@192-168-199-198 ~]# cat all2.sql |grep "EVERY 1 SECOND"
/*!50106 CREATE*/ /*!50117 DEFINER=`root`@`localhost`*/ /*!50106 EVENT `test` ON SCHEDULE EVERY 1 SECOND STARTS '2019-01-23 16:03:16' ON COMPLETION PRESERVE DISABLE ON SLAVE DO insert into fander.test values (1) */ ;;

你需要人手修改event让其可以跑。这也属于一个坑吧。

posted on 2019-01-23 16:20 fanderchan 阅读(10390) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

fanderchan