Easy & free automated Database backups

So you built something, its running, and now you need to have some kind of automated database backup running.

Wouldn’t it be nice to just be able to implement this with a few lines of code and a git repository ? Right — that’s what I thought too.

Lately I’ve been working a lot with sites, servers and users in low-bandwidth areas , and I’ve had to come up with a database backup solution that would run on almost any system, and that would not require much bandwidth.

One solution I’ve found that have worked particularly well for me in these circumstances is a small shell script which dumps the database to a sql file, and then the changes are pushed to a git repository.

This approach has a few benefits:

• Easy to set up — you only need git repository, a tiny shell script and a cron job
• Low Bandwidth — By using git, you will only ever push the changed lines in your sql backup — not the entire file.
• Easy to track changes — By using git — you can easily browse the commit’s using your favourite UI to see what changes has actually been made to the database

How to do it

• Create a git repository that will hold your database backup ( Bitbucket allows free private repositories )
• On your server, clone the git repository into a folder.

git clone git@bitbucket.org:yourusername/db-backup.git .

• Create a shell script, lets call it run-db-backup.sh, that will dump your database into this folder, and then add the changes, commit and push to git

#!/bin/bash
localPATH=’pwd’
echo “Dump Database”
cd /home/ec2-user/db-backup
mysqldump -u dbusername -p”yourdbpasswordhere” db_name > db_backup.sql
echo “Commit changes to git and push”
git add .;git commit -m “Latest Database Snapshot”; git push origin master
echo “Done”

• Create a cron job to execute your new shell script at your desired interval

0 */6 * * * /home/ec2-user/run_db_backup.sh

• Now you can log into your git repository and watch your automated database backups flow in. :)

Bitbucket commit list

Inspect commit changes

Restore backup

To restore a specific commit from the repository, just reference your commit ID/hash when doing a git checkout:

git checkout 0d1d7fc32 (where 0d1d7fc32 would be your commit hash)

Now you can import your db-backup.sql file into your database and have a well deserved cup of coffee.