How to Move Local Singlesite Database to Remote Multsite Database?

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:

End.

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

Related Posts:

  1. WP-CLI methods for get_current_blog_id, get_blog_details or $wpdb->prefix
  2. Migrating WPMS from apache to IIS7 causes Database Error
  3. Migrating Multisite – Database error, missing wp_blogs table
  4. WPML tables not deleted when removing multisite subsite, need help clearing up my sql-file! [closed]
  5. Database Tables Lost Indexes? How to Recover?
  6. Will changing the folder name two directories above a fresh wordpress installation break the site?
  7. 2 website 1 database… Local to online
  8. new multisite, old db
  9. how to migrate wpmu from one host to the other?
  10. WP MultiSite: Can’t select database error after changing to new domain
  11. How to migrate subsite from dev multisite to production multisite
  12. Add WordPress MU Network Admin via Database
  13. How to tell which database tables belongs to which multisite site
  14. find a random blogid across my multisite network that has at least one post published
  15. Multisite: How to store global options vs. site options?
  16. Organizing WP Pages based on old website database
  17. Transfer Subdomain site to Multisite setup
  18. Is there a wordpress function to cleanly delete an entry in the signups table?
  19. Which is the recommended method to transfer a wordpress site to wordpress Multisite?
  20. Multiple Domains and Subdomains Using Multisite Installation
  21. Move a WordPress multisite to subfolder (the whole site)?
  22. How to change a WordPress network domain
  23. Synchronizing Two WordPress Sites Content
  24. Would manually deleting the dumping data fix a “#1062 – Duplicate entry ‘1’ for key ‘PRIMARY'” phpMyAdmin error?
  25. SQL to check how many sites a user is a member of
  26. How to clone and locally run a network for testing
  27. Suggestions for WPMU Migration (advanced)
  28. Two WordPress sites sharing the same database
  29. Issue converting single site to multi-site – blogs.dir folder not created
  30. Multisite Subdomain Using More Resources Than Others?
  31. Importing Multiple DB to one MU database
  32. What is the best practice to get a site from local to online?
  33. Make WordPress Search for Both Main Domain and Subdomain
  34. WordPress mutisite migration
  35. Does WordPress MultiSite use separate MySql databases?
  36. Bootstrapping WordPress MultiSite Outsite of WordPress – No $wpdb
  37. Migrate a WP website from a single-server single-domain to a multi-server multi-domain installation
  38. Multisite setup creating custom table
  39. Within the database, where is the flag which says that a user has Super Admin rights?
  40. Bulk activate a theme on multisite
  41. phpMyAdmin displays error when importing database
  42. Redirect only posts to New Domain
  43. Installed domain-based Multisite but can’t access wp-admin (redirect loop)
  44. Multiple multisite instances connecting to the same database
  45. Query to import data from one MultiSite table to another (pages/sub-pages)
  46. Migrating wordpress multisite and domain mapping plugin
  47. WordPress Multisite – can I somehow merge the tables?
  48. How to move 1 WP site from a mult-site environment to a different multi-site area
  49. WordPress Database Huge wp_usermeta Table
  50. Least expensive way to get table prefix in multisite installation
  51. Better way to migrate to server?
  52. Removing Mutlisite lines stop DB connection
  53. Share DB tables between two blogs on the same WP Multisite installation
  54. Migrating Multisite with WP-Cli and/or Search-Replace-DB
  55. I want to create a 300 multi-site wordpress network using subdomains
  56. How can I create blog specific database table in multisite?
  57. Is it best to avoid using $wpdb for security issues?
  58. How many databases do I need?
  59. How to migrate a database from a server to another
  60. WordPress Multisites or Multiple WP Single sites?
  61. $wpdb->update prefixes database name to table when executing query
  62. Combining user database tables while keeping all other data in seperate for multiple sites?
  63. How to make multi site with single Database?
  64. Update WP install to WP network & it broke the DB connection
  65. Minimize Performance Issues of MultiSite Large Number of Tables Created
  66. importing data into wordpress db
  67. How to share User Database between Two Multisite Installations + More
  68. Change WordPress Multisite Domain
  69. WPMU on MySQL limited to 1GB of space?
  70. How does WP decide how to evaluate database integrity
  71. Migrating Multisite setup to a different domain
  72. A database for each category?
  73. how to convert a wp multisite into a single site with content from subsites
  74. I wanted to Know that how can I give ingore to 0000-00-00 in my config file
  75. After switch domain, some parts of my site still point to my old domain
  76. A query to substitute images paths in gutenberg (website migration)?
  77. allow multisite in the installation process
  78. Error establishing a database connection after moving local wordpressmultisite from one directory to another
  79. Can I replace the “Upgrade Network” with SQL query?
  80. WordPress Multisite keeps redirecting to HTTPS
  81. Migrating a WP site with 2 WP installations
  82. Multisite network on new domain
  83. Moving wordpress multisite to new domain
  84. Assign user to belong to a subsite in Multisite instance
  85. wordpress multisite – handle Database
  86. Importing Posts into New Website with Same User ID’s
  87. WordPress automatic Login on other page?
  88. How to retrieve user data based on role using SQL?
  89. Multi-site database upgrade claims success, but db_version not updated
  90. Get multisite count without php?
  91. Multiple domain with one database
  92. WordPress and SQL – Update and Insert from another table if column value doesn’t exist
  93. Export to WXR for pages containing script and iframe tags
  94. Moved my site to new host now getting 404
  95. Plugin data shared in Multisite
  96. WordPress multisite and one database
  97. Post migration from one site to another site in a network
  98. 4th Network Site Database Errors?
  99. Moving a WordPress Multisite site to a new install
  100. HyperDB, Multisite – on new site creation put site’s tables to specific database