Setting up a multi-site Drupal 6 installation with shared databases and single sign-on

Submitted by Eric on Mon, 09/07/2009 - 13:33

In this blog entry, I'll explain how I setup a multi-site Drupal 6 installation with shared databases and single sign-on. This will enable you to store your users (and other desired tables) in a separate database, and share them across multiple sites.

Before installing Drupal I created 3 databases, added 2 mysql users, and granted permissions. Both mysql users will have access to the shared database which will contain the shared tables:

$ mysql
# create new databases:
mysql> create database drupal_ms_1;
mysql> create database drupal_ms_2;
mysql> create database drupal_ms_shared;

# create new user "drupal_ms_1" and grant privileges to the databases:
mysql> grant all privileges on drupal_ms_1.* to 'drupal_ms_1'@'localhost' identified by 'drupal_ms_1';
mysql> grant all privileges on drupal_ms_shared.* to 'drupal_ms_1'@'localhost' identified by 'drupal_ms_1';

# create new user "drupal_ms_2" and grant privileges to the databases:
mysql> grant all privileges on drupal_ms_2.* to 'drupal_ms_2'@'localhost' identified by 'drupal_ms_2';
mysql> grant all privileges on drupal_ms_shared.* to 'drupal_ms_2'@'localhost' identified by 'drupal_ms_2';

For my setup I wanted to use a single Drupal filesystem, so I created a name-based Apache virtualhost to host both domain names (ms1.erl.dev & ms2.erl.dev):

NameVirtualHost *:80

<VirtualHost *:80>
ServerName ms1.erl.dev
ServerAlias ms2.erl.dev
DocumentRoot /var/www/vhosts/ms.erl.dev/httpdocs
ErrorLog logs/ms.erl.dev-error_log
CustomLog logs/ms.erl.dev-access_log common
</VirtualHost>

I downloaded and unpacked the Drupal installation file:

$ cd /var/www/vhosts
$ mkdir ms.erl.dev
$ cd ms.erl.dev
$ wget http://ftp.drupal.org/files/projects/drupal-6.13.tar.gz
$ tar -xzf drupal-6.13.tar.gz
$ mv drupal-6.13 httpdocs

I setup 2 new sites folders in the sites folder, and copied the default.settings.php file into my first site:

$ cd httpdocs/sites
$ mkdir ms1.erl.dev
$ mkdir ms2.erl.dev
$ cp default/default.settings.php ms1.erl.dev

At this point my databases and filesystem were ready to go so I installed Drupal for the first site (ms1.erl.dev). During installation, I entered "localhost" for the database host, drupal_ms_1 as the database, and drupal_ms_1 as the database user. I left the db_prefix setting blank (we'll change that later).

Now that Drupal was installed and the tables were created, it was time to modify the installation to enable single sign-on and shared databases.

You'll need to decide which tables to share across the sites. I decided on users, sessions, and authmap (but you could add more as desired, like roles, profile_*, etc). I used the following commands to transfer the desired tables from the drupal_ms_1 database into the shared database, remove the tables from drupal_ms_1, and then replicated drupal_ms_1 to drupal_ms_2:

$ mysqldump drupal_ms_1 users sessions authmap | mysql drupal_ms_shared
$ mysql drupal_ms_1
mysql> drop table authmap, sessions, users;
mysql> exit
$ mysqldump drupal_ms_1 | mysql drupal_ms_2

Next, I modified the settings.php for my first site (sites/ms1.erl.dev/settings.php) to enable the shared database and single sign-on configuration. The $db_prefix variable is used to specify a different database using the [DATABASE].[TABLE] syntax. You'll also need to add the $cookie_domain variable to ensure the cookie set by Drupal will work across both domain names.

<?php
// Replace:
$db_prefix = '';
// With:
$db_prefix = array(
  'default' => '',
  'users' => 'drupal_ms_shared.',
  'sessions' => 'drupal_ms_shared.',
  'authmap' => 'drupal_ms_shared.'
);
// Add:
$cookie_domain = '.erl.dev';
?>

I then copied the ms1.erl.dev settings.php file into my second sites folder. Edit the new ms2.erl.dev/settings.php $db_url variable to point to the drupal_ms_2 database (and update the user and password as well).

$ cp ms1.erl.dev/settings.php ms2.erl.dev

After you delete your domain cookies everything should be working! To test this out, log into your first site and then open a new window/tab and go to your second site. You should be logged in as the same user. If you log out from one site, you should also be logged out from the other site.

Add new comment

Comments

One little detail

Submitted by Anonymous on Tue, 07/27/2010 - 16:53.

Thank you so much for this explanation, it worked almost perfectly. But, on my hosting (doteasy, I hate them so much) I had to do something like this:

$db_prefix = array(
'default' => '',
'users' => '`drupal_ms_shared`.',
'sessions' => '`drupal_ms_shared`.',
'authmap' => '`drupal_ms_shared`.'
);

Apparently, I have to enclose the database name with the ` character

It works, it works!!!

Submitted by Anonymous on Sun, 06/13/2010 - 02:46.

Thank you so much for putting this page up. I've been looking everywhere for how to share tables and have a single sign-on with a multi-site setup, and this is the first tutorial that gave enough information that I could follow. Just saying, as most places did, to use the $db_prefix was not enough -- I needed to understand what was being done with the database also. Again, thank you!!

Susan from Los Angeles

different user and password

Submitted by Anonymous on Thu, 02/25/2010 - 07:14.

can you tell me how to setup drupal like above but with diffrent user and password?? example drupal_ms_1 with user1@localhost and drupal_ms_2 with user2@localhost
im newbies in drupal
thanks

profiles

Submitted by Eric on Tue, 09/08/2009 - 07:15.

Thanks Mike for the feedback... It's important to realize that if you do not include the profile_* tables in the shared database, users will have to create a new profile for each site. In my environment I left off those tables because I did not plan on using the Drupal Profile core modules, in favor of the Content Profile module which creates user profiles as nodes. To further complicate the situation, I was using the Domain Access module to shared nodes between the sites (including the profile nodes). Very good point, Mike!

-Eric

cookie domain

Submitted by Anonymous on Wed, 05/26/2010 - 10:29.

Single Sign on will only work if you are using subdomains from a common domain, e.g. a.mydomain.com and b.mydomain.com
As soon as you are trying it with www.mydomain1.com and www.mydomain2.com it will fail because of the cookies being different.

yes

Submitted by Eric on Tue, 06/01/2010 - 21:27.

Yes, this is correct. This tutorial ONLY works for subdomains, which is how the $cookie_domain variable works when prefixed with a "."

<?php
$cookie_domain = '.erl.dev';
?>

The above works with:

www.erl.dev
anything.erl.dev
etc.erl.dev

#cookie_domain for D.17+ requires subdomain leading "."

Submitted by Anonymous on Sat, 08/14/2010 - 23:06.

in other words, all domains are subdomains of www.domain.zzz => .domain.zzz

you could also use $cookie_domain = $_SERVER['SERVER_NAME'];

for single sites but don't think that works for multiple domain

(btw, your spam filter prevents posting triple 'x's)