Finally Backing Up

I did something today that I should have done a long time ago - set up automatic backups of my amphibian.com database.

As you may or may not know (depending on how many of my blog posts you've read before), all of the data for my web comics is stored in a MySQL database. The words coming out of the frogs mouths, their positions in each cell, and the SVG images of the frogs themselves - all stored in simple tables in MySQL. I needed to be doing backups.

I set it up with cron and mysqldump. I wrote a simple bash script that will call mysqldump to create the complete backup of my amphibian database. It looks something like this:

#!/bin/bash
fn=/path/to/backups/amphibian-$(date +%d-%b-%Y).dump
mysqldump amphibian > "$fn"

To make sure each backup file gets a unique name, I create the fn variable which includes a section made out of the formatted date, in DD-Mon-YYYY format. To the Linux date command, that is %d-%b-%Y. Then I simply call mysqldump giving it a single parameter: the database name. I redirect the output to a file of the name I made above...and that's it.

Wait, why didn't it prompt for a user name and password to dump that database? Good question! That was my initial problem - I didn't want to put the password right in the script file. I found that if I make a file in my home area named .my.cnf and have it contain a section like this,

[mysqldump]
user = username
password = pw12345

...I can use mysqldump with a default user and without being prompted for a password. As long as the .my.cnf file has 0600 (read-write only owner) file permissions, it is reasonably secure.

I set my backup script to be called once per week by cron and I'm feeling much better now. I made an additional script that automatically copies the backup files offsite to a remote server for added safety. Being prepared for catastrophic data loss will really let me sleep better tonight.

Amphibian.com comic for 19 December 2014