Manually Restoring a Crunchy Database

It’s usually prudent to back up one’s databases fully before transferring a domain from one hosting provider to another. So what do you do when not even that goes according to plan?

I made the mistake of trusting that a full transfer worked, just because the sites and the data on the new location seemed fine. It wasn’t until a few days later, when trying to create new posts, that I uncovered a MySQL glitch.

I’m still not sure what exactly caused the glitch, but on three (possibly four) of the transferred sites, the auto-increment keys were munged, at the very least. At least, that’s the one thing that makes sense from the behavior.

What’s even more interesting is that I ended up taking a different approach to restoring each of the sites.

First site: empty all tables, and restore data from the seemingly incompatible backup. This cleared up the problem, but all of the options from the previous setup were gone, along with a bunch of stats. Not an optimal end solution.

Second site: in order to avoid loss of options and stats, I did a WordPress export of the current site to extract the data needed, then deleted the tables for terms, taxonomy and posts. I recreated those tables using the SQL statements from the Wordpress code from the same version as what was currently running. Once those tables were recreated, I ran the import of the WordPress XML file, and all seemed fine. Turns out the comments were still crunchy.

Third and fourth site: repeate of process for site two, including the comments table from the start.

There are times when all those years spent troubleshooting as a sysadmin pay off.

Lesson learned: never trust what Plesk tells you about your database backup. Go commando if you can (command line interface and mysldump are your safe bets).

Next, to play with DirectAdmin and see if it can give me the same control panel flexibility as Plesk without the same headaches.