Don’t make the same mistake I did; back up your databases

Ok, listen, I’m not going to get into the details or whatever, but it was recently brought to my attention that I really, really should be backing up my MySQL databases on a daily basis at least.  So here’s the recipe.  Modify and use it for your own purposes, and don’t be as dumb as I am.

Create a directory to hold your backups.
Create the following shell script in your backup directory.  Substitute your own info for the stuff in brackets.

backup_script.sh:

#!/bin/sh
logfile=/[PATH-TO-BACKUPS]/backup_script.log
echo "------------" >> $logfile
echo "Starting MySQL Database backup script" >> $logfile
location=/[PATH-TO-BACKUPS]/backup_"$(date +'%d_%m_%Y_%H_%M_%S')".sql
mysqldump -u root --password='[YOUR-PASSWORD]' --opt [YOUR-DATABASE] > $location
echo "Completed MySQLDump." >> $logfile
gzip $location
echo "GZipped the backup file "$location >> $logfile
echo "Removing backups older than 10 days." >> $logfile
find /[PATH-TO-BACKUPS]/ -maxdepth 1 -type d -mtime +10 -exec rm -rf {} \;
echo "Old backups removed." >> $logfile
echo "Backup script completed on "$(date +'%d_%m_%Y_%H_%M_%S') >> $logfile
echo "------------" >> $logfile
exit 0


Set the permissions to 700, to make sure nobody can see your plaintext MySQL password.

Edit your cron tab to run the shell script once a day (or however often you think is prudent.)

To unzip the backed up SQL file:

gunzip -v [YOUR-BACKUP-FILE].gz


To restore your backed up SQL dump:

mysql [YOUR-DATABASE] < [YOUR-BACKUP-FILE].sql


Take it from me, kids. Only fools don’t back up their work.