Comprehensive WordPress Multisite Migrations

by Ian Dunn

Migrating multiple standalone installations of WordPress into Multisite can be relatively easy if you don’t need it to be perfect. The Codex has a basic guide and Stephanie Leary gave some more detailed tips at WordCamp Phoenix 2011.

But I’ve been working on a project for a client who had half a dozen blogs with hundreds of users and thousands of posts, and they wanted the migration to be completely transparent for the users and readers. That meant that user passwords must remain in tact, duplicate users had to be merged, plugin data had to be migrated, etc. None of that would have been possible with the standard methods, so I had to work out a process that would include everything, and I’ve written up a detailed description of it below.

This assumes that you have some intermediate knowledge of MySQL and tweaking plugins/themes, that the new Multisite installation is on the same server as the standalone ones, and that the new WPMS blog will live at the same URL as the old standalone blog did. I’m also assuming that you’re using phpMyAdmin to do some basic things like copying tables and data. so I haven’t included the queries for those. I’ve refined this process over a dozen iterations, but you may need to tweak it here and there to fit your context, and you may run into situations that I didn’t because of specific plugins you’re using, etc. I’d recommend trying it out on a development server first and then pushing to production once you’re convinced it’s all sorted.

* * *

Step 1
Rename the old blog directory from foo to _old_foo. In the wp_options table, update the siteurl, home and upload_path options to reflect the new path. The old blog is now running in at a different URL, so you can use it to compare against the new one at the end. If you don’t update upload_path then WP will recreate the upload directory in the old space, which will prevent WPMS from using it.

Step 2
Backup the old standalone database and also the WPMS database (unless this is the first blog you’re migrating into WPMS).

Step 3
If the standalone blog is significantly older than the WPMS install, upgrade it to match the WPMS version. I’ve had good luck migrating 2.9 and above into 3.0.5, but have been upgrading anything older than 2.9 to 3.0.5 before migrating.

Step 4
Install the plugins and theme that the blog uses into WPMS, and activate the theme on the Super Admin > Themes page. If you’re using a distributed theme (rather than a custom one) and it’s been modified, you’ll want to either create a child theme with the modifications, or rename the theme’s folder from foo to foo-bar and update the style.css to reflect the new name. Otherwise you might run into conflicts if multiple blogs have uniquely modified versions of the same theme. You’ll also want to check the theme for any hardcoded URLs, paths, etc and replace them with the proper method, e.g., bloginfo(), STYLESHEETPATH, etc.

You may want to upgrade any plugins to the latest version to help avoid any incompatibles with WPMS. If you’ve modified a plugin differently across different standalone installations then you’ll need to merge them into a single instance, or give the plugins unique names and metadata. Don’t activate them yet, though, because they might mess some things up since their data isn’t imported yet. They’ll be activated when we import the options table.

Step 5
Create the empty blog in WPMS. Make a mental note of the ID, because we’ll use it extensively. The site address can’t have an underscore in it, so you’ll have to use a dash or something else instead.

Step 6
If any plugins have created custom tables, copy them from wp_plugin_name to wp_ID_plugin_name, using the ID from step 5.

Step 7
Empty all of the wp_ID_* tables in the WPMS database, but leave wp_users and wp_usermeta alone.

Step 8
Copy the data from the old options table straight into the new wp_ID_options table. Edit the siteurl, home and upload_path values. WPMS stores files in wp-content/blogs.dir/ID/files instead of wp-content/uploads. Rename the wp_user_roles column to wp_ID_user_roles.

Step 9
Copy the data for wp_commentmeta, wp_postmeta, wp_terms, wp_term_relationships, and wp_term_taxonomy straight into their corresponding WPMS tables. Update the wp_attached_file data in wp_postmeta to match the new file path if any of it’s records contain the old path:

UPDATE wp_ID_postmeta
SET wp_attached_file = replace(
	wp_attached_file,
	'wp-content/uploads/',
	'wp-content/blogs.dir/ID/files/'
)

