Processing Web Input with MySQL - MySQL - page 718

specific format, issue the statements from within an API and take advantage of your
language's output production capabilities.
By handling log entry generation and storage using separate processes, you gain flexi‐
bility. Some of the possibilities are to send logs from multiple web servers to the same
MySQL server, or to send different logs generated by a given web server to different
MySQL servers.
This recipe shows how to integrate MySQL into Apache's logging mechanism and dem‐
onstrates some representative summary queries.
Setting up database logging
Directives in the httpd.conf configuration file control Apache logging. For example, a
typical logging setup uses LogFormat and CustomLog directives that look like this:
LogFormat "%h %l %u %t \"%r\" %>s %b" common
CustomLog /usr/local/apache/logs/access_log common
The LogFormat line defines a format for log records and gives it the nickname common .
The CustomLog directive indicates that lines should be written in that format to the
access_log file in Apache's logs directory. To set up logging to MySQL instead, use the
following procedure. (Adapt it as necessary if you use logging directives such as Trans
ferLog rather than LogFormat and CustomLog .)
1. Decide what values to record and set up a table that contains the appropriate col‐
umns.
2. Write a program that reads log lines from Apache and writes them to the database.
3. Set up a LogFormat line that defines how to write log lines in the format the program
expects, and a CustomLog directive that tells Apache to write to the program rather
than to a file.
Suppose that you want to record the date and time of each request, the host that issued
the request, the request method and URL pathname, the status code, the number of
bytes transferred, the referring page, and the user agent (typically a browser or spider
name). The following table includes columns for these values:
CREATE TABLE httpdlog
(
dt DATETIME NOT NULL , # request date
host VARCHAR ( 255 ) NOT NULL , # client host
method VARCHAR ( 4 ) NOT NULL , # request method ( GET , PUT , etc .)
url VARCHAR ( 255 ) # URL path
CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL ,
status INT NOT NULL , # request status
size INT , # number of bytes transferred
referer VARCHAR ( 255 ), # referring page