My disaster recovery needs from a database standpoint:
- Ability to restore to a point in time
- Retention period of 4 weeks
- E-mail notification on failure
MySQL Backups
A basic cron job dumping your databases on a daily basis:
shell> crontab -e
Add this to your crontab:
0 2 * * * mysqldump -ubackup_user -ppasswordhere --all-databases | gzip -c > /path/to/mysql_backups/backup_`date +%Y-%m-%d`.sql.gz
This will run at 2:00 AM daily and back up all of your databases. It will produce a fancy .sql.gz file that has a date stamp. Example: backup_2008-03-12.sql.gz. For generating Cron jobs, look at this site.
This is nice, but you’ll have to pay attention to your backup folder because eventually you will run out of disk space. You will not receive any notification when the backups fail or when you run out of disk space from this method. You also see that it gives anyone who has access to your crontab access to your password. Make sure this is a database user that has READ ONLY access to all dbs/tables.
SQL Server 2005 Backups
Some will say I’m a little biased… but this is one of the parts of SQL Server that makes it worth the price. If an image is blurry in the display, click it for full resolution.
- Create a new Maintenance plan
- Click ‘Back Up Database’ on the toolbox.
- Drag to the blank window.
- Select your databases, and various options for your backups. I recommend using ‘verify backup integrity’ and having your .bak files moved off site.
- Schedule your maintenance plan.
- Add a notify operator task (you must have Database Mail Configured) , and drag the red node to it. This will email your specified operator (I get them sent to my phone) when a backup fails.
- Clean up! I do separate a weekly data cleanup maintenance plan to check my indexes, delete my backups older than a month, and lots of other things. Here’s a screenshot:
Clearly, SQL Server 2005 meets and exceeds my disaster recovery needs over MySQL. Did I mention it was extremely easy to set up in SQL Server Management Studio?
I didn’t have to research bash datestamps or find a site to generate crontabs.
Tim suggests I use this perl script for deleting old MySQL backups (run at the same interval as the backups):
#!/usr/bin/perl
use File::Find;
my $path = shift;
my $threshold = 60*60*24*28;
find(sub { /mysqlbackup\.tar\.gz$/i and
unlink "$_" and
print "$File::Find::name deleted\n\n" and
(( ((stat($_))[9] ) + $threshold) > time);
}, $path);
Also from the comments:
find /path/to/mysql_backups/ -type f -mtime +N -exec rm -rf {} \;
USE AT YOUR OWN RISK.
If you want to automatically purge old MySQL backups based on being N days old you can just slap your dump command into shell script with the following command. Then schedule the shell script via cron.
find /Your_backup/directory -type f -mtime +N -exec rm -rf {} \;
Replace N with the number of days you want to keep on hand. This command uses the timestamps on the file to determine whether or not to delete it.
Uh, microsoft cronnie.
Hello have you considered using the MySQL UI tools?
That would be a more likely comparison. And just easy although maybe not as many options.
@meregistered You’re right. I’ve always had problems with MySQL’s GUI tools… particularly not wanting to run them on localhost.
The only feature that I see missing from MySQL GUI tools is the ability to clean out your backups on a regular basis.