Now that we’ve set up our multisite WordPress network, the next problem is going to be moving the content from our multiple existing single site installations into the multisite network.
WordPress comes with a built-in export tool which you can find in the “Tools” menu in the admin area, and it is possible to import this XML file using the WordPress Importer plugin. This works reasonably well out of the box and, in fact, we have used the XML file to import WordPress content into bespoke websites in the past as well.
Unfortunately, some of the content (specifically, the menus and the site settings) aren’t copied at all and there are a couple of caveats with media imports:
- Importing media doesn’t work at all if the hostname the website is currently on resolves to a local IP address (ie the old and new sites are on the same server).
- The import of media using the importer is very, very slow.
I was able to work around problem number 1 by temporarily allowing 127.0.1.1 (or whatever result you get when you look up the hostname) in the check for whether the HTTP request is external, and this meant I could import the images from a small site successfully using the importer. You can drop this code in a plugin or in your functions.php
add_filter("http_request_host_is_external", function($allow, $host, $url){
if (gethostbyname($host) == "127.0.1.1") {
$allow = true;
}
return $allow;
}, 10, 3);
But we have one client with 3GB of images on his website, so I knew problem number 2 was going to be the stumbling block for our bigger sites. I still wanted to use the importer for all the sites, because it has some handy functionality for setting the user who owns particular posts on the new site. Because we’re now using multisite, sometimes the usernames on the old site do not precisely match the new usernames.
When importing these sites, however, I did not check the option to “Download and import file attachments”. Instead I imported the data about the files using SQL and simply copied the files from the old virtual host.
SELECT * FROM new_database_name.wp_blogs;
Write down the blog_id for the site you’re importing into and use it to replace N in wp_N_posts and wp_N_postmeta below. This SQL might not work if you have added any new content to the site since the import because the IDs might collide. In that case, you’ll have to do something awkward like adding a few hundred to each ID number and selecting the other fields out from wp_posts explicitly.
-- Copy the media information from the original database. INSERT INTO new_database_name.wp_N_posts SELECT * FROM old_database_name.wp_posts WHERE post_type = 'attachment'; INSERT INTO new_database_name.wp_N_postmeta SELECT NULL, wp_postmeta.post_id, wp_postmeta.meta_key, wp_postmeta.meta_value FROM old_database_name.wp_postmeta INNER JOIN old_database_name.wp_posts WHERE wp_posts.id = wp_postmeta.post_id AND wp_posts.post_type = 'attachment';
Then physically copy the files with something like the following, replacing N with the blog_id above. You could move them, I suppose, instead of copy, if you were in a rush and confident that your backups are working.
cp -av path_to_original_virtualhost/wp-content/uploads/* \ path_to_new_multisite_virtualhost/wp-content/uploads/sites/N/
Wherever images are included in content they will have the old URLs, without sites/N/, so you’ll need to update them. You can write an SQL query to do this, or loop over the wp_N_posts entries in PHP with something like:
$content = preg_replace("[(https?:)?//(www\.)?" . preg_quote($host_name) ."]i", "", $content);
$content = preg_replace("[([\"'])/wp-content/uploads/(?!sites/)]",
"$1/wp-content/uploads/sites/$site_number/", $content);
which will get rid of any pesky absolute links — a pain while you’re testing the site on a staging domain — as well.
And how to get the menus and site configuration copied across? For most of the config, it’s as easy just to open “Appearance” > “Customise” in both sites in adjacent windows, and copy the changes across. But the menu can be a bit fiddly, so I wanted a scripted approach to it.
This would have been a relatively trivial SQL statement, similar to that for attachments above, but for an earlier silly decision. Moving to Composer for updating WordPress makes it a bit more challenging to include translations of WordPress, so I decided to use the default en_US language on the new sites whereas the old sites used en_GB. Unfortunately this means WordPress adds an “Uncategorized” category in addition to the “Uncategorised” category already in use, which, in turn, means that the term IDs for menus on the new site are not the same as the old site. Still, a bit of PHP selecting the terms by slug instead of ID will fix it.
$pdo = new PDO("mysql:host=localhost;dbname=$old_db_name",
$db_user, $db_pass,
[PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4',
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
// If you're running MySQL 5.7, zero dates (0000-00-00) are not supported by default.
// WordPress relies heavily on them, so we need to disable STRICT_ALL_TABLES and STRICT_TRANS_TABLES.
$pdo->exec("SET sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'");
// Naively copy the wp_posts and wp_postmeta rows corresponding to the menu items.
// As with attachments, this might fail if you've added any new content to the new site.
$pdo->exec("INSERT INTO $new_db_name.wp_{$site_number}_posts"
. " SELECT * FROM $old_db_name.wp_posts WHERE post_type IN('nav_menu_item')");
$pdo->exec("INSERT INTO $new_db_name.wp_{$site_number}_postmeta"
. " SELECT NULL, wp_postmeta.post_id, wp_postmeta.meta_key, wp_postmeta.meta_value"
. " FROM $old_db_name.wp_postmeta"
. " INNER JOIN $old_db_name.wp_posts"
. " WHERE wp_posts.id = wp_postmeta.post_id"
. " AND wp_posts.post_type IN('nav_menu_item')");
// Change the object IDs for any category menu items, because the terms have
// different IDs in the new database.
$pdo->exec("UPDATE $new_db_name.wp_{$site_number}_postmeta pm, $new_db_name.wp_{$site_number}_postmeta pm2,"
. " $new_db_name.wp_{$site_number}_posts p, $old_db_name.wp_terms t,"
. " $new_db_name.wp_{$site_number}_terms t2"
. " SET pm.meta_value = t2.term_id"
. " WHERE pm.post_id = pm2.post_id AND pm2.meta_key = '_menu_item_object' AND pm2.meta_value = 'category'"
. " AND p.ID = pm.post_id AND p.post_type = 'nav_menu_item'"
. " AND pm.meta_key = '_menu_item_object_id' AND pm.meta_value = t.term_id "
. " AND t2.slug = t.slug COLLATE utf8mb4_unicode_520_ci");
// You'll only need COLLATE utf8mb4_unicode_520_ci if your old site was originally installed on MySQL 5.5 or
// earlier and you're migrating to 5.6 or higher.
// Find out which menu is associated with which menu items.
$terms = $pdo->query("SELECT p.ID, t.slug FROM $old_db_name.wp_posts p"
. " INNER JOIN $old_db_name.wp_term_relationships tr ON tr.object_id = p.ID"
. " INNER JOIN $old_db_name.wp_terms t ON t.term_id = tr.term_taxonomy_id"
. " WHERE post_type IN('nav_menu_item')");
// Insert the menu relationships in the new DB.
$insert = $pdo->prepare("INSERT INTO $new_db_name.wp_{$site_number}_term_relationships"
. " SELECT ?, term_id, 0 FROM $new_db_name.wp_{$site_number}_terms WHERE slug = ?");
foreach ($terms as $term) {
$insert->execute([$term['ID'], $term['slug']]);
}
This problem of the category IDs not matching might also sting you if you’re using the category IDs in page content — for example we had a site with maps of posts from particular categories. We fixed this by hand, but if it’s going to be a big problem for you you might prefer to fix the localisation issue instead.
Then we just need to set the correct menus in the correct locations in the admin panel. The theme configuration doesn’t get copied across anyway, so this is a good time to open both customise links (the old site and the new) side by side and go through each option. When you’re done, the site content is all copied across.
Next, how to keep track of our own bespoke plugins and themes with this new structure.