Logging activity to a MySQL database

Problem
Rather than logging accesses to your server in flat text files, you want to log the information directly to a database for easier analysis.

Solution

Install the latest release of mod_log_sql from http://www.outoforder.cc/projects/apache/
mod_log_sql/ according to the modules directions (see Recipe 2.1), and then issue the following commands:

# mysqladmin create apache_log
# mysql apache_log < access_log.sql # mysql apache_log mysql> grant insert,create on apache_log.* to webserver@localhost identified by 'wwwpw' ;

Add the following lines to your httpd.conf file:


LogSQLLoginInfo mysql://webserver:wwwpw@dbmachine.example.com/apache_log
LogSQLCreateTables on


Then, in your VirtualHost container, add the following log directive:

LogSQLTransferLogTable access_log

Discussion

Replace the values of webserver and wwwpw with a less guessable username and password when you run these commands.
Consult the documentation on the referenced website to ensure that the example here reflects the version of the module that you have installed, as the configuration syntax changed with the 2.0 release of the module.