Last updated March 15, 2016. Created on May 7, 2005.
Edited by tulvit, othermachines, saurabh.dhariwal, rcross. Log in to edit this page.

You can share tables between Drupal installations by setting table prefixes ($db_prefix) on only some tables but not others. One interesting application for this is to share the taxonomy tables (vocabularies, term_data). Another interesting use is to share users across Drupal installations. To share tables in Drupal, sites must be shared in the same database.

Warnings!

This procedure could result in unexpected results, depending on which tables you choose to share, including broken version updates and/or security holes.

Upgrades to the next version of Drupal are not supported! Be sure you have read EVERYTHING on this page, most especially the issues surrounding a major Drupal upgrade, before considering using shared tables!.


Sharing tables may also introduce security issues. For instance, if one site is compromised, an attacker could compromise the shared database tables and compromise your other sites!

You will NOT be able to upgrade Drupal to the next major version unless you first convert your database back to a non-shared database. This conversion will require intermediate to advanced SQL administration skills and is not for beginners! If you cannot update Drupal core, your site has a limited lifespan of just a few years! See the bottom of this page for information on updating, and be sure you understand it and feel confident you will be able to successfully upgrade when the time comes!

Share as few tables as possible

Plan carefully and share only the tables that you need to. This will ensure that when it is time to upgrade Drupal to the next version, converting your database back to non-shared is as little work as possible.

Preparing the sites in a shared database

Before you set up shared tables, you must first have two or more sites in the same database. Drupal prefixes the table names of multiple sites using the $db_prefix variable from each site's settings.php file.

To install a new shared site, or create a multi-site database from two or more existing sites, follow the instructions at: Share a single database across multiple sites.

It is important that you ensure that your sites are working properly in the multi-site database before you try and share tables using a $db_prefix array!

Setting $db_prefix to share tables

The comments in settings.php explain how to use $db_prefix to share tables:

 * To provide prefixes for specific tables, set $db_prefix as an array.
 * The array's keys are the table names and the values are the prefixes.
 * The 'default' element holds the prefix for any tables not specified
 * elsewhere in the array. Example:
 *
 *   $db_prefix = array(
 *     'default'   => 'main_',
 *     'users'     => 'shared_',
 *     'sessions'  => 'shared_',
 *     'role'      => 'shared_',
 *     'authmap'   => 'shared_',
 *   );

To convert from a single $db_prefix to the shared table array, you should first set your existing prefix as the 'default' element, and then add in prefixes the other site's tables for each table you wish to share.

Example: Sharing Users

This example shows how to share users. The users from a master site prefixed with 'master_' are shared to a slave site prefixed with 'slave1_'.

$db_prefix = array(
    "default" => "slave1_", // the prefix for tables that are not shared.
    "users" => "master_",
    "sessions" => "master_",
    "authmap" => "master_",
    "sequences" => "master_",
    "profile_fields" => "master_",
    "profile_values" => "master_",
); 

Things to be aware of

Which tables that you will share depends on your site's specific needs, but remember that you should share as few tables as possible!

The sequences table is usually required if you are going to share other tables. If you are combining two existing sites, which both have their own sequences tables, it is essential you merge the sequences data so that the shared sequences tables have the maximum ID value for both sites. Otherwise you will receive database errors and even worse, user submitted content will probably be lost!

The following tables contain data that is highly site specific and therefore should not be shared:

  • cache and cache_*
  • variable

Note:You can only explicitly specify which tables will be shared and not the other way round. For example the following may fail and is not recommended:

$db_prefix = array(
  // Be careful of this setup.
  'default' => 'primary_',
  'cache' => 'slave1_',
  'node' => 'slave1_',
  'system' => 'slave1_',
  // etc...
);

Upgrading Drupal to a new version with shared tables

Be aware that upgrading with shared tables is not an easy task! You should be comfortable with renaming database tables, as well as cloning and merging databases.

If you try and upgrade without first preparing your database, you will have problems, because this will happen:

  1. On the first site -> Shared tables get updated with the hook_update() of particular modules which are responsible for them
  2. On the second site -> Shared tables get updated again (get broken), because the second site still holds schema info and module version info from before the install and the hook_update() script is executed again.

To update your shared table sites successfully, you can try one of the other two options. There are still other ways to tackle the problem as well, but these two options should give you some ideas on how to go about it.

