Database Storage of Apache Virtual Hosts

I came up with a workaround for storing Apache Virtual Hosts in a database. I figure it could be adapted to work on Windows using batch files, but I'm only going to go over doing it on a UNIX-like system here. Specificly Ubuntu, or Debian.

At least basic knowledge of the following is assumed.

• Apache configuration files (httpd.conf, apache#.conf)
• Databases (MySQL is used here)
• Shell scripting

The first thing that needs to be done is creating a database schema for storing virtual host containers. I'll stick with a simple schema for now so focus stays on Apache to Database connection.

This schema is for MySQL.

CREATE TABLE `vhosts` (
 `active` BOOL NOT NULL DEFAULT '0',
 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 `container` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
 INDEX ( `active` ) 
);

`container` will store the actual vhost container. For instance somthing like this.

NameVirtualHost *
<VirtualHost *>
ServerAdmin webmaster@domain.com
DocumentRoot /home/me/public_html/
</VirtualHost>

`id` is for relation to other tables. Pretty standard stuff.

`active` is an on/off switch for each virtual host. Apache will only include a virtual host from the database if it is active.


With a database table ready to be read from we can move to the part where Apache uses the data from that table.
In httpd.conf, apache#.conf, or whatever your system uses for Apaches' main configuration file there's a directive available called Include. If you edit your configuration file and look at the end of the file you may see an example of Include being used already.
If you're on a Debian system you're likely going to find somthing similar to the following.

# Include the virtual host configurations:
Include /etc/apache2/sites-enabled/

If you're not on a Debian system, you're going to need to create a directory in your Apache directory similar to that. It should be owned by root. You will also need to add an Include directive like the one shown above to your main conf file pointing to the directory you create so Apache knows to load any configuration files found in that directory.

If you are on a Debian system you're eventually going to be emptying that directory so there's no conflicts with the database method. So now would probably be a good time to import the sites from sites-available into the database & mark the `active` field according to which sites are also in `sites-enabled`, as well as backing up that directory.


Now that we have a database to store and retrieve virtual host containers from, & a place where Apache can load configuration files from it's time to write a command to build a configuration file full of virtual host containers from the database for Apache to load.

Here's an example of such a command. Note that you'll have to sort out permissions on your own, the --user & --pass are used here for example sake, though MySQL documentation warns against doing it that way because it leaves the password visible in the pid file for a short time.

Some options are on a new line for ease of reading. The command ends with output redirection to a file in the sites-enabled directory for Apache to load.
I'm using a UNIX domain socket here, but you could use a TCP connection if need be. You might also want to alter the file output redirection is beind aimed at.

mysql --user=root --pass=pass --batch --raw --silent
--database=Websites
--execute="SELECT container FROM vhosts WHERE active = 1"
--skip-column-names
--protocol=socket
--socket=/var/run/mysqld/mysqld.sock
>/etc/apache2/sites-enabled/vhosts.conf

Of course instead of using that directory as an include directory in Apaches' configuration file, you could comment out that particular include & point an include directly at the file you have the above command outputting to.

Now we have a method to connect Apache with a Database for obtaining virtual hosts, but there's still manual labor involved in needing to run that mysql command when Apache is [re]started or reloaded.

To help with this we will have to edit the shell script used to stop/start/restart Apache so that every time Apache is [re]started, or reloaded, this file is refreshed.
On Debian this script is /etc/init.d/apache2, since I'm using Apache2.

Basicly anywhere there is a log_daemon_msg that [re]starts Apache in this script, we add our own logging & our database command right before it. So we end up with some sections looking somthing like this.

log_daemon_msg "Extracting vhosts from database" "apache2"
mysql --user=root --pass=pass --batch --raw --silent --database=Websites --execute="SELECT container FROM vhosts WHERE active = 1" --skip-column-names --protocol=socket --socket=/var/run/mysqld/mysqld.sock >/etc/apache2/vhosts.conf
log_daemon_msg "Starting web server" "apache2"

Now whenever Apache is [re]started, or reloaded, it will be using the vhost containers for active sites stored in the database.
sudo /etc/init.d/apache2 restart