A (sometimes annoying) aspect of WordPress is that moving the database between installs or hosts can be painful at times. This is due to the initial domain and path being kept as settings in the database, and because WordPress, when inserting images, uses absolute references to on-site media.
It is possible to export the database to SQL, and search/replace on that. This is prone to break down because in certain cases, data is serialized, and replacing a string within serialized data can cause problems if the replacement string is of a different length.
For some time I have been using WP Migrate DB Pro (abbreviated as WMDP) to move site content back and forth between installs – especially common is the local-to-live workflow. But it’s also useful when setting up a new local install and needing to sync down the data from the live site. This tool correctly handles string replacements, including within serialized data as mentioned above.
This post isn’t per se a review of WMDP, but rather a recounting of how I used it to migrate data in an odd scenario that it doesn’t specifically support. I did mention WMDP briefly in a presentation I did last fall.
Here’s the scenario. A client asked me to help them redeploy their existing WordPress sites. There were two multisite installs, each containing a few network sites, each on a specific domain or subdomain. The desire was to move some of these network sites into standard standalone installs, and consolidate the remaining few and simple sites into a new multisite. The image below shows the before-and-after site layout.
Having previous experience with WMDP, I thought to find out whether it could handle exporting data from or to multisites – It’s still WordPress after all. It turns out that exporting from multisite does work, though is not officially supported. Apparently official support is coming in a later release. It’s worth reading the link above for background; this post is going to expand on it to include porting across multisites.
For anyone attempting to replicate this process, this post works on the assumption that pre-existing and new installations are already up and running, and WMDP is installed on each. Some familiarity with multisite wouldn’t hurt either.
Porting Out of a Multisite
In the case that a network site needs to be ported to a standalone install, the process is straightforward.
Log in to the source multisite, and go to the network site management view. It resembles the following:
For the site to be exported to a standalone install, make note of the value in the ID column. This is needed because each network site has a section within the
uploads directory for media used by the site. The exception is for the main site, which uses the
uploads directory similarly to a standalone WordPress install.
On the network administration site, navigate to Settings -> Migrate DB Pro.
The first row cannot be removed; it is used to update the domain of the site if needed. If the domain is the same, simply add that into the Replace field; if the domain is going to be different, replace accordingly.
The second row is the filesystem location of the site; this will depend greatly on the web host and configuration. The Replace field should contain the filesystem location of the new standalone install. This is why it’s useful to have WMDP also installed on the destination site, to make gaining this destination information easy. Simply go to the same view on the destination site, copy the filesystem location shown there, and paste it on this view for the site being exported.
If a subsite (not the main) is being exported, it is necessary to remove the subsite ID from media references. Recall the site ID noted earlier – this is where it comes into play. Add a third row. In the Find field, write
X is the ID of the site being exported), and in the Replace field write
/uploads. The data export will have the media references updated accordingly. If the main site is being exported, this third row is not needed, since the media references will be the same.
WMDP by default exports the whole database; there is the option to choose which tables to export, useful here since only one site of the network is being exported. Expand the Tables section, and choose the option for Migrate only selected tables below.
If exporting the main site, select the un-numbered tables, which will be named the same as in standalone sites e.g.
wp_X_comments. If exporting any other network site, select the tables which include the ID of the site. From the network site image above, I needed to export the site with the ID of 4, so I selected the tables prefixed with that ID, e.g.
If taking the existing users to the new site, make sure to select the
wp_usermeta tables. This will bring along all users that are part of the existing network, even if said users are not associated with the site being exported. Extraneous users can easily be deleted from the new site once the data is imported, so no worries.
Note that when exporting to a single site, the following tables are not needed, so can be left out of the selection:
The following image shows what this view looked like for me, having completed the preceding steps:
if done making entries and selections, hit the Export button. When the resulting download completes, extract the Zip file to a folder and open the SQL file within in a text editor. While WMDP replaces subsite references within the data, it does not update the table names. After all, the tool doesn’t know that it is exporting data from a multisite to be imported to a single site.
X is the ID of the site being exported, search for
wp_X_ and replace with
wp_. Save and close the SQL file.
Use the tool of choice – be it phpMyAdmin, MySQL WorkBench, or what have you – to import the SQL file into the database for the new standalone WordPress install. If all goes well, a multisite subsite has been successfully ported to a standalone install. Repeat the process in this section for each subsite that needs exporting to a standalone site – I had to do it two more times.
Log in to the new site and ensure that media references were successfully updated and other data imported. Also feel free to delete any ported users that need not be part of this new site.
Porting Into a Multisite
The previous section on exporting from a multisite to a single site may have seemed complicated, but porting from one multisite to another is more complicated still. Not to worry, as instructions are provided for making it happen.
Referring back to my scenario (which prompted this blog post), there were four network sites, mostly small and simple, which were to be managed through a single multisite install. They were already spread across two multisite installations, so they had to be ported out of those and imported into the new multisite install.
This section is a two-parter: the first part is about getting the new multisite configured for the incoming subsites, while the second part is about the actual porting process.
Configuring the Multisite
First I checked on the new multisite install. There was initially just the default site with three users, myself included, as shown by the following image.
Clicking the Create a New Site Link leads to the following view:
This form is really straightforward. Just fill in the intended subsite URL and title. Entering the email address of a user already associated with the multisite install will associate said user with the new subsite; else, an account will be created for the given email address and assigned to the subsite.
Shown below is what the site manager looks like after creating a few subsites; the topmost one is the “main” site, while the others are the just-created subsites. Note that each site receives a unique numeric ID – those IDs will be needed later.
Now that the subsites have been created, clicking the link for one of them will lead to a 404 error. This is because while the sites have been defined, the domain has not yet been provisioned. It is possible to have one multisite install serve sites across multiple distinct domains, but for this project I initially just used subdomains of the main site. To make this work, the WordPress MU Domain Mapping plugin must be used. On the main site, install the plugin through the repository, and network activate it so it works across all the subsites.
This plugin isn’t an install-and-done sort; it requires some manual work also. The plugin page linked above has instructions for using it, but they are briefly summarized below.
- install the plugin
- use an FTP or SSH client to go into the plugin’s folder and move or copy
- edit the
wp-config.phpfile to add
define( 'SUNRISE', 'on' );just above the
if ( !defined('ABSPATH') )line
- as the network super-admin, visit the Settings -> Domain Mapping view of the network administration
The view should resemble the following image, indicating that the domain mapping table has been created.
There is one more step to get the subsites working. The domains to be used must be mapped to the subsites already created. On the network administration panel, go to the Settings -> Domains view.
The domains list is currently empty. In the New Domain form, provide the ID of the desired subsite – see the sites management view shown previously – and the corresponding domain or subdomain. Repeat for each subsite. The main site does not need to be mapped, since it is already running on a given domain. Each successful submission should see a row added to a table below the form. Also, each site should be working at this point.
There’s possibly much more to setting up multisite installs and subsites, but this section has covered the essential steps. Details may vary due to hosting setups or specific needs, your mileage may vary etc.
Before getting to the database, plugins and themes need to be migrated. Because the four sites involved come from two different existing multisites, there’s a large pool of plugins in use by the lot. There’s nothing for it but to download the full plugin sets from both existing multisites and upload the same to the new multisite (and later sort out which are needed where). When importing data from the existing sites, the data includes which plugins were active, so used plugins should automatically get activated where needed.
Themes were simpler. Three of the sites in question were using the same theme, while the other site was using a unique theme. It was just a matter of downloading both themes from the multisite that used both, and uploading both to the new multisite.
As with porting out of a multisite, porting across multisites involves some fiddling with the database. On the old network management site, go to the Migrate DB Pro page and start a new export. Have open in another tab/window the new multisite on the same view (having already installed the plugin).
In the Replace field in the first row, replace the old site domain with the new.
In the second row, update the Replace field with the destination physical path. Not much different from the multi-to-single process.
Add a third row. In the Find field, enter the full domain name for the current subsite. Under Replace, enter the same for the new destination site. This row isn’t needed if the source and destination domains are the same (which they weren’t in my case, so I needed to add this row)
On a fourth row, enter
uploads/sites/4 under Find and Replace, respectively. This is required since when moving data across multisites, the subsite IDs may not be the same (e.g. on the new multisite a particular subsite had ID 4, while the same subsite in the existing multisite had ID 7). The replacement causes media references to be updated to work for the new site. Substitute your applicable IDs here.
If porting from a main subsite to a non-main subsite, the Find field mentioned above should contain
/uploads and Replace should contain
X is the new subsite ID). Conversely, if porting from a non-main subsite to a main subsite, Find should contain
/uploads/sites/X and Replace should contain
The following image (details covered up) shows the appearance of the form once I’ve filled it out:
Expand the Tables section and choose to migrate specific tables. In the listbox that appears, select all the tables belonging to the source subsite. In my case, the appropriate tables were all prefixed with
wp_7_. The tables should be the following:
A single WordPress site normally has eleven tables, but a multisite install uses one of each of the
user tables to serve all subsites. Consolidating users from different sites into one site is tricky due to the potential for duplicate or missing users. In my case, there were a small number of users across these sites, so I took the easy way out and didn’t export them, and manually recreated them later. I don’t know if there is a simple solution for combining users into one site like this.
Hit the Migrate button and grab the download. As with the multi-to-single process, table references in the SQL file must be updated to reflect the new site. Open the file in a text editor, and replaces occurrences of
wp_4_ (substituting the old and new IDs that apply). Import the modified SQL file into the new multisite database, and voila.
Migrating WordPress databases across hosts or domains can be tricky. All the more so when multisites are involved. Fortunately, use of WP Migrate DB Pro eases the process greatly.
Exporting from a multisite to a single site turns out to be straightforward. It is important to ensure that subsite references are removed so that media references are for a single site rather than one that is part of a multisite.
Exporting from one multisite to another is more involved. There are a number of steps just to set up and configure a new multisite install to the point where data can be ported in. I laid out the important details for defining and configuring new subsites, then pointed out the need to update the subsite reference (remember, the old subsite ID may not be the same as the new subsite ID). The extra configuration and replacements aside, this migration process resembled the multi-to-single export process.