I think I’m going at last to stop qualifying tech posts with the usual “don’t bother to read this if you’re in my family because you’ll probably find it pointless and really quite a snoozer” disclaimer. Basically, if it’s marked tech with no other categories or tags, it’s probably going to be of limited interest to most of my family.

For both work and another site I’m playing with right now, I’m using Drupal and have a need for multi-site functionality with separate content but shared users. I tried setting this up a year or so ago at spreadfirefox.com and it was anything but ideal. Things have gotten better since then, but they’re still not perfect. Here’s how I got this to work after consulting various Drupal forum posts.

1. Copy the default config. In the sites directory, copy the “default” directory and paste it in as the domain name you wish to multi-home on this Drupal install. If default/settings.php is configured to host domain.com and you wish also to host abc.domain.com, name the new directory “abc.domain.com”.

2. Tweaking per-site settings. Edit settings.php. First, change the $base_url value to match the new domain (in this example, it would read “http://abc.domain.com”). Then edit the $db_prefix variable. If the variable is a string, Drupal’s engine prefixes the string to all database tables before running queries. If it’s an array, you can define per-table prefixes. This is really the heart of the multi-homing capability, as you’ll want to share some tables but not others. Here’s the setup that has worked for me:

$db_prefix = array(
‘default’ => ‘abc_’,
‘authmap’ => ”,
‘profile_fields’ => ”,
‘profile_values’ => ”,
‘role’ => ”,
‘sequences’ => ”,
‘sessions’ => ”,
‘users’ => ”,
‘users_roles’ => ”
);

In my case, I had already created all of my tables with no prefixes. Thus for the shared tables, I set the prefix to a blank string so that those tables get used. For all tables not listed explicitly in the array, the engine falls back to the default value, which I’ve set to “abc_”. So all tables listed here (non-content tables) are shared, and all others have to be created with the appropriate prefix (“abc_”).

3. Editing table names. I found the following script somewhere in a Drupal forum thread:

#!/bin/sh
usage(){
cat – <<EOF
cat database.sql | `basename $0` prefix1 | mysql
EOF
}
[ “$1” == “-h” ] && usage && exit
[ -z $1 ] && cat && exit
PRE=$1
sed “s/\(TABLE\|INTO\|FROM\|REPLACE\) /\1 ${PRE}_/g”

Copy it into your database directory in your Drupal install. Make sure it’s executable (“chmod u+x convert” where “convert” is whatever you named the script). Then execute “cat database.mysql | ./convert prefix > prefix_database.mysql” where “prefix” in both instances is the alphanumeric database prefix you provided in the “default” slot for your $db_prefix above. In my case, it would be “abc”. This will create a file that you can slurp into mysql using your favored method.

But before you do so, you’ll probably want to hand-edit it a bit. It’s not essential, but for the sake of reducing your number of tables, it’d be ideal. Basically, for the tables you’re sharing as listed in $db_prefix, delete the create table statements and any other statements associated with those tables. If you’re creating many sites, I recommend doing this once, then using vim to edit subsequent copies and doing a regular expression to substitute values.

4. Manual database tweaks. Here I’ve had to do some manual tweaking to make things work properly. I’m assuming that your initial site already has some configuration values set up that you’d like to clone to your new sites. This was the case with my setup, at least. Some of these steps may not be necessary if this isn’t the case.

• DELETE FROM abc_variable
  Get rid of potential duplicates that may arise for the next step.
• INSERT INTO abc_variable SELECT * FROM variable
  Clone your initial site’s variable settings into your new site.
• DELETE FROM abc_permission
  Get rid of default permissions
• INSERT INTO abc_permission SELECT * FROM permission
  Clone your initial site’s permissions into your new site. If you’ve already set up permissions in your initial site, this brings them into the new site so that you don’t have to redefine them. They’ll diverge from this point onward. You could also just share this table by adding it to $db_prefix above.
• DELETE FROM abc_system
  Get rid of default system settings
• INSERT INTO abc_system SELECT * FROM system
  Clone initial site’s system settings. This and the previous step may not be necessary, but they seemed to help out with a theming issue I’ll get to momentarily.
• DELETE FROM abc_cache
  Weird things happen if you don’t do this after some of the other steps.

Even after doing these things, you may run into some weird issues with the theme. I think the sequence I’ve provided above resolves these, but if not, just get logged in as admin, make sure your theme engine is set correctly in the new site, and make sure a theme that uses that engine is selected. In my case, weird theme issues had to do with the fact that the phptemplate theme engine wasn’t enabled but a theme that used it was. I believe tweaking the system table as described above helped me resolve this.

5. Miscellaneous. There are other things you might consider sharing. Taxonomies, for example, might be something you’d want to share across sites, though in my case, I want site admins to be able to define their own distinct taxonomies.

I’m sure doing this sort of modification kills the upgrade path and makes it harder and harder to maintain upgrades as you get more and more multi-homed sites. Fair warning.

Be careful with permissions. Test any changes, as you may or may not be giving permissions for some users to do things across sites (if you share the permissions and users_roles tables, for example).

I hear you can specify database names in addition to table prefixes in $db_prefix if you’d like to select across databases. The syntax here would be “database.abc_” rather than just “abc_”.

That’s it. After following those steps per site under my setup using Drupal 4.6.5, I’ve got four sites (a main and three subdomains) all using the same user database but with different content. If I’m logged into one, I’m logged into them all, so I can participate in different segments of the community without having to manage multiple logins. It’s pretty nifty.