Share a single database across multiple sites
Posted by drupallovers on February 28, 2012
Drupal 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:
- 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.
- You could have a single database that contained 10 Drupal sites, with table prefixes ‘site1_’, ‘site2_’, ‘site3_’, and so on, up to ‘site10_’.
- 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’.
- 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.
- Proceed through the installation steps until you come to the Set Up Database screen.
- Fill in the Database name, Database username, and Database password of your existing database you wish to install into.
- 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_.
- 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’ssettings.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:
- Set up a test database and work from a backup!
- 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.
- Now combine both sets of tables from the two sites into one database
- 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_';
- 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).