A-Z quickstart guide to mod_log_mysql

This guide will hopefully help you getting your Apache 2 webserver to log into a MySQL database using mod_log_mysql, no matter if you are a newbie or an experienced user.
However, it should be mentioned that while this guide does cover installation of the modular mod_log_config and mod_log_mysql, the installation procedure for the Apache 2 and MySQL servers is out of its scope. Second, configuration of both servers is only explained as far as it is related to mod_log_mysql, and only the basics. Please see the manuals of Apache 2 and MySQL for further details on these subjects.

In the following, you will see two different shell prompts, shell> is the one of the system's shell, mysql> is the one of the MySQL console (the tool named "mysql"). Regarding MySQL, it is assumed that you have a superuser named root, who is able to create users, databases and tables within MySQL.

Contents

  1. [The content table should go here]

Quickstart guide

Installation

Installation of the modular mod_log_config

Installation of mod_log_mysql

Preparing a MySQL database

The MySQL database server handles a number of databases, which in turn are made up of one or more tables which store the data.
In this example, we will use a single database for all logs, containing one table per (virtual) host. We will also set up different MySQL users with different access levels, one for the Apache 2 server itself, one for each host owner.

mod_log_mysql currently does not create databases or tables itself. Whenever you set up a new virtual host, you have to set up the database or table to be used for logging, too.

Creating a database

Create a database named httpd:

mysqladmin -u root -p create database httpd

More information on the tool mysqladmin can be found in the MySQL manual, "Administering a MySQL server".

Creating a table

Create a new table to store the access log for host "example.com".

shell> mysql -u root -p -D httpd
mysql> CREATE TABLE examplecom ( host varchar(16), time datetime, method varchar(8), url varchar(255), status smallint(5), bytesin int(10), bytesout int(10), referrer varchar(255), agent varchar(80), type varchar(32), KEY typetime (type,time) );

The host name that gives the table its name is the one configured using the ServerName directive in your Apache 2 configuration. In our example, we would have a line ServerName example.com in the Apache 2 configuration.
Notice that it is not allowed to use a dot in a MySQL table name. We handle this by simply omitting anything from the host name that is neither a character or a number. So, in our example above, "example.com" is written as "examplecom".

Along with the table we also created an index using KEY typetime (type,time) near the end of the line. Most people will want to fetch daily page impressions and the related log data is defined by the MIME type and the time - that's why the index is a combination of the columns type and time.
This index is also useful for any other queries based on type or time, so it's actually a good general purpose index. You might want to add more indices to speed up other queries, but remember that more indices mean greater disk usage and more time needed to write the logs.

Repeat create table for all of your virtual hosts.

More information on the tool mysql can be found in the MySQL manual, "The command-line tool", information about create table in the language reference chapter, "Create table syntax".

Adding database users and database access privileges

MySQL has its own access system. In order to restrict host owners' access their own logs a separate user for each host owner is created. In addition, a special user for the Apache 2 webserver is created who only has a single privilege: insert data, but into all log tables.

mysql> GRANT SELECT ON httpd.examplecom TO joeexample@localhost IDENTIFIED BY joespassword;
mysql> GRANT INSERT ON httpd.* TO httpd@localhost IDENTIFIED BY apachepassword;

In the first line, we created the user joeexample. joeexample is only allowed to connect from the system localhost, i. e. the same machine the MySQL server runs on, and he can only use the SELECT command on his table examplecom in the database httpd.
Repeat this line for all users and their hosts.

In the second line, the special user for Apache 2 (actually mod_log_mysql) has been created. His login name is httpd and since he can only login from localhost, the MySQL server must actually run on the same machine as the Apache 2 web server. As already written, httpd can only insert data, he cannot delete anything, not even read the data he inserted earlier. However, he has access to all tables in the database httpd: httpd.*.

More information on the MySQL access priviledge system can be found in the MySQL manual, "The MySQL Access Priviledge System", adding new users is described in the paragraph "Adding new users to MySQL". The GRANT command is explained in the language reference chapter, "GRANT and REVOKE syntax".

Configuring the Apache 2 webserver

External modules need to be loaded into the webserver, insert the following one to three lines into your Apache 2 configuration, usually named httpd.conf in the conf/-directory of your Apache 2 distribution:

LoadModule log_config_module modules/mod_log_config.so LoadModule log_mysql_module modules/mod_log_mysql.so LoadModule logio_module modules/mod_logio.so

The line loading mod_log_config is most likely already in your configuration. Load mod_log_mysql after mod_log_config, mod_log_mysql needs to hook into mod_log_config. In case you would like to get exact Web traffic accounting, activate mod_logio, too (this module is delivered with your Apache 2 webserver).

Logging configuration

To start logging into the examplecom-table created above, insert the following two lines into your Apache 2 configuration:

LogFormat "insert into %{mysqlname}v set host=%h, time=%t, method=%m, url=%R, status=%>s, bytesin=%I, bytesout=%O, referrer=%{Referer}i, agent=%{User-Agent}i, type=%{Content-Type}o" mysql
CustomLog mysql:httpd!apachepassword@localhost/httpd mysql

The first line, LogFormat ..., defines a log format string named "mysql". As you can see, it will insert the usual stuff into the access log, with two exceptions: bytesin / -out, which are extensions from Apache 2's mod_logio module, and type, which stores the MIME type send by the webserver and helps us distinguish e. g. a CGI-created HTML page from a CGI-created image (on most servers, both will have the URL ending in .cgi or similar, same goes for PHP and all other "active" content).

The table name is determined by %{mysqlname}v. %v returns the configured server name, the attribute mysqlname modifies this just like we did above when creating the table: it removes anything that is not a character or a number.

The second line, CustomLog ..., activates logging for the current server, i. e. for a specific virtual host if given within a <VirtualHost>-block, or for the main server and all others without an own CustomLog if within the main configuration.
The first argument to CustomLog is the log target. mysql: obviously defines a MySQL database, then follows username (httpd), password (apachepassword), host (localhost) and the database (httpd).
We are using a short form of the MySQL logging URI here, omitting the port number or Unix socket path after the host name. This is usually defined in the MySQL configuration file my.cnf.

More information about LogFormat, the %-directives and CustomLog can be found in the Apache 2 manual, "mod_log_config".

Reloading the webserver configuration

Restart your Apache 2 so the changes to the configuration can take effect.

Done