Processing Web Input with MySQL - MySQL - page 720

The purpose of including the use lib line is so that Perl can find the Cookbook.pm
module. This line is needed if the environment of scripts invoked by Apache for logging
does not enable Perl to find the module. Adjust the path as necessary for your system.
Alternatively, modify the Apache environment using a SetEnv directive (see
Recipe 18.2 ).
httpdlog.pl assumes that input lines contain httpdlog column values delimited by tabs
(to make it easy to break input lines), so Apache must write log entries in a matching
format. The following table shows the LogFormat field specifiers to produce the appro‐
priate values:

Specifier Meaning

The date and time of the request, in MySQL's DATETIME format

%{%Y-%m-%d %H:%M:%S}t

The host from which the request originated

%h

The request method ( get , post , and so forth)

%m

The URL path

%U

The status code

%>s

The number of bytes transferred

%b

The referring page

%{Referer}i

The user agent

%{User-Agent}i

To define a logging format named mysql that produces these values with tabs in between,
add the following LogFormat directive to your httpd.conf file:

LogFormat \
"%{%Y-%m-%d %H:%M:%S}t\t%h\t%m\t%U\t%>s\t%b\t%{Referer}i\t%{User-Agent}i" \
mysql
Most of the pieces are in place now. We have a log table, a program that writes to it, and
a mysql format for producing log entries. Install the httpdlog.pl script where you want
Apache to look for it. On my system, the Apache root directory is /usr/local/apache ,
so /usr/local/apache/bin is a reasonable installation directory. This path is needed shortly
for constructing the CustomLog directive that instructs Apache to log to the script.
All that remains is to tell Apache to write the entries to the httpdlog.pl script. However,
until you know that the output format really is correct and that the program can process
log entries properly, it's premature to tell Apache to log directly to the program. To make
testing and debugging a bit easier, have Apache log mysql -format entries to a file instead.
That way, you can look at the file to check the output format, and you can use it as input
to httpdlog.pl to verify that the program works correctly. To instruct Apache to log lines
in mysql format to the file test_log in Apache's log directory, use this CustomLog directive:

CustomLog /usr/local/apache/logs/test_log mysql