Merging WordPress Multisites

ECS had a blog server for some years, home to a number of mature blogs.  As part of the university-wide systems centralisation, these blogs had to be migrated to existing Southampton WordPress server. Patrick and I were tasked with this.

Our initial googling return very little information about this, other than people saying how hard it was, so we decided that it was well worth documenting.  It wasn’t as hard as all that, though we did things that can’t be considered good computer science.

This is presented as a set of instructions, and we’re assuming that there are two multisite installations that need to be moved onto a single new server.  It relies on the database structure that wordpress 3.4.1 uses, so if you have a different version, your mileage may vary.

Preliminaries

You will need a fresh server, with a fresh install of Wordpress (verify that it works).  Don’t worry too much about plugins and themes, as you will be replacing the whole WordPress tree.

Decide upon which source server will be primary.  This is almost certainly the one with the most blogs.  The heavy lifting in this task will be moving the blogs from the secondary server to the primary one.  Note that it doesn’t matter how many posts the blogs have, just how many blogs are on the multisite.

Glossary

The following terms will be used throughout these instructions:

Primary [Server/Database/Tree]

The server/database/file tree of the WordPress install with the most blogs.

Secondary [Server/Database/Tree]

The server/database/file tree of the WordPress install with the fewest blogs.

Target [Server/Database]

The server/database onto which the blogs will be moved.

Package Up the Primary Database and Tree

On the primary server, cd into the WordPress root directory and dump the database:

mysqldump --default-character-set=utf8 --lock-tables=false -c -uUSER -pPASS DBNAME > mysqlbackup.[date].mysqldump

Then tar up the WordPress tree and scp it across to the target server.  Note that setting the character set to utf8 is important.

On the target server, create a convenient working directory and untar.

Manipulate the Primary Database Dump

Both the base URL and the base path are stored multiple times in the database.  These will need to be updated.  The easiest way to do this is to modify it using vim and regular expressions.

In the example below, we were moving from http://blogs.ecs.soton.ac.uk to http://blog.soton.ac.uk. WordPress was located at /home/blogs/blogs.ecs.soton.ac.uk/htdocs/ on the primary server and /usr/share/wordpress/ on the target server.

:%s/\/home\/blogs\/blogs\.ecs\.soton\.ac\.uk\/htdocs/\/usr\/share\/wordpress/g
:%s/http:\/\/blogs\.ecs\.soton\.ac\.uk/http:\/\/blog.soton.ac.uk/g
:%s/blogs\.ecs\.soton\.ac\.uk/blog.soton.ac.uk/g

Get the Primary Blogs Up

Move the target WordPress tree to a backup directory and swap in the Primary wordpress tree.  Then edit wp-config.php:

• Change DOMAIN_CURRENT_SITE to the target URL
• Make a note of the database information

Create a new database using the settings from wp-config.php. On the command line:

echo 'CREATE DATABASE dbname;' | mysql
echo "GRANT ALL PRIVILEGES ON dbname.* TO 'dbuser'@'localhost' IDENTIFIED BY 'password';" | mysql
echo "FLUSH PRIVILEGES;" | mysql

Then cat the mysqldump into the new database (don’t forget the utf8 option):

cat  mysqlbackup.[date].mysqldump | mysql --default-character-set=utf8 -uDBUSER -pPASSWORD DBNAME

Restart apache, and check that the blogs are all visible.

Create Blog ID Map and Placeholders for Secondary Blogs

Each blog has a numeric ID and a path. The IDs are assigned serially, and must be unique. You will need to assign new IDs to all secondary blogs. To get the information out of the database, do the following for the Secondary database (you may want to print this out to note down the new IDs):

echo 'select blog_id, path from wp_blogs' | mysql -u USER -pPASSWORD DBNAME

Next, using the target WordPress’ web interface, log in and create a new blog for every blog returned from the query above. It is essential that the same path is used.

Finally, run the above query on the target database to get a list of the new blog IDs. You will need to make a note of the mapping between IDs on the secondary server and IDs on the target server.

Merge in Secondary Users

The user accounts in the secondary blog need to be moved, and they all have numeric IDs which will need to be incremented. They also have blog permissions associated with them.

