命题: 每日 02:00 am 定时删除 表 t (id int primary key,ptime TIMESTAMP DEFAULT CURRENT_TIMESTAMP) 中三天以前的记录。
=======================================
方法一:操作系统
=======================================
(Windows)
在WINDOWS 操作系统中 的计划任务中定时操作,
进入WINDOWS的命令行, (开始->运行-> 输入 cmd 回车)
at 02:00 /every:M,T,W,Th,F,S,Su "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe" -u root -p123 csdn -e "delete from t where ptime <date_sub(curdate(),interval 3 day)"
Added a new job with job ID = 1
C:\>at
Status ID Day Time Command Line
-------------------------------------------------------------------------------
1 Each Monday... 上午 02:00 "C:\Program Files\MySQL\MySQL
Server 5.1\bin\mysql.exe" -u root -p123 csdn -e "delete from t where ptime <date_
sub(curdate(),interval 3 day)"
(linux)
1、创建backup.sh文件
#!/bin/sh
export MYSQLBAKDATE=`date +%Y%m%d`
# echo $MYSQLBAKDATE
export OUTFILE=$MYSQLBAKDATE'.TestDB.dbf'
# echo $OUTFILE
rm -f /bak/$OUTFILE
/usr/local/mysql/bin/mysqldump -uroot -p****** -R TestDB > /bak/$OUTFILE
gzip /bak/$OUTFILE
#END
如果考虑得再周全点,可以考虑删除一个星期或一个月之前的备份文件。
2、使用crontab -e命令编辑计划任务,加入以下一条记录
30 02 * * * sh /usr/local/mysql/backup.sh
表示在凌晨2点30分做备份
=======================================
方法二:MySQL计划任务(事件调度器)(Event Scheduler)
=======================================
MySQL 5.1.6 之后,你可以直接使用类似于oralce job的mysql event
首先,检查一下你的MYSQL是否打开了这项功能。
show variables like '%sc%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.00 sec)
默认下是未打开的,你可以到 my.ini (windows)/ my.cnf (linux/unix) 参数文件中定义后重启mysqld
或者直接 set global event_scheduler=on;
show variables like '%sc%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec)
创建这个event
CREATE EVENT myevent ON SCHEDULE EVERY 1 DAY STARTS '2009-10-24 02:00:00' DO delete from t where ptime <date_sub(curdate(),interval 3 day);
show events;
+------+---------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+--
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | O
+------+---------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+--
| csdn | myevent | root@localhost | SYSTEM | RECURRING | NULL | 1 | DAY | 2009-10-24 02:00:00 | NULL | ENABLED |
+------+---------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+--
1 row in set (0.00 sec)
更多示例:
1)首先来看一个简单的例子来演示每秒插入一条记录到数据表
USE test;
CREATE TABLE aaa (timeline TIMESTAMP);
CREATE EVENT e_test_insert
ON SCHEDULE EVERY 1 SECOND
DO INSERT INTO test.aaa VALUES (CURRENT_TIMESTAMP);
等待3秒钟后,再执行查询成功。
2) 5天后清空test表:
CREATE EVENT e_test
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
3) 2007年7月20日12点整清空test表:
CREATE EVENT e_test
ON SCHEDULE AT TIMESTAMP '2007-07-20 12:00:00'
DO TRUNCATE TABLE test.aaa;
4) 每天定时清空test表:
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
DO TRUNCATE TABLE test.aaa;
5) 5天后开启每天定时清空test表:
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
6) 每天定时清空test表,5天后停止执行:
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
7) 5天后开启每天定时清空test表,一个月后停止执行:
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;
[ON COMPLETION [NOT] PRESERVE]可以设置这个事件是执行一次还是持久执行,默认为NOT PRESERVE。
8) 每天定时清空test表(只执行一次,任务完成后就终止该事件):
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
ON COMPLETION NOT PRESERVE
DO TRUNCATE TABLE test.aaa;
[ENABLE | DISABLE]可是设置该事件创建后状态是否开启或关闭,默认为ENABLE。
[COMMENT ‘comment’]可以给该事件加上注释。
修改事件(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]
1) 临时关闭事件
ALTER EVENT e_test DISABLE;
2) 开启事件
ALTER EVENT e_test ENABLE;
3) 将每天清空test表改为5天清空一次:
ALTER EVENT e_test
ON SCHEDULE EVERY 5 DAY;
删除事件(DROP EVENT)
语法很简单,如下所示:
DROP EVENT [IF EXISTS] event_name
例如删除前面创建的e_test事件
DROP EVENT e_test;
当然前提是这个事件存在,否则会产生ERROR 1513 (HY000): Unknown event错误,因此最好加上IF EXISTS
DROP EVENT IF EXISTS e_test;
关于EVENT的介绍(很遗憾,没有中文版的)
http://blog.csdn.net/lxgwm2008/article/details/9088521
http://dev.mysql.com/doc/refman/5.1/en/events.html
关于create event 的语法和例子。
http://dev.mysql.com/doc/refman/5.1/en/create-event.html