Backing up small databases

November 25, 2012

I have a few apps running on my own machines that maintain databases. And I got a bit worried that while most of my documents and my code are backed up to other locations, the databases that run those apps really weren’t backed up at all.

So what’s a quick way that I can copy the structured data, make it easy to restore, distribute it off site, do it in a way that preserves my privacy, and not have to remember to do it?

The answer I came up with is to take a SQL dump, which outputs a SQL script which you execute in order to restore the database schema and data. Then compress it, encrypt it and save it to my Dropbox. All these tasks are scriptable, so I put them in a shell script and told Jenkins (the god of cronjobs) to make sure it happens regularly.

The source code is here if that kind of thing interests you.