MYSQL 基本操作之11(事件调度器)
2019-09-26 本文已影响0人
轻飘飘D
- 查看是否已开启事件调度器
#MYSQL 8 默认开启 （低版本在 my.cnf 的 mysqld 下配置 event_scheduler=1）
[root@XAG816 etc]# cat /etc/my.cnf | grep event_scheduler
root@127.0.0.1 : testdb【07:54:47】2 SQL->SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
root@127.0.0.1 : testdb【07:55:36】3 SQL->SELECT @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| ON |
+-------------------+
- 创建测试表
create table test_scheduler
(
sch_seq int auto_increment not null,
sch_name varchar(50),
sch_createtime datetime,
primary key(sch_seq)
) auto_increment=1001;
root@127.0.0.1 : testdb【08:11:07】61 SQL->insert into test_scheduler(sch_name,sch_createtime) values(uuid(),now());
root@127.0.0.1 : testdb【08:12:02】62 SQL->select * from test_scheduler;
+---------+----------+---------------------+
| sch_seq | sch_name | sch_createtime |
+---------+----------+---------------------+
| 1001 | sch01 | 2019-08-31 20:12:02 |
+---------+----------+---------------------+
- 创建存储过程
DROP PROCEDURE IF EXISTS proc_test_scheduler;
DELIMITER $$
create procedure proc_test_scheduler()
begin
delete from test_scheduler where sch_createtime<date_sub(now(),interval 5 minute);
insert into test_scheduler(sch_name,sch_createtime) values('sch01',now());
end$$
DELIMITER ;
- 调用存储过程
root@127.0.0.1 : testdb【08:24:08】124 SQL->select * from test_scheduler;
+---------+----------+---------------------+
| sch_seq | sch_name | sch_createtime |
+---------+----------+---------------------+
| 1001 | sch01 | 2019-08-31 20:12:02 |
+---------+----------+---------------------+
root@127.0.0.1 : testdb【08:24:31】125 SQL->call proc_test_scheduler();
root@127.0.0.1 : testdb【08:24:43】126 SQL->select * from test_scheduler;
+---------+----------+---------------------+
| sch_seq | sch_name | sch_createtime |
+---------+----------+---------------------+
| 1002 | sch01 | 2019-08-31 20:24:43 |
+---------+----------+---------------------+
root@127.0.0.1 : testdb【08:24:46】127 SQL->call proc_test_scheduler();
root@127.0.0.1 : testdb【08:24:52】128 SQL->select * from test_scheduler;
+---------+----------+---------------------+
| sch_seq | sch_name | sch_createtime |
+---------+----------+---------------------+
| 1002 | sch01 | 2019-08-31 20:24:43 |
| 1003 | sch01 | 2019-08-31 20:24:52 |
+---------+----------+---------------------+
- 创建事件语法
CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE] -- 事件执行完成后是否被删除,默认 NOT PRESERVE（不保留），即非周期性事件默认完成后就会被删除
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement;
schedule:
AT TIMESTAMP [+ INTERVAL INTERVAL]
| EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]
INTERVAL:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH
6.创建事件
#每30秒调用一次 proc_test_scheduler()
DROP EVENT IF EXISTS even_test_scheduler;
CREATE EVENT IF NOT EXISTS even_test_scheduler
ON SCHEDULE EVERY 30 SECOND
DO call proc_test_scheduler();
root@127.0.0.1 : testdb【02:40:54】13 SQL->SELECT * FROM information_schema.EVENTS \G;
*************************** 1. row ***************************
EVENT_CATALOG: def
EVENT_SCHEMA: testdb
EVENT_NAME: even_test_scheduler
DEFINER: root@localhost
TIME_ZONE: SYSTEM
EVENT_BODY: SQL
EVENT_DEFINITION: call proc_test_scheduler()
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
INTERVAL_VALUE: 30
INTERVAL_FIELD: SECOND
SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
STARTS: 2019-09-01 14:40:52
ENDS: NULL
STATUS: ENABLED
ON_COMPLETION: NOT PRESERVE
CREATED: 2019-09-01 14:40:52
LAST_ALTERED: 2019-09-01 14:40:52
LAST_EXECUTED: 2019-09-01 06:40:53
EVENT_COMMENT:
ORIGINATOR: 8
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
DATABASE_COLLATION: utf8mb4_0900_ai_ci
root@127.0.0.1 : testdb【02:41:56】18 SQL->show events\G
*************************** 1. row ***************************
Db: testdb
Name: even_test_scheduler
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 30
Interval field: SECOND
Starts: 2019-09-01 14:40:52
Ends: NULL
Status: ENABLED
Originator: 8
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
root@127.0.0.1 : testdb【02:57:24】22 SQL->show processlist;
+----+-----------------+-----------------+--------+---------+------+-----------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+--------+---------+------+-----------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 10 | Waiting for next activation | NULL |
| 8 | root | localhost:29260 | testdb | Query | 0 | starting | show processlist |
+----+-----------------+-----------------+--------+---------+------+-----------------------------+------------------+
root@127.0.0.1 : testdb【02:41:49】17 SQL->select * from test_scheduler;
+---------+----------+---------------------+
| sch_seq | sch_name | sch_createtime |
+---------+----------+---------------------+
| 1004 | sch01 | 2019-09-01 14:40:53 |
| 1005 | sch01 | 2019-09-01 14:41:22 |
| 1006 | sch01 | 2019-09-01 14:41:52 |
+---------+----------+---------------------+
- 调度举例
#一分钟后执行
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
#定时执行在2018-02-01 16:40:00 执行
ON SCHEDULE AT TIMESTAMP '2018-02-01 16:40:00'
#定时定期任务 xx时间后,每隔一段时间执行一次 (5秒之后,每3秒定时清空tdemo表)
ON SCHEDULE EVERY 3 SECOND
STARTS CURRENT_TIMESTAMP + INTERVAL 5 SECOND
#间隔执行定时终止 每隔一秒钟执行一次,10秒后终止执行
ON SCHEDULE EVERY 1 SECOND
ENDS CURRENT_TIMESTAMP + INTERVAL 10 SECOND
#时开启间隔执行定时结束 --5秒开启,每秒执行,10秒钟停止执行
ON SCHEDULE EVERY 1 SECOND
STARTS CURRENT_TIMESTAMP + INTERVAL 5 SECOND
ENDS CURRENT_TIMESTAMP + INTERVAL 15 SECOND
#每天定时1点执行
ON SCHEDULE EVERY 1 DAY
STARTS '2010-12-18 01:00:00'
#每天定时清空evnt表(只执行一次,任务完成后就终止该事件)
create evnet e_evnt_3
on schedule every 1 day
on completion not preserve
do truncate table yang.evnt
- 修改语法
ALTER EVENT event_name
[ON SCHEDULE schedule]
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT 'comment']
[ENABLE | DISABLE] [DO sql_statement]
#举例
#临时关闭某个event
ALTER EVENT even_test_scheduler DISABLE;
#开启某个event
ALTER EVENT even_test_scheduler ENABLE;
- events 表数据字典
root@127.0.0.1 : testdb【03:06:26】25 SQL->select * from information_schema.events \G
*************************** 1. row ***************************
EVENT_CATALOG: def
EVENT_SCHEMA: testdb
EVENT_NAME: even_test_scheduler
DEFINER: root@localhost
TIME_ZONE: SYSTEM
EVENT_BODY: SQL
EVENT_DEFINITION: call proc_test_scheduler()
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
INTERVAL_VALUE: 30
INTERVAL_FIELD: SECOND
SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
STARTS: 2019-09-01 14:40:52
ENDS: NULL
STATUS: ENABLED
ON_COMPLETION: NOT PRESERVE
CREATED: 2019-09-01 14:40:52
LAST_ALTERED: 2019-09-01 14:40:52
LAST_EXECUTED: 2019-09-01 07:06:22
EVENT_COMMENT:
ORIGINATOR: 8
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
DATABASE_COLLATION: utf8mb4_0900_ai_ci
#相关参数介绍：
EVENT_CATALOG：一般都是def，不管
EVENT_SCHEMA：event所在的schema
EVENT_NAME：event的名称
DEFINER：event的定义者，和定义这个event时，默认selectcurrent_user()的结果一致，如果该user有super权限，可以指定为其他用户
TIME_ZONE：event使用的时区，默认是system，建议别做修改
EVENT_BODY：一般都是SQL，不用管
EVENT_DEFINITION：该event的内容，可以是具体的insert等SQL，也可以是一个调用存储过程的操作
EVENT_TYPE：这个参数比较重要，定义的时候指定，有两个值：RECURRING和ONE TIME，RECURRING表示只要符合条件就会重复执行，而ONE TIME只会调用一次
EXECUTE_AT： 针对one-time类型的event有效，如果是RECURRING类型的event一般为NULL，表示该event的预计执行时间
INTERVAL_VALUE：针对RECURRING类型的event有效，表示执行间隔长度
INTERVAL_FIELD：针对RECURRING类型的event有效，表示执行间隔的单位，一般是SECOND，DAY等值，可参考创建语法
SQL_MODE：当前event采用的SQL_MODE
STARTS：针对RECURRING类型的event有效，表示一个event从哪个时间点点开始执行，和one-time的EXECUTE_AT功能类似。为NULL表示一符合条件就开始执行
ENDS：针对RECURRING类型的event有效，表示一个event到了哪个时间点后不再执行，如果为NULL就是永不停止
STATUS：一般有三个值，ENABLED, DISABLED和 SLAVESIDE_DISABLED，其中ENABLED表示激活这个event，该event只要符合其他条件就会执行；DISABLED状态改event将不会执行，SLAVESIDE_DISABLED表示在从库上不执行该event。需要特别注意在从库上不要执行任何形式的event，因为如果主库执行一次，复制到从库后，从库再执行一次的话，那就数据不一致了，一般来说直接禁用掉从库上的总开关event_scheduler就行。
ON_COMPLETION：只有两种值，PRESERVE和NOT PRESERVE，PRESERVE
CREATED：event的创建时间
LAST_ALTERED：event最新一次被修改的时间
LAST_EXECUTED：event最近一次执行的时间，如果为NULL表示从未执行过
EVENT_COMMENT：event的注释信息
ORIGINATOR：当前event创建时的server-id，用于主从上的处理，比如SLAVESIDE_DISABLED
CHARACTER_SET_CLIENT：event创建时的客户端字符集，即character_set_client
COLLATION_CONNECTION：event创建时的连接字符校验规则，即collation_connection
DATABASE_COLLATION：event创建时的数据库字符集校验规则
- 主从复制对事件的影响
对于主从架构的数据库要注意将从库的任务调度关闭，以防止在主库执行了之后，从库又重复在备库进行调度evnet。
在主库创建：
mysql> create event e_insert on schedule every 2 second do insert into test.evnt values (now());
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT EVENT_SCHEMA,EVENT_NAME,EVENT_DEFINITION,INTERVAL_FIELD,STATUS,LAST_EXECUTED FROM information_schema.EVENTS\G
*************************** 1. row ******************
EVENT_SCHEMA: test
EVENT_NAME: e_insert
EVENT_DEFINITION: insert into test.evnt values (now())
INTERVAL_FIELD: SECOND
STATUS: ENABLED
LAST_EXECUTED: NULL
在从库查看：
mysql> SELECt EVENT_SCHEMA,EVENT_NAME,EVENT_DEFINITION,INTERVAL_FIELD,STATUS,LAST_EXECUTED FROM information_schema.EVENTS\G
*************************** 1. row *******************
EVENT_SCHEMA: test
EVENT_NAME: e_insert
EVENT_DEFINITION: insert into test.evnt values (now())
INTERVAL_FIELD: SECOND
STATUS: SLAVESIDE_DISABLED
LAST_EXECUTED: NULL
1 row in set (0.00 sec)
新创建的event在master上的状态是ENABLED，在slave上的状态是SLAVESIDE_DISABLED。
另外，在主库上修改event的status状态也不会改变slave上面的状态。
总结一下主从对event_scheduler的影响：
1. 在主库上新建event，在slave上event的状态为SLAVESIDE_DISABLED没有影响。
2. 通过xtrabackup恢复出来的从库，如果有events那么需要在slave上把event_scheduler设置为off，并且检查 events的status状态,如果是enable，则需要关闭事件调度器。
3. 当主从发生切换时，需要人工（或者有配套的机制）来维护event的状态。新主库
# 新主库
set global event_scheduler=on;
alter event e_insert enable;
# 新从库
set global event_scheduler=off;
alter event e_insert disable on slave;
4. 在主库上对event的进行状态修改不影响从库的状态。
