Log Apache2 access log into a MySQL database on Debian Lenny

This article describes how you can log your apache access log entries into a MySQL database.

You need a installed and configurated Apache2 webserver and MySQL server.

Installation:

On the webserver:

aptitude install libapache2-mod-log-sql-mysql
a2enmod unique_id

On the database server:

Create a new database „apachelogs“ and a user for this database.

Configurate a virtual host:

Replace „PASSWORD“ and „DATABASE_SERVER“ with your own data.

LogSQLLoginInfo mysql://apachelogs:PASSWORD@DATABASE_SERVER/apachelogs
LogSQLCreateTables on
LogSQLDBParam socketfile /var/run/mysqld/mysqld.sock
LogSQLTransferLogFormat IAbPcMfRhluTrmHtUapzqQsSV

<virtualhost www.testsrv.local:80>
ServerName www.testsrv.local
ServerAdmin servermaster@testsrv.local
DocumentRoot /var/www/vhost1/htdocs
ErrorLog /var/www/vhost1/logs/error.log
CustomLog /var/www/vhost1/logs/access.log common

LogSQLTransferLogTable vhost1_access_log

</virtualhost>

/etc/init.d/apache2 reload

mod-log-sql-mysql will create a table „vhost1_access_log“ and log the Apache2 access there.mod-log-sql-mysql

Problems:

You can get several errors in the apache error log when running the module:

1. „No such file or directory: attempted append of local preserve file ‚/etc/apache2/logs/mod_log_sql-preserve‘ but failed.“

You have to create the directory „/etc/apache2/logs“ manual or create a symlink to „/var/log/apache2“.

2. „failed to create table: score_board“

There is a problem with scoreboard table creation, you have to create it:

CREATE TABLE IF NOT EXISTS `scoreboard` (
`id` int(14) NOT NULL auto_increment,
`vhost` varchar(50) NOT NULL default '',
`bytes_sent` int(14) NOT NULL default '0',
`count_hosts` int(12) NOT NULL default '0',
`count_visits` int(12) NOT NULL default '0',
`count_status_200` int(12) NOT NULL default '0',
`count_status_404` int(12) NOT NULL default '0',
`count_impressions` int(18) NOT NULL default '0',
`last_run` int(14) NOT NULL default '0',
`month` int(4) NOT NULL default '0',
`year` int(4) NOT NULL default '0',
`domain` varchar(50) NOT NULL default '',
`bytes_receive` int(14) NOT NULL default '0',
PRIMARY KEY  (`id`),
UNIQUE KEY `vhost` (`vhost`,`month`,`year`,`domain`)
) TYPE=MyISAM;

Thanks to Alexandre Bulté for the solution !

Links:

http://blog.bulte.net/2008/04/apache-modlogsql-problem-with.html
http://www.outoforder.cc/projects/apache/mod_log_sql/
http://www.howtoforge.com/apache2-logging-to-a-mysql-database-with-mod_log_sql-on-debian-etch