Step 10
Copy wp_links to a temp table, like _ID_links, and drop the link_category column from the temp table if it exists. Then copy the data into wp_ID_links and drop the temp table.

And now it’s going to get a little more involved. WPMS stores all blog-specific data in the blog’s own set of tables, but users are shared across the entire network and are stored in a common table. If you try to copy all of the users from various blogs to that table, the IDs will conflict. The way we get around that is to come up with a prefix for each blog, and add it to the various IDs involved. For example, the prefix for the first blog is 1000, so user ID 1 becomes 1001; the prefix for the second blog is 2000, so user ID 5 becomes 2005, etc. You’ll want to make sure that the prefixes are large enough to not overlap, so if the first blog has 1200 wp_usermeta records, then its highest umeta_id would be 2200, and you’d want to skip the 2000 prefix and use 3000 for the second blog.

Step 11
Copy wp_users to a temporary table like _ID_users and delete the admin user (because WPMS has its own already). Update the IDs with the prefix,

UPDATE _ID_users
SET ID = ID + [prefix]

Add two columns to the end of the table, spam and deleted. Both are TINYINT(2). Check for duplicate users already in the WPMS table:

SELECT user_email FROM wp_users
WHERE user_email IN (SELECT user_email FROM _ID_users)

If you have duplicate users, make a note of their current usernames and IDs, and then delete them from the temp table. Then copy the data into wp_users and delete the temporary table.

Step 12
Copy wp_usermeta to _ID_usermeta, delete the values for the admin user, update the IDs and rename a few keys:

DELETE FROM _ID_usermeta
WHERE user_id = 1

UPDATE _ID_usermeta
SET user_id = user_id + [prefix]

UPDATE _ID_usermeta
SET umeta_id = umeta_id + [prefix]

UPDATE _ID_usermeta
SET meta_key = ‘wp_ID_user_level’
WHERE meta_key = ‘wp_user_level’

UPDATE _ID_usermeta
SET meta_key = ‘wp_ID_capabilities’
WHERE meta_key = ‘wp_capabilities’

If you have any duplicate users, update their IDs (which you wrote down in step 11) to match the ones already in the wp_users table.

UPDATE _ID_usermeta
SET user_id = 14
WHERE user_id = 1005

In that query, 14 would be the ID of the user already in WPMS, and 1005 would be the ID of the corresponding duplicate user in blog we’re currently migrating. Then remove all of the rows in _ID_users for the duplicate user, except for wp_ID_user_level and wp_ID_capabilities.

The copy the data into wp_users and drop the temporary table.

Step 13
Copy wp_posts to _ID_posts and drop the post_category column if it exists. Update the post_author IDs with the same prefix as before (with the exception of the admin user):

UPDATE _ID_posts
SET post_author = post_author + [prefix]
WHERE post_author != 1

If you have duplicate users, update their post_author IDs just like we did in the previous step. Then copy the data into wp_ID_posts and delete the temp table.

Copy all of the files from the old blog’s wp-content/uploads directory into the new wp-content/blogs.dir/ID/files directory. This’ll be a lot faster and easier if you have SSH access to the server, but if you don’t you can pull them down to your local machine and re-upload them.

Now we’ll need to update any references to the old files in posts:

UPDATE wp_ID_posts
SET post_content = replace(
	post_content,
	'wp-content/uploads/',
	'wp-content/blogs.dir/ID/files/'
)

Note that you don’t want to change the guid fields, because those are used by RSS readers and other programs to identify posts. It won’t hurt anything to leave them using the old URL.

Step 14
Copy wp_comments to _ID_comments and update the user_id fields for any comments left by registered users,

UPDATE _ID_comments
SET user_id = user_id + [prefix]
WHERE user_id > 1

If you have duplicate users then update the IDs in the same way you did for the previous steps. Then copy the data to wp_ID_comments and drop the temporary table.

