[12 December 2008]

Having just moved this blog from people.w3.org to cmsmcq.com, I think it might be useful (to others, or to me down the road) to record what I did in order to make the move relatively smooth.

I started out thinking that I would have to export all the data from MySQL on people.w3.org (using my handy backup routine), move the resulting mib.sql file to cmsmcq.com, and load it from the command line.

Reading the various documents about moving blogs from one site to another, or within a site, however, I discovered that that wasn’t what anyone recommended. Export from the old site, I read, and then import into the new site. WordPress has developed a handy export format that can be used conveniently for this purpose.

I tried it. Export worked fine, and I edited the resulting XML document to change all occurrences of “http://people.w3.org/~cmsmcq/blog” to “http://cmsmcq.com/mib”. Then I imported the file to the new WordPress installation. Several articles loaded successfully, but by no means all, and those that did load did not have similar query strings in their URIs. Article http://people.w3.org/~cmsmcq/blog/?p=12 might appear as http://cmsmcq.com/mib/?p=3, not as ...?p=12. That’s a pain, because I’d like to redirect from the old locations to the new, so existing references to the blog don’t break. I know I can build a table containing all the URIs of everything in the blog, and map each to the appropriate URI on the new host, but I’d really rather not have to spend time on that.

I never did figure out why only part of the data was loading successfully; deleting spam from the site, and then re-exporting helped some (more of the posts loaded), but I never got everything to load.

So I reconsidered. I made a new SQL dump of the database on the old site, and edited it to change URIs from the old name to the new. (I also deleted the commands to load data into the user and options tables, since I didn’t want to overwrite them. I deleted the Spam Karma 2 tables, too, since my new host has a newer version of Word Press and the existing SK2, which is no longer maintained, may or may not work with it. I’ll install Bad Behavior instead.)

I tried to load this edited SQL dump to the new host by using the Web interface to MySQL provided by phpMyAdmin; it complained about a problem, and after I fixed that the process kept hanging.

So I split the file into smaller pieces, to evade any timeout and data-volume restrictions, and tried importing each in turn. Either the host choked, or my name service went away about this time; I think some of the smaller SQL files were successfully imported, but not all. Tried again the next day, and it hung again.

So I went back to Plan A: I copied the entire edited SQL file to the new host and loaded it in MySQL from the command line — took about five minutes (including the file transfer), if you don’t count the six or eight hours of time I burned trying to follow other people’s directions.

For the sake of keeping the old URIs stable, I then added a .htaccess file to the ~/cmsmcq/blog directory on people.w3.org to redirect from the old addresses to the new.

Concisely, what worked best for me was:

1. Export the data from the old server. I did this with the command mysqldump --verbose --add-drop-table --all --extended-insert --quick --skip-lock-tables --user mysql-userid --password dbname > mib.sql, but it might have been better to export individual tables more selectively.
2. Edit the mib.sql file, changing the old address (in this case “http://people.w3.org/~cmsmcq/blog”) to the new address (in this case “http://cmsmcq.com/mib”) wherever it occurs (it will occur primarily in cross references from one post to another). Some authorities also recommend doing a global search and replace on your old email address. I also took this opportunity to delete tables I didn’t want in the dump: wp_options, wp_usermeta, wp_users, and the tables used by Spam Karma 2 (RIP). And I modified the wp_ prefix in the table names to match the one provided by my hosting service’s auto-install of WordPress.
3. Copy the edited SQL dump file (in my case named mib.edited.sql) to the new host.
4. Invoke MySQL from the command line in the obvious way: mysql -h hostname -u username -p dbname < mib.edited.sql
5. On the original host, add a .htaccess file to the blog directory (here “~cmsmcq/public_html/blog”) including

   RedirectMatch permanent ^/~cmsmcq/blog/(.*)$ http://cmsmcq.com/mib/$1
   Redirect permanent ^/~cmsmcq/blog$ http://cmsmcq.com/mib
   

No WordPress export/import, no phpMyAdmin, just command line tools. I'm all in favor of Web interfaces and so I think that WordPress export and import, and phpMyAdmin, are great ideas; they just didn't work at all well for me in this situation. But one possible take-home message is: it pays to be comfortable with the command line.