A simple MySQL database backup process

Wednesday, 15 February 2012

by Silumesii Maboshe

BillUp is a Ruby on Rails application I wrote to do invoices and receipts for Pencil Case Studios. Last year, I re-wrote it and manually re-entered all the data because the structures of the tables were different. Yes, there are smarter ways of doing it but I was not feeling particularly smart at the time. It was a painful exercise but it made me realise that the application had no database backup strategy whatsoever! Again, not smart but, now, bordering on reckless!

Thankfully, my Linux server never went down.

I smartened up and now have full hourly backups of the MySQL database for the application. The process uses a Linux shell script run at scheduled intervals. Here’s how I did it. Allow me to build up to it so I can show you some gems I found along the way!

The UNIX date command

Before there was “an app for that”, there were UNIX commands. I think apps in an App Store sense are a re-incarnation of them—single programs that do a specific task.

The date command displays the date:

$ date
Fri 27 Jan 2012 12:08:42 CAT

What I learned is that you can customise the date format in whatever imaginative way you’d like. Here are some of the things you can do:

$ date "+DATE: %Y-%m-%d%nTIME: %H:%M:%S"
DATE: 2012-01-27
TIME: 12:20:01

$ date "+Today is %A"
Today is Friday

$ date "+Today is %A, %d %B %Y"
Today is Friday, 27 January 2012

The developers of UNIX rock harder than the Flintstones! They thought of everything! Have a look at the man page man date for all the glorious details. We’ll come back to this command.

Scheduling tasks with cron

Manually doing anything can be a bother. People have lives or at least some of you do. The last thing I want is to have to put client work on hold because it is that special time in the hour when I need to run my backup. Computers love to do grunt work. So, why deny them the satisfaction.

My flavour of Linux uses cron to schedule tasks. Other variants and implementations exist but mine came with cron and it’s so well documented that I didn’t try anything else.

The syntax can be a touch cryptic. Like riding a bike, you’ll get the hang of it after falling off several times.

I want my backup job to run every hour. So, my cron job syntax would look something like this:

# m	h	dom	mon	dow		command
0	*	*	*	*		do_that_thing.sh

Thankfully, there is a more descriptive way to write it:

# m h  dom mon dow   command
@hourly  do_that_thing.sh

The way you actually schedule the entry is to edit your crontab using:

crontab -e

When you are done, you can have a look at what is in your crontab using:

crontab -l

Play with it a little, get acquainted. You probably won’t be best friends but you’ll be seeing each other now and then. It’s awkward to have the “what’s your name again” conversation too many times!

Backing up a MySQL database

You’ll be happy to know that there is yet another command that will allow you to output the entire contents of a MySQL database. the command is mysqldump. You give it a database, access credentials and an output file and it will take care of everything for you. It won’t even ask you to love it in return. That’s service!

mysqldump -u GatoradeUser -pGatoradePassword Gatorade > "TheCoach.sql"

The above command works if you have a database called Gatorade which is accessible to GatoradeUser using GatoradePassword as the password. You want it to be output to a file called TheCoach.sql.
Watch out for the -p. After that is were your password goes. They should be right next to each other.

Putting it all together

To put all the pieces together to do the database backup, I used a very simple shell script. To the informed you’re thinking, it’s about time! To you, I say, I am also a photographer and like to admire the sights along the way…look a tree-frog!

The shell script

Fire up your favourite command line text editor. I use nano on my server.

nano backup-Gatorade.sh

In that file, add something like this:

#!/bin/bash

mysqldump -u Gatorade -pGatoradePassword Gatorade > "/home/Documents/Backups/DB/Gatorade/`date +%Y%m-%H`.sql"

Did you notice our date command format? What it does is get the year, month and hour at the time the command is run. It uses that as the name of file! So, every time the script is run, a new file gets generated because the times will be different. No need to worry about name clashes and overwriting files and so on…well actually not quite. We’ll come back to this.

Save the file and close your text editor. To test the command you can use:

bash backup-Gatorade.sh

Since we want the system to run it automatically, we’ll need to make it executable:

chmod +x backup-Gatorade.sh

Now, we can test it again more simply and run it the same way as any other command:

backup-Gatorade.sh

The crontab

Our updated crontab can now be set to look like this:

# m h  dom mon dow   command
@hourly /home/Documents/Backups/DB/Gatorade/backup-Gatorade.sh

Give it a test. Does it work? Well done!

Back to the date format

Let’s look at our date format, again:

date +%Y%m-%H

The reason, I chose to use this format for the backup was because I wanted an hourly backup. Remember that mysqldump does a full backup of the database. For now, it’s fine for my Rails application because the database is quite small. At the same time I do not want my entire hard disk full of hourly backups from now until eternity.

Naming the files with this date format means that every day of the month, I only get a set of 24 backups for a particular day until the last day of the month. Everyday, the backup for that hour the previous day gets re-written (because the file name is the same). So, at the end of any day, I only get 24 backups to go back to. Also, at the end of each month I get a full set of backups for each of the last 24 hours of that month. Not bad for a snippet of code!

Reflection

Yes, there is room for this backup strategy to get better, for example, it may be more efficient to do hourly “diffs” instead of a full backup. I’m open to suggestions for improvements. What is amazing is how little code this actually took up.
Open Source is the ketchup on my development sandwich!