Last updated March 15, 2016. Created on August 26, 2003.
Edited by tulvit, ashish_nirmohi, gisle, drupalshrek. Log in to edit this page.

Drupal 6 and later supports sharing a database with other web applications with the table prefix, also known as $db_prefix from settings.php.

The table prefix is defined in your Drupal site's settings.php file, and when it is present Drupal will place the prefix before each table name in the database. So if the table prefix were 'mysite_' then Drupal would look for tables named 'mysite_access', 'mysite_actions', and so on (instead of the default tables names 'access', 'actions', etc.) This allows more than one Drupal site, or even Drupal and other products, to share the same database, because the table names will not collide with one another. You can have another Drupal instance with table prefix 'mysite2_' sharing the database with 'mysite_' and indeed many other instances as long as each one has a unique table prefix.

If your hosting company only provides you with one database, then sharing it between multiple Drupal sites with table prefixes can be a cost effective alternative to paying for extra databases!

Here are some more examples of how you might use table prefixes:

  1. You set up a main company website, www.example.com, which was a default Drupal install with no table prefix. The following year, you add a second website for intranet.example.com, with table prefix 'intranet_'. Both sites share your example.com database that comes with your hosting company's package.
  2. You could have a single database that contained 10 Drupal sites, with table prefixes 'site1_', 'site2_', 'site3_', and so on, up to 'site10_'.
  3. You could add a Drupal installation to an existing Wordpress database, by using a table prefix like 'drupal_'. Then your existing Wordpress user table named 'users' would not collide with the Drupal users table named 'drupal_users'.
  4. Once you have set up a Table Prefix, it is also possible to share tables between Drupal installations. This can allow you to share taxonomy terms, users, etc. Note: Sharing tables is for advanced users only and may cause problems with your site, including being unable to upgrade it!

How do I install a new Drupal site with a Table Prefix?

Usually, you will have the Drupal installer set your prefix when you are first installing your site.

  1. Proceed through the installation steps until you come to the Set Up Database screen.
  2. Fill in the Database name, Database username, and Database password of your existing database you wish to install into.
  3. Expand the Advanced Options section and add a prefix underneath Table Prefix. You should end your table prefix with an underscore _ so that the table names are more readable in the database.

    If you are not sure what to use, try the prefix: drupal_. You can only use a prefix once, so for the third site you install you must call it something else, like drupal2_.

  4. Continue the setup as normal.

Where is the Table Prefix stored and how do I change it?

The Table Prefix for each site is stored in the variable $db_prefix, found in your site's settings.php file. In your Drupal codebase this file is often at 'sites/default/settings.php'. Or, it might be at 'sites/example.com/settings.php' if you have a multisite setup with more than one site running.

Once Drupal has been installed, you cannot change the the table prefix! If you must change the prefix or add a new one, you can do so by editing settings.php and manually change the $db_prefix line. Please note that if you change the prefix you must also update the names of all your database tables to start with that prefix!. Otherwise your site will not function, because it will not be able to locate its database tables. You will most likely see the Drupal install page instead of your site if you try and load it without updating the table names.

How do I combine two or more existing sites into one database using Table Prefixes?

To combine two or more existing sites into a single database, you will need to accomplish these steps:

  1. Set up a test database and work from a backup!
  2. Rename all the tables in one (or even both) of the databases to start with a prefix. For example, if your prefix was test_, your access table would be renamed to test_access, your users table would be called test_users, and so on, for all of your Drupal tables.
  3. Now combine both sets of tables from the two sites into one database
  4. Set the $db_prefix variable in both sites' settings.php files to match the correct prefix for each site. Our example site with tables named like test1_user would then have the following in its settings.php
    $db_prefix = 'test1_';
    
  5. Try and load both sites using their prefixes, and verify they are working correctly. You now have a multi-site database!

Upgrading to a new Drupal version

Major Drupal upgrades with a shared database will work without any special steps. Just remember that if you have 3 websites in a database, then having a proper backup and testing everything on a development copy is at least 3 times as important! Never attempt a major upgrade without having a backup of both your database and your PHP codebase, so that you can get back to a working version of your sites if anything goes wrong.

Sharing tables between databases

Once you have multiple sites set up in the same database, it is possible (though not recommended) to share tables between the Drupal sites. By doing so, you may share things like users, taxonomy terms, and content, to name a few.

However, sharing tables comes with some security and upgrade implications, and is for advanced users only. For instructions on table sharing, please see Share tables across instances (not recommended).

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

Comments

webchick’s picture

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?

mfb’s picture

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.

len_chan’s picture

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.

machershell’s picture

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!

daltuverho’s picture

Удивительно, я думал все ровно наоборот. Глаза открыли, большое спасибо!!!

sumeshkj’s picture

We are developing a site ,we have more than one installations but are using same database.is it a good practice or it is good to have different database for different installations.kindly advice.mutiple installation is done separate sections but it is a part of the site