WordPress migration to new host

I purchased a new VPS (virtual private server) host and have been moving many blogs I host for myself and others to the new host (Linode — which I’m very satisfied with and recommend highly). There’s multiple ways to do this, including just saving the mysql table as a backup, and reimporting, but since I’ve already created blogs on the new host, using the wordpress export, then import, features sounded like a good idea.

And it was, except for a few potential show stoppers that I hit, and I later cleaned up.

One blog was being split into two. All topics about Second Life were to go into the new blog and everything else carried over with the same blog name.  So theory was to import the data twice into each blog on the new host, then delete unwanted data.

Two issues hit me. In the examples below the old site is named voices.catzilla.org and the new one is www.ellcee-tabak.com

1) Even though I swore I told it to also import thumbnails, it imported the pictures from the old site, but the thumbnails were still IMG SRC’ed to the old site.  ie, the HTML on the new site had the URL of the old site for the thumbnail and the main pic linked on the new site as expected. This required two steps to solve. One was to copy over from the downloads directory manually all files that weren’t imported. The second was a scary find/replace in the database of all links to the old site URL and replace with the new one. Fortunately, it was fairly easy using SQL.

update lc_posts SET post_content = replace(post_content,"voices.catzilla.org","www.ellcee-tabak.com");

Basically find all references two voices.catzilla.org and change to www.ellcee-tabak.com.  

2) The harder one was I couldn’t log onto the blog, at all. Got a permission denied. I had to change the db prefix to get two new sites out of the old one, and doing so caused logon problems. To fix that required a few more edits on the wordpress db tables.

update `lc_usermeta` SET `meta_key` = REPLACE( `meta_key` , 'voices_', 'lc_');
update `lc_options` SET `option_name` = 'lc_user_roles' WHERE `option_name` = 'voices_user_roles' AND 'blog_id' = 0;

Not bad. Of course, back up everything before going into DB surgery like this, cause you could always kill the patient, and that’s not good!

Another minor complication from moving a blog this way is that the blogroll and other lists of links don’t come over. In order to do that, must use OPML data to do so.

I got these steps from googling, but unfortunately lost my browser history and can’t find  now what sites I used to dig this up to give them credit, so feel free to return the favor and don’t give me credit if you blog about this either!

Update: 11 Jan 09

I found a far better way is to simply export and import the mysql data. NOT THE ENTIRE DATABASE (unless you are moving over all your blogs) but just the tables associated with the blog being moved.  This requires access to the database the blog lives in on both hosts.  Since I use MySQL it was as simple as (assuming the db name is wordpress and the table prefix is example_:

mysqldump wordpress  > /tmp/blogs.sql

Then edit the blogs.sql file using a text editor and remove any lines referencing tables that do not begin with your prefix_ and save. Note some lines can be horribly long so use an editor that supports very long lines. I used emacs. 

After editing, transfer file to new host and then simply run “mysql wordpress < blogs.sql” where blogs.sql is the edited and copied file from above. 

Note: You may have to specify -u and -p options to the mysql commands above if they are not specified in a cnf file somewhere.

Finally, after doing that copy your entire wordpress directory tree from source host to destination, checking the wp-config.php file to ensure it all is still set up correctly for the new host.

For those who don’t have access to their databases or doing this makes you nervous, I still think the export/import procedure is the best.

Leave a Reply

Your email address will not be published. Required fields are marked *