First, find the highest ids in the user and usermeta tables in both the target and the secondary databases. The mysql for doing this is:

SELECT MAX(ID) FROM wp_users;
SELECT MAX(umeta_id) FROM wp_usermeta;

Make a note of the highest ID and highest umeta_id (from whichever database has the highest). Round these numbers up to the nearest round number.

Then, dump the wp_users and wp_usermeta tables from the secondary database and cat them into a temporary database under your control (don’t forget the utf8 option). Run the following commands in the temporary database (using the numbers rounded up above instead of the examples of 50000 and 600000):

UPDATE wp_users SET ID=ID+50000;
UPDATE wp_usermeta SET user_id=user_id+50000;
UPDATE wp_usermeta SET umeta_id=umeta_id+600000;

Dump the temporary database (note the use of the –no-create-info argument so as to not drop the tables when inserting):

mysqldump --default-character-set=utf8 --no-create-info -u USER -pPASSWORD temp_db wp_usermeta wp_users > user_migration.mysqldump

…and then edit it with vim to update the blog permissions.  For each blog in your ID map, run the following substitution command (in this example, the secondary blog ID of 7 maps to the target blog ID of 88):

:%s/wp_7_/wp_88_/g

This will update all the user permissions in the wp_usermeta table.

Finally, cat this file into the target database.  This will insert all of the new user records and user metadata into the two tables.

Merge Plugins and Themes

Create a copy of the secondary WordPress tree in a convenient location on the target server.  You will need to diff and merge the following directories in the secondary and target wordpress trees:

• /[wordpress_root]/wp-content/plugins
• /[wordpress_root]/wp-content/mu-plugins
• /[wordpress_root]/wp-content/themes

Use diff -rq on the directories of the target install and the copy of the secondary install, and copy across anything that’s missing in the target install. If there are differences, you’ll have to figure out what to keep.  Note that plugins and mu-plugins may need to be configured through the target web interface.  Any themes copied in from the secondary tree will also need to be enabled through the web interface ( My Sites -> Network Admin -> Dashboard -> Themes ).

Copy Blog Media Files

Each blog has a directory for uploaded files.  These need to be copied across.  Refer to your blog ID map, and copy these directories.  For example, in our map, the secondary blog ID of 7 maps to the target blog ID of 88.  This means that the contents of of:

/[secondary_root]/wp-content/blogs.dir/7/

…needs to be copied into a new directory at

/[target_root]/wp-content/blogs.dir/88/

Import Secondary Blog Database Tables

Each blog has a number of database tables that hold posts, setting and other bits of data.  A list of them would look something like this:

• wp_7_commentmeta
• wp_7_comments
• wp_7_email
• wp_7_links
• wp_7_options
• wp_7_postmeta
• wp_7_posts
• wp_7_term_relationships
• wp_7_term_taxonomy
• wp_7_terms

Cat a dump of the secondary database (don’t forget the utf8 options) into your temporary database (empty it beforehand if necessary).  Drop all tables from the database that aren’t blog tables (tables that don’t start with ‘wp_N_’).  For each blog ID, run the following mysql command (use the rounded user ID from above):

UPDATE wp_7_posts SET post_author = post_author + 50000;

Dump the database and edit with vim.  For each ID in your map, run the following (replace the digits according to your blog ID map):

:%s/wp_7_/wp_84_/g

Save the file and exit vim, then cat the file (which must only contain blog tables — verifying this may be a good idea) into the target database.

Verify the Front Page

If you wish to use the front page of the secondary multisite as the frontpage of the target multisite, then you should set this up by hand.  Open both in your web browser and copy across the settings.

Configure Apache Redirects

You will want to maintain any old URLs.  In your apache configuration you will need:

RewriteEngine On
RewriteCond %{HTTP_HOST} ^(oldblogurl.domain.com) [NC]
RewriteRule ^(.*)$ http://newblogurl.domain.com$1 [R=301,L]

Then update your DNS entries.

Job Done!

A Final Word

We noticed that some (but not all) theme settings had not moved across and had to be set manually.  It may be a good idea to inspect every blog for issues after the migration, particularly those from the secondary server.