Scheduled execution of MySQL database tasks

Proposition:Delete table t (ID int primary key, ptime timestamp default
Current_timestamp) records generated three days ago.

========================================================
Method 1: Operating System
========================================================

(Windows)

Scheduled operations in scheduled tasks in Windows operating systems,
Go to the Windows Command Line (START-> Run-> Enter cmd and press Enter)

C: \> at/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: \>
Status ID day Time Command Line
-------------------------------------------------------------------------------
1 each Monday... am "C: \ Program Files \ mysql
Server 5.1 \ bin \ mysql.exe "-u root-p123 csdn-e" delete from t where ptime <date _
Sub (curdate (), interval 3 day )"

C: \>

Linux

1. Create a backup. Sh file.

#! /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

If you want to delete the backup files one week or one month ago.

2. Use the crontab-e command to edit the scheduled task and add it to the next record.

30 02 *** sh/usr/local/MySQL/backup. Sh

Backup at 02:30 AM

========================================================
Method 2: MySQL event
========================================================
After MySQL 5.1.6, you can directly use a MySQL event similar to oralce job

First, check whether your MySQL has enabled this function.

Mysql> show variables like '% SC % ';
+ ----------------- + ------- +
| Variable_name | value |
+ ----------------- + ------- +
| Event_scheduler | off |
+ ----------------- + ------- +
1 row in SET (0.00 Sec)

It is not opened by default. You can go to the my. ini (Windows)/My. CNF (Linux/Unix) parameter file to define it and restart mysqld
Or set global event_scheduler = on;

Mysql> show variables like '% SC % ';
+ ----------------- + ------- +
| Variable_name | value |
+ ----------------- + ------- +
| Event_scheduler | on |
+ ----------------- + ------- +
1 row in SET (0.00 Sec)

Create this event

Mysql> Create event myevent
-> On schedule every 1 day starts '2017-10-24 02:00:00'
-> Do
-> Delete from t where ptime <date_sub (curdate (), interval 3 day );
Query OK, 0 rows affected (0.09 Sec)

Mysql> 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 | 02:00:00 | null | enabled |
+ ------ + --------- + ---------------- + ----------- + ------------ + ---------------- + ------------------- + ------ + --------- + --
1 row in SET (0.00 Sec)

Mysql>

Introduction to event (unfortunately, there is no Chinese Version)

Http://dev.mysql.com/doc/refman/5.1/en/events.html
19.4. Using the event Scheduler
19.4.1. Event scheduler Overview
19.4.2. Event Scheduler configuration
19.4.3. Event syntax
19.4.4. Event metadata
19.4.5. Event scheduler status
19.4.6. The event scheduler and MySQL privileges

Syntax and example of create event.
Http://dev.mysql.com/doc/refman/5.1/en/create-event.html
12.1.11. Create event syntax