Friday, October 10, 2014

Converting Wordpress Multisite to Single Site

Wow this is not easy.  Here are some notes on the steps that I took.
  1. Create a new fresh wordpress install.  Some basic instructions are here https://www.digitalocean.com/community/tutorials/how-to-install-wordpress-on-ubuntu-14-04
    NOTE: This tutorial assumes you will using the same MySQL database server and a MySQL database user that can access both databases.
  2. Make a copy of all the plugins and themes that you need.  If this on the same server it is easy.  If not use rsync, scp or ftp.
    cp -r /old/path/to/wordpress/mu/wp-content/plugins/* /new/single/site/wordpress/wp-content/plugins/.
    cp -r /old/path/to/wordpress/mu/wp-content/themes/* /new/single/site/wordpress/wp-content/themes/.
  3. Get the blog number of the old site.  You can use the network admin and go to the Sites or /wp-admin/network/sites.php and the link to the blog will have ?id=#.  This will be used to prefix the tables that are needed wp_#_tableName.
  4. Copy over the existing data from the old database to the new one
    mysql -u root -p NewDb
    mysql> INSERT INTO wp_commentmeta SELECT * FROM wpmu.wp_#_commentmeta;
    mysql> INSERT INTO wp_comments SELECT * FROM wpmu.wp_#_comments;
    mysql> INSERT INTO wp_links SELECT * FROM wpmu.wp_#_links;
    mysql> INSERT INTO wp_posts SELECT * FROM wpmu.wp_posts;
    mysql> INSERT INTO wp_term_relationships SELECT * FROM wpmu.wp_#_term_relationships;
  5. Empty tables that have data in them already and then copy over old data.
    mysql> TRUNCATE TABLE wp_options;
    mysql> INSERT INTO wp_options SELECT * FROM wpmu.wp_#_options;
    mysql> TRUNCATE TABLE wp_posts;
    mysql> INSERT INTO wp_posts SELECT * FROM wpmu.wp_posts;
    mysql> TRUNCATE TABLE wp_term_taxonomy;
    mysql> INSERT INTO wp_term_taxonomy SELECT * FROM wpmu.wp_#_term_taxonomy;
    mysql> TRUNCATE TABLE wp_terms;
    mysql> INSERT INTO wp_terms SELECT * FROM wpmu.wp_#_terms;
    mysql> TRUNCATE TABLE wp_usermeta;
    mysql> INSERT INTO wp_usermeta SELECT * FROM wpmu.wp_usermeta;
    mysql> TRUNCATE TABLE wp_users;
    mysql> INSERT INTO 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 wpmu.wp_users;
    mysql> TRUNCATE TABLE wp_postmeta;

    mysql> INSERT INTO wp_postmeta SELECT * FROM wpmu.wp_#_postmeta;
  6. Create tables and copy data for plugins as necessary
    mysql> CREATE TABLE wp_calendar LIKE wpmu.wp_#_calendar;
    mysql> CREATE TABLE wp_calendar_categories LIKE wpmu.wp_#_calendar_categories;
    mysql> CREATE TABLE wp_calendar_config LIKE wpmu.wp_#_calendar_config;
    mysql> CREATE TABLE wp_contact_form_7 LIKE wpmu.wp_#_contact_form_7;
    mysql> CREATE TABLE wp_duplicator LIKE wpmu.wp_#_duplicator;
    mysql> CREATE TABLE wp_duplicator_packages LIKE wpmu.wp_#_duplicator_packages;
    mysql> CREATE TABLE wp_eventscalendar_main LIKE wpmu.wp_#_eventscalendar_main;
    mysql> CREATE TABLE wp_forum_forums LIKE wpmu.wp_#_forum_forums;
    mysql> CREATE TABLE wp_forum_groups LIKE wpmu.wp_#_forum_groups;
    mysql> CREATE TABLE wp_forum_posts LIKE wpmu.wp_#_forum_posts;
    mysql> CREATE TABLE wp_forum_threads LIKE wpmu.wp_#_forum_threads;
    mysql> CREATE TABLE wp_forum_usergroup2user LIKE wpmu.wp_#_forum_usergroup2user;
    mysql> CREATE TABLE wp_forum_usergroups LIKE wpmu.wp_#_forum_usergroups;
    mysql> CREATE TABLE wp_ngg_album LIKE wpmu.wp_#_ngg_album;
    mysql> CREATE TABLE wp_ngg_gallery LIKE wpmu.wp_#_ngg_gallery;
    mysql> CREATE TABLE wp_ngg_pictures LIKE wpmu.wp_#_ngg_pictures;
    mysql> INSERT INTO wp_calendar SELECT * FROM wpmu.wp_#_calendar;
    mysql> INSERT INTO wp_calendar_categories SELECT * FROM wpmu.wp_#_calendar_categories;
    mysql> INSERT INTO wp_calendar_config SELECT * FROM wpmu.wp_#_calendar_config;
    mysql> INSERT INTO wp_contact_form_7 SELECT * FROM wpmu.wp_#_contact_form_7;
    mysql> INSERT INTO wp_duplicator SELECT * FROM wpmu.wp_#_duplicator;
    mysql> INSERT INTO wp_duplicator_packages SELECT * FROM wpmu.wp_#_duplicator_packages;
    mysql> INSERT INTO wp_eventscalendar_main SELECT * FROM wpmu.wp_#_eventscalendar_main;
    mysql> INSERT INTO wp_forum_forums SELECT * FROM wpmu.wp_#_forum_forums;
    mysql> INSERT INTO wp_forum_groups SELECT * FROM wpmu.wp_#_forum_groups;
    mysql> INSERT INTO wp_forum_posts SELECT * FROM wpmu.wp_#_forum_posts;
    mysql> INSERT INTO wp_forum_threads SELECT * FROM wpmu.wp_#_forum_threads;
    mysql> INSERT INTO wp_forum_usergroup2user SELECT * FROM wpmu.wp_#_forum_usergroup2user;
    mysql> INSERT INTO wp_forum_usergroups SELECT * FROM wpmu.wp_#_forum_usergroups;
    mysql> INSERT INTO wp_ngg_album SELECT * FROM wpmu.wp_#_ngg_album;
    mysql> INSERT INTO wp_ngg_gallery SELECT * FROM wpmu.wp_#_ngg_gallery;
    mysql> INSERT INTO wp_ngg_pictures SELECT * FROM wpmu.wp_#_ngg_pictures;

  7. To ensure that there is no wp_#_tableName in the data you may need to dump the DB and then search and replace that string then import it again.
    mysqldump -u root -p database > database.sql
    sed -i 's/wp_#_/wp_/g' database.sql
    mysql -u root -p database < database.sql
  8. This assumes you have a new apache or nginx setup pointing to the old domain and the domain name is not changing.  If you want to change the domain you can edit the wp_options table
    mysql> UPDATE wp_options SET option_value = 'http://new.domain.name/' where option_name=siteurl;
    mysql> UPDATE wp_options SET option_value = 'http://new.domain.name/' where option_name=home;
That should do most of it.  If you have issues turn on debug in the wp-config.php file, and get your google foo going, most wordpress issues have been addressed in the forums or elsewhere.