Step 15
Update plugin data. Obviously this will depend on what plugins you’re using, but I’ll use Subscribe2 as an example. Subscribe2 allows people to get e-mail notification when new posts are created, and it stores the subscription data in two different places. Users who don’t have a WP account are stored in the wp_ID_subscribe2 table, and the only thing you need to do for them is copy the table to your WPMS database. Registered users are stored in wp_ID_options, though, and you’ll need to update the meta keys it stores on individual blogs, but not the global ones:

UPDATE wp_usermeta
SET meta_key = CONCAT(‘wp_ID_’, meta_key)
WHERE
	meta_key LIKE ‘%s2%’ AND
	meta_key != ‘s2_format’ AND
	meta_key != ‘s2_autosub’ AND
	user_id > [prefix]

If you had any duplicate users, you’ll need to update them manually, because their IDs will be lower than the prefix.

And that’s it. Now that all of the data is imported, you can log in to WPMS and compare it side by side with the old blog to make sure that nothing was missed or broken. If you renamed your theme you’ll need to reapply your widgets.

11 Comments

  1. Lox

    Hello,

    The username of users is uniq too, and has to be handled.

  2. Florin

    Hello,

    I am trying to move a single-site to a multi-site and I can not find the plugins table wp_plugin_name.
    Is this because the latest version of WP does not have such a table?

    Thank you.

    Florin

  3. Ian

    Hi Florin, there’s isn’t a table literally called “wp_plugin_name”, that was just a placeholder for whatever table name is used by the specific plugin that you’re trying to migrate.

  4. Steve

    Hi Ian,

    Thanks for this post. Utterly helpful in every way.

    I followed the post step by step, and I got all my posts and articles from a single site into a multisite environment. Hooray!

    The site works perfectly… only one problem.

    When I call a function like wp_list_authors, none of the authors show up, and yet the posts are associated with the correct authors, and appear on the site just fine.

    Somehow the authors show up in the network, but not associated with the subsite I have created, and thus when the theme calls wp_list_authors I get nothing.

    Suggestions?

    Thanks for the help, and thanks for the great and detailed article.

    Steve

  5. Ian

    Hi Steve, I can’t think of anything off the top of my head. I’d recommend looking at the source of the wp_list_authors() function and maybe adding some debug output to it. If you figure out what’s going wrong, please post the details here so other people can benefit from it too :)

  6. Joaquin

    Hello Ian,

    Let start by saying that this site help me in a migration I am doing of 30+ sites into a multisite environment. The information you provide here is very accurate and I thank you for making our lives a bit easier.

    I have only one suggestion in regards to steps that is missing. I had a problem of media no showing in my case I had to do the following:

    I run a query to update the posts table post_content and guid columns by replacing the ‘wp-content/uploads’ with ‘files’. after this no more issues.

    Thanks again Ian.

  7. Joaquin

    Hi Ian,

    Disregard my previous comment. I see that you have the queries referencing to file in posts.

    Thanks

  8. I don’t even know how I ended up here, but I thought this post was good. I don’t know who you are but definitely you’re going to a famous blogger if you are not already ;) Cheers!

  9. Extraordinarily clear and useful. I’m migrating a bunch of single blogs into a WP multisite, and this has been very helpful.

    I think there is a typo in the last sentence of Step 12: “The copy the data into wp_users and drop the temporary table.” should be “The copy the data into ***wp_user_meta *** and drop the temporary table.”

    Also, I saw three other meta_key values that might need to be massaged:
    wp_user-settings
    wp_user-settings-time
    wp_dashboard_quick_press_last_post_id
    into the wp_NN_ format

    Probably not critical, however.

  10. Thank you very much! saved me a lot of time.

    N.B: wp upload path is now changed from “wp-content/blogs.dir/ID/files/” to “wp-content/uploads/sites/ID”
    (step 8 & 9)

Trackbacks for this post

  1. Delicious Bookmarks for June 2nd through June 3rd « Lâmôlabs

Leave a Comment

Add this ribbon to your WordPress website re-abolish slavery