Extracting Subsite from WordPress Multisite with WP-CLI and bash •

Extracting WordPress subsites from multisite can be an arduous challenge if you are unfamiliar with the multisite database and folder structure. This guide walks you through step by step to extract the subsite from multisite using WP-CLI and bash.

If it any point you struggle with this and want the subsite extracted for you then feel free to get in touch on Codeable.

Converting WordPress Multisite Subsite to Single Site with WP-CLI and bash

Create Temporary Folders

Here wp-bullet.com is the multisite domain

The subsite I want to extract is the subdomain guides.wp-bullet.com

mkdir /tmp/wp-bullet.com/
mkdir /tmp/wp-bullet.com/guides.wp-bullet.com

Now the basic folders exist for our multisite and subsite.

Extracting Database for Subsite

Enter your multisite root, usually in a similar path to below, remember to change your multisite path (wp-bullet.com)

cd /var/www/wp-bullet.com

Before getting a list of all subsites here is the basic structure of the folders

Info	Subsite 1 Values	Subsites > 1 Values
blog_id	1	`2`
Database prefix	`wpb_`	`wpb_2_`
Plugins folder	wp-content/plugins	wp-content/plugins
Themes folder	wp-content/themes	wp-content/themes
Uploads folder	wp-content/uploads	wp-content/uploads/sites/2/

Get a list of all subsites with WP-CLI

wp site list

You will get this formatted output below which is critical to extracting the WordPress subsite correctly.

The blog_id is 2 which the database prefix is made up of for the url value guides.wp-bullet.com subsite

+---------+------------------------------------------+---------------------+---------------------+
| blog_id | url                                      | last_updated        | registered          |
+---------+------------------------------------------+---------------------+---------------------+
| 1       | http://wp-bullet.com/                    | 2017-02-14 16:09:14 | 2017-01-31 11:35:34 |
| 2       | http://guides.wp-bullet.com/             | 2017-02-21 21:30:15 | 2017-01-31 11:37:04 |
| 3       | http://security.wp-bullet.com/           | 2017-02-21 21:30:48 | 2017-01-31 11:37:33 |
+---------+------------------------------------------+---------------------+---------------------+

WP-CLI allows you to export database tables specific to each subsite by simulating that the request came from the subsite’s URL.

wp db export --tables=$(wp db tables --url=guides.wp-bullet.com --format=csv --skip-plugins --skip-themes --allow-root) /tmp/subsite.sql --allow-root

Get the WordPress database prefix from wp-config.php with grep

grep "table_prefix" wp-config.php

Here wpb_ is the database prefix

$table_prefix = 'wpb_';

The subsite database prefix is made up of the multisite prefix wpb_ and the blog_id (subsite id) 2 for the wp-bullet.com multisite’s subsite guides.wp-bullet.com

If your blog_id is 1 then you should skip this step!

This uses sed to replace the database table names and redirect to a new sql dump we can use to import into the single site installation.

sed "s#wpb_2_:#wbp_#g" /tmp/subsite.sql > /tmp/wp-bullet.com/guides.wp-bullet.com/database.sql

If you are copying the database – regardless of your blog_id – to a remote location you can gzip it so it’s much smaller

gzip /tmp/wp-bullet.com/guides.wp-bullet.com/database.sql -c > /tmp/wp-bullet.com/guides.wp-bullet.com/database.sql.gz

Extracting WordPress Subsite Contents

We need to compress these folders and extract them to the new singe site installation later

WordPress plugins
WordPress themes
WordPress wp-content uploads which contains images, videos etc for that subsite

Compressing the plugins is done from the multisite root folder wp-bullet.com

tar -czf /tmp/wp-bullet.com/guides.wp-bullet.com/plugins.tar.gz -C /var/www/wp-bullet.com/wp-content/plugins/ .

Similarly, compressing the themes folder is done from the multisite root folder wp-bullet.com

tar -czf /tmp/wp-bullet.com/guides.wp-bullet.com/themes.tar.gz -C /var/www/wp-bullet.com/wp-content/themes/ .

If your blog_id is 1 then you should skip this command

The wp-content uploads folder is specific for the subsite using 2 as your blog_id (subsite id) for the 2 subfolder in the uploads/sites folder

tar -czf /tmp/wp-bullet.com/guides.wp-bullet.com/uploads.tar.gz -C /var/www/wp-bullet.com/wp-content/uploads/sites/2/ .

If your blog_id is 1 then you should use this command since the blog_id 1 is special and has its uploads folder in the root of wp-content rather than in the sites subfolder of the uploads folder.

