Migrating a WordPress Blog into a Multisite Installation

Version 3.0 of WordPress introduced the possibility to run a multisite setup, that allows to have multi WordPress sites on a single installation. A common scenario is to migrate a blog into a multisite setup. Although there are a couple of plugins available to do this, I found none of them to be reliable enough. Or they only migrate the content and not all the settings.

So I started to do these kind of migrations manually for a while now. The steps I take are:

  1. Backup the database of the multisite installation. Just to be sure.
  2. Make a MySQL dump of the single site blog.
  3. Create a new multisite blog
  4. Write down the blog ID of the new site
  5. Copy uploads of the single site to the multisite instance: wp-content/uploads/2013 -> wp-content/uploads/sites/[blogID]/2013 etc.
  6. Delete database tables of the new blog. They start with wp_[blogID]_.
  7. Change the prefix of the table names in the MySQL dump (typically wp_) into the new prefix. You can also rename the tables before the dump or after the import.
  8. Import the MySQL dump.
  9. Remove shared database tables wp_[blogID]_blogs, wp_[blogID]_blog_versions, wp_[blogID]_site, wp_[blogID]_sitemeta, wp_[blogID]_usermeta, wp_[blogID]_users, wp_[blogID]__signups, wp_[blogID]_registration_log
  10. Change site URL in the database (SQL statement 1)
  11. Change user roles in the database (SQL statement 2)

Now you should be good to go. Unfortunately there are some caveats.

If plugins have created their own database tables, you might have adapt SQL statement 1 to run on those tables and the appropiate columns.

SQL statement 1 does not work with serialised data. I have not found a fool proof way yet to do this.

SQL statement 1
Example assumes the blog ID is 2 and the prefix is wp_. Adjust to your situation.


SET @from = 'blog.tastywhe.at';
SET @to = 'blog2.tastywhe.at';
UPDATE `wp_2_posts` SET `guid` = REPLACE(`guid`, @from, @to);
UPDATE `wp_2_posts` SET `post_content` = REPLACE(`post_content`, @from, @to);
UPDATE `wp_2_comments` SET `comment_author_url` = REPLACE(`comment_author_url`, @from, @to);
UPDATE `wp_2_comments` SET `comment_content` = REPLACE(`comment_content`, @from, @to);
UPDATE `wp_2_links` SET `link_url` = REPLACE(`link_url`, @from, @to);
UPDATE `wp_2_postmeta` SET `meta_value` = REPLACE(`meta_value`, @from, @to);
UPDATE `wp_2_commentmeta` SET `meta_value` = REPLACE(`meta_value`, @from, @to);
UPDATE `wp_2_options` SET `option_value` = REPLACE(`option_value`, @from, @to) WHERE ( `option_name` = 'siteurl' || `option_name` = 'home' || `option_name` = 'fileupload_url' );
SQL statement 2
Example assumes the blog ID is 2 and the prefix is wp_. Adjust to your situation.


SET @from = 'wp_user_roles';
SET @to = 'wp_2_user_roles';
UPDATE `wp_2_options` SET `option_name` = REPLACE(`option_name`, @from, @to);