I found myself working on a project last week to migrate a WordPress site to WordPress multiuser (WPMU). I did the usual Google search for guidance and didn’t see much that encouraged me, I have to say. Posts, like this one were pretty helpful. But I wanted a process that could be clearly articulated, practiced a couple times on a test system, and then executed in something like 10 minutes, to keep the outage as short as possible. (The site has an active world-wide following.)
I had a big advantage – just one blog was being migrated, so no mess around which author gets what privileges on which blog. Merging several multi-author blogs into a single WPMU site would be more work, for sure.
The approach I took is different from any I saw elsewhere. I chose to build a second database for WPMU and used a script to pull the data out of the original WP db into the WPMU db. This approach allowed me to commit the data migration process to code, which can be tested repeatably.
At a high level the process of migrating the site went like this:
- Create a new database for WPMU
- Redirect all users but me to an under-construction page
- Install WPMU (preloaded with all of my plugins and themes) to initialize the database
- Use a SQL script I wrote to copy content from the old WP database into the new WPMU tables
- Relocate the files in the wp-content/uploads directory to the wp-content/blogs.dir/1 directory
- Jump into the WPMU administration console and fix up the configuration
- Test
The script I created is for an average blog with no tables added for plugins. The key message, as is noted in most posts on this subject is that the blog content tables are named using the convention: ‘wp_blogid_table‘. Users and options, however, remain in a single set of tables for all blogs.
Here is the SQL script:
-- users
-- purge users installed by WPMU
DELETE FROM new_wpmu_db.wp_usermeta;
DELETE FROM new_wpmu_db.wp_users;
-- copy users from WP db
INSERT INTO new_wpmu_db.wp_users(ID, user_login, user_pass, user_nicename,
user_email, user_url, user_registered,user_activation_key, user_status, display_name)
(SELECT ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered,
user_activation_key, user_status, display_name FROM old_wp_db.wp_users);
-- copy usermeta
INSERT INTO new_wpmu_db.wp_usermeta (SELECT * FROM old_wp_db.wp_usermeta);
-- fix user privileges
UPDATE new_wpmu_db.wp_usermeta SET meta_key=REPLACE(meta_key, 'wp_', 'wp_1_');
-- clean out generated content from primary blog db
DELETE FROM new_wpmu_db.wp_1_comments;
DELETE FROM new_wpmu_db.wp_1_links;
DELETE FROM new_wpmu_db.wp_1_postmeta;
DELETE FROM new_wpmu_db.wp_1_term_relationships;
DELETE FROM new_wpmu_db.wp_1_term_taxonomy;
DELETE FROM new_wpmu_db.wp_1_terms;
DELETE FROM new_wpmu_db.wp_1_posts;
-- copy posts from oritinal blog db
INSERT INTO new_wpmu_db.wp_1_posts (ID, post_author, post_date, post_date_gmt,
post_content, post_title, post_excerpt, post_status, comment_status, ping_status,
post_password, post_name, to_ping, pinged, post_modified, post_modified_gmt,
post_content_filtered, post_parent, guid, menu_order, post_type, post_mime_type,
comment_count)
(SELECT ID, post_author, post_date, post_date_gmt, post_content, post_title,
post_excerpt, post_status, comment_status, ping_status, post_password, post_name,
to_ping, pinged, post_modified, post_modified_gmt, post_content_filtered, post_parent,
guid, menu_order, post_type, post_mime_type, comment_count FROM old_wp_db.wp_posts);
-- copy postmeta from oritinal blog db
INSERT INTO new_wpmu_db.wp_1_postmeta (SELECT * FROM old_wp_db.wp_postmeta);
-- copy links from oritinal blog db
INSERT INTO new_wpmu_db.wp_1_links (link_id, link_url, link_name, link_image,
link_target, link_description, link_visible, link_owner, link_rating, link_updated,
link_rel, link_notes, link_rss)
(SELECT link_id, link_url, link_name, link_image, link_target, link_description,
link_visible, link_owner, link_rating, link_updated, link_rel, link_notes, link_rss
FROM old_wp_db.wp_links);
-- terms, taxonomy & relationships
INSERT INTO new_wpmu_db.wp_1_terms (SELECT * FROM old_wp_db.wp_terms);
INSERT INTO new_wpmu_db.wp_1_term_relationships (SELECT * FROM old_wp_db.wp_term_relationships);
INSERT INTO new_wpmu_db.wp_1_term_taxonomy (SELECT * FROM old_wp_db.wp_term_taxonomy);
-- comments
INSERT INTO new_wpmu_db.wp_1_comments (SELECT * FROM old_wp_db.wp_comments);
The only slightly tricky part in the whole script was figuring out how to fix the user permissions to point at the right blog.
Let me introduce myself and what I intend to do with this blog.