Exporting a Single Site Install as a Subsite for a Multisite Install

NOTE: The following is a very manual process, we have since released Multisite Tools 1.1 which includes Pull a Single Site Install into a Subsite functionality.

Follow the steps below to convert a standalone WordPress site into a WordPress Multisite subsite.

Here are some of my environment details for context:

Source single site address	http://example.dev
Multisite type	subdomain (subdirectory also supported)
Multisite address	http://example.com
Subsite address	http://blog.example.com
Subsite table prefix	wp_2_

Finding the Subsite Table Prefix

To import a single site install of WordPress as a subsite of a multisite install you’ll first need to create the subsite on the multisite.

You’ll then need to find the table prefix of the subsite you’ve created. Follow the steps below to find this information:

1. Log into the WordPress network admin dashboard. (e.g. http://example.com/wp-admin/network)
2. Click the “Sites” menu item, here you’ll be presented with a list of your subsites.
3. Click the subsite you wish to extract.
4. The address bar in your web browser will now contain the ID of your subsite, e.g. http://blog.example.com/wp-admin/network/site-info.php?id=2

In the example above my subsite ID was “2”. See the formula below to calculate your subsite table prefix:

WordPress table prefix + subsite ID + underscore = subsite table prefix

Here’s what mine looked like in practice:

wp_ + 2 + _ = wp_2_

Export a SQL File

1. Log into the single site install of WordPress’ admin dashboard. (e.g. http://example.dev/wp-admin/)
2. Navigate to the WP Migrate DB Pro admin page, i.e. Settings → Migrate DB Pro.
3. Ensure “Export File” is selected.
4. In the Find & Replace section copy the contents of the URL find column into the URL replace column (click the right arrow).
5. Update the URL replace column to match the subsite’s address (“//blog.example.com” in our example).
6. Fill in the file path Find & Replace row as required (or remove it if not required).
7. Later we’ll be copying across your uploads directory (see Copying Themes / Plugins / Uploads below) so you may want to add another Find & Replace row to convert the subsite specific uploads paths, for example: “/uploads/” => “/uploads/sites/2/”.
8. Here’s what my find & replace section looks like so far:
9. Click the “Migrate” button, wait for the export to complete.

Edit The SQL File

1. Open the SQL file in your text editor of choice.
2. Find and replace all instances of the single site table prefix with your new subsite table prefix, e.g. “wp_” → “wp_2_”.
   • Tip: table and index names should be surrounded with ` (back ticks), this means searching for `wp_ and replacing with `wp_2_ will get you far.
   • Tip: Most other required replacements should be in the “meta_key” and “option_name” fields of the “usermeta” and “options” tables respectively and can be found with 'wp_.
3. Save and close the SQL file.

Import The SQL File

Import the SQL file into your multisite’s database.

Cleanup Users

This is where things get a little tricky.

At this point all the database tables from the single site install of WordPress have been imported into the multisite with their proper subsite prefix. However, both the “users” and “usermeta” tables were imported with the new prefix too, but in a multisite install the users and usermeta tables are global and hold information about all the users across all the subsites.

This means you need to carefully merge the users and their meta data from the wp_2_ prefixed tables into the wp_users and wp_usermeta tables. You then need to update all their posts, comments and other data to have the correct user_id.

By far the easiest way to do this is to simply run a little bit of SQL that copies the wp_2_users into wp_users, and then inserts the wp_2_usermeta data into wp_usermeta with the new IDs of their respective users. This takes advantage of the fact that the WordPress database schema does not enforce unique usernames. If you’ve already go duplicated usernames in the data you’re importing, you should fix that first. The SQL will be something like the following:

-- To enable matching of old and new user ids.
ALTER TABLE wp_users
ADD COLUMN old_user_id bigint(20) unsigned;

INSERT INTO wp_users (user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name, old_user_id)
SELECT u2.user_login, u2.user_pass, u2.user_nicename, u2.user_email, u2.user_url, u2.user_registered, u2.user_activation_key, u2.user_status, u2.display_name, u2.id
FROM wp_2_users AS u2;

INSERT INTO wp_usermeta (user_id, meta_key, meta_value)
SELECT u.id, m2.meta_key, m2.meta_value
FROM wp_2_usermeta AS m2
JOIN wp_users AS u ON m2.user_id = u.old_user_id;

UPDATE wp_2_posts, wp_users
SET wp_2_posts.post_author = wp_users.id
WHERE wp_2_posts.post_author = wp_users.old_user_id;

UPDATE wp_2_comments, wp_users
SET wp_2_comments.user_id = wp_users.id
WHERE wp_2_comments.user_id = wp_users.old_user_id;

ALTER TABLE wp_users
DROP COLUMN old_user_id;

As well as updating posts and comments in the new subsite with the changed user ids, if you have any other tables that hold user authored data (such as the old links table) you should update them too.

Once you’re happy that everything is updated and working as intended, you can delete the used wp_2_users and wp_2_usermeta tables from your multisite database.

This isn’t a perfect method of importing users as you may end up with duplicate users if your users have created accounts on both the single site install or any of the sites hosted in the multisite network. You could conceivably run something similar to the above SQL to merge and delete duplicate users.

Copying Themes / Plugins / Uploads

See the diagram below illustrating which files to copy from your existing single site installation to your multisite installation.

Done!

You have now successfully imported a single site install of WordPress as a multisite subsite.