How to create MySQL back up executable file?

Question

I am using php, mysql and my server is Windows Server 2003 with IIS6.

I am planning to backup my database on hourly basis. I can do the cronjob, tested by write date&time into a log file. It works perfectly.

Now I want to use the cronjob to backup my databases. How do I do that? Create a php script and let it run every hour??

Answer 1

Windows has built-in Task Scheduler which you can use to perform your backups.

Answer 2

Use the mysqldump tool to dump your databases to a file.

Answer 3

Use mysqldump. Example (with the options that I usually use):

mysqldump --single-transaction --hex-blob --opt -e --quick --quote-names -r put-your-backup-filename-here put-your-database-name-here

Answer 4

I think you can user cron job to start a copy of the db files, I don't think that cron job to run php script that makes backup is a good choice (it's complex without need it). I think that also mysqldump is a good choice, but I can't help you about it.

Answer 5

Edit: This script is intended for Unix (or variants).

I wanted to do the same (including sending an email of the backup and archiving my code/pages as well) and wrote something like this:

#! /bin/bash

NOW=`date +"%Y-%m-%d"`
MAIL_TO="[email protected]";
MAIL_SUBJECT="Hourly backup"
MAIL_MESSAGE="mail-message";

DB_FILE="backup-database-$NOW.sql.gz"
SITE_FILE="website-$NOW.tar.gz"

echo "Database dump:" >> $MAIL_MESSAGE
mysqldump --defaults-extra-file=.mysql-pwd --add-drop-table -C my_databse 2>> $MAIL_MESSAGE | gzip > $DB_FILE 2>> $MAIL_MESSAGE

echo "Site dump (www and php-include):" >> $MAIL_MESSAGE
tar -zcf $SITE_FILE /path/to/www/ /path/to/php-include/ 2>> $MAIL_MESSAGE

echo >> $MAIL_MESSAGE
echo >> $MAIL_MESSAGE
echo "Done" >> $MAIL_MESSAGE

mutt -s "$MAIL_SUBJECT" -a $DB_FILE -a $SITE_FILE $MAIL_TO < $MAIL_MESSAGE

Answer 6

As others have written, the mysqldump tool is the simplest solution, however if you have a large database, even with the --quick setting, I'd recommend you do some experimenting. With the old C-ISAM engine, queries are processed one at a time. Although this is less of an issue with InnoDB, I'm not sure whether MySQL now supports full concurrent queries. Your backup may affect the transactional processing.

If it does prove to be a problem, then a simpple solution would be to configure a slave instance of the database running on the same machine, then either run mysqldump against that, or shutdown the slave temporarily while backing up the raw data files.

Alternatively you could push the mirroring down to the OS level and perform a disk level snapshot - but you'd need to stop the transactions on the database and flush it before creating the snapshot (or breaking the mirror).

C.