Merging A Single Site Into a WordPress Multisite Network

WordPress Multisite allows webmasters to create hundreds of sites powered by a single WordPress core, inside a single directory. Converting WordPress into a Multisite installation is pretty simple. Just follow the instructions at the Codex or use the multisite-convert WP-CLI command.

What if you’d like to merge existing standalone WordPress websites into a Multisite installation? Ready? Let’s get merging.

0. Before You Begin

Moving an existing single-site installation into an existing WordPress Multisite network can be confusing and error-prone, so we encourage you to create a full backup of both sites before you begin.

When the backup is complete, copy all the contents (including a database dump) of the single site to the same server hosting your Multisite network.

If you’d like a migration with zero downtime, use your local /etc/hosts file to point the single-site domain to your Multisite network for the duration of the whole process. When done, remove those entries and point your real domain to the network via DNS. If you’re okay with some downtime, feel free to make the DNS change immediately.

1. Create a Blank Site

Create a new site via the Multisite administration panel (Sites – Add New).

WordPress Multisite Create
Creating a New Blank Site

You’ll need to enter a temporary subdomain when creating the site (for example migration1.example.org) and then Edit it to type in the correct domain for the new website. Take note of the site ID as you’ll need it later.

2. Move the Plugins

The first thing you’ll need to do is figure out which active plugins should be ported over. Make a list of existing plugins and their versions in the Multisite (using WP-CLI gets you extra points), then make a list of the plugins and their versions in the single-site install you’re migrating over.

Go through the list one by one, copying any plugins in the site into the wp-content/plugins directory. You can do this via SFTP or SSH. For existing plugins make sure versions match (you should be running the latest version of a plugin, so this is a good time to update).

cp -r /path/to/single-site/wp-content/plugins/akismet /path/to/multisite/wp-content/plugins/
...

The same goes for wp-content/mu-plugins but note that anything placed into mu-plugins, will be activated across your entire network. If you would like to avoid that behavior, you’ll need to pack your single-site mu-plugins into regular plugins that can be activated/deactivated on each subsite individually.

3. Move the Theme

This can get tricky, especially if you’re working with child themes. If you’re using a different parent and child theme in on site simply copy them both to the wp-content/themes directory and move on.

If, however, you’re using the same theme, you’ll need some additional steps. If both themes are the same version, or can be brought to the same version by updating, then you’re lucky. But if your single-site theme is a different version, has local modifications, etc., then it’s best to rename the theme before copying it over.

When renaming the theme, make sure you change both the theme folder as well as its name in the style.css file. Take note of this change — you will need it later when migrating the database tables.

4. Copy the Uploads

In a Multisite installation site uploads are kept under wp-content/uploads/sites/N, where N is the ID of the site. Copy over all media uploads (directories with 4-digit year names) here. Some plugins do not support Multisite and will want their uploads to live in the global wp-content/uploads directory. Such situations have to be resolved on a case by case basis.

5. Migrate the Database Tables

This is the second hardest part of the migration. You’ll either need to use wp-cli search and replace or a visual search and replace tool like this one.

Before doing anything with the Multisite database — BACKUP (again)! This is very important, because one false move and you can overwrite some tables by mistake, and there’s no going back without a backup. Backup done? Good. Let’s move on then.

Export the WordPress database of the single-site you’re about to migrate. This can be done using WP-CLI, mysqldump, phpMyAdmin or Adminer. Here’s the WP-CLI command to export your single-site database:

wp db export /path/to/single-site.sql

Import this SQL dump into a fresh temporary database. DO NOT import it into the live Multisite database, if you have done so, restore your backup immediately. Again, you can use various tools to import a database dump, here’s a mysql example:

mysql -uusername -ppassword migration-db < /path/to/single-site.sql

When the import to your new migration-db is complete, you can alter all the table name prefixes to wp_N_ where N is the site ID of the newly created site in the network. Renaming can be done using the RENAME TABLE query in a MySQL shell:

RENAME TABLE oldprefix_posts TO wp_2_posts;
RENAME TABLE oldprefix_postmeta TO wp_2_postmeta;
...

