Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When scheduling database backups on MySQL Server (v5.1.45-community) running on Windows Server 2008, what mechanism is used to trigger the backups to run? I think it's using the Windows Task Scheduler, but I don't see any obvious tasks for these backups. SQL Server uses a separate agent service. Is there something similar in MySQL?

ADDITIONAL INFO: The backups were setup with MySQL Administrator. I'm aware of how to schedule the backups, but they're not running and I'm looking for insight as to where to begin trouble shooting.

share|improve this question

3 Answers 3

Are you running MySQL Administrator? That includes a backup scheduling mechanism.

share|improve this answer
    
+1 for helping me clarify the question. –  Scott Mayfield Feb 21 '11 at 1:46

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

share|improve this answer

we've been using todo backup server for a long time. it offers full, incremental, and differential backup. we usually set up a daily backup schedule, and save three versions of the images, which means that the software automatically delete the old images. a specific file can also be restored in Windows explorer from a folder or partition backup image. we are small business users, and cannot afford some backup software costing 1k dollar. so we found todo backup suits us very well.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.