Splitting a Site out of an ExpressionEngine Multiple Site Manager Installation

As part of the Building a Church Site Series I’ll be auctioning off a copy of the complete site with proceeds going to charity.  Part of my preparations for that auction are getting the site installed on it’s own hosting account.  No big deal, except for Train-ee.com uses the Multiple Site Manager to run the main site plus the church site and the Small Business Site featured in an earlier series.  There is no automated way to pull a single site out of an MSM installation, so this article will cover the process I went through to do so.

 

Disclaimer
Methods used in this article directly manipulate the EE database and as such are somewhat risky.  They are best undertaken by people experienced with databases and SQL statements. By no means do this on a live site!  Make a backup or two before proceeding, and do your manipulations on a copy. And - proceed at your own risk.  I or Train-ee.com will not be held responsible for damage inflicted to your site by attempting this work on your own.

The Situation:
OK - so here’s what I had:

  • Three sites total, with the target site being #2
  • A whole bunch of registered user accounts in multiple member groups
  • All templates stored in the database (vs external flat files)
  • Site hosted at EngineHosting (who do, in fact, rock)

Starting Out
I did what anyone in this situation might do - searched the EE Forums to see if anyone else had done this and posted any tips or gotchas.  I came up with this thread which served to lay out my basic workflow:

  • Make a backup of the entire site
  • Download the database locally for manipulation
  • Delete Site #3 entirely
  • Clean out all weblogs, field groups, status groups, member accounts, template groups and templates from Site #1
  • Go through the database and change all site_id’s currently storing Site #2 to store Site #1 instead (effectively moving all EE backend objects from Site #2 to Site #1)
  • Once Site #2 objects have been moved over to Site #1, delete Site #2
  • Once done/successful upload single site to new host

I had a feeling this would make a good article for Train-ee so kept notes during the process.  Below are those notes, prettied up a bit, but still including the stops and starts that I ran into as I felt it was important to note what kind of issues can come up in this type of work.

Loading the Database
I logged into the EngineHosting Control Panel and did a databse backup of Train-ee.com using PHPMySQL.  So far so good.

Once I had the db backup saved locally I fired up my WAMP setup and set about to load the database locally.  Attempting to import the database using the PHPMySQL interface I got a PHP file size limit error.  I’ve run into this before trying to load tables back into the database and found this Staggered MySQL Dump Importer Script named BigDump that works a treat.  I configured that and ran it, but then ran into an error about the database backup containing Extended Inserts.  I went back to the Train-ee hosting control panel and re-ran the export with Complete Inserts only.  I ran BigDump again and the database imported without error.

EE Control Panel
I loaded ExpressionEngine but found that the Control Panel wasn’t loading any styles. I updated paths under Admin / System Preferences to no avail.  I then also noticed that my local EE install was 1.6.6 yet - and worried about bringing in 1.6.7 data to an 1.6.6 install.  I updated ExpressionEngine to 1.6.7 successfully but still had no Control Panel styles.

I found that the Theme Folder Path still wasn’t correct (I looked at the WAMP/EE setup on another system for direction).  I specified a full path for the theme folder starting from C:\ and once I updated the Control Panel styles returned.  I also had to remember to update these same paths in the configuration of the other MSM sites.

Re-arranging the Furniture
I deleted Site #3 using the CP interface - easy peasey there.

I then set about deleting all the objects within Site #1:

  • Weblogs
  • Field Groups
  • Category Groups
  • Custom Status Groups
  • Template Groups
  • Member Groups

Member Accounts
Remember - deleting members also deletes their entries and comments.  This was fine in my case but may not be for you if your site has user-generated content that you wish to move over.

While I was deleting member groups, ExpressionEngine put all the members into Banned group - which you can’t delete.  I still wanted all these accounts gone, so at first started deleting members via the Control Panel.  This only deletes 30 or so at a time, however so I started to wonder if a SQL-based approach might work faster. I turned on Display SQL Queries in hopes of being able to copy and modify the query that EE generates and delete all the members at once, but the queries are relatively complex with ID’s getting specified everywhere so that approach looked too hard.  I ended up just turning the queries back off and used interface controls to delete all member accounts except for my SuperAdmin login.

Database Updates
When you install the MSM Expansion, EE makes use of a Site ID contained in many of the tables.  The value in this field tells EE which site a particular database record is associated with.  The task at hand, then, is to go through the database, find those records that have a site_id of “2”, and change those records to instead have a site_id of “1”.  This effectively moves all backend objects from Site #2 to Site #1.  I recommend doing this table by table, as I ran into a few that I choose not to touch. 

Here is the query statement I used:

UPDATE exp_table_name_here SET exp_table_name_here.site_id "1"

I kept this query in a Notepad document where I could change the table name as needed. 

There are two spots in the EE interface you want to be in during this process - so have two tabs or two browser windows available. Both of these are found under Admin > Utilities > SQL Manager.  The first one you want is the SQL Query Form.  The other one is the Manage Database Tables interface.  Note that you could do this all within PHPMySQL as well, I just chose to stay in ExpressionEngine.

The workflow is:

  • Go through the Manage Database Tables interface one table at a time
  • Look for tables that have data
  • Click the Browse link to take a look at the data
  • If the table has the site_id field (usually it’s one of the first couple of fields) then the update query needs to run on that table
  • Edit the SQL query to use the name of the table you’re looking at
  • Paste that query into the SQL Query Form
  • Click Submit - You should get a message about the number of rows affected

