• Skip to primary navigation
  • Skip to main content
  • Skip to footer
  • Books
    • Content Strategy for WordPress (2015)
    • WordPress for Web Developers (2013)
    • Beginning WordPress 3 (2010)
  • Blog
    • Content Modeling for WordPress
    • WordPress Hidden Gems
    • Web Design
  • Work
    • MLIS Class Projects (2019-2022)
    • Portfolio (2002-2019)
    • Services
    • WordPress Plugins
    • WordPress Themes
    • Presentations and Interviews
    • on GitHub →

Stephanie Leary

Writer, WordPress consultant, recent MLIS grad

  • About
    • Press Kit
    • Presentations and Interviews
  • Contact Me

Migrating single WordPress installations into multisite networks

September 8, 2010 Stephanie Leary 7 Comments

Last week, I had to migrate a bunch of single WordPress installations into one big network. As part of the tinkering I’d done with the new network features while I was writing about the 3.0 features, I’d done this sort of migration in limited tests, but I’d never tried it with real data.

I had eight sites to move. Some of them were very small — between 10 and 15 pages, no posts — and one was very large, with nearly a thousand posts and pages carefully divided into about 30 categories, with lots of post tags and about 40 users.

Buckle up; this is going to be bumpy.

Working Without Wildcard DNS and Getting Around Campus Bureaucracy

It’s tricky to set up a WordPress network when the top-level domain isn’t yours, but this is the case on most college campuses that have a decentralized web presence. In other words, I work for a small office deep in the university hierarchy, while someone else maintains www.tamu.edu. I had talked to a couple of other people who were planning on doing this, and we’d all agreed that we’d use the Domain Mapping plugin. We’d set up someserver.tamu.edu as the main site, create foo.someserver.tamu.edu subsites (which aren’t actually valid addresses on campus, but more on that in a minute), then use Domain Mapping to point the real department.tamu.edu address to the foo subsite.

Convoluted? Yep. As it happens, one of the people in charge of www.tamu.edu wrote up instructions on doing exactly this, which took some of the guesswork out of the process.

Due to the way DNS on campus works, I have to request a new CNAME (someserver.tamu.edu), then wait until the next morning for it to go live. This makes the testing process awfully slow, as you can imagine. The administrator I work with suggested that I fake it by editing my own hosts file, which worked beautifully.

To set up each subsite, this is what I did:

  1. Submit my request for department.tamu.edu to the central campus helpdesk.
  2. Add foo.someserver.tamu.edu as a ServerAlias in the WordPress site’s httpd.conf file. Restart Apache. (This is the step you’ll have to do if you don’t have wildcard DNS, no matter where your site is hosted.)
  3. Add [ip address] foo.someserver.tamu.edu as a line in my Mac’s /etc/hosts file.
  4. In the WordPress Network Admin menu, create a new site called foo.
  5. Visit the backend at foo.someserver.tamu.edu. Go to Tools → Domain Mapping. Enter the real address, department.tamu.edu, as the primary domain.
  6. Keep visiting the site at foo.someserver.tamu.edu until the next morning, when the DNS changes take effect and department.tamu.edu actually works.

This meant that all the sites’ file upload URLs were wrong, but I was going to have to change those anyway, as described below.

Import/Export Bugs

I tried two different methods of moving the sites: using the WordPress export format, and moving the database tables. I learned that the export format works fairly well for the smaller sites, but there were two major bugs that made it a poor choice for the large site.

First, the new menus are not included in the export format as of 3.0.1. The day after my experiment, this feature was added to the trunk, so if you use the Beta Tester plugin to upgrade to the latest nightly, you’ll get your menus exported. (You’ll also get the new Ajax-ified admin screens, which are fantastic, but beware: the developers are also monkeying around with the network management screens, and some things are hard to find or missing altogether. For one thing, I can’t figure out how to add new super admins at the moment.)

Second, a much bigger problem for my large site: if any of your categories have altered slugs, they’ll be duplicated during the import — and your posts will be assigned to the clones rather than the originals. I presume what’s happening here is that, since the import/export process does away with IDs, it’s storing the posts’ categories by name. Categories and tags are imported before the posts, but when the posts are processed, WP appears to be failing to find the correct categories, so it recreates them, generating new slugs in the process, and assigns the posts to the new categories. Categories were crucial to the organization of the posts on my large site, so when I discovered this problem, I gave up on the WordPress export format, nuked the new database tables, and copied the ones from the old installation as described in this tutorial.

That tutorial is fantastic, but it left out a couple of things…

Users and Roles

When you move tables over, you have a whole new problem: users. The first thing I noticed, after I’d migrated one site by copying the tables, was that my roles were all gone. The role dropdowns were empty in all my edit/create user screens, and no one had permission to do anything. The problem? There’s a field in your options table called wp_userroles, where the wp_ matches your database prefix. It’s there because WordPress allows developers to create new roles and capabilities, and that stuff has to get stored somewhere! You just need to change the name of the field to match whatever prefix your new options table is using, like wp_3_userroles.

Moving the user accounts themselves is a big hassle. On network installations, you have a separate set of database tables for every site you set up, and the tables correspond to the ones you’d find in a single installation — with one big difference: there’s no site-specific user table. Instead, users for the entire network are stored in the main site’s user table, and users can then be assigned to various subsites. (If you copy the user tables along with all the others, it’ll just be ignored.)

You can export individual rows from your old database and import those into the user table, although then you have to do the same with the usermeta table, and that gets tedious. The biggest problem, though, is that the IDs in your old table will almost certainly conflict with the user IDs in your new network setup, which means you’ll have to change those old user IDs when you copy them over.

