skip to Main Content
WordPress Multisite Database Migration

Recently we did a WordPress Multisite Database Migration on Trellis. All is pretty straightforward files wise though we did have to do some extra media work using a volume. The database on the other hand was a bit more problematic.

WP CLI Database Import & URL Replacement

Using wp-cli and aliases for the import and replacement:

# sync-db-prod-to-staging.sh
# chmod +x sync-db-prod-to-staging.sh
read -r -p "Do you solemnly swear that you have had fewer than 2 alcoholic beverages in the last hour and that you would really like to reset your staging database and pull the latest from production? [y/N] " response
if [[ $response =~ ^([yY][eE][sS]|[yY])$ ]]; then
wp @staging db reset --yes &&
wp @production db export -> sql-dump-production.sql &&
wp @staging db import sql-dump-production.sql &&
wp @staging search-replace --network --url=domain.com http://domain.com https://staging.domain.com --skip-columns=guid
fi

things didn’t work out. The database was not imported properly. It was a large database so we decided to just import it.

WP CLI Search & Replace

For the replacing of the urls we tried to use WP CLI however. This as this did work locally on a new Vagrant setup. So we did the following command:

wp search-replace --network --url=domain.com http://domain.com https://staging.domain.com --skip-columns=guid

we got the following error:

2018/04/22 08:04:11 [error] 13636#13636: *92588 FastCGI sent in stderr: "PHP message: WordPress database error Table 'staging_domain_com_staging.wp_blogs' doesn't exist for query SELECT  blog_id FROM wphsm_blogs  WHERE domain IN ( 'staging.domain.com' ) AND path IN ( '/de/', '/' )  ORDER BY CHAR_LENGTH(path) DESC LIMIT 1 made by require('wp-blog-header.php'), require_once('wp-load.php'), require_once('/srv/www/staging.domain.com/releases/20180418081603/web/wp-config.php'), require_once('wp-settings.php'), require('wp-includes/ms-settings.php'), ms_load_current_site_and_network, get_site_by_path, get_sites, WP_Site_Query->query, WP_Site_Query->get_sites, WP_Site_Query->get_site_ids" while reading response header from upstream, client: xx.xxx.xx.xx, server: staging.domain.com, request: "GET /de/europe/country/province/region/flaine/feed HTTP/1.1", upstream: "fastcgi://unix:/var/run/php-fpm-wordpress.sock:", host: "staging.domain.com"

NB Related Trellis issue

Oddly enough the table was there. In another Github issue https://github.com/Chassis/Chassis/issues/324 I read we should use wp core multisite-convert .Or at least that helped him, but we were just cloning the multisite database from production to staging here so why should I use that?

Manual Multisite unique table changes

We did some more research and bumped into a great article by Eric Holmes. So we did some manual database changes in:

  • wp_blogs – this holds record of all your WPMU blogs. Alter the domain field(s).
  • wp_options – here do the normal alterations from home_url and site_url, as if you were transferring a regular WordPress website. If you have multiple blogs, you will need to go into wp_2_options, wp-3_options, etc.
  • wp_site – This defines the multisite domain and path. Change the domain here.
  • wp_sitemeta – Search for the meta key siteurl – change the domain.
  • wp_usermeta – Search for the meta key source_domain – change the domain.

Items listed as recommended by Eric and also partly recommended in the Codex .

Media URLS

Now the staging site seemed to be loading again with latest database from the production site. Only the image paths needed updating so we did a wp search replace using:

wp search-replace http://domain.com https://staging.domain.com --skip-columns=guid

This fixed all further mixed content issues and allowed the site to be loaded properly. WordPress Multisite Database Migration complete!

Jasper Frumau

Jasper has been working with web frameworks and applications such as Laravel, Magento and his favorite CMS WordPress including Roots Trellis and Sage for more than a decade. He helps customers with web design and online marketing. Services provided are web design, ecommerce, SEO, content marketing. When Jasper is not coding, marketing a website, reading about the web or dreaming the internet of things he plays with his son, travels or run a few blocks.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top