Skip to content

February 2, 2011

Make that MySql Backup Put Up or Shut Up

Ok, so we’ve set up a little system to backup a remote MySQL database to another machine periodically. If the host ever loses our data, we just hit the restore button and we’re back in business, right? I mean, we can see by looking in the backup folder that our little robot process is dilligently creating a new file every day and that none of the files are empty. It must be working correctly, right? Wrong! If programming and science fiction have one imperative in common, it’s NEVER TRUST A ROBOT.

Don't worry, broseph! I got this on lock.

And Joel Spolsky agrees. Our backup strategy needs to be verified or it’s worthless. If you’re not testing your backups, you’re begging the data gods to hit you with the double whammy: losing your primary data and then realizing your backups have never worked (or maybe you have that one backup from last spring when you were setting up the process.) Think of how terrible your future self will feel when he or she realizes that they were backing up the wrong database every day. How will the Family Guy fanfic community react to the content vacuum created by the loss of your blog?

They’ll move on. Some other Peter will start a blog and become more successful than you ever were. He’ll be lauded by the community and you’ll be shunned. You won’t be invited to Stewie-con next year, and people will think you’re crazy when you try to point out that the plot of the new guy’s opus, “Cirque de Family” is suspiciously similar to your masterpiece, “Brian’s Carni-fail.”

Neither of us want that to happen, so we have a choice. We can put all our trust into some broseph-spewing robot, or we can test our backups by faking an emergency and trying to restore to a new database. It’ll be like a fire drill, except we don’t have to stand outside our apartment building at 3 am in the middle of February (I know that’s a great opportunity to chat-up your cute neighbor, but you wouldn’t need the “perfect opportunity” if you just grew a pair and said hi in elevator.)

The Emergency

Just for narrative interest, lets invent an emergency. We want something that covers all aspects of the backup process. Since the main point of backing up to a local drive was so that Time Machine would pick it up, we should include a failure of the hard drive to which the backups are being written.

Picture this: you’re on an airplane returning from a vacation in Italy (fun time, bad drivers). A terrorist sitting behind you shoulder-surfs your MySQL password and overwrites your database with threats against American freedom using his CLEAR 4G card. Then he stands up and starts threatening the pilot with bombs that he smuggled in his shoes AND underwear. He’s standing right in front of you with his back turned, so you quietly shut your laptop and wait for just the right moment. At the crescendo of his anti-imperialist soliloquy, you whack him with your MacBook so hard the drive crashes, knocking him unconcious. Your database is corrupted, your hard drive is destroyed, but you’re a hero.

Weeks later, after the parade down Broadway in your honor and the loads of free hardware Apple has thrown at you, you decide it’s time to restore all of your data from your external Time Machine drive.

Get the MySql Backup File from Time Machine

Open a Finder window and navigate to the folder where your WordPress backups are stored. Click the Time Machine toolbar icon and click Enter Time Machine. Scroll through history to a time that has the most recent backup. Right-click the file and choose Restore.

I put it here somewhere...

Time Machine then prompts you for a location to put the restored file. I chose to create a new folder called Restore, just so I don’t get my restored backups and my actual backups mixed up.

Create a Restore Environment

We obviously don’t want this restore to overwrite your existing Family Guy fanfic blog (Fanficly Guy). After all, what if the backup is incomplete or corrupted? We’d end up destroying the data we’re trying to protect. We’ve got to create a separate restore environment.

Note: This section is going to cover creating a restore environment for WordPress on DreamHost. If you’re looking for how to restore from a MySqlDataDump in general, skip to Finally do the restore!

To recreate a similar environment, we’re going to make new Worpress and MySQL instances. That way we can restore the data to a new clean database. Plus, once we’re done restoring, we can check that it worked by hitting the new WordPress instance in our browser. If everything works correctly, we should see an exact clone of our original blog.

Just to keep the two environments separated, we’re also going to create a new subdomain. To add the new subdomain, go to Dreamhost’s web panel, and click Domains > Manage Domains > Add New Domain/Sub-Domain. Name it whatever you want; I named mine restore.pr.ogra.ms. Leave all of the other options at their defaults and click Fully host this domain.

Now to install a new WordPress instance, click Goodies > One-Click Installs > WordPress. Click Custom Installation so that it gives you the option of creating a new MySql database. Make sure that Automatically Create Database is highlighted in the Select Database dropdown and click Install it for me now!

They make it too easy.

Verify the MySql Connection

You should be redirected to a confirmation page that tells you the name of the new MySql database. In my case, it’s restore_pr_ogra_ms.

We need to make sure we can access the new database from our local machine, but what username and password do we use? If we click Goodies > MySql Databases, we can see the new database in the list. It also tells us the name of a new user that the installation created for us. We still need the password though!

Locked out of our own database.

Don’t worry, the web panel can do anything. Click on the user with access (in my case, restoreprograms). It will show the autogenerated password in the New Password section (after Currently:…). You should probably not change the autogenerated password because WordPress has connection information hard-coded in the wp-config.php file. If you change it here, you’ll have to change it there. We will be making changes to that file, but keeping the autogenerated password just makes troubleshooting easier.

