Send Apache Logs to MySQL

In one of my previous employer where we had to deal with thousands of server serving web content, space issue was one of the major concern which was filled up very fast by the apache logs. Not only this as you all know Log retention policy definition is never clear to many organizations. I know IT managers dealt with document retention as a way to keep their boss' boss off their boss' back. Hence it was more of administrative concern rather than a technical issue. Anyway, we tried to deal with it with a Apache_MySQL simplee setup with the help of mod_log_sql/mod_log_mysql. we used to push apache logs to a centralized DB server(MySQL MyISAM master-slave) with a very simple config in apache.

The strategy was as below:

Decide on a MySQL DB schema like, based on your apache log files fields.
Decide if you need to additiocal apache log parameter. (We had to add %v, as we had to deal with multiple virtual host per server.
Decide of Database cluster architecture (We chose MySQL MyISAM master-slave)
Decide on how failover will work for database.
Construct DML SQL for data mining on logs to derive various needed stats. like

- How many 404 i have today
- is there any 5xx on my site today
-how much traffic was server by my apache today etc etc,

What we achieved:

This helped us concentrating space maintenance in one specific server when we had a web-cluster in place.
Even failover was easy to either switch to slave DB server or even quick SVN CO of a different apache configuration file which stores logs locally in case if DB cluster itself goes down
Also a quick report like http error code say 5XX etc. can be pull out with a select query as part of troubleshooting.
Seamless integration into the standard Apache logging configuration.
Logs data as it is: e.g. times as SQL DATETIME and n/a items as SQL NULL, with simple config.

How To:

1. Download mod_log_sql tarball -> Compile install
apxs -a -i -c -L/usr/mysql/lib -I/usr/mysql/include -lmysqlclient_r -lz mod_log_mysql.c
2. Edit httpd.conf

LoadModule log_mysql_module [path to your mod_log_mysql.so, usually "modules/mod_log_mysql.so"]

and Most importantly,

LogFormat "INSERT INTO sqlserver SET virtualhost=%v,
remoteip=%a,remoteport=%S,serverid=%{SERVERID}e,
connectionstatus=%X,remoteuser=%u,identuser=%l,
datetime=%t,requestmethod=%m, url=%R,protocol=%H,
statusbeforeredir=%s,statusafterredir=%>s,
processid=%{pid}P,threadid=%{tid}P,seconds=%T,
microseconds=%D,  bytesin=%I,bytesout=%O,
bytescontent=%B,useragent=%{User-Agent}i,
referer=%{Referer}i,uniqueid=%{UNIQUE_ID}e" sqlserver

CustomLog mysql:logwriter!password@ipaddress/apachelog\
sqlserver

DB server settings:

Create database , based on your log parameters or fields.
Grant privileges, user@apache web server ip address (here logwriter is my web mysql user)


Check with restarting apache and validate with failover too!

Cheers.!!!