Option 1 - Convert back to non-shared tables with multiple databases

  1. Create separate temporary databases for each site by cloning the shared database. So if you had three sites in database shareddb, you'd make three new databases. Name them something like shareddb_site1, shareddb_site2, and shareddb_site3. Each of these databases would have a copy of all of the shared tables.
  2. Place your sites in Maintenance Mode or otherwise take them offline for the rest of the procedure. You do not want users logging in and making changes during the procedure, or you might lose the changes!
  3. Modify the settings.php files for each site so that they are running out of their temporary databases, no longer out of the shared one.
  4. Upgrade your Drupal codebase (PHP files) with the new Drupal core files.
  5. Run update.php for each site to upgrade its database.
  6. Merge all of the upgraded tables back into one database again. You will have to choose one site to take each shared table from. As long as uses have not been creating content while you have been following these steps, it will not matter which site you take them from. Otherwise, take the shared tables from the site that has been updated the most recently.
  7. Modify the settings.php files back to how they were, using the new merged/shared database with the upgraded tables. Your sites should now be upgraded!

Option 2 - Upgrade in shared database (Advanced)

  1. Run update.php on one installation and look through the report of changes which prints itself at the end to see which tables were updated.
  2. Determine which modules are responsible for updates to shared tables - this should not be difficult as usually modules do not change tables of other modules.
  3. Manually update schema versions in the {system} table of each of the other sites for these modules. THIS IS TRICKY - if a module does changes to both shared and not shared tables you have to notice this and fall back to Option 1, as otherwise some updates to the not shared tables will be skipped. (Note: you can use drush to update the schema version with this custom command or use this method using sql-query.)
  4. Run update.php for remaining sites – this will not run hook_update() on shared tables again because drupal will think these tables are up to date (from the {systems} table).

Option 2 is more complex but has it benefits. It will not require you to put your site into maintenance mode for longer periods of time. With Option 1 you have to take your sites down for the entire procedure, otherwise users may change data on a site whose non-shared table you are planning on throwing away. So if you have an active community on you site, Option 2 is preferable and will be much faster, but will take more preparation and more advanced skills.

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

Comments

suffering drupal’s picture

I find it profoundly misleading to pretend that Drupal is "Multisite" if the first thing you find when searching how to implement this is a WARNING that says that "this procedure could result in unexpected results, [...] including broken version updates and/or security holes."
And even: "Upgrades to the next version of Drupal are not supported!"

In reality Drupal is simply MONOSITE, but with some adaptions you might even try to get some stuff working on more one sites.

I started with Drupal in 2007 and then my life got stuck...

didlix’s picture

You don't have to share tables to do multi-site :-) Drupal does multi-site perfectly well!

Jaypan’s picture

Yes, this page is about sharing database tables between versions of Drupal, which while related to multisite, is a separate topic.


The Drupal organization has shut down discussion on improvement of the forums: https://www.drupal.org/node/2536122

It's time to start a new forum somewhere else. The Drupal organization does not care about the forums.


Niek_Kloots’s picture

I try to share some tables in a database between two sites (mainsite and sub-site) in D6 on a shared hoster.
This because there are views with nodes of the type 'date' that I want to share along with the users.

In settings.php I got the following code on line 85 and further:

$db_url = 'mysql://user:pw@localhost/db';
$db_prefix = 'slave_';
$db_prefix = array(
    'default'   => 'slave_',
    'authmap'   => 'main_',         //needed for shared users
    'content_node_field'   => 'main_',
    'content_node_field_instance'   => 'main_',
    'content_type_date'   => 'main_',
    'content_type_iframe'   => 'main_',
    'node'   => 'main_',           //without this no nodes visible 
    'node_revisions'   => 'main_',
    'node_type '   => 'main_',
    'profile_fields'   => 'main_',
    'profile_values'   => 'main_',
    'sessions'  => 'main_',
    'term_data'   => 'main_', 
    'term_hierarchy'   => 'main_',
    'term_node'   => 'main_',
    'term_relation'   => 'main_',
    'users'     => 'main_',               //needed for shared users
    'users_role'      => 'main_',            //needed for shared users
    'views_display'   => 'main_',
    'views_view'   => 'main_',
  );

