MySQL 创建定时任务 详解
自 mysql5.1.6起,增加了一个非常有特色的功能–事件调度器(event scheduler),可以用做定时执行某些特定任务,来取代原先只能由操作系统的计划任务来执行的工作。事件调度器有时也可称为临时触发器(temporal triggers),因为事件调度器是基于特定时间周期触发来执行某些任务,而触发器(triggers)是基于某个表所产生的事件触发的,区别也就在这里
在使用这个功能之前必须确保 event_scheduler 已开启,可执行 :
mysq> set global event_scheduler = 1;
# 或
mysql> set global event_scheduler = on;
也可以在配置文件中添加设置 : event_scheduler=1
也可以直接在启动命令加上 : --event_scheduler=1
查看当前是否已开启事件调度器 :
mysql> show variables like 'event_scheduler';
+-----------------+-------+
| variable_name | value |
+-----------------+-------+
| event_scheduler | on |
+-----------------+-------+
1 row in set (0.01 sec)
# 或
mysql> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| on |
+-------------------+
1 row in set (0.00 sec)
# 或
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| id | user | host | db | command | time | state | info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 6 | root | localhost | null | query | 0 | starting | show processlist |
| 7 | event_scheduler | localhost | null | daemon | 81 | waiting on empty queue | null |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)
创建事件(create event)
create event [ifnot exists] event_name
on schedule schedule
[on completion [not] preserve]
[enable | disable]
[comment 'comment']
do sql_statement;
[on completion [not] preserve] 可以设置这个事件是执行一次还是持久执行,默认为 not preserve
[enable | disable] 可是设置该事件创建后状态是否开启或关闭,默认为enable
[comment 'comment'] 可以给该事件加上注释
# 创建表
mysql> use test;
mysql> create table aaa(timeline timestamp);
# 每秒插入一条记录到数据表
mysql> create event e_test_insert on schedule every 1 second
-> do insert into test.aaa values(current_timestamp);
mysql> select * from aaa;
# 5天后清空 test表
mysql> create event e_test
-> on schedule at current_timestamp + interval 5 day
-> do truncate table test.aaa;
# 2018年8月8日8点整清空 test表
mysql> create event e_test
-> on schedule at timestamp '2018-08-08 8:00:00'
-> do truncate table test.aaa;
# 每天定时清空 test表
mysql> create event e_test
-> on schedule every 1 day
-> do truncate table test.aaa;
# 5天后开启每天定时清空test表
mysql> create event e_test
-> on schedule every 1 day
-> starts current_timestamp + interval 5 day
-> do truncate table test.aaa;
# 每天定时清空 test表,5天后停止执行
mysql> create event e_test
-> on schedule every 1 day
-> ends current_timestamp + interval 5 day
-> do truncate table test.aaa;
# 5天后开启每天定时清空test表,一个月后停止执行
mysql> create event e_test
-> on schedule every 1 day
-> starts current_timestamp + interval 5 day
-> ends current_timestamp + interval 1 month
-> do truncate table test.aaa;
# 每天定时清空test表(只执行一次,任务完成后就终止该事件)
mysql> create event e_test
-> on schedule every 1 day
-> on completion not preserve
-> do trunca tetable test.aaa;
修改事件(alter event)
alter event event_name
[on schedule schedule]
[rename to new_event_name]
[on completion [not] preserve]
[comment 'comment']
[enable | disable]
[do sql_statement]
# 临时关闭事件
mysql> alter event e_test disable;
# 开启事件
mysql> alter event e_test enable;
# 将每天清空test表改为5天清空一次
mysql> alter event e_test on schedule every 5 day;
注 : 如果将 event 执行 alter event event_name disable. 那么当重新启动 mysql服务器后,该 event 将被删除
删除事件(drop event)
drop event [if exists] event_name
# 删除创建的 e_test事件
mysql> drop event e_test; # 如果该事件不存在将会产生 error 1513 (hy000): unknown event 错误,因此最好加上if exists
mysql> drop event if exists e_test;
每秒钟调用一次存储过程,用于判断 slave 是否正常运行,如果发现 slave 关闭了,忽略 0 次错误,然后重新启动 slave
mysql> delimiter //
-> create procedure slave_monitor()
-> begin
-> select variable_value into @slave_status from information_schema.global_status wherevariable_name='slave_running';
-> if ('on'!= @slave_status) then
-> set global sql_slave_skip_counter=0;
-> slave start;
-> endif;
-> end; //
-> delimiter;
# 由于存储过程中无法调用类似 show slave status 这样的语句,因此无法得到确切的复制错误信息和错误代码,不能进一步的处理 slave 停止的各种情况
mysql> create event if not exists slave_monitor
-> on schedule every 5 second
-> on completion preserve
-> do call slave_monitor();
————————————————
版权声明:本文为csdn博主「小柴的生活观」的原创文章,遵循 cc 4.0 by-sa 版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/chenshun123/article/details/79677193