How to Move Local Singlesite Database to Remote Multsite Database?

Question

I'm fairly new to Wordpress and I'm a little confuse on how to take the database from my local server and replace it with the one on the server on a Wordpress multisite.

I've replace database before from local to server, but it was a multisite.

The step I usually take is:

1. backup the database on the server
2. export local database
3. drop all table on the server database
4. import the local database
5. go to wp_option
6. change value for siteurl and home to the site url

When I try that, the table on the server is very different from the one on my local.

My local database structure looks like this:

Usually the server database is the same, but not in this case though.

Server database structure:

So i'm a little confuse on how to tackle this. Can I still do what I usually do, or is there additional steps I need to take?

Thanks in advance.

Edit: Think I overcomplicated this a bit, I could just create a new database on the server and point the wp_config.php file to that instead and everything should be good

Edit 2: nope doesn't work like I thought it would. keep getting redirect loop error

Edit 3: I finally got everything to migrate properly all thanks to @Michael Ecklund! Since I was updating the database of the primary page I didn't need to create a new site, but still follow the rest of the instruction he wrote and it went fairly smooth. A bit of confusion at first, but that was my own lack of knowledge.

Edit 4: Just a FYI, if after doing the database migration and you somehow lost access to the dashboard. Go into your main database and in the *_options (in my case the tenp_options) table and look for an wp_user_roles under the option_name column and change that column name to your site prefix (which in my case is tenp_) and you should now be able to access your dashboard again.

Answer 1

Merging WordPress Standalone Installation with WordPress Multisite Installation

Before You Begin:

1. Create a new site in your Multisite Network, which will be the site you're migrating from the Standalone installation. (Take note of the site ID #, you'll need it later.)
2. You need to make sure that all of the users from the Standalone installation, are created and exist in the Multisite installation.

Step 1 - Backup Databases.

Find the directory on your Web Server which the the Standalone copy of WordPress is installed and the directory on your Web Server which the Multisite copy of WordPress is installed and open the wp-config.php file of both installations. Locate the PHP constant DB_NAME. It contains the name of the database that particular installation of WordPress is using.

1. Backup the Standalone instillation's database.
2. Backup the Multisite installation's database.

Step 2 - Identify the Database Table Prefixes.

By default the database table prefix is wp_.

If you can't identify the database table prefix just by examining the database. You can look in the base directory of your WordPress installation and open the wp-config.php file of the site in question and look for a line like $table_prefix = 'wp_';.

In your situation, it looks like:

• The Standalone installation's database table prefix is the default of wp_.
• The Multisite installation's database table prefix is custom of tenp_.

Step 3 - Export Databases. Import Into Local Environment.

On a local Database Server, create a temporary database for each of these databases. Perhaps to keep things simple, label one database "standalone", and the second one "multisite".

• Import the Standalone installation's database (which you just exported) into the "standalone" database (which you just created) on your local Database Server.
• Import the Multisite installation's database (which you just exported) into the "multisite" database (which you just created) on your local Database Server.

Step 4 - Search and Replace.

This is the step where you would likely replace any necessary URL changes. (http to https), (non-www to www), (add or remove directories from URL), etc.

Perform this task on the "standalone" database you created on your local Database Server.

Remember to change things to how you would like them to be in the Multisite installation (the end result).

For this procedure, you're going to need a Database Tool:

1. WP-CLI :: wp search-replace which is all command line.
2. Alternatively, if you prefer a GUI, there's the Database Search and Replace Script in PHP by interconnect/it.

Step 4.2 - Users and Post Authors

You'll want to probably create a note about all the user's from your Standalone installation and map the old user ID's to the new user ID's (from the Multisite installation).

You can simply just create a temporary text file and do something like this:

1 => 4
8 => 23
15 => 9

The numbers on the left are the ID's from the Standalone installation and the numbers on the right are the ID's on the Multisite installation.

You'll then want to update the post_author column of the wp_posts table to update all of the old user ID's to the new user ID's. Otherwise when view your migrated site from Standalone to Multisite, you're going to be one confused kitten. It's going to say things on your site were posted by random people and probably even people from different sites in your network. This can be catastrophic if overlooked.

For each of the user ID mappings in your text file, you'll want to issue a command much like this into MySQL:

