Although I keep telling people I don’t do servers, I’m looking into moving a friend’s active Phorum-based BBS to a new host. I’m considering FutureQuest as the new host. I’d heard good things about them, though they’re a little pricey compared to other plans.

This is a good opportunity for me also to evaluate new hosts for the blog. I’m currently using Pair Networks, who have been awesome for the past 8 years, but their database servers start pooping out when you get over a certain number of transactions per minute, and it’s starting to become an issue.

This is the first time I’ve had to move a WordPress blog from one host to another, so I’m documenting the process. WARNING! GEEKY NOTES FOLLOW!

Different Server, Different MySQL Versions

Conceptually, it is as simple as uploading my current WordPress files to the new server, and then transferring the MySQL database that holds all my posts and stuff. POOF, it should work, right?

As it turns out, the FutureQuest DB servers are using MySQL version 4.0.x, and Pair is using 4.1.x, so I have to downgrade to an older server version.

MySQL Import Woes

There were three easy ways I found that would backup a WordPress database, which I tried one-after-the-other.

• First up was Aaron Brazell’s WordPress-to-WordPress Import exporter / importer. WordPress doesn’t come with a WordPress importer, so that’s what this package adds. The plugin exports your posts in an extended RSS format, which you can then upload via the import plugin from your browser. Great in theory! Unfortunately, my blog backup is 9MB, which exceeds the 2MB upload cap of this server for PHP. I may try hacking this to read the backup file from a location on disk, but I decided to try another approach.

• Next, I looked at Skippy’s WP-DB-Backup plugin, which is included with WordPress 2.0, gives you the ability to produce a SQL dump file from the convenience of your admin panel, without all that fussing around with the command line. So I did this, and then tried importing the dump file into the new server using the standard mysql < dumpfile.sql style approach. It didn’t work: 1064 – You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ‘DEFAULT CHARSET=latin1’ Darn! This looked so promising.

• I moved on to Lester Chan’s WP-DBManager 2.05 plugin, which allows you to do a lot more with your WP database. It uses mysqldump to create a compatible SQL dump file, which you can then use to re-initialize a new WordPress installation. SAME ERROR in attempting to import. DOUBLE DARN!

Downgrading from MySQL 4.1 to MySQL 4.0

After some digging, I found that MySQL 4.1.x added a different way of handling character sets, and this was reflected by the DEFAULT CHARSET command added to the end of the CREATE TABLE statements in the dump file. MySQL 4.0.x has no idea what this is, so it throws an error. The workaround I found was to modify lines 32 and 36 of Lester’s dbmanagerdatabase-backup.php file. I added:

--create-options --compatible=mysql40

…before the ‘|’ character on line 32, and before the ‘<‘ character on line 36. The first parameter tells mysqldump to use MySQL-specific syntax for creating the table, and the second parameter makes those problematic DEFAULT CHARSET commands go away.

The Backup Procedure

So here’s what I ended up doing:

1. On the CLONE blog: Install WordPress 2.0.4, plain vanilla, uncustomized. This is the “blank clone” on which my old blog will be imprinted.

2. Install Lester Chan’s WP-DBManager 2.05 plugin on both old and clone blogs.

3. On my OLD server: I edited the plugin as described in the previous section, so the database backup file created would be compatible with the new server. Again, my current server uses MySQL 4.1, and the new server uses MySQL 4.0.

4. On my OLD server: Created a database backup with the patched version of Lester’s plugin. Yay!

5. Then, copy the database backup to the CLONE server. It’s in wp-contentbackup-db if you’ve successfully installed the DBManager plugin; just copy it using FTP or SCP to the same location.

Preparing for Restore

• First of all, my CLONE server has only a numeric IP address, not a domain name. I don’t plan on transferring the davidseah.com domain over until I make sure everything works. So the URL I use for it is, for the same of example, is http://192.168.1.100.

• WordPress keeps track of your blog’s URL in the database table, and uses that to generate links to various parts of your blog. When you do a database restore with DBManager, it overwrites your entire database, including that blog URL setting. When that happens, you won’t be able to use the admin page, because you’ll be redirected to your OLD blog everytime you try to login.

• To get around that, you need to be able to edit the database directly and reset the BlogURL setting. I installed PHPMyAdmin on the clone, to be ready to edit the database manually when I did the database restore.

1. To the CLONE server: Copy the database backup file to the wp-content/backup-db folder, where DBManager looks.

Now you’re ready to restore the database.

The Restore Procedure

1. On the CLONE server: Copy all your plugins, template files, and so forth over to the new WordPress installation.

2. On the CLONE blog: Go to the admin page and click the DATABASE tab that DBManager has added. Go to the MANAGE BACKUP DB tab, and choose the database file to restore (you did copy it to the right place, right?).

3. Restore the database by clicking the RESTORE button! If everything goes well, you’ll see a SUCCESS message. Congratulations! Your database has been cloned onto the new wordpress installation!

4. Visit the cloned blog server and see if the home page works. It’s probably messed up in some ways if you’ve forgotten to transfer everything over, and you’ll find that you keep getting re-directed back to the old blog if you click on anything. That’ll be fixed next.

5. On the CLONE server: Run the MySQL Administration Tool of your choice. Using PHPMyAdmin, I went to the wp-options table and clicked BROWSE. I edit the siteurl option value to point to the CLONE server (not the CURRENT blog). Here’s an example: My old site is davidseah.com. My new clone site is, say, on a local server 192.168.1.100, and WordPress is installed in a directory called wordpress. The new siteurl (at least for now) is http://192.168.1.100/wordpress, so that’s what I should set the siteurl to. Your new webhost should have provided you a URL that you can use to FTP files to, so just use that.

6. You should now by able to login and visit the CLONE blog’s admin page. Go to Options and change BLOG ADDRESS so it’s also pointing to the new server for testing.

<

p>After all that, the cloned blog seems to be working, but I won’t be able to tell what genetic defects may be lying dormant until they manifest. Database performance seems a little sluggish to me, so I’m not sure if the way that I performed this database move somehow messed up the DB. Maybe some MySQL guru out there can give me some pointers.

Next Steps

Before I move everything over, I need to make sure that my .htaccess is set up correctly, get ALL the downloadable files moved over, update my basecamp FTP settings, and also ensure that my new email addresses are set up on the new server. What a pain in the butt!

My DNS is handled by another registrar, so I should be able to just change the nameservers when I’m ready and see what happens. If it doesn’t work out, I can just switch the nameservers back, and resume operations at my current host.

UPDATE:

• My Server Move Log, which also details the different way I moved the database with straight mysqldump and mysql restore commands from the shell.