How to Save Apache Access Log into MySQL Database Using libapache2-mod-log-sql on Debian Squeeze

The purpose of saving Apache access log into MySQL database is to make it easier to parse website’s visitor information.

First of all, we do repository update:

# apt-get update

Install libapache2-mod-log-sql

# apt-get install libapache2-mod-log-sql

Create apachelogs and scoreboard table:

# mysql -u root -p

create database apachelogs;

grant insert,create on apachelogs.* to loguser@localhost identified by 'loguser_password';
grant insert,create on apachelogs.* to loguser@localhost.localdomain identified by 'loguser_password';
FLUSH PRIVILEGES;

use apachelogs;

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;

quit;

Modify /etc/apache2/sites-available/default:

LogSQLLoginInfo mysql://loguser:loguser_password@localhost/apachelogs
LogSQLDatabase apachelogs
LogSQLCreateTables on
LogSQLDBParam socketfile /var/run/mysqld/mysqld.sock
LogSQLPreserveFile /var/log/mod_log_sql-preserve
NameVirtualHost *
<VirtualHost *>
ServerAdmin webmaster@localhost

DocumentRoot /var/www/web1/
<Directory /var/www/web1/>
Options Indexes MultiViews
AllowOverride None
Order allow,deny
allow from all
</Directory>

LogSQLTransferLogTable web1_access_log
</VirtualHost>

If you have other virtual hosts, you can put LogSQLTransferLogTable directive into each virtual host tag.

Create mod_log_sql-preserve file:

# touch /var/log/mod_log_sql-preserve

# chmod go+w /var/log/mod_log_sql-preserve

Restart Apache:

# /etc/init.d/apache2 restart

Browse to the website and verify web1_access_log table.

# mysql -u root -p

use apachelogs;

show tables;

select * from web1_access_log;

quit;

Done.

To check any problem, view apache error log:

# tail -f /var/log/apache2/error.log