Creating Advanced MySQL-Based Virtual Hosts On Lighttpd (Debian Etch)

Version 1.0
Author: Falko Timme <ft [at] falkotimme [dot] com>
Last edited 07/31/2008

This guide explains how you can create advanced virtual hosts on a lighttpd web server on Debian Etch that are stored in a MySQL database. The method described here does not use the lighttpd mod_mysql_vhost module, and unlike mod_mysql_vhost (which allows you to store only the hostname and document root of a vhost in a database), this method allows to store individual configuration directives for each vhost in the MySQL database.

This tutorial is based on the chapter "Per-vhost configuration" from http://trac.lighttpd.net/trac/wiki/Docs%3AModMySQLVhost.

I do not issue any guarantee that this will work for you!

1 Installing MySQL 5.0

First we install MySQL 5.0 like this:

apt-get install mysql-server mysql-client

Create a password for the MySQL user root (replace yourrootsqlpassword with the password you want to use):

mysqladmin -u root password yourrootsqlpassword

Then check with

netstat -tap | grep mysql

on which addresses MySQL is listening. If the output looks like this:

tcp        0      0 localhost.localdo:mysql *:*                     LISTEN     2713/mysqld

which means MySQL is listening on localhost.localdomain only, then you're safe with the password you set before. But if the output looks like this:

tcp        0      0 *:mysql *:*                     LISTEN     2713/mysqld

you should set a MySQL password for your hostname (my hostname is server1.example.com here), too, because otherwise anybody can access your database and modify data:

mysqladmin -h server1.example.com -u root password yourrootsqlpassword

2 Installing Lighttpd, Python, And python-mysqldb

We will use a Python script to read the vhost configurations from the MySQL database, therefore we must install Python and python-mysqldb in addition to lighttpd.

You can install these packages as follows:

apt-get install lighttpd python python-mysqldb

3 Preparing The MySQL Database

Now we log in to MySQL...

mysql -u root -p

... and create the database lighttpd:

CREATE DATABASE lighttpd;

Next we create a database user (which we name lighttpd as well) with SELECT privileges for the lighttpd database:

GRANT SELECT ON lighttpd.* TO lighttpd@localhost IDENTIFIED BY 'secret';
GRANT SELECT ON lighttpd.* TO lighttpd@localhost.localdomain IDENTIFIED BY 'secret';
FLUSH PRIVILEGES;

(Replace secret with a password of your choice.)

Then we create the domains table in the lighttpd database and leave MySQL:

USE lighttpd;

CREATE TABLE IF NOT EXISTS domains (
domain varchar(64) NOT NULL PRIMARY KEY,
docroot varchar(128) NOT NULL,
config text
);

quit;

4 Creating The Python Script To Read The Vhost Configuration From The Database

Now we create the Python script /usr/share/lighttpd/mysql_vhost.py which will connect to the lighttpd database and read the vhost configurations from it:

vi /usr/share/lighttpd/mysql_vhost.py

#!/usr/bin/env python
import sys
import MySQLdb

# load configuration data from the database
db=MySQLdb.connect(host='localhost', db=sys.argv[1], user=sys.argv[2], passwd=sys.argv[3])
cur = db.cursor()
cur.execute("SELECT * FROM domains")
rs=cur.fetchall()
db.close()

for domain in rs:

    print "$HTTP[\"host\"] == \"%s\" {\nserver.document-root = \"%s\"\n%s\n}" % (domain[0], domain[1], domain[2])

Make the script executable:

chmod 755 /usr/share/lighttpd/mysql_vhost.py

Now we must tell lighttpd to use that script. Therefore we open /etc/lighttpd/lighttpd.conf and add the following line at the end of it:

vi /etc/lighttpd/lighttpd.conf

[...]
include_shell "/usr/share/lighttpd/mysql_vhost.py lighttpd lighttpd secret"

(The first lighttpd refers to the name of the MySQL database, the second lighttpd to the database user, and secret to the MySQL password.)

Restart lighttpd afterwards:

/etc/init.d/lighttpd restart