Tutorial: VHost config from SQL Database – Lighttpd an alternative to Apache
I look after a number of web servers on a day to day basis and over the last few years I have developed an on-line system to allow easy admin of those web servers. The stumbling block in us having fully web-driven administration of our servers has always been Apache Vhost config because, as far as I know, there is no freely available way of drawing Apache Vhost config directly from a Database such as MySQL. The only solutions I know of involve dynamically rewriting the Apache config file(s) which has some serious potential pitfalls if you have a bug in your script which writes the config file as Apache may die, not good!
For a couple of years now, I have been keeping an eye on the Lighttpd project which is a very fast and efficient alternative to Apache and is used to serve some major websites such as Youtube. Just recently I decided to have a go at installing lighttpd on a spare Virtual Machine we have and whilst doing that I found something amazing, you can install a Lighttpd module to allow Vhost configuration directly from Database! This is good news indeed…
Lighttpd works in a modular fashion and there seem to be 2 modules used to provide a MySQL interface for Vhost configuration, mod_sql_vhost_core and mod_mysql_vhost. From looking at the Lighttpd documentation, it appears that mod_sql_vhost_core provides the base functionality (and is included by mod_mysql_vhost) and mod_mysql_vhost is a MySQL specific implementation though I may be wrong about that.
For my purposes, I installed lighttpd (version 1.4.19) and lighttpd-mod-mysql-vhost using apt (which in my opinion puts Debian leagues ahead of any other Linux distribution) on our Debian Lenny Virtual Machine:
apt-get install lighttpd lighttpd-mod-mysql-vhost
Then you simply need to edit your lighttpd config file (which for Debian Lenny is stored in /etc/lighttpd/lighttpd.conf) – adding the new module to your existing modules (remember not to put a comma after the last module):
server.modules = ( "mod_access", "mod_alias", "mod_accesslog", "mod_compress", "mod_mysql_vhost" )
then add the MySQL server access information and query to your lighttpd.conf file (I added mine right below the above module includes) as per the example in the Lighttpd mod_mysql_vhost documentation:
mysql-vhost.db = "vhost_db" mysql-vhost.user = "db_user" mysql-vhost.pass = "db_password" mysql-vhost.sock = "/var/run/mysqld/mysqld.sock" mysql-vhost.sql = "SELECT docroot FROM domains WHERE domain='?' limit 1;" mysql-vhost.hostname = "localhost" mysql-vhost.port = 3306
I am presuming that if you are using a table whose column name for the document root is not “docroot”, you should alias the column e.g:
mysql-vhost.sql = "SELECT document_root as docroot FROM domains WHERE domain='?' limit 1;"
If you have a lot of domains, make sure you add a “limit 1″ at the very end of your query, otherwise your database server will keep searching through every vhost record even after it has found a match!
if you are likely to have more than one possible match you may want to add an “order by” to ensure you get the correct vhost config.
If no match is found, Lighttpd will use the default static vhost config from your lighttpd.conf file which by default has a document root of /var/www. You could use this as a fallback to perhaps display a notice such as “Sorry, no Website found” with a relevant HTTP response code (probably a 404).
You can use MySQL-style wildcards for your domain name in your database e.g. “%.example.com” would match any sub-domain of “example.com”. If you want to use wildcards, you’ll need to amend your query to use “like” rather than “=” e.g:
SELECT vhost_document_root as docroot FROM vhost WHERE '?' like vhost_domain_name and vhost_active=1 and vhost_date_deleted=0 limit 1;
I’ll post back here when i’ve had a moment to create a query which selects an exact match first, then a wildcard if no exact match is found as I believe this will be the setup most people will want in practice.
Be careful if you enter the wildcard as e.g. “%example.com” as this would match “www.example.com” and “secure.example.com” but would also match “thisisanexample.com”.
Once you have set up your lighttpd.conf file and your database, you need to restart Lighttpd so that it picks up your new config:
If you get a failure upon restart as I did when I first tried this:
/etc/lighttpd# /etc/init.d/lighttpd restart Stopping web server: lighttpd. Starting web server: lighttpd2010-06-10 17:16:07: (plugin.c.165) dlopen() failed for: /usr/lib/lighttpd/mod_sql_vhost_core.so /usr/lib/lighttpd/mod_sql_vhost_core.so: cannot open shared object file: No such file or directory 2010-06-10 17:16:07: (server.c.621) loading plugins finally failed failed!
then check the Lighttpd error file which (on Debian Lenny) is in /var/log/lighttpd/error.log – I had a typo in my query.
I am not sure what the performance of Lighttd with the MySQL Vhost interface is like under heavy load, for instance I don’t know whether it caches the query result or relies on MySQL for that but i’ll try to find some information and post it back here.
Lighttpd also supports a great many features which will be well known to Apache web servers admins such as mod_rewrite (albeit with slightly different syntax) and even flash FLV streaming! I am seriously thinking about switching lock-stock to Lighttpd from Apache from just this simple experiment. I also like very much the config file syntax, it’s more like dot-notation in modern programming languages.
Main Lighttpd wiki documentation is here: http://redmine.lighttpd.net/projects/lighttpd/wiki
For reference, my complete lighttpd conf file (which I have made very few changes from the standard file, MySQL access details removed) is:
Which works with my vhost config table:
CREATE TABLE `vhost` ( `vhost_id` int(20) unsigned NOT NULL auto_increment, `vhost_active` enum('1','0') NOT NULL default '1', `vhost_domain_name` varchar(255) NOT NULL, `vhost_document_root` varchar(255) NOT NULL, `vhost_date_added` datetime NOT NULL, `vhost_date_deleted` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`vhost_id`), UNIQUE KEY `uniq` (`vhost_domain_name`,`vhost_date_deleted`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
NOTE: I always prefix database table column names with the name of the table to which they belong and use a datetime field to indicate the deleted status of a record.
|This entry was posted by Neil Craig on June 10, 2010 at 4:15 pm, and is filed under Apache, General Web-Development, lighttpd, MySQL, Sysadmin. Follow any responses to this post through RSS 2.0. You can leave a response or trackback from your own site.|
No comments yet.
No trackbacks yet.
about 1 year ago - 3 comments
AS I type, i’m setting up an Ubuntu server to use a virtual server host on my test rig. Slight problem though, after getting bored/frustrated with command line configuring (odd because usually command line is my preference) I got lazy and decided to install Gnome and virt-manager. All good, so I thought, I added xrdp More >
about 1 year ago - No comments
It’s been quite some time since I’ve posted an article here and this one will be very brief… If you feel so inclined and are able, please consider sponsoring the UK National Computer Archives Colossus display. More information can be found here: http://www.colossusonline.org/ I’ll soon be posting on my findings over the last year or More >
about 2 years ago - 3 comments
This will be a really quick post which will be obvious to unix sysadmin veterans but hopefully will be helpful to us mere mortals… wget is a linux/*nix command line tool which can download files (including entire websites if options are set correctly) and at first glance, from looking at the wget man page you’d More >
about 3 years ago - 5 comments
A brief article describing how I set up Qmail (on Debian Etch) running virtual domains to forward Email to another Email server using a simple addition to a Qmail config file.
about 3 years ago - 1 comment
We’ve recently been working on a Web-Server migration for a regular client at work, part of which involved transferring the MySQL Databases (or strictly the schemas) for each of their websites from their old Web-Server to the shiny new replacement. In doing this, I rediscovered a really handy method which really simplifies transferring MySQL data More >