Mixu's blog

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

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;

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-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

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");

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

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.

Comments

Hikari: 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

leenoux: thank you,

you save me lots of time :)

John: 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.

Seluna: 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.

Mikito Takada: Thanks for the tip!

Seluna: Haha, no problem! Sharing makes the word go round!

Ian Dunn: 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/