Category: Plugins

WP Migrate DB Manually : Multisites & Single Sites

Recently, I needed to migrate a WordPress site from a Multisite installation to a Single Site .com.

I usually use the WP Migrate DB plugin because it works well for my basic needs, but going through the Multisite -> Single Site transition very manually, sure made me see the value in the Pro version of their plugin.

For a full video tutorial walkthrough of the transfer process, I highly recommend Morten Rand-Hendricksen’s Lynda.com course on the subject. Below are a few step-by-step notes detailing the process.

Setting things up

  1. Setup your <TARGET> site first
    1. Install WordPress with default options
  2. Migrate plugins, themes, and uploads
    1. Move them from the <ORIGIN> site -> the <TARGET> site *
    2. Using FTP, I download what I want to keep to my Desktop
    3. Then, upload them back to the NEW site (watch a video, it’ll take a while)
  3. Install WP Migrate DB on both sites and open it

migrate_db

Single Site -> Single Site

  1. From the <TARGET> site:
    1. Copy the URL -> <ORIGIN> site’s “New URL”
    2. Copy the file path -> <ORIGIN> site’s “New file path”
  2. On the <ORIGIN> site:
    1. Export the SQL file
  3. Prepare <TARGET> site’s wp-config.php file **
    1. Open <ORIGIN> site’s wp-config.php file
    2. Find $table_prefix down in the file and copy that table prefix String
    3. Replace $table_prefix String in the <TARGET> site’s wp-config.php with the <ORIGIN> site’s String
    4. Save the modified <TARGET> site’s wp-config.php
  4. Open phpadmin on the <TARGET> site
    1. Drop all <TARGET> site’s default wp_ tables
    2. Import <ORIGIN> site’s SQL file (from WP Migrate DB)

Multisite -> Single Site

I did all the previous steps for the Multisite configuration (to test it), but ended up with the default site and not any of the Posts or content I needed in my subsite. Luckily, DeliciousBrains has written up a tutorial detailing this kind of migration as well:

Extracting a Subsite from Multisite to Create a New Single Site Install

These are the steps I followed (and some notes about mistakes I made):

Notes:

* Originally, when you want to move your uploads folder from Multisite to a single site, you’ll notice another folder in the uploads folder:

  • /uploads/sites/4/2016/
    • The sites folder contains all the uploads for each subsite
    • The 4 (or other numbered folder) is the site ID (you can find the site ID by going to that particular site’s admin Page)
      • http://sitename.com/wp-admin/network -> Sites -> Subsite name
      • http://sitename.com/wp-admin/network/site-info.php?id=4
  • So, you move the content from the site ID’s folder to your <TARGET> site’s uploads folder
    • /uploads/sites/4/2016/ -> /uploads/2016/

** This also means that your $table_prefix String in wp-config.php will include this site ID between the wp_ and the rest of the String:

  • Ex: $table_prefix = 'wp_ex54gTv'; will be 'wp_4_ex54gTv' in the SQL database for your particular subsite

1. Prepare the SQL Export

  1. Migrate plugins, themes, and uploads
    1. Move them from the <ORIGIN> site -> the <TARGET> site *
  2. Replace URL and file path from the <TARGET> site:
    1. Copy the URL -> <ORIGIN> site’s “New URL”
    2. Copy the file path -> <ORIGIN> site’s “New file path”
    3. Click “Add Row” to add subsite details
    4. //subsite.sitename.com -> //newsite.com
    5. Click “Add Row” to replace your uploads folder Strings
    6. /uploads/sites/4/ -> /uploads/

migrate_db_subsite

Now, you’re ready to Export the file.

DeliciousBrains says:

  1. Expand the “tables” section and select the “Migrate only selected tables below” option.
  2. Select all the relevant subsite tables as well as the “users” and “usermeta” tables.

However, that wasn’t an option for me while using the free version of the plugin. Therefore, ALL the next steps were very manual. But, I think the image from their website is helpful to know which tables to keep and which to get rid of in your SQL file:

Source: DeliciousBrains.com
Source: DeliciousBrains.com

Basically, you will want to KEEP all the WordPress data for your subsite (everything with the site ID between underscores _4_ and DELETE all the WordPress data for the main site.

Notes:

* This was one of my problems going through. I didn’t DELETE all the main site data from the SQL file, so it would import the subsite data, then overwrite it with the main site data.

2. Prepare the SQL upload

  1. After preparing everything as described previously, Export the SQL file
    1. I turned OFF Gzip so I could immediately modify the file after download
  2. Modify the .sql file in any code or text editor
    1. Go ahead and go through and DELETE all the SQL tables that we didn’t need to select (everything that ISN’T blue in the image above).
    2. These will be listed in the alphabetical order shown in the image, so should be easy to find. DELETE everything from wp_blog_versions to wp_terms (because you’ll notice you still have wp_4_terms and the like that contains your subsite data
    3. Find and Replace any instances of wp_4_ with just wp_
    4. Save and close the file
  3. Open phpadmin on the <TARGET> site
    1. Drop all <TARGET> site’s default wp_ tables
    2. Import the NEW SQL file (you just modified)
  4. Run cleanup
    1. DeliciousBrains has some SQL queries you can run to remove duplicate usermeta rows as well – these didn’t work for me as the syntax in my phpadmin was wrong, so I manually deleted old users, etc.
    2. Alternatively, it looks like this SQL script will do the job as well

Problems?

  1. Whitescreen of Death? Your theme may be missing from the target site – /wp-admin will still work – choose a different theme, or be sure to upload the one you want
  2. Manual DB import fails? Don’t use GZIP and export it again (larger file size)
  3. WP Migrate DB ate my posts? This is a migration, not merge – use WP Migrate DB Pro’s push/pull if you just want “updates”
  4. Media files not working? Be sure everything is uploaded (and for Multisite, be sure you changed the upload folder during your SQL export – from /uploads/sites/4/ to /uploads/
  5. Migration failed? Be sure the $table_prefix Strings in your <TARGET> site wp-config.php match those in the <ORIGIN> site’s wp_config.php

Was this helpful?

I hope this write-up was helpful. Let me know in the Comments. I plan to use these notes for my next Migration as well.

Side note: WP Migrate DB Pro can do all of this FOR you automagically. It can also UPDATE your site if there is newer content on one side or the other using their push/pull function. It would be a great option if you do lots of migrations or just want to save yourself the headache of doing it all manually.