UPDATE wp_posts
SET post_author = '4'
WHERE post_author = '1'

• The line SET post_author = '4' is the new user ID (the user ID from the Multisite installation)
• The line WHERE post_author = '1' is the old user ID (the user ID from the Standalone installation)

Step 5 - Users and Usermeta

I haven't really found a good solution for this step yet. I usually just recreate the users manually in the Multisite installation.

In otherwords, I usually just drop two tables wp_users and wp_usermeta.

If anyone would like to improve on this step feel free to edit and add guidance here.

Step 6 - Update Database Table Names

This step is much like Step 4.

You'll want to map old table names to new table names in the "standalone" database on your local Database Server.

This is the step where you will need to know the site ID # from your Multisite installation.

As an example: If your site ID is 15, and your database table prefix use on your Multisite installation is tenp_, then the database table wp_posts would become tenp_15_posts.

Here's the MySQL command you can use to update your database table names:

RENAME TABLE `wp_posts` 
TO `tenp_15_posts`;

• The first line is the old database table name (the database table name from the Standalone installation)
• The second line is the new database table name (the database table name format to be used in the Multisite installation)

Alternatively, if your database is small enough. You could just export the entire database and open it in a text editor. Then find all & replace. Save it when completed.

Step 7 - Export and Import

Once all of the above changes have been made, export the "standalone" database from your local Database Server.

1. Import the exported .sql file into the "multisite" database on your local Database Server.
2. Export the "multisite" database from your local Database Server.
3. Drop the current Multisite database tables used for your existing Multisite database (not the one on your local Database Server).
4. Import the .sql file for your "multisite" database you exported from your local Database Server and import it to your database used by your existing Multisite installation (the one you just dropped all the tables from). Essentially just replacing the current Multisite installation's database with the modified one which contains the newly migrated Standalone site.

---

Moving Uploads files from ./wp-content/.

Standalone and Multisite store uploaded files differently. Multisite stores them in ./wp-content/sites/{$site_id}/. Make sure you move your uploaded files appropriately as well.

---

Changing the Primary Site:

Look for database table wp_site in your Multisite database. Edit the column id and domain appropriately.

You might need to also edit the site_id column in the wp_blogs table.

Also look in wp-config.php for these lines and once again, adjust them accordingly.

define( 'DOMAIN_CURRENT_SITE', 'www.your-domain.com' );
define( 'SITE_ID_CURRENT_SITE', 1 );
define( 'BLOG_ID_CURRENT_SITE', 1 );

---

Useful Links:

• Moving WordPress
• Migrating Multiple Blogs into WordPress 3.0 Multisite

---

End.

If anything is confusing, please comment and I'll try to clear it up.

Answer 2

The database is indeed configured different for a multiwp, those tenp_12 are prefixes for each site in the multisite. It seems the site you run locally isnt a multisite. My suggestion if you're trying to add another site (not primary) is to first add it from the multisite dashboard, drop tables on that one (i.e. tenp_x where x resembles your site id) and import into those tables. This would require you to preamptive rename the table name aswell as collumns, after which you may configure value for siteurl and home to the site url as you describe in point 6.

Answer 3

I followed this tut step by step and successfully migrated 8 sites out of multisite. I am SO grateful for this. Thank you!!!

In my case, I had set up the sites as subdomains using a wildcard subdomain in cPanel. We wanted to keep the sites in their respective subdomains once extracted to single sites. In other words, the urls would not change. This created the following extra steps for us, which I am sharing in case anyone else has a similar set up.

1. Pause CloudFlare (if you're using it) for the subdomain that you are moving out of multisite.
2. Follow the steps in this tutorial to get all of the data out of the site (subdomain).
3. Go into WP Network Admin and set the site to "Archive".
4. Add the subdomain to cPanel (remember, we were using wildcard subdomain in mu - now we need to create a named subdomain record so we have a directory to install to).
5. Once this subdomain is set up, go back to the steps in this tutorial and install the new site into the subdirectory.
6. Make sure the site is working as expected. If it is, you can go into WP Network Admin and delete the site that you just migrated or leave it a few days in case you need to roll back.
7. Turn CloudFlare back on. You're good to go.

Again, I am sharing because this tutorial got me through a really stressful process. Thank you again!