MadMax⁸³

Recently I have been looking into ways to create a free off site back up for my MySQL database. My site is running on my own box and is primarily Data Base driven, I have never been too bothered about backing up the site, as the main code for the site doesn't change too often, so it doesn’t need a constant backup. Even if I did loose the site files, it wouldn’t take me long to put up a new site which runs off the current database structure.

I have been concerned about is loosing my MySQL Database, as that’s where all the main data for the site is stored (Blogs, Pages, Comments etc…). My server does its own backups on the box, so if you delete something by mistake, you can restore it from the back up, this wont help me if the disc in the box dies. The company I get my server from do offer a backup, but its quite an expensive monthly extra, so I decided I needed a way of keeping an off site backup of my database for free.

When I started to look into this, I worked out I could actually do it really easily, all I needed was 4 lines of code and a free Google Mail account, I chose to use Google Mail as they give you quite a lot of space and there system can handle pretty big attachments. Here is how I did it.

1. To start with setup a Gmail account to send your backups to (or you can use an existing one if you want)

2. Create a file on the server called DataBaseBackup.sh (or what ever you want to call it), I would advise doing this on an area which isn’t web accessible.

3. In that file, add the following lines of code

DATE=`date +%Y-%m-%d`
mysqldump -u username -ppassword databasename > /backups/mySQL/DBBackup-${DATE}.db.out
gzip -v9 /backups/mySQL/DBBackup-${DATE}.db.out
echo "DataBase Backed Up" | mutt -s "Site DB Backup ${DATE}" -a /backups/mySQL/DBBackup-${DATE}.db.out.gz YourGmail@Gmail.com

4. Now modify the code and add in your database username, password and the database name, then add in your gmail address.

5. Set up a cron, on Unix box’s you can usually get to this by typing “vi /etc/crontab”, once you are in there, just add the following line to the bottom of the existing items in the cron. If you dont have access to the box, there may be a Cron option on your web control panel.

0 4 * * 1 root /backups/ DataBaseBackup.sh

That’s it, this will now do a dump of the database ever Monday morning at 4am, and email it to a Gmail account. I picked Gmail as they give you a ton of space for free, but you can use any email you like really, as long as they will accept attachments big enough for your database. If you have any questions, either leave me a comment or drop me an email.