If you have a separate MySQL user for each site database, you must grant both users privileges to use the shared_db. For instance, if you have db_user1 using db1, you must grant db_user1 privileges for both db1 AND shared_db. Same with db_user2, must have privileges on both db2 AND shared_db.

It is very difficult to understand for newbie like me. Can you explain on how to do it on cPanel based hosting such as Hostmonster. I mean where to put

127.0.0.1      site1
127.0.0.1      site2

and how to do this in cpanel

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.

I wonder if it's necessary to create the drupal_shared_tables database to store the users data.

What's the difference between creating a 'drupal_shared_tables' db and using the tables 'authmap, users, profiles_, etc' from the database 'site1' to share with site2, site3...

wouldn't this make easier and risk less this part?:

"so you will need to be aware of install and update hooks which want to make changes to any shared tables"

I'm also guessing that 'drupal_shared_tables' db needs to have the same username/password combination from site1 and site2... Because I don't see anywhere settings for the 'drupal_shared_tables' db, so I'm assuming that the same username/password are necessary.

Now that sharing users between two separate drupal installations is clear, is there a way of telling the users apart? How can we tell which site (Drupal installation) a user has registered on?
This is really important for finding out the amount of registrees to one site compared to the other(s) for marketing / analysis purposes. A conceptual answer as to how to target this issue will prove useful too.

I wonder where Drupal connects to the "drupal_shared_tables" database. The dot seems fine for running queries, but where does the database connection happen?

Is anyone using this successfully?

i am guessing this is drifting away from the main topic of the post, which is most excellent info.
and thanks for replying so quick. i am still having issues though..

i am using pretty much your exact same setup. except the inclusion of the domain module.. :(

whats happening is the shared sign on module is going into a infinite redirect loop when those same tables are shared the same way. i went ahead and created a whole new install. on a seperate domain, subdomain, all different ways. no subdirectory deals, even tried setting the $cookie var in settings.php and got the same redirect loop when sharing the sessions table.

i have read here in the comments that sharing tables is somewhat sketchy with shared sign on. anyways. so i have another question. i am trying to merge three user bases together . and ideally allow single sign on. when i just share the users table alone, (or more specifically omit the sessions table) it seems to work more. in that each group of domains only require one login that persists across them all.

i am also using the domain module, and when i share the sessions tables (i think thats gotta be it) SSon goes into a loop. at first i was using the array you describe, including the users, profiles, etc.. tables.

I tried the 3 releases of sson for D6, clean urls enabled / disabled ,, etc.. sometimes creating the page not found issue which came up just before the last release of share sign on... ie redirecting to a page not found on the master url when trying to confirm the login... sometimes getting page not found on the slave url...

So back to the question. if the sequence ids are unique and random enough to sync to Uids securely; and if the user table itself still works to generate unique Userids/account logins/etc.. for folks across domain "trees", people are still signing up on all three sites and using the same passwords, etc. I havent tried the other tables, i would suspect that its in the sessions bit.

So looking at the table strucure, each session hash has a corresponding Uid entry. So each session would still only map to the proper id. As all of the users have unique Uids, even across all the domains, so you arent gonna log into "site a" with "userid 32" and have it map to a session on site b that is supposed to be unique and somehow map to a different "userid 32" or anything, no??? or for that matter any other session at all ??

sorry if this makes no sense. i cant explain it clearly.. if as admin i log into each master domain, a session entry for Uid 1 is created local to that domain. AFAIK that isnt causing security trouble is it?? this seems to be the best i can get out of the combination of modules for now.

by NOT sharing the sessions table it preserves single sign on per domain "tree", allows for a shared user base / logins /etc... and people just have to log in to each group of sites where share sign on will work within each group of domains... ???

anyways
thanks again. i can live with this. i am just jealous of the setup you describe. using a main users.foo.com site as the master domain for any number of other toplevel domains. if the multisite conflict with domain module is causing the problem i am bogarted. but the redirect loop seems like something that is specific to Single Sign, no??

i intend to re-merge the 3 domain trees into 1, so eventually it will get back to a unified login. sooner or later. if someone else is trying something similiar maybe this will help a little?

I'm currently running 5 different sites I am planning to migrate over to Drupal. Two of them are very database intensive. I'm planning to use pgsql for my database for various reasons. The techniques you describe are pretty standard sql and php operations from what I can see. Have these steps been tested with pgsql? Is there anything different I need to do vs mysql?

Hello,
I tried this tuto and it works, but i've got a question:
If you have 4 sites, the site 1 is the master (in SSO module) the others are the slaves, so fisrtly you have to be log in site 1, then you are log in all of your sites. But if you begin to log in site 2 (site3, or 4), the sso doesn't work.
So, how do you ensure that you can log into the site 2 (or the site 3 or site 4) and the SSO still works on all your sites?

That's the only problem i have, if someone can help me, thanks.

Does anybody know any possible consequences when not sharing the 'role' table?

$db_prefix = array(
    'default' => 'main_',
    'authmap' => 'drupal_shared_tables.',
    'profile_fields' => 'drupal_shared_tables.',
    'profile_values' => 'drupal_shared_tables.',
    'sessions' => 'drupal_shared_tables.',
    'users' => 'drupal_shared_tables.',
);

I already have a multisite installation and I don't want to stop sharing the roles since is getting complicated...

I have a simplified setup that works for me. It has worked for multisite as well seperate drupal installations on different servers

0. Please look at this page above and read the instructions first.

1. In your settings.php I have the shared tables as above but I am sharing only following tables
users, sessions, profile_fields, profile_values.

2. I have turned off registrations on all subdomains. You can register only on one domain I call master subdomain. All links to register point to master domain URL. I use www.domain.com as master domain.

3. I have turned off permission to change passwords on all sundomains. You can change password only on master domain.

4. Make sure you have correctly set your cookie domain to top level domain in all settings.php. That is "domain.com" and NOT "www.domain.com". This will be same in all settings.php

5. Also set the base url correctly for each subdomain in settings.php. So this will be different in each settings.php. Subdomain1 will have base_url=subdomain1.domain.com

6. There is no need to install any contributed module. The key to save sessions from one subdomain to another, is cookie_domain variable.

7. This works in both 5.x and 6.x. In fact I have a muiltisite on one server using 6.x and another server using 5.x all sharing same user database. This gives me flexibility to upgrade each subdomain seperately. I started with all 5.x sites and slowly upgrading as time permits.

8. You don't need to login first from master domain. You can login to any subdomain and the sessions will be carried over. You can logout anywhere and you will be logged out everywhere.

9. Works only if all sites share same top level domain. You can have as many subdomains as you want.

A minor note: As the author notes, the user profile information is kept in the tables profile_fields and profile_values. The default Drupal 6 installation does not have the profile module turned on, so those tables won't exist in the default database. If you want shared profiles, turn on the profile module, and the tables will be created.

though hard to grasp at first, I managed to follow this guide step by step and got my multisites working :) Thanks!!

