Multisite Database Configuration Example

alexanderpas's picture

Okay, since i'm busy creating a fairly heavy multisite (W.I.P.) let me share some research i've done about shared database tables etc.

We're starting with a multisite that has different content on each site, but are using the same userbase for each site, we're operating from a single database
We're starting from the drupal core essentials perspective, meaning we start with no modules enabled besides drupal required core.

Database Tables:

seeing that drupal has 40 database tables, it can take some time to understand the implications of each table

  • access - you probaly want this table shared, since this is your access rules table, altrough seperation is possible if you want your content-admins to manage this. (but if you ban a person, you probaly want to ban him all over your multi-site.)
  • authmap - more info needed
  • blocks - this is a table you want to seperate, since every multisite (and theme) can have a different block setting and this table is NOT multisite aware
  • blocks_roles - more info needed
  • boxes - more info needed
  • cache & cache_* - Keep this set of tables sepeate, since different sites can have different contents cached and those tables are NOT multisite aware, also seperation leads to a smaller cache (per-site) meaning faster response.
  • Comments - keep this table seperated, since comment are content specific and each site can have different contents and this table is NOT multisite aware
  • boxes - more info needed
  • files & file_revisions - you want these seperated because attached files are content specific and each site can have different contents and those tables are NOT multisite aware
  • filters - You probaly want to share this table, since this hold only the combinations of filter that make up the formats
  • filter_formats - You probaly want to share this table, since the only thing stored here is which filter formats are availble for the sites and which roles can use them.
  • flood - more info needed
  • history - more info needed
  • menu - simple, seperate, since different sites have different layout, and this table is NOT multisite aware
  • node - simple, seperate, since different sites have different content and this table is NOT multisite aware
  • node_* - more info needed
  • permission & role - you probaly want this shared, since this way similar roles behave similar on all sites
  • sequences - this table MUST BE SHARED since this table tracks all tables including shared tables and this table IS multisite aware
  • sessions - as far as i can see, you can safely share this table. more info needed (there are modules that require this table shared.)
  • system - This table MUST BE SEPERATED since it consist of site specific settings (enabled modules etc.)
  • users - this table is shared... doh, that's the whole point here...
  • term_* - more info needed
  • Url_alias - you want this table seperated, since different sites can have similar alias to different content, and this table is NOT multisite aware, also speeds up site if seperated.
  • Users_roles - This table is seperated because different users can have different functions on different sites and this table is NOT multisite aware
  • Variable - This table MUST BE SEPERATED since it consist of site specific settings (sitename, slogan etc.)
  • Vocabulary & Vocabulary_node_type- more info needed
  • Watchdog - depends on your likings, suggesting to seperate but can be shared since this table IS multisite aware

 

a quick recap, the following tables are shared:

  • access
  • filters
  • filter_formats
  • permission
  • role
  • sequences
  • sessions
  • users

 

Near-Required Modules

I would suggest to install always the following modules on any multisite config:

  • Single Sign-On - Logs you in in all sites at the same time
  • Paranoia - disallowes PHP usage in content etc.

Comments

Cache_ separation

delf's picture

Duplicate of http://drupal.org/node/126867, please ignore this comment.

I am trying to separate cache_* tables on a multisite, but I am receiving the following errors:

Warning: Table 'multi.shared_cache' doesn't exist query: SELECT data, created, headers, expire FROM shared_cache WHERE cid = 'variables' in D:\xampplite\htdocs\multi\includes\database.mysqli.inc on line 151

Warning: Table 'shared_cache' was not locked with LOCK TABLES query: UPDATE shared_cache SET data = 'a:9:{s:13:\"theme_default\";s:7:\"garland\";s:13:\"filter_html_1\";i:1;s:18:\"node_options_forum\";a:1:{i:0;s:6:\"status\";}s:17:\"menu_primary_menu\";i:2;s:19:\"menu_secondary_menu\";i:2;s:15:\"install_profile\";s:7:\"default\";s:17:\"node_options_page\";a:1:{i:0;s:6:\"status\";}s:12:\"comment_page\";i:0;s:14:\"theme_settings\";a:1:{s:21:\"toggle_node_info_page\";b:0;}}', created = 1181084399, expire = 0, headers = '' WHERE cid = 'variables' in D:\xampplite\htdocs\multi\includes\database.mysqli.inc on line 151

Warning: Table 'multi.shared_cache_page' doesn't exist query: SELECT data, created, headers, expire FROM shared_cache_page WHERE cid = 'http://www.test.appealnetworks.com/?q=user/register' in D:\xampplite\htdocs\multi\includes\database.mysqli.inc on line 151

Warning: Cannot modify header information - headers already sent by (output started at D:\xampplite\htdocs\multi\includes\database.mysqli.inc:151) in D:\xampplite\htdocs\multi\includes\bootstrap.inc on line 488

