Train-ee | Learn Expression Engine Fast

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:

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:

(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

Add Your Comment

Comment on this Post

1. Name:
2. Email:
3. URL:
4. Comment:
5. Remember Me?
6. Email Notify of Response?
7. Enter this word:
   In this field: