Share a single database across multiple sites
Some web hosts limit their customers to one database. Thus, no duplicate table names are possible. In order to assure that these admins can still use Drupal, and even use multiple installations of Drupal, Drupal offers table prefixing.
In order to use this feature, you must currently edit the script database/database.x in order to create tables prefixed by the string of your choice. If you are adding contributed modules you will need to also modify INSERT and REPLACE statements to have a prefix as well. For example, change all statements from the format of
CREATE TABLE access
INSERT INTO system VALUES ('modules/filter.module','filter','module','',1,0,0);Becomes,
CREATE TABLE dr1_access
INSERT INTO dr1_system VALUES ('modules/filter.module','filter','module','',1,0,0);Then use dr1_ (for example) as value of $db_prefix in your sites/example.com/settings.php file.
You can also use the prefix.sh in the scripts subdirectory of your Drupal install to do this automatically. You can then update several sites in one swoop with a (bash shell) command-line like
for F in '' prefix1 prefix2; do \
for S in `find ./modules --name \*mysql`; do \
scripts/dbprefix.sh $F < $S | grep -v DROP |\
mysql -h DBHOST -u DBUSER -pPASSWD DATABASE; \
done;
doneNOTE:that not all scripts end in mysql, but you get the idea; the '' prefix tells the script to run with no prefix at all, ie a straight cat - command that just lets me process all my sites together.
Here is a PHP script that creates all of the database tables for all sites in /drupal/sites/*. It works for DRUPAL-4-6. See the Database Table Creation script issue for more info.
If you want to share users across multiple sites, you'll need to use a $db_prefix along these lines:
<?php
$db_prefix = array(
'default' => 'thissite_',
'authmap' => 'shared_',
'profile_fields' => 'shared_',
'profile_values' => 'shared_',
'role' => 'shared_',
'sequences' => 'shared_',
'sessions' => 'shared_',
'users' => 'shared_',
'users_roles' => 'shared_',
'users_uid_seq' => 'shared_', // for pgsql
);
?>See the sharing tables and sharing tables across databases sections.
Houston, we have a problem...
We tried the snippet to share users across sites... problem is that the sequences table needs to be shared in order for user ids to be generated... however, since node, menu, and other tables which *also* use the sequences table are *not* shared, this results in errors on the slave site when adding modules, content, etc.
Any ideas?
the fix for #comment-86337
This problem only manifests itself when you are first merging two sites into one database. Can be fixed by correcting the values in the shared sequences table to match the max(id) in the corresponding tables.
There is a shell script in the \scripts folder
In 4.7rc3 (and possibly prior) I found a prefix.sh shell script in the distribution. It worked for me. Also, one of the comments on this node: http://drupal.org/node/11325 provides a MySQL query to drop the default table names.
Minor problem with create.php.v2.txt on my host
Wow! This script [create.php.v2.txt] is great!
For some reason the PHP environment (I'm using 5.1.6) on my host (Bluehost.com) doesn't provide a value for $_SERVER['PHP_SELF']!?? I simply modified the first line of code in the script like so:
original: if (basename($_SERVER['PHP_SELF']) == (basename(__FILE__)) {
modified: if (TRUE) {
I could not run it from the browser due to other issues... so I executed it from a shell. Everything appears hunkydory. So, the first line *may* be a guard for executing the script from within the browser... I cannot verify that.
Thank you for such a wonderful tool!