But now all the node types are displayed on the sub-site and I need only those nodes of the type 'date'.

How can I restrict the sharing to the nodes of the type 'date'?

Jaypan’s picture

You can't. Nodes of all types are saved in the same database table(s). You can share entire tables between Drupal installations, but that is as specific as you can get - you cannot share rows between installations.


The Drupal organization has shut down discussion on improvement of the forums: https://www.drupal.org/node/2536122

It's time to start a new forum somewhere else. The Drupal organization does not care about the forums.


jgardezi’s picture

Hi,

Sharing table works perfectly but not before the installation process. If I configure in settings.php file after installation

$databases = array (
  'default' =>
  array (
    'default' =>
    array (
      'database' => 'database2',
      'username' => 'root',
      'password' => 'root',
      'host' => '127.0.0.1',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => array (
        'default'      => '',
        'users'        => 'database1.',
        'sessions'     => 'database1.',
        'role'         => 'database1.',
        'authmap'      => 'database1.',
        'block_role'      => 'database1.',
        'role_permission' => 'database1.',
        'users_roles'     => 'database1.',
      ),
    ),
  ),
);

Users and Roles are being shared perfectly amount site1 and site2. When I tried to add the above configuration before installation process, Drupal 7 throws the follow error message.

"Table sessions already exists."

Is their a way to configure Drupal (Profile) before installation to know about the shared tables or modify "Set up database" page to let Drupal know about the existence of shared tables. I am trying to build a multi-tenant application with separate database and files directory for clients.

Please let me know any other approach to this.

Kind regards,
Javed Gardezi

othermachines’s picture

Hi, Javed. Did you find a way around this? It would be nice not to have to manually administer the databases after installation.

Sheeep’s picture

Some say that this is probably a bug.

I've used this workaround:

1. Backup the shared tables;
2. Delete the shared tables;
3. Install the new site;
4. Delete the newly created shared tables;
5. Import the previous backup tables back into the database.

Note that the step 1,4,5 won't be necessary if the shared tables have no data.

Alex777’s picture

Hi, I have been trying to make 2 drupal installations work together (Commons - main domain - and Recruiter - on subdomain) - i want my users, having logged in on one site, to be able to go to the other one withoutlogging in.

What I have understood from reading and testing is that sharing tables for 2 sites cannot ensure that the user can be authenticated on BOTH sites after he signed in on one of them.

Is it correct?

For my purpose I will need SSO modules, for example Bakery. It will let users to sign in only once for both sites.

Thanks to confirm my understanding is correct.

Jaypan’s picture

That's correct.


The Drupal organization has shut down discussion on improvement of the forums: https://www.drupal.org/node/2536122

It's time to start a new forum somewhere else. The Drupal organization does not care about the forums.


Alex777’s picture

Thanks, Jaypan for yr instant response!

jgardezi’s picture

Hi Alex777,

Is their SSO modules you recommend for drupal 7? The one I have seen is
https://www.drupal.org/project/cas

The other way to approach this issue to convert one Drupal installation into oauth provider and other into consumer. Can anyone list good oauth provider and consumer modules?

Jaypan’s picture

jgardezi: Drupal.org uses Bakery.


The Drupal organization has shut down discussion on improvement of the forums: https://www.drupal.org/node/2536122

It's time to start a new forum somewhere else. The Drupal organization does not care about the forums.


Alex777’s picture

Hi, jgardezi

indeed I use Bakery on my test server. It work quite ok, though there are some moments that could confuse your users travelling from one site to the other.
What I am trying to solve now is saving roles during registration. Bakery does not save it so I am trying to allow users to select the role after registration via hook_form_alter. Giving birth to the code )

jgardezi’s picture

Hi,

I will try it and let you know how it goes.

othermachines’s picture

Is it an impossible notion that as a matter of practice update_N should (in instances where a repeat run might blow things up) first check if the update has already been implemented before executing anything (and if so, do nothing)? I understand that there are never any guarantees, but since so much care should be taken in writing these functions, anyway - in the API docs it actually states "Writing hook_update_N() functions is tricky [..] you'll need to use care in writing your update function" - so why not add one more (very sensible, IMO) item to the list of recommended practices? At least then I could review the functions before an update knowing that there is at least a better than nothing chance that it's safe to run in this situation.