Wednesday, December 26, 2007

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.


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

9 comments:

Anonymous said...

Hi!

Your idea has work ;)
Thanks !!!

Agnello Dsouza said...

hi
This is a grea tutorial !! frm where did u come up with this ... the reason being i am a linux admin working for a webhosting company ( qualispace.com ) ... we were looking at storing the virtul host containers on a mysql database as we have huge amounts of domains and clients !! .... curently we are using the cpannel set up ... now we are wokin on our own control pannnel ..... i would love to hear from you if you have come up with any improved techniques.

Regards
Agnello
agnello.dsouza@gmail.com

Niclas said...

Hey,
Great tutorial.. I was trying this setup on a server where I'm using Ubuntu but I'm getting this respons when trying to start apache2 "Invalid command 'mysql', perhaps misspelled or defined by a module not included in the server configuration".
Are there any moduls I might be missing?

Joe said...

Hi Niclas.

You probably need to install "mysql-client".
Either through the Synaptic Package Manager, or with the following command via gnome-terminal/etc.

"sudo apt-get install mysql-client"

http://packages.ubuntu.com/hardy/mysql-client

Anonymous said...

I'm getting the same error like niclas ("Invalid command 'mysql', perhaps misspelled or defined by a module not included in the server configuration")... I installed mysql-client, but the same error... anyone can help me?

Vaggelis said...

Your Idea was enlightening man!

Thank you so much!


Vag.

Anonymous said...

mysql --user=xxx--pass=xxx--batch --raw --database=xxx--execute="SELECT container FROM vhosts WHERE active = 1" --skip-column-names --protocol=socket --socket=/var/mysql/mysql.sock >/etc/vhosts.conf

/etc/init.d/apache reloadapache

Works fine, is perfect!
Thank you so much!

Adrian Badertscher said...

Thank you very much for this hint!

I've a question: Why do you use the script for rewrite the vhost.conf only on reload of apache?
I've created a php script to create vhost.conf - That's no problem, because the user for select the data from mysql, is only a 'reading' user on Db, not root!

update_vhosts.php:

$datei = fopen("/etc/apache2/sites-enabled/vhosts.conf","w+");
$verbindung = mysql_connect("localhost","user","passwd");
if (!$verbindung){
die('Kann keine Verbindung aufbauen: ' . mysql_error());}

mysql_select_db("virtuelle_domains", $verbindung);
$resultat = mysql_query("SELECT container FROM vd_virtuelle_domains WHERE aktiv = 1");
while($zeile = mysql_fetch_array($resultat)){
fwrite($datei, $zeile['container']);
fwrite($datei, "\n");}
mysql_close($verbindung);
fclose($datei);


greets - Adrian Badertscher

universel4 said...

thank u for this tuto, but i'm a begginner so i want to ask u more:
my question is how can i use a script inside the vhost.conf file to extract data if a have aleardy created this vhost.conf file before?
i.e i need something like this (if i use php script)


$serverAdmin
$docRoot


thank for answear.