The Multisite Tools addon extends WP Migrate DB Pro with WordPress multisite specific functionality.
- Push a subsite (including media) to a single site install.
- Pull a single site install (including media) into a subsite.
- Export a subsite’s data as a SQL file ready for importing into a single site install.
The following are some capabilities we’re considering to add to the Multisite Tools addon:
- Push/pull a single site install (database and media) into a multisite install as a subsite
- Push/pull a subsite (database and media) from one multisite install to another multisite install (current workaround)
Push a Subsite to a Single Site Install
With the Multisite Tools addon, push migrations from a multisite install to a single site install of WordPress have a “Push specific subsite” checkbox. Once you’ve picked a subsite from the list, the target table prefix is automatically set and the Find & Replace rows and list of tables are also updated to match. See the following screenshot for an example.
The list of tables you can select for migration is narrowed down to those specific to the subsite, plus the global users and usermeta tables.
All the other options usually available for a push migration are still enabled, including the Advanced Options, Backup Tables and of course Media Files.
This means that if you have the Media Files addon, you no longer need to hunt down the media files for the subsite and manually transfer them to the new site. Multisite Tools takes care of copying the files to the new site and ensuring all links to them are correct in the newly updated site.
The users pushed to the single site install are only those that are currently a member of the subsite, or are authors of migrated content.
To see this in action, check out the video at the top of this article that shows a speed run of pushing a subsite to a single site install, adding users and content and then pulling it back into a subsite.
Pull a Single Site Install Into a Subsite
Multisite Tools includes a “Pull into a specific subsite” checkbox when setting up a pull migration on a multisite install of WordPress that is connecting to a single site install. Now when you check the box and select a subsite to pull into, the target table prefix is automatically set and the Find & Replace rows and list of tables are updated as appropriate. The following screenshot shows an example of this.
All the other options usually available for a pull migration are still enabled, including the Advanced Options, Backup Tables and of course Media Files if you have the Media Files addon.
Multisite Tools takes care of copying the Media Library files into the subsite and ensuring all links to them are correct in the newly updated site.
Where possible, the users being pulled into the subsite are matched up with existing users via their username and email address, as long as they are also a member of the subsite already. If there is no match then the user is imported along with their metadata. Either way, the migrated posts and comments are updated to ensure the correct user record is referenced in the subsite.
The second half of the video at the top of this article shows this in action.
Great Power, Great Responsibility
You may be thinking, “Finally, I can setup one multisite install for developing all my client sites.” While you certainly can do this easier than ever now, just because you can doesn’t mean you should. Instead, we recommend setting up your development environment to mirror the production environment as closely as possible. So if the site will be a single site install in production, set it up as a single site install in development. You’re much less likely to run into issues after moving the site to production if the two environments are similar.
Migrate a Subsite Between Multisites
In the future we intend to add the ability to migrate subsites between multisites with push or pull. However, with the current version of Multisite Tools there is a way to do it already. You can use a single site as an intermediary, push a subsite into it, then from a different multisite install pull it out as a subsite.
- On the source multisite Push the subsite to a single site install.
- On the target multisite Pull the single site install into an empty subsite.
Export Subsite as Single Site Install
When the Multisite Tools addon is installed and activated on a multisite install of WordPress, you will see an “Export a subsite as a single site install” checkbox above the Find & Replace rows when the “Export File” migration option is selected.
When this new checkbox is checked a new select box is shown for selecting the subsite to export.
Once you’ve selected a subsite you’ll be presented with a “New Table Name Prefix” textbox. By default this textbox shows the multisite’s base table name prefix, but you can change it to match the table name prefix that your single site install will use.
When exporting a subsite for a single site install the “Tables” select box will show only the tables that belong to the selected subsite, plus the usermeta and users tables that all subsites use. By default all the tables are selected for export, but you can select just the tables you need.
When you are happy with all the selected options, you can click the “Export” (or “Export & Save Profile”) button to create the SQL file.
Now you can can use your preferred MySQL administration tool (e.g. phpMyAdmin, Sequel Pro, the CLI) to import the SQL file and overwrite update the database for a single site install of WordPress with the content and settings from the subsite.
Once the data has been imported into the single site install you can then edit or delete the imported users as required.
How It Works
Each subsite of a multisite network install of WordPress has a unique ID number (often called the blog_id), this number is used in the table name prefix for the tables that are created specifically for the subsite as well as in the keys of various settings. You can read more about the table structure of a WordPress multisite database in our WordPress Multisite Database Tour post.
When the tables for a subsite are exported for use in a single site install they are renamed to remove the blog_id. For example, if a subsite with blog_id 3 is being exported from a site with base table prefix “wp_”, the table “wp_3_posts” will be renamed to “wp_posts”. If the subsite with blog_id 1 (usually the primary site) is being exported then there is no blog_id to be removed from the table names.
If a new table name prefix is entered for the migration, then the prefix is also changed. So in our example above, if we are using the new table name prefix “xyz_”, then the table “wp_3_posts” will be renamed to “xyz_posts” in the export file.
There are often records in the options and usermeta tables that are subsite specific, hence when those tables are exported for a subsite their keys are updated to work for a single site install and subsite records for other subsites are removed from the exported data.