A few days ago, I decided to finally do something about my Apache logging mess. The "mess" is that I host about 15 virtual domains on a couple of colocated Linux servers. Most of the domains either belong to me or my friends. A few are business related. For the longest time, I've had apache configured to log in the typical combined log format with one log file per domain. I haven't rotated (or cleaned up) the logs for a long, long time.

Enter mod_log_mysql, an apache module that allows you to log directly to a MySQL server, optionally logging to disk as well. The module is simple and straightforward to setup. Most importantly, it Just Works. There's even a cool MySQLMassVirtualHosting option so that it'll log each domain to a separate table and even auto-create new ones as you add domains to your apache configuration. Very cool.

I did manage to find a bug along the way. The module didn't properly quote table names. So if you have a table named something like advanced-mysql_com (all dots become underscores), MySQL will barf on the INSERT and CREATE TABLE statements. But since I had the source code, it was quite easy to fix. I'll be sending a patch to the maintainer soon.

Why this is so cool.

Once everything was up and running smoothly, I had a number of MySQL tables collecting data about traffic to my various web sites. "So what?", right? The cool thing is that by adding a couple of indexes to make queries fast (there are no indexes by default), I can whip up a PHP-based application that presents interesting stats to me in real-time. The PHP app isn't done yet, but it's quite functional for providing a high-level picture of what's going on. I can see which of my blog entries are the most popular, who is visiting them, and where they came from (referer tracking).

More to do.

I'm not going to make the URL public yet, because the app needs some work and a good security audit. But now that the data is in a more accessible format, I can do tons and tons of stuff with it. I plan to have a module on my blog index page that lists the most popular entries in almost-real-time. I could have it updated every minute or two without putting much of a drain on the system at all.

The other thing I need to get around to is importing a few years worth of old log data from the existing access_log files. That's just going to take a bit of time to write a Perl script to do the job. Once that's done, I'll be able to answer a lot of interesting questions about my web sites.

Posted by jzawodn at July 06, 2002 04:55 PM

Reader Comments
# Bryan Strawser said:


This is seriously cool - I look forward to seeing what you're able to post publically. I host more than 40 domains across 4 servers..

Take care--

on July 6, 2002 06:36 PM
# Chris Powell said:

Jeremy -- I'm delighted to read your very nice review of mod_log_sql; it sounds like it's working well for you. You're right about the buglet that you mention, and the good news is that it's fixed in 1.17 (about to be released). You'll be happy to hear that 1.17 can also log headers and notes (e.g. mod_gzip compression information), plus numerous other little improvements.

Chris Powell
mod_log_sql maintainer

on July 9, 2002 12:55 PM
# Rachael said:

Is it possible to post or email the hypen-bug fix?

on November 5, 2002 07:26 AM
# AdamHimself said:

Late to post..haha. I've been reading an interesting book from O'Reilly...something about mysql..hmm?

I'm just getting to the mysql logging of IIS. ;)

I was wondering if you had found/written a little conversion script for exisiting log files. I have some floating around the 100mb mark. they were common from apache 1.3.x

Thanks, and I seriously enjoy reading about your work!

on April 1, 2005 10:13 PM
Disclaimer: The opinions expressed here are mine and mine alone. My current, past, or previous employers are not responsible for what I write here, the comments left by others, or the photos I may share. If you have questions, please contact me. Also, I am not a journalist or reporter. Don't "pitch" me.


Privacy: I do not share or publish the email addresses or IP addresses of anyone posting a comment here without consent. However, I do reserve the right to remove comments that are spammy, off-topic, or otherwise unsuitable based on my comment policy. In a few cases, I may leave spammy comments but remove any URLs they contain.