Apache2: Logging To A MySQL Database With mod_log_sql (Debian Etch)

Version 1.0
Author: Falko Timme <ft [at] falkotimme [dot] com>
Last edited 06/19/2008

This guide shows how you can write the Apache2 access log to a MySQL database instead of a file. To achieve this, I use the Apache2 module mod_log_sql. I'm using a Debian Etch server in this tutorial.

I do not issue any guarantee that this will work for you!

1 Installing Apache2, MySQL, mod_log_sql

Unfortunately libapache2-mod-log-sql-mysql is available as a Debian package only for Debian Lenny (testing) and Sid (unstable), but not for Etch. Therefore we will install the libapache2-mod-log-sql-mysql package from Lenny. To do this, open /etc/apt/sources.list and add the line deb http://ftp2.de.debian.org/debian/ lenny main; your /etc/apt/sources.list could then look like this:

vi /etc/apt/sources.list

deb http://ftp2.de.debian.org/debian/ etch main
deb-src http://ftp2.de.debian.org/debian/ etch main

deb http://ftp2.de.debian.org/debian/ lenny main

deb http://security.debian.org/ etch/updates main contrib
deb-src http://security.debian.org/ etch/updates main contrib

Of course (in order not to mess up our system), we want to install packages from Lenny only if there's no appropriate package from Etch - if there are packages from Etch and Lenny, we want to install the one from Etch. To do this, we give packages from Etch a higher priority in /etc/apt/preferences:

vi /etc/apt/preferences

Package: *
Pin: release a=etch
Pin-Priority: 700

Package: *
Pin: release a=lenny
Pin-Priority: 650

(The terms etch and lenny refer to the appropriate terms in /etc/apt/sources.list; if you're using stable and testing there, you must use stable and testing instead of etch and lenny in /etc/apt/preferences as well.)

Afterwards, we update our packages database:

apt-get update

If you're getting an error like this:

Segmentation faultsts... 96%

or this one:

E: Dynamic MMap ran out of room

open /etc/apt/apt.conf and add a line for APT::Cache-Limit with a very high value, e.g. like this:

vi /etc/apt/apt.conf

APT::Cache-Limit "100000000";

Then run

apt-get update

again and upgrade the installed packages:

apt-get upgrade

(If you see any questions, you can accept the default values.)

To install Apache2, MySQL, and mod_log_sql, we run:

apt-get install apache2 mysql-server mysql-client libapache2-mod-log-sql-mysql

Create a password for the MySQL user root (replace yourrootsqlpassword with the password you want to use):

mysqladmin -u root password yourrootsqlpassword

Then check with

netstat -tap | grep mysql

on which addresses MySQL is listening. If the output looks like this:

tcp        0      0 localhost.localdo:mysql *:*                     LISTEN     2713/mysqld

which means MySQL is listening on localhost.localdomain only, then you're safe with the password you set before. But if the output looks like this:

tcp        0      0 *:mysql *:*                     LISTEN     2713/mysqld

you should set a MySQL password for your hostname, too, because otherwise anybody can access your database and modify data:

mysqladmin -h server1.example.com -u root password yourrootsqlpassword

mod_log_sql got enabled automatically when we installed it, but there's another module which we should enable: mod_uique_id (it's not necessary, but without it, mod_log_sql will leave the id column in the MySQL table for the access log empty). The id column will contain the unique request ID supplied by the standard Apache module mod_unique_id.

a2enmod unique_id

Reload Apache:

/etc/init.d/apache2 force-reload

2 Creating A Virtual Host

I will now create a default Apache vhost in the directory /var/www/web1/web. For this purpose, I will modify the default Apache vhost configuration in /etc/apache2/sites-available/default. If you already have a vhost for which you'd like to enable MySQL logging, you must adjust this tutorial to your situation.

First, we create the directory /var/www/web1/web and make the Apache user (www-data) the owner of that directory:

mkdir -p /var/www/web1/web
chown www-data /var/www/web1/web

Then we back up the default Apache vhost configuration (/etc/apache2/sites-available/default) and create our own one:

mv /etc/apache2/sites-available/default /etc/apache2/sites-available/default_orig
vi /etc/apache2/sites-available/default

NameVirtualHost *
<VirtualHost *>
        ServerAdmin webmaster@localhost

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

</VirtualHost>

Then reload Apache:

/etc/init.d/apache2 reload

3 Configure The Virtual Host For MySQL Logging

You can find the documentation for mod_log_sql in the HTML file /usr/share/doc/libapache2-mod-log-sql/manual.html. You should copy it to your workstation (e.g. with WinSCP) and study it.

Having read this file, we create a MySQL database called apachelogs which will contain the tables for our access logs (it can contain multiple access log tables, for example one for each virtual host you have). In addition to that we create the MySQL user loguser - this user will be used by mod_log_sql to connect to MySQL later on. loguser will have CREATE and INSERT privileges so that it can automatically create tables for the access logs (if the tables don't already exist) and insert records in these tables:

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;

(Replace loguser_password with a password of your choice.)

Then we leave the MySQL shell:

quit;

Now we modify our vhost in /etc/apache2/sites-available/default so that it looks as follows:

vi /etc/apache2/sites-available/default

LogSQLLoginInfo mysql://loguser:loguser_password@localhost/apachelogs
LogSQLCreateTables on
LogSQLDBParam socketfile /var/run/mysqld/mysqld.sock
LogSQLTransferLogFormat AbHhmRSsTUuvI

NameVirtualHost *
<VirtualHost *>
        ServerAdmin webmaster@localhost

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

        LogSQLTransferLogTable web1_access_log
</VirtualHost>

The LogSQLLoginInfo (which holds the login credentials to the database), LogSQLCreateTables (which tells mod_log_sql to create tables if they don't exist), LogSQLDBParam socketfile (which specifies the MySQL socket), and LogSQLTransferLogFormat (which specifies what gets logged in the database) directives go into the global Apache configuration. The LogSQLTransferLogTable directive goes into the vhost configuration - that way you can specify different access log tables for each virtual host (e.g. web1_access_log, web2_access_log, web3_access_log, etc.).

The LogSQLTransferLogFormat directive is explained in more detail in the /usr/share/doc/libapache2-mod-log-sql/manual.html file.

Reload Apache afterwards:

/etc/init.d/apache2 reload

4 Testing mod_log_sql

Now access some pages from your vhost in a browser and check the apachelogs database afterwards:

mysql -u root -p

use apachelogs;
show tables;

You should now see that the table web1_access_log has been created by mod_log_sql:

mysql> show tables;
+----------------------+
| Tables_in_apachelogs |
+----------------------+
| web1_access_log      |
+----------------------+
1 row in set (0.00 sec)

mysql>

Now let's check what's in that table:

select * from web1_access_log;

If all went well, you should find access logs in that table:

mysql> select * from web1_access_log;
+---------------------+---------------------------------------------------------------------------------------------...
| id                  | agent                                                                                       ...
+---------------------+---------------------------------------------------------------------------------------------...
| JpEF8MCoAGQAAFyuEBU | Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.14) Gecko/20080404 Firefox/2.0.0.14...
| JpPHIMCoAGQAAFyuEBY | Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.14) Gecko/20080404 Firefox/2.0.0.14...
| JpTj0MCoAGQAAFyuEBc | Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.14) Gecko/20080404 Firefox/2.0.0.14...
+---------------------+---------------------------------------------------------------------------------------------...
3 rows in set (0.00 sec)

mysql>

Type

quit;

to leave the MySQL shell.

5 Links

• Apache: http://httpd.apache.org
• Debian: http://www.debian.org