This is where it gets tricky. You’re going to have to search and replace the user IDs in your MySQL tables. These are the three queries I ran in PHPMyAdmin (assuming your table prefix is wp_, your blog ID is 8, and you’re changing the user ID from 5 to 13):

  • update `wp_users` set ID = replace(ID,'5','13');
  • update `wp_usermeta` set user_id = replace(user_id,'5','13');
  • update `wp_8_posts` set post_author = replace(post_author,'5','13');

In my situation, I didn’t have to worry about the comment tables, because none of these sites have many comments, and none of them were written by logged-in users.

If I had to do this again, I’d be tempted to use the WordPress import feature and let it create new user accounts for all the incoming authors. Then I’d use this user export method to get the old users into the database, delete the newly created users, and have WordPress assign their posts to the users I just imported. Convoluted, yes, but it has to be easier than changing all those IDs with SQL queries.

Files, Paths, and the Upload Directory Settings

If you copied your options table from an old installation, your file upload paths will be wrong. You can fix this right from the Network Admin screen. Edit the site and look for the Site Options section. There are two or three settings you’ll need to change.

  • Upload Path: this should be the path to this site’s directory in blogs.dir, usually something like wp-content/blogs.dir/8/files (where 8 is the site ID).
  • Upload URL Path: this should be something like http://yoursite.com/files.
  • You might also have a Fileupload Url setting, which should be the same as the Upload URL Path.

You’ll also need to copy the uploaded files from your old /wp-content directory into the new blogs.dir/8/files directory. Just use your favorite FTP client (or whatever) to copy the files.

Last but not least, you need to do a search and replace on all the links to uploaded files in your content. I like to use the Search and Replace plugin. The Content table is probably the only one you need to check off the list, although I like to include Excerpts as well, just in case. Then make the following replacement:

  • http://yoursite.com/wp-content/ to http://yoursite.com/files/

If you had files stored elsewhere, like an /images root directory, you can move those into /files and do a search and replace those paths, too.

That’s it! At last, all my new sites were working…

… except that I still had to turn on CAS authentication.

CAS and Login Redirects

As part of our migration, we needed to start using the campus CAS server for authentication. The wpCAS plugin works great, but switching to CAS meant that we had to change a few other things. No more Sidebar Login, alas, though my users loved it. Rather than make them navigate to /wp-admin — some of them are professors who aren’t logging in to edit content, but just to see some members-only pages on the site — I created a link to wp-login.php as part of our navigation menu.

With that, a new problem cropped up: our login redirects weren’t working anymore. We’ve been using Peter’s Login Redirect to whisk those professors, and other non-editing users, to the various pages they need to see. Once we activated CAS, it overtook the login system entirely and redirected everyone back to the Dashboard. I dug around a bit, and found in the plugin’s source code (not in the FAQ or the readme!) a comment that when using an external login system, you can set the redirect URL to http://www.yoursite.com/wp-content/plugins/peters-login-redirect/wplogin_redirect_controller.php. Turns out it should be wplogin_redirect_control.php; I’m guessing the file name changed at some point after this feature was implemented. So I changed our login link to include this redirect URL explicitly: http://yoursite.com/wp-login.php?redirect_to=http://yoursite.com/wp-content/plugins/peters-login-redirect/wplogin_redirect_control.php — and lo! our users are once again redirected to the appropriate pages.

WordPress

Reader Interactions

Comments

  1. Serj says

    November 6, 2010 at 6:26 pm

    Hello Stephanie =)

    Big thanx for sharing the experience.
    Very clear and useful.
    Again thank You.

    Sincerely, Serj.

    Reply
  2. Ian says

    February 23, 2011 at 4:14 pm

    There’s an easier way to update the IDs in the users/usermeta tables so that they don’t conflict. Just copy the table from the standalone installation into your WPMS database and add a prefix number to each of the IDs before inserting into the WPMS table.


    DELETE FROM _temp_users WHERE user_id = 1 (because there's already an admin user in WPMS)

    UPDATE _temp_users
    SET user_id = user_id + 1000

    INSERT INTO wp_users
    (SELECT * from _temp_users)

    The WPMS users table has two extra columns on it — ‘spam’ and ‘deleted’, both TINYINT(2) — so you’ll need to add those to the temp table before inserting.

    Then for usermeta you’d do,


    DELETE FROM _temp_usermeta WHERE user_id = 1

    UPDATE _temp_usermeta
    SET user_id = user_id + 1000

    UPDATE _temp_usermeta
    SET umeta_id = umeta_id + 1000

    UPDATE _temp_usermeta
    SET meta_key = 'wp_[id]_user_level'
    WHERE meta_key = 'wp_user_level'

    UPDATE _temp_usermeta
    SET meta_key = 'wp_[id]_capabilities'
    WHERE meta_key = 'wp_capabilities'

    INSERT INTO wp_usermeta
    (SELECT * FROM _temp_usermeta)

    Then use 2000 for the next blog, and 3000 for the one after that, etc. If you have thousands of posts, just use 10000, 20000, 30000, etc so that they don’t overlap.

    You can use a similar query to update comment and post authors to match.

    Reply
    • Stephanie says

      February 23, 2011 at 4:45 pm

      That’s very clever, Ian. Thank you!

      Reply
      • Ian says

        February 23, 2011 at 7:58 pm

        No problem. I wrote up a detailed description of the process I’ve been using if anyone is interested.

        Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Footer

My Books

I’m a front end developer at Equinox OLI, working on open source library software. I was previously a freelance WordPress developer in higher education. You can get in touch here or on LinkedIn.

Copyright © 2023 Stephanie Leary · Contact