In this blog entry, I'll explain how I setup a multi-site Drupal 6 installation with shared databases and single sign-on. This will enable you to store your users (and other desired tables) in a separate database, and share them across multiple sites.

Before installing Drupal I created 3 databases, added 2 mysql users, and granted permissions. Both mysql users will have access to the shared database which will contain the shared tables:

For my setup I wanted to use a single Drupal filesystem, so I created a name-based Apache virtualhost to host both domain names (ms1.erl.dev & ms2.erl.dev):

I downloaded and unpacked the Drupal installation file:

I setup 2 new sites folders in the sites folder, and copied the default.settings.php file into my first site:

At this point my databases and filesystem were ready to go so I installed Drupal for the first site (ms1.erl.dev). During installation, I entered "localhost" for the database host, drupal_ms_1 as the database, and drupal_ms_1 as the database user. I left the db_prefix setting blank (we'll change that later).

Now that Drupal was installed and the tables were created, it was time to modify the installation to enable single sign-on and shared databases.

You'll need to decide which tables to share across the sites. I decided on users, sessions, and authmap (but you could add more as desired, like roles, profile_*, etc). I used the following commands to transfer the desired tables from the drupal_ms_1 database into the shared database, remove the tables from drupal_ms_1, and then replicated drupal_ms_1 to drupal_ms_2:

Next, I modified the settings.php for my first site (sites/ms1.erl.dev/settings.php) to enable the shared database and single sign-on configuration. The $db_prefix variable is used to specify a different database using the [DATABASE].[TABLE] syntax. You'll also need to add the $cookie_domain variable to ensure the cookie set by Drupal will work across both domain names.

I then copied the ms1.erl.dev settings.php file into my second sites folder. Edit the new ms2.erl.dev/settings.php $db_url variable to point to the drupal_ms_2 database (and update the user and password as well).

After you delete your domain cookies everything should be working! To test this out, log into your first site and then open a new window/tab and go to your second site. You should be logged in as the same user. If you log out from one site, you should also be logged out from the other site.

Here's a quick code snippet I just wrote to dump and compress (gzip) all mysql databases on a server into separate files using PHP and PEAR.

It's important to realize that making changes to Drupal (configuration changes, uploading a file, etc) affect both the file system and the database. That's why I feel it's important to integrate your MySQL database with your subversion file system. For instance, if you upgrade a Drupal module, you should commit a backup of your database with the file system changes to ensure you have a definitive snap snot of your project, and can revert to a previous revision as necessary.

All of my Drupal projects are subversion integrated for proper version control. A typical subversion tree resembles:

When I checkout projects to my Linux file system, I use the following command to ensure I checkout everything parallel to the httpdocs folder. I use the "archive" folder for items (like database dumps) that I'd like to keep outside of the httpdocs, so they are not public.

Now that my file system is integrated with subversion, I can backup my MySQL database and check it into subversion before I make a critical change to the Drupal configuration, such as upgrading a module:

NOTE: if you've already checked your database into subversion and you're creating another backup, your svn commands (and output from the svn stat command) will be slightly different. A question mark represents a new file, while a capital "M" represents a modified file:

Let's say you upgrade a module (or Drupal) and get an unexpected error, you can now revert your database: