Mixu's tech blogWhen I read what I write I learn what I think

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.

This entry was posted on Sunday, May 23rd, 2010 at 8:33 am and is filed under Uncategorized. You can follow any comments to this entry through the RSS 2.0 feed. You can leave a comment, or trackback from your own site.