With the recent posts i've come across on tragic downtime due to database corruption, I thought it might be beneficial to share a set of .php scripts I've found (http://www.dagondesign.com/articles/automatic-mysql-backup-script/) and have been using to maintain hourly & daily DB backups of my site.
I have attached two text files that should be renamed to .php:
backup_dbs.txt is the meat and potatoes, no changes are really required under this file.
backup_dbs_config.txt is the file that you need to define your credentials in, e-mail address for alerting, destination directories and compression. As it stands, this script will backup every database that the provided credentials have access to, and pack them up in a nice & tidy compressed file.
It is really quite simple, after making the required parameter changes to backup_dbs_config & renaming both files from .txt to .php, find a spot on your server to run them from (they should not be accessible via public web, keep them completely internal).
After you set up your directory to run from and create a directory to store the backup files, just create a cron job that calls backup_dbs.php at whichever interval you prefer.
In my configuration, I actually duplicated the scripts and designated one set for hourly. This allows me to run a daily backup at 1am and place the files in one directory, and run the other set of scripts every hour and place them in another hourly directory. I then created another cron job that will run every day and purge all files older than 3 days from my hourly directory, otherwise it grows quite large.
The purge Cron command looks like this:
find /var/path/to/sqlbackup/bkup-hourly/ -mtime +3 -exec rm -f {} \;
I don't purge my daily backups automatically, instead I'll manually clean those up on a quarterly basis. I will typically keep one backup from every month prior to the current & keep dailys for the current month only.
I will warn you that while this is a nice 'set it and forget it solution', if you let it go for too long without manual or automatic purging you will quickly run out of disk space - which in turn can cause corruption and you'll be faced with the outage you were trying to avoid!
It is also good practice to pull your backup files to a destination other than your production web host for safe keeping.
This has already saved my bacon on more than one occasion with minimal interruption to service. Additionally, you have archived content that you can now access if it were to ever become deleted. This can be very useful if you were to ever get into a legality issue with members & need to pull up incriminating evidence that they tried to destroy. If/when authorities ever come knocking, you don't want to turn up empty handed.
