How to use separate databases for Drupal tables?

Question

So far, what I've read is you can config multisites to use different tables and share any table with other sites in that multisite config.

My question is if it is possible to get this config:

• Database A has Site A's users and Site A content
• Database B will only have Site B's content
• Site B will use users table in database A.
• Site B will have its content in database B.

It may not be a multisite question after all but rather, in short...

Is it possible for a Drupal site to use two different databases for some of its tables? How?

Answer 1

In the settings.php file used for the site, you can specific a different prefix for each database table.

  $databases['default']['default'] = array(
    'driver' => 'mysql',
    'database' => 'databasename',
    'username' => 'username',
    'password' => 'password',
    'host' => 'localhost',
    'prefix' => array(
      'default'   => 'main_',
      'users'     => 'shared_',
      'sessions'  => 'shared_',
      'role'      => 'shared_',
      'authmap'   => 'shared_',
    ),
    'collation' => 'utf8_general_ci',
  );

You can also refer different databases, or schema, using code similar to the following one.

  $databases['default']['default'] = array(
    'driver' => 'mysql',
    'database' => 'databasename',
    'username' => 'username',
    'password' => 'password',
    'host' => 'localhost',
    'prefix' => array(
      'default'   => 'main.',
      'users'     => 'shared.',
      'sessions'  => 'shared.',
      'role'      => 'shared.',
      'authmap'   => 'shared.',
    ),
    'collation' => 'utf8_general_ci',
  );

The difference between the last snippet, and the previous one is that the last character used for the prefixes is the dot, not the underscore. That causes the database engine to select a different database/schema, instead of the currently set one.
To be clearer, in the code I shown the database engine is still MySQL running on localhost; what changes is that for the users table, Drupal will use the shared database instead of the databasename one.

As far as I know, this is valid for Drupal 7, or higher. The same trick is not reported in the comments present in the default.settings.php file that comes with Drupal 6.

Reference

The comments shown in the default.settings.php file.