Here’s my list - if a table is mentioned with no notes I ran the query against it.  Tables not mentioned either had no data or no site_id field.  Tables with notes had the data but I chose to leave them alone for reasons indicated:

  • exp_categories
  • exp_category_field_data
  • exp_category_groups
  • exp_comments
  • exp_cp_log
  • exp_field_groups
  • exp_html_buttons
  • exp_member_groups: I left this one alone as I didn’t want to mess up my own login.  I figured appropriate member groups will be deleted when site #2 is deleted.
  • exp_online_users
  • exp_referrers
  • exp_search_log
  • exp_sites - I’m going to leave this one alone as well as I expect it’ll get updated when I delete Site #2
  • exp_specialty_templates - going to leave this one alone assuming deletions will happen here when I delete Site #2
  • exp_stats: I’m also leaving this one alone as it’s not mission critical and I expect it to be updated when I delete Site #2.
  • exp_status_groups
  • exp_template_groups
  • exp_templates
  • exp_upload_prefs
  • exp_weblog_data
  • exp_weblog_fields
  • exp_weblog_titles
  • exp_weblogs

With the database updates all done I went back through the EE Control Panel - and it appeared to have worked.  All template groups, weblogs, posts, field groups, category groups etc that were all previously in Site #2 were now showing in Site #1. I made a few quick posts, saving then deleting them with no error. I then went into the Sites menu and deleted Site #2.

The Rest of the Site
I then moved all ancillary files from server to local - images, JavaScript files, etc. Once these were pulled down I tried to load the site in my browser.  I forgot a couple of plugin files and got an error to that extent.  Once I installed the missing plugins the site loaded and functioned normally.

Success!  At this point the site is running well locally - and I foresee no real issues with loading it up to a new hosting account where it can stand on it’s own feet. 

The Last Step
There’s no real way to “completely uninstall” MSM - the site_id field will remain in the database The last step - to remove the MSM related options from the CP - would be to edit the config.php file to include:

$conf['multiple_sites_enabled'"n"

(Thanks to the EE Tech Support staff for input on that one).

I’d say this process took roughly 2.5 to 3 hours—and while it’s always a bit unnerving to go muck about at the database level it seemed to come off fairly easily.  If you undertake this process and encounter other issues or gotchas be sure to leave a comment!

Category Navigation

Next Entry >>

 

Previous Comments

Picture of Travis Smith

by Travis Smith

Date: Wednesday, April 8th, 2009
Comment: #1

Mike,

This is a great and useful article, and I’ll be bookmarking it.

One thing I can add, though, is that the site_id fields are always there, even for a site that’s never had multiple site manager installed. They are in every modern EE site; they just simply have no job to do until you buy the multiple site manager.

TTFN
Travis
Hop Studios

Mike Boyink

by Mike Boyink (Author)

Date: Thursday, April 9th, 2009
Comment: #2

Good catch Travis - edited the article.

Picture of Simon Cox

by Simon Cox

Date: Thursday, April 16th, 2009
Comment: #3

Mike, great article. I had not thought of the process and any related issues with disassemble a MSM setup as I am sure the majority of people using MSM haven’t either.

Picture of Stephen McIver

by Stephen McIver

Date: Saturday, June 20th, 2009
Comment: #4

Hi Mike,

Good article - I’ve used this to help me move a few sites out of the MSM now and it’s been really useful.

I had a real hassle importing my database as the exported SQL file was 150mb (even when optimised, as there were 13 sites within the MSM, 350 custom fields in the various weblogs, and over 8,000 weblog entries), and even BigDump didn’t work for me (even when doing it locally on a MAMP server where I I’d edited all the limits, max filesizes, and timeouts.

I then came across a really easy way to import these big SQL files so I thought I should share it here:

1. Create a new, blank database via cPanel or phpMyAdmin (or whatever).

2. FTP the SQL file into the public_html folder of your web space (or any folder you want, it’s only temporary).

3. Connect to the server using SSH and type the following:

mysql -uUSERNAME -p DATABASENAME < /home/serverpath/public_html/MYSQLFILE.sql

Then type in the password when prompted.

Thanks Mike,

Stephen

Mike Boyink

by Mike Boyink (Author)

Date: Saturday, June 20th, 2009
Comment: #5

Thanks Stephen !  That will be handy for those with SSH accesss to their sites.

Picture of Stephen McIver

by Stephen McIver

Date: Saturday, June 20th, 2009
Comment: #6

I hate using SSH and am a bit of a novice to it, but it can’t half help with some things if you have access to it on your site. But generally, give me a nice GUI any day! :-)

I forgot to mention one more thing from the notes above:

“exp_sites - I’m going to leave this one alone as well as I expect it’ll get updated when I delete Site #2”

I found that without touching this table, I was unable to delete the site I wanted to. So what I did was change Site #1 to #9 (or whatever number wasn’t being used), then changed Site #2 to Site #1.

The next step for me was to delete the un-wanted site via the Control Panel.

Picture of Natalie

by Natalie

Date: Thursday, September 15th, 2011
Comment: #7

Thanks for the article! I just finished up the process of separating two sites. The only issue I ran into was that somehow during my database hacking I lost all of the page URIs (the ones used in the Pages module). Luckily it was fairly easily for me to manually update the pages because there was only 16 of them, but I couldn’t find where that info was stored.

Overall, pretty smooth process and once I had the files copied, only took me one hour.

Thanks again!

Picture of Marc

by Marc

Date: Thursday, January 5th, 2012
Comment: #8

Wow that article just saved my life. Very well done, but I noticed you didn’t mention exp_global_variables and exp_snippets which I had to clean in my site.

Add Your Comment

Commenting is not available in this channel entry.

Unless otherwise stated all content is © Michael Boyink of Train-ee.com & Boyink Interactive. Please don't steal - I've got kids to feed...

dy>