Within MySQL 5.1+, there exists the ability to create event scheduling.
Here is an example of how to schedule the deleting of rows from a table that are more than 2 hours old:
drop database rolando;
create database rolando;
use rolando
create table mydata (id int not null auto_increment primary key,ti_time timestamp DEFAULT current_timestamp) ENGINE=MyISAM;
DELIMITER $$
DROP PROCEDURE IF EXISTS `rolando`.`LoadMyData` $$
CREATE PROCEDURE `rolando`.`LoadMyData` ()
BEGIN
DECLARE NDX INT;
SET NDX = 0;
WHILE NDX < 100 DO
INSERT INTO mydata (ti_time) VALUES (NOW() - INTERVAL CEILING(14400*RAND()) SECOND);
SET NDX = NDX + 1;
END WHILE;
END $$
DELIMITER ;
show create table mydata\G
SHOW CREATE PROCEDURE LoadMyData\G
CALL rolando.LoadMyData();
CREATE TABLE ta_table LIKE mydata;
ALTER TABLE ta_table DISABLE KEYS;
INSERT INTO ta_table SELECT SQL_NO_CACHE * FROM mydata;
ALTER TABLE ta_table ENABLE KEYS;
CREATE EVENT ev
ON SCHEDULE
EVERY 1 MINUTE
STARTS (NOW() + INTERVAL 1 MINUTE)
DO
DELETE FROM ta_table WHERE ti_time > NOW() - INTERVAL 2 HOUR;
SELECT COUNT(1) FROM ta_table;
SELECT SLEEP(62);
SELECT COUNT(1) FROM ta_table;
In order to activate event scheduling you must do two things:
First, activate by hand:
SET GLOBAL event_scheduler = 1;
Then, add this to my.ini to keep the event scheduler in case you restart mysql
[mysqld]
event_scheduler = 1;
From here, you can design your own customer backup procedure using stored procedures and then schedule that stored procedure. However, the one drawback I have heard of was updating InnoDB tables within stored procedure called from an event I once tried to help troubleshoot this.
A more stable approach without using MySQL Events is also the simpler approach.
Write a DOS batch file that would execute a mysqldump and save the output to the destination file of your choice. Then, simply use the Windows scheduler to call that DOS batch file.
Give it a Try !!!
