This is the second part of a series on migrating a site from a single installation WordPress blog to a WordPressµ multi-user implementation. It assumes you have everything installed and have taken the steps in part 1 of the series.

If you’re not interested in managing WordPress systems then move along. There’s nothing to see here.So, now we’ve done all of our preparatory work, we’re ready to build up the new blog. WordPressµ uses a different database schema than the single user installation, so we’re going to modify the database manually.

Databasey Stuff

0. Fire up PHPMyAdmin or whatever you use for database admin on your old blog. You’re going to copy the database.

1. Kill the post revisions to clean up the database (unless you want to keep them all for some reason) by issuing the following SQL command:

DELETE FROM wp_posts WHERE post_type = "revision";

2. Grab copies of the following database tables by using the “Export” function. I suggest copying each of these tables separately in plain SQL format because we have to modify each table in a text editor.

wp_comments
wp_links
wp_options
wp_postmeta
wp_posts
wp_term_relationships
wp_term_taxonomy
wp_terms

There are probably more tables there because many plugins install tables. You might want to consider investigating those if you have complex plugins but I won’t go over them here. You need not copy wp_users and wp_usermeta because there is a different usertable in WPMU and you’ll probably want to add users using the admin pages.

3. WPMU has different tables for each database, so instead of wp_posts, your database will use the table wp_ID_posts where ID is the database ID that you figured out in step 2.71828183 above (we’ll use 15 for illustration). For each of the tables that you exported, you basically want to use replace “wp_” with “wp_15_”. You can do this easily using a text editor.

π. The commandline geeks will probably use a command looking something like:¹

for i in `ls wp_* | grep -vE gz | awk -F. ‘{print $1}’`; do cat ${i}.sql | sed -e ’s/wp_/wp_15_/g’ > ${i}-out.sql; done

Be careful doing this, however, since there’s a bunch of things in wp_postmeta and such that have values like meta_key=_wp_attached_file that you may screw up (uh, like I did .

4. Import the new data. We are essentially re-creating a fully functional database, so we don’t need the first post, default category, etc. They’ll cause a duplicate entry error, so you’ll probably want to empty each table in the new database as you import the new information.

Note: I’m writing this to go from WP 2.7 to WPMU 2.8, and as I write it, there’s a 2.9 version about to be released. These releases sometime accompany DB table changes. It’s a good idea to look through each import statement and compare the old table structure to the new structure. What I say here ma change.

wp_posts: Add a new column called post_category to your wp_15_posts table in your new blog.² The old WP versions have this, but the new WPMU does not; thus, your import will fail. Doing this is and then dropping the column later is easier than modifying all of the insert scripts. Once you have an empty wp_15_posts table and the post_category column, do the import.

wp_options: A bit of a tough one. wp_options contains a lot of information in some blogs, and much of that is plugin specific. I opted to just ignore this one actually and let it rebuild everything as needed. It’s a bit of a conservative option to not mess with this table. You should look through the import statement, just so you know what’s there at least. You can also selectively delete things that don’t matter, and insert a bunch of options like comments_notify and posts_per_page just to save time later. I can say that if you change siteurl, things will break.

The rest: Just empty the new database table and import the old one, everything should be fine.

5. “AAHH! My pictures are gone!” Calm down! Fear is the mindkiller! Master your fear and follow these steps: Read-understand-then-use the following SQL commands:

UPDATE wp_${ID}_posts SET guid = REPLACE (guid, 'wp-content/uploads', 'files');
UPDATE wp_${ID}_posts SET post_content = REPLACE (post_content,
                                              'http://${DOMAIN}/wp-content/uploads',
                                              'http://${DOMAIN}/files');
UPDATE wp_${ID}_postmeta SET meta_value = REPLACE (meta_value,
                                               '/path/to/your/site/wp-content/uploads/',
                                               '/path/to/your/site/wp-content/blogs.dir/${ID}/')
                     WHERE meta_key='_wp_attached_file';

where DOMAIN is your domain (e.g. positivelyglorious.com) and ID is your blog’s id (e.g. 15). You could, of course, do much of this by modifying the SQL text files, but I like the power of SQL because it allows me to search and, say, find what the database is using for “/path/to/my/site” in different cases.

In some cases, the meta_value for meta_key=’_wp_attached_file’ may look like ‘2009/12/10/file.jpg’ instead of ‘/path/to/your/site/wp-content/uploads/2009/12/10/file.jpg’. The above command will ignore these files, and they will react as before in WordPress.

Coda

Alright, at this point you have all of the information from your previous blog correctly placed into the database of the new WordPressµ installation. You should be able now to browse to your new blog and see all of your posts. From here, you’ll want to explore your old plugins, grab your theme, and work from the admin interface. In other words, you’re on your own.

Go, young padawan. You are ready.

1. This lists all of the files that are wp_TABLENAME.sql. I downloaded my tables in gzipped format and then unzipped them, so this command exclude the gzipped files by filtering with “grep -vE gz” since I wanted to keep them. It also passes each listed name through awk and strips off the .sql extension. For each file name pipes the contents (adding the .sql extension) to a sed script that replaces wp_ with wp_15_ (my kzas.jmetta.com database id) and then spits them out to a new file that has a different name wp_TABLENAME-out.sql. Piping through awk and then adding the extension back on is not necessary, but allows us to have a new .sql file at the end for importing.
2. Best to add this column after post_title if you can.