Multi-site with single codebase, different content databases, shared user database, shared sign-on

Last updated August 13, 2009. Created by bekasu on August 13, 2009.
Log in to edit this page.

IMPORTANT NEW INFORMATION - In light of this comment regarding the ability of Drupal to handle different databases this HOWTO has been re-written. You do not need to use MySQL views at all to achieve this functionality.

WARNING - Following this approach will make updates potentially trickier. It's not a major issue, but you must be mindful that you are sharing some tables amongst several Drupal instances, so you will need to be aware of install and update hooks which want to make changes to any shared tables. For example, it is possible for the update or install scripts of a module unique to one instance to break other instances if it makes changes to shared tables. There are other multi-site solutions, so plan carefully before selecting which one is right for you.

This tutorial makes two assumptions:

1. You are _not_ a total newbie
2. You have downloaded MySQL GUI Tools (to make life easier) - clearly this is not a pre-requisite - if you know and prefer command line MySQL then you can replicate the steps I provide easily enough:
http://dev.mysql.com/downloads/gui-tools/5.0.html

Make a copy of /sites/default to /sites/site1 and another to /sites/site2. You may add modules and themes directories in these new directories in the usual way for site-specific configurations. A files directory should be there already but create it if not. Rename /sites/site1/default.settings.php to settings.php and /sites/site2/default.settings.php to settings.php (and, for Linux, make sure both new files directories are writeable by the web user).

Add these lines to your hosts files:

Create three empty MySQL databases:

Go to http://site1 and run through the install process in the normal way, using the database name 'drupal_site1'.

In MySQL Administrator create a new back-up project of the 'drupal_site1' database containing _only_ the tables you want to share, save it and run it. The result should be a .sql file somewhere on your computer. For reference, the Drupal 6.x tables for sharing users are as follows:

(This is not a definitive list. Nothing stops you from sharing taxonomy, etc. For example, with this configuration permissions are _not_ shared - each site has its own permissions matrix - but you might want to share one across all sites. Ditto with enabled modules.)

Edit the .sql script you created and update the database name within to 'drupal_shared_tables'. Using the Restore interface, run the .sql script. You should now have a database called 'drupal_shared_tables' containing only the list of tables above.

Go to http://site2 and run through the install process in the normal way, using the database name 'drupal_site2'.

Go back to your original 'drupal_site1' database and drop the same list of tables from the schema. Ditto for the 'drupal_site2' database.

Now our databases are ready.

Browse to your Drupal application and edit /sites/site1/settings.php. Change these lines (usually starting at line 93 in Drupal 6.3):

To:

Note the all important trailing dot on the end of drupal_shared_tables - this is vital! You're pre-pending table name info to Drupal's default table name, so you need the dot or Drupal will try to select:

Instead of the desired:

Do the same for /sites/site2/settings.php, but with $db_url set to drupal_site2.

You should now be able to login as the super user on both sites separately (http://site1/user or http://site2/user), using the credentials you entered when you installed site1, give them both different settings, modules, permissions and content but use the same user credentials. Moreover, if you create a user on site1, you will see them in users in site2, but with an entirely independent set of permissions you can decide on a site level.

Now for the shared logins. Download and unzip the Shared Sign-On module in to /sites/all/modules. Enable it in both sites and go to the settings (admin/settings/singlesignon). Set the 'Master URL' to http://site1 for both sites. (Note, you could have two sets of sites with different 'Master URL' settings, effectively two shared sign-on groups - this is untested by me.) Save settings and logout. Clear your cookies to avoid any hang-overs from pre-shared-sign-on days.

Go to http://site1/user and login. Now go to http://site2. You should stay logged in. Logout again and see you are logged out in both locations. Reverse it so you login on site2 and visit site1. Neat, huh? =)

Need a new site? Take a copy of /sites/default to /sites/new-site, make the necessary vhosts (optional, see below) and DNS/hosts changes, create a blank database, run the Drupal installer, drop the tables you don't want from your new database (the shared ones), edit settings.php adding the $db_prefix array as above, configure Shared Sign-On and you're done!

Optional Apache settings

If you would like to set different Apache settings for your various sites (e.g. different access and error logs for each) add a vhost entry to Apache looking something like this:

This is not usually necessary.

IMPORTANT - if you provide web services, the site hosting them _must_ have a line in the 'Target URL' box of Shared Sign-On settings (admin/settings/singlesignon) referring to each endpoint your site provides, for example:

\/services\/xmlrpc$

This example is for the XML-RPC server provided with the Services module. Adjust to suit your endpoint(s). You do not need to do this if your site hosting the web services is also the 'Master URL'.