Just excluding the sites subfolder with the other subsites’ uploads folders provides a tar for just blog_id 1 subsite

tar -czf /tmp/wp-bullet.com/wp-bullet.com/uploads.tar.gz -C /var/www/wp-bullet.com/wp-content/uploads/ . --exclude=sites

Now you have 3 tar files containing the essential components for your WordPress multisite subsite.

/tmp/wp-bullet.com/guides.wp-bullet.com/plugins.tar.gz
/tmp/wp-bullet.com/guides.wp-bullet.com/themes.tar.gz
/tmp/wp-bullet.com/guides.wp-bullet.com/uploads.tar.gz

The database has both an sql and gzipped versions

/tmp/wp-bullet.com/guides.wp-bullet.com/database.sql
/tmp/wp-bullet.com/guides.wp-bullet.com/database.sql.gz

These tars will be extracted to the single site in the next section.

Restore Converted Subsite to Single Site

We need to perform the following tasks to make the converted multisite subsite to single site

Import the WordPress database from subsite to single site
Search and replace in the database
Extract wp-content tars from subsite to single site
Fix permissions if necessary

Import WordPress Database from Subsite to Single Site

Enter your fresh single site installation with your WordPress files, here it is guides.wp-bullet.com but if you made a new domain name just replace that value.

cd /var/www/guides.wp-bullet.com

Import database for the subsite and import it into the WordPress single site installation

wp db import /tmp/wp-bullet.com/guides.wp-bullet.com/database.sql

If you want to use a different domain or subdomain name then do a search and replace with WP-CLI.

If you are using a subdomain that you want to keep the same then you can skip these next two search-replace steps.

It uses guides.wp-bullet.com as the original string to find and wp-bullet-guides.com as its replacement

wp search-replace "//guides.wp-bullet.com" "//wp-bullet-guides.com" --dry-run --allow-root

This will perform the actual replacement

wp search-replace "//guides.wp-bullet.com" "//wp-bullet-guides.com" --allow-root

Regardless of your subdomain choice you should do a search-replace command for the old and new main domain path.

You should replace your multisite path wp-bullet.com with your new single site path wp-bullet-guides.com.

The --dry-run flag only shows you which replacements will be made.

wp search-replace /var/www/wp-bullet.com /var/www/wp-bullet-guides.com --dry-run --allow-root

Please replace your multisite path wp-bullet.com with your new single site path wp-bullet-guides.com.

You can do the actual search and replace by omitting the --dry-run flag

wp search-replace /var/www/wp-bullet.com /var/www/wp-bullet-guides.com --allow-root

If you happen to have additional problems you may want to read the advanced database search and replacement guide for WordPress.

Extract wp-content

Extract the themes.tar.gz to your new single site installation

tar -xf /tmp/wp-bullet.com/guides.wp-bullet.com/themes.tar.gz -C /var/www/guides.wp-bullet.com/wp-content/themes/

Next, extract the plugins.tar.gz to your new single site installation

tar -xf /tmp/wp-bullet.com/guides.wp-bullet.com/plugins.tar.gz -C /var/www/guides.wp-bullet.com/wp-content/plugins/

Finally, extract the uploads.tar.gz to your new single site installation

tar -xf /tmp/wp-bullet.com/guides.wp-bullet.com/uploads.tar.gz -C /var/www/guides.wp-bullet.com/wp-content/uploads/

Fix permissions, if www-data isn’t the owner’s user and group change them to the correct values.

Remember to change your new single site installation path guides.wp-bullet.com

sudo chown -R www-data:www-data /var/www/guides.wp-bullet.com
sudo find /var/www/guides.wp-bullet.com -type f -exec chmod 644 {} +
sudo find /var/www/guides.wp-bullet.com -type d -exec chmod 755 {} +

You can remove these tables using Adminer or phpMyAdmin, remember to adjust the database prefix if yours is not wp_

wp_blogs
wp_blog_versions
wp_registration_log
wp_signups
wp_site
wp_sitemeta
wp_sitecategories

You can also use WP-CLI to drop these tables with the wp db query command.

wp db query "DROP TABLE IF EXISTS wp_signups, wp_site, wp_sitemeta, wp_sitecategories, wp_registration_log, wp_blog_versions, wp_blogs;"

That should do it!

Sources

tar Directory without Storing Absolute Paths
WP-CLI db tables command
Extracting Subsite from Multisite
Deleting Multiple MySQL Tables
10up Multisite Package