Upgrading / moving WordPress 2.9 installs to multisite WP 3.0

In the previous blog post I discussed how the multi-site functionality in WP 3.0 can be enabled and how it can be hacked to use multiple different domains. Now, let’s have a look at how you can migrate all of your old WP 2.9 sites under a single WP3.0 installation.

The basic steps are:

  1. Move posts
  2. Move attached files
  3. Fix URLs in posts
  4. Move/recreate users

1. Moving the posts from WP 2.9 to 3.0

Moving the posts, links, comments and terms to WP 3.0 is quite easy. A table-by-table comparison shows that the main tables have identical structures (comparison between a 2.9.2 site and 3.0 beta 2). This means that we can use SQL for the migration. Alternatively, you could export and import (see the functionality under Tools -> Export / Import).

In WP 3.0, the tables are sharded – each blog has it’s own set of tables, and the id of the blog is used as a part of the table name: for example, wp_2_commentmeta is the wp_commentmeta table for the multisite blog with id=2. Find out what the blog_id for your blog is, and use it in the SQL queries below.

I performed the following queries, which move all the tables EXCEPT the options table. Moving the options table seems to result in a broken blog, since the themes, settings and plugins referenced by WordPress will not exist in the new environment.

TRUNCATE newblog.wp_2_commentmeta;
INSERT INTO newblog.wp_2_commentmeta SELECT * FROM oldblog.wp_commentmeta;

TRUNCATE newblog.wp_2_comments;
INSERT INTO newblog.wp_2_comments SELECT * FROM oldblog.wp_comments;

TRUNCATE newblog.wp_2_links;
INSERT INTO newblog.wp_2_links SELECT * FROM oldblog.wp_links;

TRUNCATE newblog.wp_2_postmeta;
INSERT INTO newblog.wp_2_postmeta SELECT * FROM oldblog.wp_postmeta;

TRUNCATE newblog.wp_2_posts;
INSERT INTO newblog.wp_2_posts SELECT * FROM oldblog.wp_posts;

TRUNCATE newblog.wp_2_terms;
INSERT INTO newblog.wp_2_terms SELECT * FROM oldblog.wp_terms;

TRUNCATE newblog.wp_2_term_relationships;
INSERT INTO newblog.wp_2_term_relationships SELECT * FROM oldblog.wp_term_relationships;

TRUNCATE newblog.wp_2_term_taxonomy;
INSERT INTO newblog.wp_2_term_taxonomy SELECT * FROM oldblog.wp_term_taxonomy;

What if these queries fail?

Some WordPress plugins modify the default tables – which means there are extra fields that need to be ignored. If the INSERT INTO statements below don’t work for you, you need to explicitly state the names of the source and destination fields instead of using *. This means losing the added fields, so you need to think about it on a case-by-case basis. See the MySQL docs for INSERT INTO … SELECT syntax.

2. Moving attached files

WP 3.0 uses a slightly different directory structure for multisite. The files for each blog are under blogs.dir/[blog_id]/, and use the same substructure as before. Hence, the new directory structure is like this:

/wp-content/blogs.dir/[blog_id]/files/...

and if you keep using the same structure (ex. “year/month/filename”) you can just copy the files:

mkdir -p ./wp-content/blogs.dir/[blog_id]/files/
cp -R ./oldblog/wp-content/uploads/* ./newblog/wp-content/blogs.dir/[blog_id]/files/

3. Fixing URLs in posts

Try loading a post with images. The URLs (may) change from:

http://blog.domain.com/wp-content/uploads/2010/04/image.png

to:

http://blog.domain.com/files/2010/04/image.png

You can fix this by performing a replace on the wp_[blog_id]_posts table:

UPDATE wp_2_posts SET guid = replace(guid, "http://oldblog.domain.com/wp-content/uploads", "http://blog.domain.com/files");
UPDATE wp_2_posts SET post_content_filtered = replace(post_content_filtered, "http://oldblog.domain.com/wp-content/uploads", "http://blog.domain.com/files");
UPDATE wp_2_posts SET pinged = replace(pinged, "http://oldblog.domain.com/wp-content/uploads", "http://blog.domain.com/files");
UPDATE wp_2_posts SET post_excerpt = replace(post_excerpt, "http://oldblog.domain.com/wp-content/uploads", "http://blog.domain.com/files");
UPDATE wp_2_posts SET post_content = replace(post_content, "http://oldblog.domain.com/wp-content/uploads", "http://blog.domain.com/files");

If you also changed the domain, you need to fix the links (do this after you fix the file upload paths):

UPDATE wp_2_posts SET guid = replace(guid, "http://oldblog.domain.com", "http://blog.domain.com");
UPDATE wp_2_posts SET post_content_filtered = replace(post_content_filtered, "http://oldblog.domain.com", "http://blog.domain.com");
UPDATE wp_2_posts SET pinged = replace(pinged, "http://oldblog.domain.com", "http://blog.domain.com");
UPDATE wp_2_posts SET post_excerpt = replace(post_excerpt, "http://oldblog.domain.com", "http://blog.domain.com");
UPDATE wp_2_posts SET post_content = replace(post_content, "http://oldblog.domain.com", "http://blog.domain.com");

4. Moving the users

You can either 1) manually create new user accounts for your users, or 2) try to move the user accounts. Most blogs don’t really have many users beyond the admin, since commenting doesn’t need a user account. My recommendation would be to manually add the very few users you have.

If you want to move the users in bulk using SQL, then you will need to take into account that:

  1. In WP 3.0, the wp_users table is shared among all the sites.
  2. The wp_users table in WP 3.0 has two extra fields: spam and deleted.
  3. Each blog site has its own wp_capabilities, wp_user-settings and wp_user-settings_time fields (e.g. wp_2_capabilities) in the wp_users table. The format (serialized PHP arrays) appears to be the same, so you will need to create one user with the rights you want manually, then these three keys for each of the blogs you want to user to be able to access.

I didn’t try this out, since the blogs I have only use a small number of users. I just created individual admin accounts for those blogs that I am not administering personally.


12 comments


  1. That’ tricky!

    It’s exactally what I wanna do with my sites, tnx for the tutorial!

    Could you make a tutorial about wp_options? :D

  2. thank you,

    you save me lots of time :)

  3. This post is the Holy Grail. This information is completely missing from the official WordPress documentation on how to start a network. They don’t factor in the possibility that we may have EXISTING sites to bring into a network.

    This saved me many, many hours of frustration. Thank you.

  4. I am so glad I found this post of yours. I tried the Import/Export method and compared to that, your method is much much quicker. Thank you so much!

    I have a little trick with the wp_options though, and what I do is in the old website, I access the “http://www.domain.com/wp-admin/options.php” page and I save it down into my computer. Then I access the options.php page for the new website, open the saved options.php and I manually match the fields and change the values to fit.

    It’s actually faster then going through the options pages one by one.

  5. I recently had to work out a process for a client where I needed to migrate the options, users, and everything else completely in tact. It takes a little bit of work, but it’s definitely possible. I wrote up a detailed description of the process at http://iandunn.name/workblog/comprehensive-wordpress-multisite-migrations/

Leave a comment

You must be logged in to post a comment.