Skip to content

January 18, 2011

Ridin’ Dirty With your MySQL Database Backups

I switched credit cards a while back and forgot to change the autopay settings for my Dreamhost account so they turned off my hosting. During the recovery process, I lost a few (terrible and uninteresting) blog entries. Even though it was my fault, it’s left me twice shy about leaving my data integrity in someone else’s hands. For most of my data, I’m running Mac OS X on my laptop and use Time Machine to back up to an external drive. I needed to find a way to get my WordPress database onto the Time Machine train. I know this blog is starting out a little Dreamhost heavy, but this should apply to anyone wanting to backup a remote MySql database.

MySQLDataDump

MySQL comes bundled with an awesome utility called MySQLDataDump. You throw it a database, user name and password, and it outputs a SQL script that creates all the tables and inserts all the data. First, you have to install MySQL locally though. Finding the right download for MySQL is a little like finding the installer for Java was back in the day. They try to hide it to make you accidentally pay money for it or something. I’ve gone ahead and sussed out the link for you though. After you install MySQL locally, it’s really easy to get a dump of your database from Terminal (This should all be on one line, but I didn’t want to make you have to scroll to the right forever so I broke it up a little):

/usr/local/mysql/bin/mysqldump
	-h WordPressDatabase.pr.ogra.ms
	--user=TheBestHuman
	--password=AReallyStrongPassword WordPressDatabaseName

Once the terminal window stops scrolling, we can talk about piping this output to a nice text file and zipping it up, but first a Dreamhost digression: In order to access a MySQL database remotely, you need to add the host to the Allowable Hosts list on their web panel. Don’t believe me? Check out the wiki. I’m not sure what I’m going to do when my IP address changes, but for now, I just went to Goodies>MySQL Databases on the web panel, then clicked on the user that I wanted to have remote access, then added my current IP address to the list of Allowable Hosts.

Put The Output of MySQLDataDump Into a File

I knew I would probably want to schedule this backup with a cron job later, so I wanted to put a date stamp in the filename that we pipe the output of MySQLDataDump to. Fortunately, Unix’s date does exactly what we need it to (again, this was put onto multiple lines for readability, the real command should be just a single line).

/usr/local/mysql/bin/mysqldump
	-h  WordPressDatabase.pr.ogra.ms
	--user=TheBestHuman
	--password=AReallyStrongPassword WordPressDatabaseName
>
	~/Documents/Projects/pr.ogra.ms/Wordpress Database Backup/
	$(date +%m%d%Y)-pr.ogra.ms.MySqlBackup.sql.zip

That’s nice… real nice. All this crap is going to start filling up the drive though, so let’s zip it up

/usr/local/mysql/bin/mysqldump
	-h WordPressDatabase.pr.ogra.ms
	--user=TheBestHuman
	--password=AReallyStrongPassword WordPressDatabaseName
	| gzip >
	~/Documents/Projects/pr.ogra.ms/Wordpress Database Backup/
	$(date +%m%d%Y)-pr.ogra.ms.MySqlBackup.sql.zip

Make sure you name the output file .sql.zip so that when you unzip, it has the .sql extension. Who knows, we might run this script in Windows some day.

Schedule it wih Crontab

I had never used crontab before this, though I knew it existed. It’s a little idiosyncratic, but easy to pick up. You can always use a third-party task scheduler like Lingon, but I find cron jobs to be way simpler to use.

In order to schedule a task, you need to add a line to crontab’s configuration text file. The line format is split into two sections, the first is five numbers separated by spaces indicating the minute, hour, day of the month, month, and day of the week that you want the job to run. You can substitute an asterisk (*) for any of these numbers to tell cron to execute the job every hour, minute, etc. The second section (after another space) is the actual command you want to execute. Here’s a nice little diagram that I stole from this site:

*     *     *   *    *        command to be executed
-     -     -   -    -
|     |     |   |    |
|     |     |   |    +----- day of week (0 - 6) (Sunday=0)
|     |     |   +------- month (1 - 12)
|     |     +--------- day of        month (1 - 31)
|     +----------- hour (0 - 23)
+------------- min (0 - 59)

How do you get to this config file though? just type

crontab -e

and you get a nice VI window with the file opened. If you’re confused or just don’t want to think right now, here is the line I inserted to tell crontab to execute the data dump every day at noon:

0 12 * * * /usr/local/mysql/bin/mysqldump
	-h   WordPressDatabase.pr.ogra.ms
	--user=TheBestHuman
	--password=AReallyStrongPassword
	WordpressDatabaseName
	| gzip >
	~/Documents/Projects/pr.ogra.ms/Wordpress Database Backup/
	$(date +%m%d%Y)-pr.ogra.ms.MySqlBackup.sql.zip

This should all be on one line though, here it is for copying:

0 12 * * * /usr/local/mysql/bin/mysqldump -h   WordPressDatabase.pr.ogra.ms --user=TheBestHuman --password=AReallyStrongPassword WordPressDatabaseName | gzip > ~/Documents/Projects/pr.ogra.ms/Wordpress Database Backup/ $(date +%m%d%Y)-pr.ogra.ms.MySqlBackup.sql.zip

Just paste that line into the crontab config file and your MySQL(YourSQL?) database is on the Time Machine Train!

"Your data's safe with me, Marty!"

Leave a Reply

Your email address will not be published.