WP Multisite to single WordPress manual installation

- 3 mins read

This post was made after a very intense session of 6 hours trying, without luck, to export a wp multisite site using the WordPress tools and plugins. So I decided to do it manually like a real man.

Well, now follow the steps below to extract a subsite from a Multisite Installation:

1 – Identifying the wp multisite’s subsite

  1. Go to your WordPress network admin dashboard (https://example.com/wp-admin/network).
  2. Go to ‘Sites‘.
  3. Click in the site you want to export.
  4. See the URL in your browser (https://example.com/wp-admin/network/site-info.php?id=3).

The site ID is 3.

2 – Export the wp multisite database

  1. Install the plugin ‘Adminer‘.
  2. Go to ‘Tools » Adminer‘.
  3. Click in ‘Start Adminer inside‘.
  4. Go to ‘Export‘.
  5. Deselect all the tables and select only the ones starting with ‘wp_3_‘.
  6. Select ‘wp_usermeta‘ and ‘wp_users‘.
  7. Click in the button ‘Export‘.

3 – Edit the *.sql file

  1. Open the downloaded sql file with your favourite code editor.
  2. Find and replace ‘/uploads/sites/3/‘ with ‘/uploads/‘.
  3. Find and replace the URL of the old subsite with the new URL for the single site.
  4. If you think you need to find and replace something else to fit your new installation, this is the moment.

4 – Upload the *.sql file

  1. Create a new database in your server as usual for a new WordPress site.
  2. Import the modified sql of the last stage.
  3. Perform the following SQL Query:
-- delete any usermeta specific to the other subsites
FROM wp_usermeta
WHERE meta_key REGEXP '^wp_([0-9]+)_';

-- duplicate the wp_usermeta structure in a working data table,
-- but add a unique index for filtering out duplicates
CREATE TABLE _fix_usermeta LIKE wp_usermeta;
ALTER TABLE _fix_usermeta ADD UNIQUE (user_id, meta_key);

-- copy the site-specific usermeta, keeping only the last of each duplicate
INSERT INTO _fix_usermeta SELECT * FROM wp_usermeta
WHERE meta_key LIKE 'wp\_%'
ORDER BY user_id, meta_key, umeta_id
ON duplicate KEY
UPDATE umeta_id=
VALUES (umeta_id), meta_value=
VALUES (meta_value);

-- remove the first of each duplicate
FROM wp_usermeta
WHERE meta_key LIKE 'wp\_%'
  AND NOT EXISTS(SELECT * FROM _fix_usermeta WHERE umeta_id = wp_usermeta.umeta_id);

-- remove that working data table
DROP TABLE _fix_usermeta;

5 – Prepare the file structure for the new site

  1. Download and copy the files for a new WordPress installation.
  2. Copy all the plugins and themes folders from the Multisite to the new site.
  3. Copy ‘/media/sites/3‘ to ‘/wp-content/uploads‘ in the new site.

6 – Et voilà!

  1. Enter in your new site dashboard and check everything works good.
  2. Finished!

Share: Link copied to clipboard


Previous: RSCSS
Next: Script to make paged online tests with jQuery and Bootstrap

Where: Home > Technical > WP Multisite to single WordPress manual installation