-- 修改变量 event_scheduler 来动态启用或者关闭 event mysql> show variables like '%event_scheduler%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+ 1 row in set (0.00 sec) mysql>
-- 查看 show processlist 中 event_scheduler 的信息，可以看到 stats 为 Waiting for next activation mysql> select * from information_schema.processlist where user='event_scheduler'; +-------+-----------------+-----------+------+---------+------+-----------------------------+------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +-------+-----------------+-----------+------+---------+------+-----------------------------+------+ | 12869 | event_scheduler | localhost | NULL | Daemon | 58 | Waiting for next activation | NULL | +-------+-----------------+-----------+------+---------+------+-----------------------------+------+ 1 row in set (0.00 sec) mysql>
-- 我们在从库上看下 event 的信息，可以看到 STATUS 为 SLAVESIDE_DISABLED，因此不用担心从库重复执行 event mysql> select * from information_schema.events\G *************************** 1. row *************************** EVENT_CATALOG: def EVENT_SCHEMA: universe EVENT_NAME: delete_logs_event DEFINER: root@localhost TIME_ZONE: SYSTEM EVENT_BODY: SQL EVENT_DEFINITION: DELETE FROM logs WHERE log_time EVENT_TYPE: RECURRING EXECUTE_AT: NULL INTERVAL_VALUE: 1 INTERVAL_FIELD: MINUTE SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION STARTS: 2023-06-19 00:00:00 ENDS: NULL STATUS: SLAVESIDE_DISABLED ON_COMPLETION: NOT PRESERVE CREATED: 2023-06-18 23:54:23 LAST_ALTERED: 2023-06-18 23:54:23 LAST_EXECUTED: NULL EVENT_COMMENT: ORIGINATOR: 1862993913 CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_COLLATION: utf8mb4_bin 1 row in set (0.00 sec)
修改
-- 使用 ALTER 语句修改，其他高权限用户也可以执行，且 event 的用户会变成最后一个 ALTER 的用户 mysql> ALTER EVENT delete_logs_event -> ON SCHEDULE EVERY 1 DAY STARTS '2023-06-19 00:00:00' -> DO -> DELETE FROM logs -> WHERE log_time Query OK, 0 rows affected (0.01 sec) mysql>
-- 可以看到 DEFINER 已经变成了修改的用户，且时间间隔也修改为了 1 天，DELETE 语句也去掉了 LIMIT mysql> select * from information_schema.events\G *************************** 1. row *************************** EVENT_CATALOG: def EVENT_SCHEMA: universe EVENT_NAME: delete_logs_event DEFINER: qin@% TIME_ZONE: SYSTEM EVENT_BODY: SQL EVENT_DEFINITION: DELETE FROM logs WHERE log_time EVENT_TYPE: RECURRING EXECUTE_AT: NULL INTERVAL_VALUE: 1 INTERVAL_FIELD: DAY SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION STARTS: 2023-06-19 00:00:00 ENDS: NULL STATUS: ENABLED ON_COMPLETION: NOT PRESERVE CREATED: 2023-06-18 23:54:23 LAST_ALTERED: 2023-06-19 00:23:03 LAST_EXECUTED: 2023-06-19 00:23:00 EVENT_COMMENT: ORIGINATOR: 1862993913 CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_COLLATION: utf8mb4_bin 1 row in set (0.00 sec)
删除
mysql> drop event delete_logs_event; Query OK, 0 rows affected (0.01 sec) mysql> show events\G Empty set (0.00 sec)