While we’re in this part of the web panel, we need to add your IP address into the Allowable Hosts box. Otherwise, we will not be able to connect to this database from your local machine. Copy the IP address of your current computer (it’s displayed on the page under Your current computer is:) and paste it into the Allowable Hosts box. Save your settings.

Now we’re ready to test connectivity from our local machine. Open up a terminal window and run the MySqlDataDump command that we’ve been using to create these backups, but change the address and login credentials to point to your restore database. You could also connect via MySql and run some SELECT statements, but MySqlDataDump is a quick, simple and unambiguous way to check the connection.

/usr/local/mysql/bin/mysqldump
	-h restore.pr.ogra.ms
	--user=restoreprograms
	--password=AReallyStrongPassword restore_pr_ogra_ms

If you get a scrolling window of gibberish SQL code, congratulations! Your MySql restore environment is ready.

Verify the Restore WordPress Instance

Since we’re verifying everything, we might as well navigate to the new WordPress installation in a browser. Set up the Admin user and check to make sure the default blog entries show up. Logging in as Admin for the first time creates some database tables and sets up some configuration variables, so it’s important to do this step. Don’t worry about any of the settings too much, we’re going to replace all of the new tables with our restored data. Just make sure that you give the Admin user a password and login at least once. If you can see the new blog with the default posts when you navigate to your restore WordPress instance, you’re all set.

Finally do the restore!

This part is almost really simple, but there’s one trick. First, open a terminal window and navigate to the folder to which you restored your Time Machine backup. Now execute the following command:

/usr/local/mysql/bin/mysql
	-h mysql.restore.pr.ogra.ms
	--user=restoreprograms
	--password=AutoGeneratedPassword restore_pr_ogra_ms
	< 02022011-pr.ogra.ms.MySqlBackup.sql

make sure to substitute the credentials of your restore database (restore_pr_ogra_ms is the database within my restore MySql instance). Also substitute the name of your extracted backup file for 02022011-pr.ogra.ms.MySqlBackup.sql.

The backup file is a SQL script that CREATEs all of the WordPress tables and INSERTS all of your data (posts, comments, styles, etc). The above command just pipes the backup file to MySql, which knows to run the script against your restore database. So after this command is run, all of your backed-up data should be in the restore database.

Here’s the tricky part though: Every WordPress site has a unique prefix that it uses to name its tables. When we logged in to our new WordPress instance as Admin the first time, it generated all of the tables with this unique prefix. The same thing happened the first time you logged in to your real blog. When we ran the backup script, it recreated the tables from our real WordPress blog, prefix and all. This means that now we have two sets of tables in our restore database. That’s fine, we just need to tell the WordPress config file to use the prefix from our primary site instead of the new blank site and we should be golden.

Go back to the DreamHost web panel and click Goodies > MySql Databases. Find your restore database’s hostname (it should be of the form mysql.<new subdomain that we made for the restore>) and click phpMyAdmin.

phpYourAdmin

This brings up phpMyAdmin, a nice little web interface for MySql. We’re going to use this interface to figure out the prefix of our restored tables. To get a listing of all the tables in the WordPress database, click on your database name in the left sidebar (under the phpMyAdmin logo.)

Pre Fixe

When the list loads, we can see that there are two sets of tables with the same names but different prefixes. One set is the default tables that were installed when we logged in to our new WordPress instance as Admin, and the other is our restored blog data. Which is which though?

To figure that out, we are going to look at the configuration file that tells WordPress which prefix to look for. The, we’re going to change the file to point to the real tables. If you have SSH configured for passwordless login, run this command to login:

ssh TheBestHuman@pr.ogra.ms

Substituting your username for TheBestHuman and your primary domain name (not the new restore subdomain) for pr.ogra.ms. Now cd into the directory of your restore WordPress instance.

cd restore.pr.ogra.ms/blog

Open the wp-config.php file in your favorite editor. Find the line that says

$table_prefix  = 'wp_5qvylv_';

This tells us that ‘wp_5qvylv_’ is the prefix of the new default installation of WordPress. We want to point it to our restored tables, so simply change this value to the prefix of the other set of tables.

$table_prefix  = 'wp_wg0xrn_';

Finally, navigate to your restore instance (restore.pr.ogra.ms/blog) and BOOM, we have an exact copy of our blog!

This is Important, Just Do It

If you follow these instructions, it should take less than half an hour to verify a WordPress backup. If you weigh that half-hour against the hours and days you’ve spent crafting your online identity with an interesting and useful blog, it becomes laughable not to do a test restore at least once a month. If you never do a test restore, you have no idea whether or not your backups are working. Why spend time setting up and running a backup process every day if you’re not going to test it? As far as I’m concerned, an untested backup is the same as no backup.

Don’t just trust your robots; you never know when they’re going to turn evil and kill your data.

Leave a Reply

Your email address will not be published.