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.
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.
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".
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".
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".
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).
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".
Restart your Apache 2 so the changes to the configuration can take effect.