Tag: backups

Simple, automated and low cost MySQL backup strategy

Setting up a mysql backup strategy it’s hardly an exciting task, so having a simple solution it’s key to actually get it out of your to-do list.

Here’s a simple, automated and low-cost alternative that I use to keep MySQL database backups of small to medium-sized projects.

Setting up automatic backups

automysqlbackup it’s a simple shell script that automates the creation of daily, weekly and monthly MySQL backup.

It’s available on the repositories for Debian and Ubuntu. The project it’s officially hosted on SourceForge but you can also find lots of several of forks hosted on GitHub.

If you’re using Ubuntu, the installation it’s completely straightforward; all you need it’s sudo apt install automysqlbackup and you’re done.

Backups are saved on /var/lib/automysqlbackup, organized by daily/weekly/monthly directories and then by database name.

There are a few settings that you can modify on /etc/default/automysqlbackup, such as the backup dir, whether to send informative e-mails or to keep a “latest” directory.

Off-site backups

Of course, having automated backups it’s just part of the solution: you need to keep an off-site copy of your data in case your server it’s compromised.

A very simple and cheap alternative it’s using Google Drive as external storage: for USD 1.99 you get 100 GB which are shared with other Google services such as Photos and Gmail, but even the free 15 GB are plenty.

For saving your backups to Drive you can use drive, which it’s a tiny program to pull or push files to the service. There are several platform packages for various distributions.

After the installation, you need to initialize the client, so you can get an OAuth token to authorize the application access to your Drive account.

The client doesn’t do synchronization, it trusts the user to determine the authoritative version of a file or folder, which might be problematic in some cases but it’s specially useful for copying the backups, since that will automatically take care of deleting older backups —which you can still find on your “Trash” for 30 days since deletion.

You can set a daily cron job to upload your latest backups using something like this:

25 4 * * * cd /root/gdrive/mysql-backups && drive push -no-prompt

Backups are simple

… or they should be, anyway.

I think that one of the more popular excuses around for not having backups it’s “I haven’t gotten to it”; usually because you don’t have the time to try that fantastic tutorial you found for encrypted-incremental-automatic-deduplicated-control-versioned-backups on Amazon S3.

The thing it’s… it’s ok if you don’t have time for it, because it means you’re doing your job… which very likely isn’t Chief Backups Officer. What it’s not ok it’s that you keep postponing your backups!

That’s why I think that when you’re first configuring your server you should immediately configure some sort of backup that:

  1. It’s very quick to setup, so you actually do it
  2. It’s easy to restore from, so it’s actually useful

And since I’m assuming you’re not an idiot, I know you’ll do your best to keep them safe; which doesn’t mean creating some new fancy encryption scheme but using existing tools to do the job (for instance, ssh and rsync are both encrypted, so they’re good enough for transmitting the data to another server).

I’m sure there are plenty cool alternatives to keep your data safe, but the truth it’s that if they don’t comply with these two basic requirements you should wonder if there’s a better, simpler way.