Advanced WordPress Database HTTPS Search + Replace with WP-CLI

When migrating your WordPress site to https you can have leftover http references causing mixed content warnings and missing green secure locks in browsers! Despite using a migration tool or some plugin to do a simple search and replace, they don’t catch all the http references which can cause mixed content errors errors and insecure … Learn more…

Automatically Delete Expired WordPress Transients using WP-CLI

WordPress plugins and themes can store a lot of temporary data known as transients. By default these transients are stored in the wp_options table and can accumulate rather quickly. Recently on codeable I cleaned a site with 58000 expired transients that were causing the database to crash daily. Due to an idiosyncrasy in WordPress core, … Learn more…

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 is the multisite domain

The subsite I want to extract is the subdomain

mkdir /tmp/
mkdir /tmp/

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 (

cd /var/www/

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

InfoSubsite 1 ValuesSubsites > 1 Values
Database prefixwpb_wpb_2_
Plugins folderwp-content/pluginswp-content/plugins
Themes folderwp-content/themeswp-content/themes
Uploads folderwp-content/uploads

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 subsite

| blog_id | url                                      | last_updated        | registered          |
| 1       |                    | 2017-02-14 16:09:14 | 2017-01-31 11:35:34 |
| 2       |             | 2017-02-21 21:30:15 | 2017-01-31 11:37:04 |
| 3       |           | 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 --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 multisite’s subsite

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/

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/ -c > /tmp/

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

tar -czf /tmp/ -C /var/www/ .

Similarly, compressing the themes folder is done from the multisite root folder

tar -czf /tmp/ -C /var/www/ .

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/ -C /var/www/ .

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/ -C /var/www/ . --exclude=sites

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

  • /tmp/
  • /tmp/
  • /tmp/

The database has both an sql and gzipped versions

  • /tmp/
  • /tmp/

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 but if you made a new domain name just replace that value.

cd /var/www/

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

wp db import /tmp/

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 as the original string to find and as its replacement

wp search-replace "//" "//" --dry-run --allow-root

This will perform the actual replacement

wp search-replace "//" "//" --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 with your new single site path

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

wp search-replace /var/www/ /var/www/ --dry-run --allow-root

Please replace your multisite path with your new single site path

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

wp search-replace /var/www/ /var/www/ --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/ -C /var/www/

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

tar -xf /tmp/ -C /var/www/

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

tar -xf /tmp/ -C /var/www/

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

sudo chown -R www-data:www-data /var/www/
sudo find /var/www/ -type f -exec chmod 644 {} +
sudo find /var/www/ -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_


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!


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

WP-CLI WordPress Provision Script for New Installations

I have previously have demonstrated how WP-CLI can be used to batch install plugins. If you are on Cloudways or Siteground then you have WP-CLI access and get easily provision new installations. If you are running wplib box, VVV or something else Vagrant based you likely have WP-CLI as well. Automatically install the following on your local install … Learn more…

Install APCu Object Cache for PHP7 for WordPress on Ubuntu 16.04

WordPress object cache is a common way to speed up your site. PHP-APCu on Ubuntu 16.04 can help achieve your goal of supreme WordPress or WooCommerce speed. This guide will show you how to install APCu Object Cache for PHP7 for WordPress on Ubuntu 16.04. There are three installation methods outlined here to best suit … Learn more…