Multi-Site Configuration Using Apache Virtual Hosts, One Database, and Table Prefixes (Success)

Ok... The documents suck. I think we all agree. Well, now. They don't actually suck, but there's a missing overview/general introduction. I'm going to try, and explain, as a Drupal newb (Linux since Slackware .89, or some such), but LAMP expert.

First, you need to know that Drupal does way more to help you than you think in making multiple sites work off a single installation. That's part of the problem with the docs. It's assumed you know this stuff. The key bit is that Drupal looks for sub-site subdirectories based on the inbound host/domain name, and uses the appropriate one, if found. You don't have to do anything other than set these things up so they work.

Second, you only need to set up the Apache virtual hosts to point at the Drupal installation we're talking about. Read the Apache docs on the VirtualHost, and ServerAlias directives. You may have to read up on DNS configurations to get your domain names to point at the proper IP. 99.9999999% of the time, when you register the domain name, you can take care of these both. In any case, it's probably going to be your service provider in a commercial environment, and your systems administrator in other environments. If you're administering your own box, or test box, like me, you may have some reading to do.

Let's assume you have Drupal working properly on an Apache site with a domain name of example.com. You'd better get this far before trying to get the multi-site thing going.

Third, you need to set up Drupal to handle these virtual web sites:
======================================
Step One: In the Drupal 'sites' directory create a subdirectory with a name identical to each of the host/domain names your Apache server will resolve to, other than the main site, example.com. If you set it up to answer example.com, bingo.example.com, and bango.example.com, you need subdirectories named bingo.example.com, and bango.example.com. At this point, if you try to access bingo.example.com, you'll get the original Drupal installation.

Step Two: In the sites directory you found a subdirectory named 'default.' Believe it, or not, this is the configuration for the default site, example.com. In that directory there's a file, settings.php, which you already had to adjust for your database. Copy that file to each of your new subdirectories. This gives you a setup where the sub-sites all work off the same code, same database, and same tables. Since I wanted a separate configuration for each sub-site, I need to use either a separate database for each one, or table prefixes. Since the provider I intend to use charges by the database, I'm going to use table prefixes. In the file, settings.php, there's a line containing $db_prefix = '';. All you have to do is insert something inside the single quotes. I used the domain, or host names, just to be pedantic. So I'd have two settings.php files, one with $db_prefix = 'bingo_';, and the other with $db_prefix = 'bango_';. If you try it now you get a wonderful page of MySQL/PHP errors, since the tables with the prefixes don't exist, so next...

NOTE: I put an underscore after the prefix. It's purely arbitrary, but it makes the table names easier to read. You get things like bingo_system, and bingo_user, instead of bingosystem, and bingouser.

Step Three: You have to actually create the tables in the database. I found a node with a snippet of sed script. Don't use it. It doesn't work on the current mysql files, and neither does the script referenced in the docs. Ok... I use MySQL, so I'm using the file, database.4.1.mysql. I recommend making a copy for each of your installations. I would end up with bingo.mysql, and bango.mysql. Now you have to edit these files, and change all table references, so the table names have the appropriate prefixes. These will be 'CREATE TABLE', and 'INSERT INTO' SQL statements. I just did this on my (gasp) Windows box using an appropriate text editor, so...

In the file bingo.mysql I searched for all instances of 'CREATE TABLE ' (note the space at the end), and replaced each one with 'CREATE TABLE bingo_' (note the underscore). The 'bingo_' string is the exact string I set as the prefix in '..sites/bingo.example.com/settings.php' And you do likewise for each of the other sub-sites you want to set up.

Summary to do one sub-site using table prefixes:

1. Get the DNS, and Apache to resolve, and answer, the host/domain you want from the original Drupal install
2. Create a sub-directory of Drupal's sites directory with a name equal to the host/domain name as in 1
3. Copy the ..sites/default/settings.php to the sub-directory created in 2
4. Change the settings.php file from 3, so the prefix is unique
5. Duplicate the tables from the original Drupal database, but with the unique prefix prepended to their names