Warning: Cannot modify header information - headers already sent by (output started at D:\xampplite\htdocs\multi\includes\database.mysqli.inc:151) in D:\xampplite\htdocs\multi\includes\bootstrap.inc on line 489

Warning: Cannot modify header information - headers already sent by (output started at D:\xampplite\htdocs\multi\includes\database.mysqli.inc:151) in D:\xampplite\htdocs\multi\includes\bootstrap.inc on line 490

Warning: Cannot modify header information - headers already sent by (output started at D:\xampplite\htdocs\multi\includes\database.mysqli.inc:151) in D:\xampplite\htdocs\multi\includes\bootstrap.inc on line 491

Warning: Table 'testsite_watchdog' was not locked with LOCK TABLES query: INSERT INTO testsite_watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', '<em>Table &amp;#039;shared_cache_menu&amp;#039; was not locked with LOCK TABLES\nquery: UPDATE shared_cache_menu SET data = &amp;#039;a:4:{s:10:\&amp;quot;path index\&amp;quot;;a:118:{s:17:\&amp;quot;admin/build/block\&amp;quot;;i:-1;s:22:\&amp;quot;admin/build/block/list\&amp;quot;;i:-2;s:27:\&amp;quot;admin/build/block/configure\&amp;quot;;i:-3;s:24:\&amp;quot;admin/build/block/delete\&amp;quot;;i:-4;s:21:\&amp;quot;admin/build/block/add\&amp;quot;;i:-5;s:30:\&amp;quot;admin/build/block/list/garland\&amp;quot;;i:-6;s:21:\&amp;quot;admin/content/comment\&amp;quot;;i:-7;s:26:\&amp;quot;admin/content/comment/list\&amp;quot;;i:-8;s:30:\&amp;quot;admin/content/comment/list/new\&amp;qu in D:\xampplite\htdocs\multi\includes\database.mysqli.inc on line 151

Although while using all prefixed tables for all sites, everything works fine.
This is my current multisite DB config:

$site_prefix = 'testsite_';
$db_prefix = array(
  'default'   => 'shared_',
  'blocks'    => $site_prefix,
  'cache'    => $site_prefix,
  'cache_filer'    => $site_prefix,
  'cache_menu'    => $site_prefix,
  'cache_page'    => $site_prefix,
  'comments'     => $site_prefix,  
  'node'      => $site_prefix,
  'node_revisions' => $site_prefix,  
  'system'    => $site_prefix,
  'users'    => $site_prefix,
  'sessions'    => $site_prefix,  
  'url_alias'     => $site_prefix,  
  'variable'  => $site_prefix,
  'watchdog'     => $site_prefix,
);

I will appreciate any explanations for this issue.

Delf.

One thing...

pcorbett's picture

looks like you spelled "filter" wrong in "cache_filer"

On the error, it seems to me

alexanderpas's picture

On the error, it seems to me there is a problem with table locking... try optimizing etc. for your tables

Secondly, you might want to invert your database prefix settings, this also might prevent the error from happening

you want your default pointing to your siteprefix_ and only list your shared tables... this way you'll prevent problems when installing additional modules

<?php
$db_prefix
= array(
 
'default'        => 'testsite_',
 
'access'         => 'shared_',
 
'filters'        => 'shared_',
 
'filter_formats' => 'shared_',
 
'permission'     => 'shared_',
 
'role'           => 'shared_',
 
'sequences'      => 'shared_',
 
'sessions'       => 'shared_',
 
'users'          => 'shared_',
);
?>

combining separate sites with separate databases

weemiss's picture

Is it even possible to reverse engineer multi-site management. I have two sites with separate databases and different modules installed. Is there any hope for combining these sites?
Erin

Erin

Different use cases

Boris Mann's picture

The multisite described above deals with shared database tables.

The "simple" version of multisite is when you want to reduce maintenance and code overlap, and run multiple Drupal sites off a single codebase. So...yes, you can combine your sites.

  1. One install of core Drupal
  2. Put each site into sites/example.com folder (where example.com is the domain of each one) -- your settings.php will contain details on connecting to two separate databases
  3. Put shared modules in sites/all/modules
  4. Put site specific modules in sites/example.com/modules
  5. Done! Enjoy a cool refreshing drink and bask in the glory of having less bits of Drupal code floating around, so it's easier to update and maintain.

Multi site within localhost environment

jbc's picture

