2

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?

7

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.

  • Yes! Thank you! Should've looked more in the sample. This is, of course, with the assumption that the username used has sufficient privileges on all databases being referenced. – radj Feb 10 '13 at 14:16
  • In your prefix array, you reference main. and shared. How does Drupal know what databases this is talking about? How does it know what server to find them on? – BeardedGeek Sep 11 '13 at 20:39
  • @BeardedGeek In the code I am showing, the database engine is still MYSQL, and the host is localhost. What changes is that for the users table, Drupal will not look at the databasename database, but at the shared database. – kiamlaluno Sep 12 '13 at 9:33
  • @radj That is correct: Since username and password are the same for both the databases, the user accounts need to have the minimum necessary permissions that would allow Drupal to work with those credentials. – kiamlaluno Mar 30 '16 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.