Incrementally backing up our clients databases

A couple of years ago, I made a simple mistake while working on a clients Live database. I already knew that it was a big ‘No-No’ to work directly on a Live database, but the change I was making was to be a small one and I told myself I’d get away with it.

I didn’t get away with it. I ended up removing a very small number of important product related records from a table.

I immediately contacted the owners to let them know (which was my second mistake, I should have waited until I had resolved it first) and I contacted the hosting provider at the time, hoping they had a back up of the database.

The hosting providers luckily had a backup dating back a few days and with that I was able to restore the database to the way it was. I only needed one small table from the database, which probably took only a couple of minutes to retrieve and forward on to me but I had to pay out for an hour’s worth of work,¬†totaling¬†nearly 100 Euro. This was back when I had started out on my own so I didn’t have 100 Euro to be throwing around.

I learned my lesson and since then I have backed up client databases every time before making even the smallest change. It became part of our deployment process to back up client databases when deploying changes to a customer’s Live site, which was usually once a week while actively developing their application.

More recently, it’s becoming more and more important to back up clients live databases as often as we can, regardless of any active development going on or not.

Thanks to AmazonS3 we have been storing database backups online at a low cost, transmitted and stored securely for the past few months, retrievalable at any time.

My co-developers can also access those backups if the need arises. In the past I was storing those backups on a local drive which wasn’t ideal if a co-worker needed something and I was out of the office.

Backing up client databases every single day is now something we do for all clients we host and its fully automated. Those backups are incremental, meaning that it is not just one backup for each client, it is one backup per day (or more) for as long as the client needs. This means, if there is a problem, we can revert back to earlier that day, last week, last month or 6 months ago or a combination of the above.

So far, the costs of keeping these backups is affordable. At some point we’ll have to think about the cost benefits of storing so many backup files for each client and decide to archive them or delete them after a period of time. I find it hard to delete anything though, so I don’t look forward to that day when a decision has to be made.

Here is a sample Bash script we use on our Ubuntu servers for those that might like to use a script, called by a Cron schedule to automatically backup mysql databases to an Amazon S3 bucket

Leave a Reply

Your email address will not be published. Required fields are marked *