Elsewhere > Backup your Database in Git
Posted 2009-05-08 on viget.com
Short version: dump your production database into a git repository for an instant backup solution.
Long version: keeping backups of production data is fundamental for a well-run web application, but it’s tricky to maintain history while keeping disk usage at a reasonable level. You could continually overwrite the backup with the latest data, but you risk automatically replacing good data with bad. You could save each version in a separate, timestamped file, but since most of the data is static, you would end up wasting a lot of disk space.
When you think about it, a database dump is just SQL code, so why not manage it the same way you manage the rest of your code — in a source code manager? Setting such a scheme up is dead simple. On your production server, with git installed:
mkdir -p /path/to/backup
cd /path/to/backup
mysqldump -u [user] -p[pass] --skip-extended-insert [database] > [database].sql
git init
git add [database].sql
git commit -m "Initial commit"
The --skip-extended-insert option tells mysqldump to give each table
row its own insert statement. This creates a larger initial commit
than the default bulk insert, but makes future commits much easier to
read and (I suspect) keeps the overall repository size smaller, since
each patch only includes the individual records added/updated/deleted.
From here, all we have to do is set up a cronjob to update the backup:
0 * * * * cd /path/to/backup && \
mysqldump -u [user] -p[pass] --skip-extended-insert [database] > [database].sql && \
git commit -am "Updating DB backup"
You may want to add another entry to run
git gc
every day or so in order to keep disk space down and performance up.
Now that you have all of your data in a git repo, you’ve got a lot of
options. Easily view activity on your site with git whatchanged -p.
Update your staging server to the latest data with
git clone ssh://[hostname]/path/to/backup. Add a remote on
Github and get offsite backups with a simple
git push.
This technique might fall down if your app approaches Craigslist-level traffic, but it’s working flawlessly for us on SpeakerRate, and should work well for your small- to medium-sized web application.