{{{ I've belatedly posted this as a question directly to the group, at http://groups.drupal.org/node/5336; perhaps you might contribute there...thnx }}

Hello Boris

I've been trying to get a multi-site Drupal 5.1, within a localhost (LAMP) environment and I've done all the things that you mention in your succinct list above, but have been unable to surf to the {secondary} sites. (receiving "You Do Not Have Permission to Access..." error)

  1. Do you know if this is a limitation of a localhost environment? Will it magically work if I 'go live'?
  2. I surf to the primary site at http://localhost/mainsite/ — I've tried both http://localhost/secondarysite/ and even http://localhost/mainsite/secondarysite/ and even http://localhost/secondarysite.com (even though the .com is redundant in localhost / LAMP environment isn' it?)
  3. Any other reasons why this shouldn't be working? A problem with Apache?

thanks for any assist you can give.

shalom,
John

shalom from wales!
John

System table can't be shared?

pcorbett's picture

Can the system table be shared if you are okay with cascading module actions? I am setting up a multisite setup for a school district and all the sites will share the same modules and be more or less the same save the logo, colors, and content. Same goes for the blocks table .... can't be shared if you're okay with the implications?

Also, I believe the contact & flood tables need to be separate unless you want to see the contact info options from other sites.

Authmap

pcorbett's picture

I believe authmap needs to be separate and not shared in the case you have two sites and the same user logs into both with the same username, but their uid is different on each site. When you log in it attempts to update the row, but if you have a diff uid it throws an error since it can't find a perfect match.

anything possible with local server

najibx's picture

I am using XAMPP, and works great.
You can have :

http://mainsite
http://secondarysite

or http://secondarysite.mainsite
or http://mainsite/secondarysite

just follow instruction manual.
-najib -

-najibx -
<a href="http://www.successideaweb.com>Drupal web developer | designer in Malaysia

Great list, but making

1kenthomas's picture

Great list, but making shared table choices is going to be a bit more complex than the above: you're often going to wish to share nodes and content, for instance. . .

Anyone got an updated list?

~kwt

I'll make an updated list.

alexanderpas's picture

I'm currently busy on a drupal 6 multisite project... and will be sharing my findings with the community soon.

My setup...

not.used.915's picture

I've been working on setting up my first multisite today... Read tons of info about tons of solutions, but ended up just using table prefixes.

My requirements are as follows:
- one codebase
- one database
- lots of sub.domain.com multisites
- one domain.com site
- everything shared (including modules, blocks, users, etc.) except actual content

using this in each settings.php:

<?php
$table_prefix
= "sales_";
require_once(
'./sites/db_prefix.php');
?>

and this is my db_prefix.php:
<?php
    $db_prefix
= array(
       
'default'                   => 'shared_',
       
       
'cache'                     => $table_prefix,
       
'cache_filter'              => $table_prefix,
       
'cache_menu'                => $table_prefix,
       
'cache_page'                => $table_prefix,
       
'comments'                  => $table_prefix,
       
'forum'                     => $table_prefix,
       
'node'                      => $table_prefix,
       
'node_access'               => $table_prefix,
       
'node_comment_statistics'   => $table_prefix,
       
'node_counter'              => $table_prefix,
       
'node_field'                => $table_prefix,
       
'node_field_instance'       => $table_prefix,
       
'node_group'                => $table_prefix,
       
'node_group_fields'         => $table_prefix,
       
'node_revisions'            => $table_prefix,
       
'node_type'                 => $table_prefix,
       
'url_alias'                 => $table_prefix
   
);   
?>

Seems to be working so far and doing what I need :)

> this table is NOT multisite aware

HansBKK's picture

Edit - posted a sample settings.php for my scenario (sharing everything but layout/theming/navigation) here: http://groups.drupal.org/node/11795

I have a hard time imagining what this snippet is supposed to mean - how can a table be "aware" of multi-site?

After a lot of reading posts from people that have done shared-database multi-site configurations, the main concepts I've taken away are:

Some tables really shouldn't ever be shared, but most can be depending on what you want to accomplish. The trick is making sure to keep tables that depend on each other "together", in other words either shared or site-specific.

If you are just doing something simple like sharing users, maybe adding profiles and roles, it seems pretty straightforward and well documented.

Sharing other tables seems to be a very complex challenge that requires detailed knowledge of the database schema, which is unfortunately one of those areas that is poorly documented and therefore thorough testing for your specific scenario on scratch sites is important.

Most people want to keep separate layout and theming (inter-related) and navigation (can be kept separate?)

Sharing the node-related data seems the most challenging, and taxonomy-related information is hardly documented at all.

In any case my bottom line here is a warning for those researching this issue that it's not nearly as simple as the OP makes out, and in fact you can only follow a shared database "recipe" if you have the same shared vs site-specific scenario as the poster.

It would certainly be great if there were a central place for all our fragmented experiences to be collected - maybe a "shared database" group?

Very confused

amnion@drupal.org's picture

I am thinking of doing a multisite setup (actually, I have to) between several domains, each with its own database. No problem, I'll just follow Boris Mann's instructions above. But after that, is there a way to have shared users, etc. across all the sites? Obviously, single sign-on will work with that module, but how do you connect tables across multiple databases?

Also, I'm doing this with my site, but when I try to visit the second Drupal site I just get a 403 error. Is there something I'm missing?

Okay

amnion@drupal.org's picture

I just did it with everything using one database and sharing tables, such as user, etc as you guys did above. Works good.

My setting for Multisite

ardnet's picture

Hi all,

Currently I'm in the middle of implementing multisite for Drupal 6.
Just wanna share what I've been done for this, and if you have guys have some comments or thoughts about it, please feel free to do it.

The details of multisite that I've implemented is like below:
1. I have 3 sites, which is: sg.xxx.com, bk.xxx.com, and hk.xxx.com
2. User need to be share.
3. Content need to be share.
So that means I need to share the content_type_* table and also content_field_* table.
4. Taxonomy need to be share.
5. System tables need to be share, because I believe this is the tables that keep the information about modules that currently being use in the site.
The reason I shared this tables is, if I install one module, then it will affect to those 3 sites as well.
6. The Block, Panels, and Views need to be SEPARATED, because basically need to have a different settings for each site.
ie: in sg.xxx.com, theres a Views that currently being filtered by Taxonomy with term SG, which is in bk.xxx.com is filtered by Taxonomy with term BK.

And here's the settings of my $db_prefix:

<?php
$db_prefix
= array(
   
    
//Users related...
   
'authmap' => 'shared_db.',      
   
'profile_fields' => 'shared_db.',
   
'profile_values' => 'shared_db.',
   
'role' => 'shared_db.',
   
'sessions' => 'shared_db.',
   
'users' => 'shared_db.',
   
   
    
//Taxonomy related...
       
'vocabulary' => 'shared_db.',         
  
'vocabulary_node_types' => 'shared_db.',        
  
'term_data' => 'shared_db.',        
  
'term_hierarchy' => 'shared_db.',       
  
'term_image' => 'shared_db.',       
  
'term_lineage' => 'shared_db.',         
  
'term_node' => 'shared_db.',        
  
'term_relation' => 'shared_db.',        
  
'term_synonym' => 'shared_db.',         
  
'system' => 'shared_db.',       
  
'taxonomy_manager_merge' => 'shared_db.',       
  
  
'calais_geo' => 'shared_db.',       
  
'calais_geo_term calais_term' => 'shared_db.',      
  
'calais_term_data_company' => 'shared_db.',         
  
'calais_term_data_geo' => 'shared_db.',         
  
'calais_term_data_product' => 'shared_db.',         
  
'calais_term_node' => 'shared_db.',         
  
  
    
//Content related...
      
'node' => 'shared_db.',       
  
'nodereview' => 'shared_db.',       
  
'nodereview_axes' => 'shared_db.',      
  
'nodereview_reviews' => 'shared_db.',       
  
'nodewords' => 'shared_db.',        
  
'nodewords_custom' => 'shared_db.',         
  
'node_access' => 'shared_db.',      
  
'node_comment_statistics' => 'shared_db.',      
  
'node_composite' => 'shared_db.',       
  
'node_composite_references' => 'shared_db.',        
  
'node_composite_sets' => 'shared_db.',      
  
'node_counter' => 'shared_db.',         
  
'node_revisions' => 'shared_db.',       
  
'node_type' => 'shared_db.',        
  

//Shared also tables that start with content_type_
'content_type_' => 'shared_db.',


//Shared also tables that start with content_field_
'content_field_' => 'shared_db.',


  
'files' => 'shared_db.',        
  
'location' => 'shared_db.',         
  
'location_instance' => 'shared_db.',        
  
'location_phone' => 'shared_db.',       
  
'location_search_work' => 'shared_db.',         
  
  
'workflow_access' => 'shared_db.',
'workflow_node' => 'shared_db.',
'workflow_node_history' => 'shared_db.',
'workflow_scheduled_transition' => 'shared_db.',
'workflow_states' => 'shared_db.',
'workflow_transitions' => 'shared_db.',
'workflow_type_map' => 'shared_db.',
'workflows' => 'shared_db.'
      
);

?>

So I dunno, by doing this, is this consider OK? or will there be an implication in the future?
I also heard that cache tables, some say it needs to be SEPARATED, and some say needs to be SHARED.

Thanks in advance.

Regards,
Ardi

Update?

xtiansimon's picture

Some of the content in the posted examples contradict the "table is NOT multisite aware" statements". The page seems due for an update, since Drupal 6 is still very much in use.

Multisite

Group organizers

Group notifications

This group offers an RSS feed. Or subscribe to these personalized, sitewide feeds:

Hot content this week