MySQL Query to Migrate WordPress Multisite Database

MySQL Query to Migrate WordPress Multisite Database

MySQL Query to Migrate WordPress Multisite Database

Migrating WordPress multisite is a very difficult task. It’s not as straightforward as it is with single-site. The database structure of WordPress Multisite is very different as compared to WordPress single website.

WordPress multisite creates a bunch of database tables for one child website. and this is the main reason for the difficulty of WordPress multisite migration.

Although there are many plugins for migrating WordPress sites, but sometimes, in some cases, we need to migrate websites manually. So in this case we have to manually download the website’s files and database and put it on the new server. We will put the files on the direct server but we cannot import the website database directly if the website has to run on the new host.

If you want to migrate wordpress multisite to a new host then you have to first replace the old URL from the database with a new URL. and for this, you have to run some MySQL queries. But which MySQL queries? 🙂

Here it is…

Multisite Database Migration Queries

First of all, we have to run the MySQL query in the main three tables from the database.

  1. wp_site
  2. wp_sitemeta
  3. wp_blogs

UPDATE wp_site SET domain = replace(domain, 'old.com', 'new.com');
UPDATE wp_sitemeta SET meta_value = replace(meta_value, 'https://old.com', 'https://new.com') WHERE meta_key = 'siteurl';
UPDATE wp_blogs SET domain = replace(domain, 'old.com', 'new.com');


Now we have to update the tables of the main website.

UPDATE wp_options SET option_value = replace(option_value, 'https://old.com', 'https://new.com') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET guid = replace(guid, 'https://old.com', 'https://new.com');
UPDATE wp_posts SET post_content = replace(post_content, 'https://old.com', 'https://new.com');


As described earlier wordpress multisite creates a bunch of database tables for one child website. so now we have to replace URLs from the child website tables.

Here I only prepare queries for only one child website. so in this case our database table prefix is wp_1_

WordPress creates the table prefix by the site ID. Therefore, as many child websites as you have, there will be as many bunches of tables in the database.

UPDATE wp_1_options SET option_value = replace(option_value, 'https://test.old.com', 'https://test.new.com') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_1_posts SET guid = replace(guid, 'https://test.old.com', 'https://test.new.com');
UPDATE wp_1_posts SET post_content = replace(post_content, 'https://test.old.com', 'https://test.new.com');

Now you have to use the above three queries for another child website, just make sure to replace the site id and site URL.

WordPress multisite migration queries

I have created a tool using which you can generate WordPress multisite migration queries. this tool is a great time saver. Just generate the queries and run them in MySQL.

If you would like to generate a MySql query for a single WordPress site, then you can generate it from here.

NOTE
Before running the MySql query in the database, remember to backup any database you are going to change. You have been warned!

Generate Queries for Main Website

Copy to clipboard

Generate Queries for Child Website

Copy to clipboard

Need more detailed instructions? Check out the WordPress CODEX

I trust this post has been helpful, and now you’ve got a working migrated WordPress multisite website to a new host.

Share your love

One comment

  1. […] MySQL Query to Migrate WordPress Multisite Database […]

Leave a Reply