The part where I got stuck (hopefully useful to someone in the same situation) are below:

Stuck point 1: Add these lines to your hosts files:
127.0.0.1 site1
127.0.0.1 site2
Resolution: is to point both your site1.com and site2.com to the same folder location, for example if your site1.com points to /document_root/ , don't let cpanel auto insert /document_root/site2.com! Manually change it back to /document_root/ ... big thanks to rjoy for pointing this out in the comments :)

Stuck point 2: Make a copy of /sites/default to /sites/site1 and another to /sites/site2.
Resolution: it is actually /sites/site1.com/ and /sites/site2.com , the '.com' is very important or it won't detect as multisites... no one told me this.. T-T

Stuck point 3: don't have profile_fields and profile_values in my database..
Resolution: Not everyone has these two tables unless you enabled the profiles module.. if you don't have it just leave it out. Thanks to jim_at_miramontes for pointing this out :)

I vote for Drupal making this easier, as it stands, setting up multi-site on Drupal is a piece of cake, couldn't be more simpler. Now if there was only a simple way to share content, blocks, without having pull teeth that would be over the top.

I set up two site multi-site mostly following the instructions here except I did not modify the hosts file with 127.0.0.1, etc... because I am on a shared web-server. Instead, I have a symbolic link from sub.site1.com -> public_html/drupal, so my sites are:

1st site: site1.com/drupal
2nd site: sub.site1.com

Everything worked fine, so I went to install the SSO module. I got all the way through the installation and then got this error when entering the controller url into the client SSO module settings:

The controller URL must be at the root of a domain.

So, I deleted sso from sites/all/modules until I felt like dealing with the trailing "drupal" in the controller url...

NOW, I have done two things:
1) removed the trailing "drupal"
and 2) changed the domain name of the 2nd site from sub.site1.com -> site2.com

To do (1), I modified public_html/.htaccess to:

