So I have been wondering how to do something useful with the 623 logfiles I have archived. Sure, I run nightly stats, but they’re pretty basic and they’re not queryable. If I want to analyze something over time, how do I do that?
Well, if I had taken note of this article — Linux Magazine | October 2002 | LAMP POST | Getting a Handle on Traffic — when it came out, I wouldn’t be asking these questions now.
But this is a great article on this topic and points to the installation, care, and feeding of mod_log_sql which — wait for it — is an Apache module that lets you log web server traffic directly into your MySQL database. The mod_log_sql docs are very good and will get you going in no time.
But how to handle the ever-increasing backlog of old logfiles? Not so difficult after all. In Jeremy’s article, he lays out the schema for the database, so you can simply crib that and write something in the language of your choice to turn log data into SQL INSERT statements.
And of course, you can pull it out yourself, once you have mod_loq_sql running.
This is what the table for access_log looks like:
mysql> desc access_log;
+