Make sure all the tables in the temporary database are set to the new prefix. You can get a list of all the tables with a SHOW TABLES; query.

Tables in the Temporary WordPress Database
Tables in the Temporary WordPress Database

After renaming is complete, you'll need to change the prefix for user roles:

UPDATE wp_2_options SET option_name = 'wp_2_user_roles' WHERE option_name = 'oldprefix_user_roles';

You might want to look into other option names with "oldprefix" too -- some themes and plugins may use the database prefix for their own options, meta keys, etc. If you've renamed your theme in the third step above, this would be a good time to make sure that renamed theme is active and configured:

UPDATE wp_2_options SET option_value = 'new-theme-name' WHERE option_name IN ( 'stylesheet', 'template' );
UPDATE wp_2_options SET option_name = 'theme_mods-new-theme-name' WHERE option_name = 'theme_mods-old-theme-name';

You'll want different values for the stylesheet and template options if you're using a child theme. Also worth looking into other theme option names and renaming them as well (both in code and in the db).

At this point you're ready to export the temporary database and import it into your Multisite network:

mysqldump -uusername -ppassword migration-db /path/to/single-site-clean.sql
mysql -uusername -ppassword real-db < /path/to/single-site-clean.sql

When the import is complete, you'll want to fix all occurrences of the wp-content/uploads directory on the new site, since these are now in a different directory. You can do this with a WP-CLI command:

wp search-replace 'newsite.org/wp-content/uploads' 'newsite.org/wp-content/uploads/sites/2 --url=newsite.org --dry-run

This will show you a list of database tables and columns and the number of changes in each column. Remove the --dry-run flag when you're ready to run this for real.

6. Merge the Users

In Multisite the users table is shared across all sites. There's only one wp_users table and one wp_usermeta table. This is the hardest part of the migration if you have many users.

Users have to be merged from your old wp_N_users table into the new wp_users table. If there's only one user (the administrator, for example), and the user ID is the same as the administrator in the Multisite you're pretty much done and feel free to drop the wp_2_users tables.

For cases where there are many users the merging strategy is usually to offset their IDs by a nice big number (like 1000 or 10000, depending on how many users already exist in wp_users), and update any references to their ID - wp_usermeta, wp_posts, wp_comments and any plugin that uses user IDs and the database. Update them by offsetting by the same nice big number. You'll also need to resolve individual e-mail and username collisions if any by manually resolving any conflicts:

UPDATE wp_2_users SET ID = ID + 1000;
UPDATE wp_2_usermeta SET user_id = user_id + 1000;
UPDATE wp_2_posts SET post_author = post_author + 1000 WHERE post_author > 0;
UPDATE wp_2_comments SET user_id = user_id + 1000 WHERE user_id > 0;
...

Next, you'll want to make sure that the users have the same roles and capabilities on the new site in the Multisite network, as they had on the single-site. Capabilities and roles are stored in the wp_usermeta tables, along with possible other site-dependent keys:

SELECT meta_key FROM wp_2_usermeta WHERE meta_key LIKE 'oldprefix_%' GROUP BY meta_key;

You'll see a list all possible keys that may need to be updated:

UPDATE wp_2_usermeta SET meta_key = 'wp_2_capabilities' WHERE meta_key = 'oldprefix_capabilities';
UPDATE wp_2_usermeta SET meta_key = 'wp_2_user_level' WHERE meta_key = 'oldprefix_user_level';
UPDATE wp_2_usermeta SET meta_key = 'wp_2_user-settings' WHERE meta_key = 'oldprefix_user-settings';
...

Double check and make sure you're running all these queries on the wp_2_* user tables and not the actual wp_usermeta tables. When done, you should be able to get a clean insert of all this data into the actual user tables:

INSERT INTO wp_users SELECT ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name, 0, 0 FROM wp_2_users;
INSERT INTO wp_usermeta SELECT '', user_id, meta_key, meta_value FROM wp_2_usermeta;

Drop the wp_N_users and wp_N_usermeta tables and that's it.

Hopefully you didn't break anything :)