Options -Indexes
RewriteEngine on
Options +FollowSymLinks
RewriteRule ^$ drupal/index.php [L]
RewriteCond %{DOCUMENT_ROOT}/drupal%{REQUEST_URI} -f
RewriteRule .* drupal/$0 [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule .* drupal/index.php?q=$0 [QSA]

I did not need to modify public_html/drupal/.htaccess, but here it is:

<IfModule mod_rewrite.c>
  RewriteEngine on
  RewriteCond %{REQUEST_FILENAME} !-f
  RewriteCond %{REQUEST_FILENAME} !-d
  RewriteCond %{REQUEST_URI} !=/favicon.ico
  RewriteRule ^(.*)$ index.php?q=$1 [L,QSA]
</IfModule>

Finally, I modified /sites/site1.com/settings.php to have the following:

$base_url = 'http://www.site1.com';

To do (2), I created symbolic links from public_html/site2 -> public_html/drupal and sites/site2.com -> sites/sub.site1.com

At this point, everything works as expected.

Now to install SSO...
I put sso into sites/all/modules. site1.com works fine. site2.com now gives a page not found error and redirects to the following URL:

http://site1.com/singlesignon/associate?origin=http%3A%2F%2Fsite2.com%2F...

(XXX is a unique alphanumeric string)

Note that I haven't done *anything* except extract the sso module - it hasn't been enabled on any site, etc...

I suspect that because I previously went through the entire activation of SSO, something was modified somewhere and it is causing this problem - is this correct? If so, what do I need to do to reverse it?

For those of you wondering how this works here's the quick rundown
http://api.drupal.org/api/function/db_prefix_tables/6 is the function where all the action happens

...
    if (array_key_exists('default', $db_prefix)) {
      $tmp = $db_prefix;
      unset($tmp['default']);
      foreach ($tmp as $key => $val) {
        $sql = strtr($sql, array('{'. $key .'}' => $val . $key));
      }
      return strtr($sql, array('{' => $db_prefix['default'], '}' => ''));
    }
...

strtr($string, $replace_pairs_array) (strtr — Translate certain characters) is the magic part of this operation. I'll use a role query as an example.

$db_prefix = array(
    'default' => '',
    'role' => 'drupal_shared_tables.',
);

SELECT * FROM {role} becomes SELECT * FROM drupal_shared_tables.role. This is important because we are using db_name.tbl_name syntax (http://dev.mysql.com/doc/refman/5.1/en/select.html). If you wanted 1 shared database but with different prefixes then you could do something like this

$db_prefix = array(
    'default' => '',
    'role' => 'drupal_shared_tables.prefix_a_',
);

$db_prefix = array(
    'default' => '',
    'role' => 'drupal_shared_tables.prefix_b_',
);

Which would result in SELECT * FROM drupal_shared_tables.prefix_a_role or SELECT * FROM drupal_shared_tables.prefix_b_role
I hope this explanation makes how the dot hack works a little bit clearer.

*Subscribing to thread*

I have successfully installed my multisite (subdomains) using the instructions on this page and i just want to say THANK YOU!!!!!!!!!!!!! :)

Hi all,

Thank you for all your explanations and ideas! I am in the process of a multi-site installation using Drupal 6.19. Not a total newbie (have used Joomla for the last 6 years since it was Mambo), but now considering moving to Drupal for more flexibility and serious functionalities (one main being multi-site).

My site is structured as:
domain.com - where users login
sub.domain.com - 10 total
I am using a shared-hosting solution in a LAMP environment. Do not have access to php.ini nor can restart apache daemons. Only control is through .htaccess

After reading the how-to and the discussions, I have a few questions as follows:

The author (Greg Harvey) explains that the following tables are to be shared to achieve the desired functionality (subject of this how-to):
authmap
profile_fields
profile_values
role
sessions
users
openid_associations (if using openID)

I do acknowledge the author's remarks that any set of tables can be shared.
My first question is related to some of the tables listed above. If we were to move the last 2 tables into the "drupal_shared_tables" then should we not also move the 2 tables below?
openid_nonce
users_roles
In other words, the question is: Do the above set of tables consist a necessary and sufficient minimal set of tables for using the shared userID functionality?

My second question: Following the explanations of Ajay-Luis-RPSU, is it then confirmed that the SSO module is NOT required to be installed/enabled?

Thanks in advance for your clarifications!
Anirvan

If for example I have site1.com and site2.com in one server , I can then create a MySQL VIEW of the users table from site1.com's database and store in site2.com's database. In that way, any changes to the users table will be seamlessly 'shared' across these 2 sites.

Thats my theory but i dont know if it will work . I should try it out :D

Hi, i tried this tutorial on my local machine. It works very well. But now i want to set it up on my webspace. I have a shared hosting package with multiple datebase and each database has a new user and password. Is there a way to handle it?

best regards
freq

http://drupal.org/project/multisite_wizard - for Drupal7 only