Last updated April 21, 2012. Created by bekasu on August 13, 2009.
Edited by xtfer. 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:

127.0.0.1      site1
127.0.0.1      site2

Create three empty MySQL databases:

drupal_site1
drupal_site2
drupal_shared_tables

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:

authmap
profile_fields
profile_values
role
sessions
users

(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):

<?php
$db_url
= 'mysqli://user:password@localhost/drupal_site1';
$db_prefix = '';
?>

To:

<?php
$db_url
= 'mysqli://user:password@localhost/drupal_site1';
$db_prefix = array(
   
'default' => '',
   
'authmap' => 'drupal_shared_tables.',
   
'profile_fields' => 'drupal_shared_tables.',
   
'profile_values' => 'drupal_shared_tables.',
   
'role' => 'drupal_shared_tables.',
   
'sessions' => 'drupal_shared_tables.',
   
'users' => 'drupal_shared_tables.',
);
?>

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:

drupal_site_1.drupal_shared_tablesauthmap

Instead of the desired:

drupal_shared_tables.authmap

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:

<VirtualHost *:80>
    DocumentRoot "c:/path/to/drupal"
    ServerName site1
    ServerAlias site2
    <IfModule log_config_module>
        CustomLog logs/d6_access_log.log common
    </IfModule>
    ErrorLog logs/d6_error_log.log
    <Directory "c:/path/to/drupal">
        Options +Indexes
        AllowOverride All
        Allow from all
    </Directory>
</VirtualHost>

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'.

Looking for support? Visit the Drupal.org forums, or join #drupal-support in IRC.

Comments

Although the "about this article" zone says this is for Drupal 7, the text in the article is for Drupal 6.

The tables "profile_fields" and "profile_values" don´t exist in Drupal 7. Also the db variables are slightly different.

I´ve tried the solution ignoring the missing tables and so far is performing good. The behavior is as expected.

This is how the settings.php looks like:

<?php
$databases
= array (
 
'default' =>
  array (
   
'default' =>
    array (
     
'database' => 'drupal_site1',
     
'username' => 'user',
     
'password' => 'password',
     
'host' => 'localhost',
     
'port' => '',
     
'driver' => 'mysql',
     
'prefix' => array(
           
'default' => '',
           
'authmap' => 'drupal_shared_tables.',
           
'role' => 'drupal_shared_tables.',
           
'sessions' => 'drupal_shared_tables.',
           
'users' => 'drupal_shared_tables.'
     
),
    ),
  ),
);
?>

I´ll continue testing. If anyone has more information about this please post it.
I think further testing is needed before updating the documentation.

Hello,

After trying and failing to get this process to work on 7.x using the instructions in the article above, I tested the above post by Rodre and it works great. While the article itself still works well in 6.x (used it for 3 years on my older installation of 6.x), it is nice to see someone put together a solution in simple terms for 7.x.

Kudos to you for a job well done!

How about user field ?
I have tested by some field below

<?php
'field_data_field_first_name' => 'shared_', //stored with shared_ prefix
'field_revision_field_first_name' => 'shared_', //stored with shared_ prefix
'field_data_field_last_name' => 'shared_', //stored with shared_ prefix
'field_revision_field_last_name' => 'shared_', //stored with shared_ prefix
'field_data_field_phone_ext' => 'shared_', //stored with shared_ prefix
'field_revision_field_phone_ext' => 'shared_', //stored with shared_ prefix
'field_data_field_business_unit‎' => 'shared_', // -----> PROBLEM : did not stored with shared_ prefix
'field_revision_field_business_unit‎' => 'shared_', // -----> PROBLEM : did not stored with shared_ prefix
'field_data_field_departement‎' => 'shared_', // -----> PROBLEM : did not stored with shared_ prefix
'field_revision_field_departement‎' => 'shared_', //-----> PROBLEM : did not stored with shared_ prefix
?>

I do not know why my business unit and departement field did not stored with shared_ prefix ?

i am trying since weeks to make a shared user login on several websitesrunning D7 and don't understand how you authenticate with the password from db1, db2 or whatever to the shared_user db without the password ?. I read a million tutorials. No matter how i try it i always get authentication failures. The same with civicrm, views integration, but that doesn't belong here.
I have 2 databases with different usernames and passwords. Is there any way to resolve this problem ?
thx

Can anyone tell me the disadvantages of shared users? When it comes to problems with updates. What i have to consider?
What happens if I install modules that want to access one of the shared tables? Is this automatically redirected to the shared SQL database?

Thanks for the instruction. It works really well. I have 1 comment and 2 question:

(Comment): I have configured all the subsites' settings.php to point those tables (roles, sessions, ect.) to the default root site database. (Q1): Does such change will have same/different issues on future updates? (Q2): Shared Sign-On . . . has been marked as insecure by Drupal Security Team. All of it's releases have been unpublished. Disable the module and remove it from your site. Is there an alternative?

Thanks in advance for all the help.
NSL

The module for Single Sign On has been abandoned and there is no release for D7 or D8. They tell us to use CAS (https://drupal.org/project/cas) instead, but that is not really a solution for single sign-on between two or more drupal sites with shared users table unless you have a CAS server. I think what needs to be used instead is the Bakery module...