WordPress Migration

Introduction

Originally, when I first started my job, I installed WordPress for the team that I worked on as a way to easily communicate and share ideas. The popularity of what I had done for the team quickly spread throughout the entire company. Before long, multiple WordPress blogs were popping up. Eventually, from a system administration standpoint, having multiple WordPress blogs became a complete nightmare for managing users, themes, plugins, etc. so I started converting the multiple WordPress blogs to WordPress MU blogs instead.

The following is an analysis of the database structure for WordPress and WordPress MU and the things learned while migrating WordPress blogs to WordPress MU. This assumes you have some familiarity with both WordPress and WordPress MU, otherwise you can following the links provided above or learn more in previous posts by searching this site (specifically the WordPress Usage Demo and WordPress MU Overview).

Getting Started

There are many ways to convert WordPress blogs to WordPress MU blogs but the easiest is to probably grab the WordPress Import/Export Plugin and install it in your existing WordPress blogs (as this comes default in WordPress MU). Once the plugin is installed, you can export the data from each WordPress blog to an XML file which you can then import into WordPress MU. This plugin, unfortunately, completes only part of the conversion process as it only imports/exports your posts, comments, and category data. The rest you will have to do manually. Read on.

Directory Structure

By default, all files uploaded for a post/page usually go the <WordPress root>/wp-content/uploads/ directory. This directory structure is further broken down by month and day sub-directories.

For WordPress MU, the directory structure looks slightly different: <WordPress MU root>/wp-content/blogs.dir/<blog ID>/files. As with WordPress, this directory structure is also broken down by month and day sub-directories. The <blog ID> is a number that varies depending on the order in which one of your WordPress MU blogs were created. For example, lets say that you have the following WordPress MU blogs (created in the order listed):

1. My Blue Blog
2. My Purple Blog
3. My Red Blog

The IDs for each blog would be: blue = 1, purple = 2, and red = 3. The IDs of each blog can be easily found by logging into WordPress MU, clicking on the Site Admin --> Blogs menu, and looking at the IDs of each blog listed. These IDs will correspond to the directories associated with each blog.

With both directory structures understood, all you need to do is copy contents from the <WordPress root>/wp-content/uploads/ directory structure to the <WordPress MU root>/wp-content/blogs.dir/<blog ID>/files directory structure.

While this gets the data in the right place, this does not mean that your posts, pages, and comments are not linking to the data correctly. For that, you will have to manually go through and edit the URLs of each post, page, and comment. Alternatively, you might think about exporting the post and comment tables to an SQL file, doing a find and replace on the URL paths within the code of the SQL file, adjusting the paths for the new structure, and importing the updated SQL data back into the database.

Database Structure

The following is a structural view of the MySQL database used by WordPress MU. PHP My Admin was used to view each table of the database. As mentioned earlier, the same blog ID principals applied to the directories applies to the tables as well.

At this point, you probably do not need to be tweaking anything in the database. If you have used the plugin mentioned above and followed the steps for setting up the directories structures, then you can do the rest of the work within WordPress MU. However, there are some things that are done a little easier using the MySQL database directly. The following highlights the differences between WordPress MU and WordPress and provides a few tips for dealing with the various tables:

wp_<blog ID>_categories

Identical to the WordPress wp_categories table except for the additional fields highlighted in yellow: link_count, posts_private, and links_private.

When importing WordPress data into this table keep in mind that WordPress MU defaults the “Uncategorized” category to cat_ID=1 and the “Blogroll” category to cat_ID=2. This means that you will need to adjust the SQL code exported from your original WordPress table to use cat_IDs higher than a cat_ID of two to avoid conflicts.

wp_<blog ID>_comments

Identical to the WordPress wp_comments table.

wp_<blog ID>_link2cat

Unique to WordPress MU only.

wp_<blog ID>_links

Identical to the WordPress wp_links table.

wp_<blog ID>_options

Identical to the WordPress wp_options table.

wp_<blog ID>_post2cat

Identical to the WordPress wp_post2cat table.

wp_<blog ID>_postmeta

Identical to the WordPress wp_postmeta table. You might think about editing and updating the URL paths of the meta_value field.

wp_<blog ID>_posts

Identical to the WordPress wp_posts table except for the post_status field highlighted in yelllow. This field has two additional types: “inherit” and “future”. This will not cause any modifications to be made to your SQL code when importing data from WordPress into WordPress MU.

wp_blogs

Unique to WordPress MU only.

wp_blog_versions

Unique to WordPress MU only.

wp_registration_log

Unique to WordPress MU only.

wp_signups

Unique to WordPress MU only.

wp_site

Unique to WordPress MU only.

wp_sitecategories

Unique to WordPress MU only.

wp_sitemeta

Unique to WordPress MU only.

wp_usermeta

Identical to the WordPress wp_usermeta table.

wp_users

Identical to the WordPress wp_users table except for the additional fields highlighted in yellow: “spam” and “deleted”. In my case, I exported the user data from all WordPress site as separate SQL statements, joined them together, and imported the combined data into this table. When joining the user data together, just make sure that all user IDs are unique.

Notes

• The tables that are unique to WordPress MU means that the data they store are are common to all blogs being managed by WordPress MU. The blog specific data is always stored in a table that is identified by a unique number in the name. The number used is determined by the order in which the blog was created.
• There is a table in WordPress called wp_linkcategories that is unique to WordPress and not found in WordPress MU. The data this table stores is used to categorize your blogrolls in WordPress. With WordPress MU, the usage has changed. You can still add links to your blogroll but you end up using the same categories as used for posts instead of having different categories for each.

Conclusion

Hopefully this has been helpful and will get you pointed in the right direction. Feel free to comment on anything discussed.

Tags: WordPress MU

No comments yet.

Leave a comment

You must be logged in to post a comment.