Skip navigation links
MySQL 5.1 Reference Manual :: 3 Tutorial :: 3.8 Using MySQL with Apache
4 MySQL Programs »
Section Navigation      [Toggle]

3.8. Using MySQL with Apache

There are programs that let you authenticate your users from a MySQL database and also let you write your log files into a MySQL table.

You can change the Apache logging format to be easily readable by MySQL by putting the following into the Apache configuration file: 

LogFormat \
        "\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\",  \
        \"%U\",\"%{Referer}i\",\"%{User-Agent}i\""

To load a log file in that format into MySQL, you can use a statement something like this: 

LOAD DATA INFILE '/local/access_log' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'

The named table should be created to have columns that correspond to those that the LogFormat line writes to the log file.


User Comments

Posted by Adrian Ariza on December 18 2002 3:27pm[Delete] [Edit]


What do you thing of doing that:

Change httpd.conf:

LogFormat "INSERT INTO access_log
(remote_ip,remote_logname,servername,remote_us
er,datetime,status,bytes_sent,content_type,url_requ
ested,referer,user_agent) VALUES ('%h','%l','%v','%
u',%{%Y%m%d%H%M%S}t,%>s,'%B', '%
{Content-
Type}o','%U','%{Referer}i','%{User-Agent}i');"
mysql

CustomLog "|mysql -hhost -uuser -ppass database"
mysql

Mysql Table:

CREATE TABLE access_log
(remote_ip CHAR(15) NOT NULL,
remote_logname VARCHAR(20) NOT NULL,
servername VARCHAR(20) NOT NULL,
remote_user CHAR(10) NOT NULL,
datetime DATETIME NOT NULL,
status SMALLINT NOT NULL,
bytes_sent INT,
content_type VARCHAR(50),
url_requested VARCHAR(250),
referer VARCHAR(250),
user_agent VARCHAR(250),
INDEX (datetime))


Posted by [name withheld] on May 19 2003 9:38pm[Delete] [Edit]

Also, the mysql password wil show up in the process list (ps -aux), bad idea

/thomas

Posted by Mike Nix on June 9 2003 11:02pm[Delete] [Edit]

How about:
LogFormat "pass\nINSERT INTO ....

CustomLog "|mysql -hhost -uuser -p database" mysql

I haven't checked if mysql asks for password on stdin or stderr - you might need to try

CustomLog "|mysql -hhost -uuser -p database 2>&1" mysql

Posted by [name withheld] on October 22 2003 11:59pm[Delete] [Edit]

I tested it on redhat7.3/apache 1.3.28 and it work very well
whay this function doesn't work on redhat 9 with default apache 2?

what module must be enabled for apache 2 to have log in a mysql database?

Posted by [name withheld] on December 6 2003 8:19am[Delete] [Edit]

Although it may work like a charm, what happens if you unleash this on a server that receives a lot of traffic? I'd say having Apache open up a MySQL connection on *every* hit is going to do you much good in terms of server load.

A better route may be using a script to write data to your database and have Apache pipe the log through that. But this will still mean a bigger load on the server, so post-processing the logs (i.e., after they've been rotated) is a much better idea.

Posted by Marcus Taylor on August 24 2005 1:03pm[Delete] [Edit]

Be aware that the example above does not escape mysql control characters. Referrers can be and frequently are forged, and so could readily contain the ' character which would break the INSERT statement in Adrian's example. If the referrer also contained something like "')VALUES((..,..,)_;DELETE FROM tablename;" (or similar) you'd be the victim of SQL injection. Hard-coding SQL into a log file would also make the logfile huge and given it's repeating the same thing over and over, is not recommended. Far better to store the raw fields and process via an external script.

Posted by Bauke Scholtz on October 10 2005 10:05pm[Delete] [Edit]

Reply on Marcus Taylor: indeed, the ' will break down the query and even the MySQL logging will die. But swapping the ' and the " in the query, the query will just continue without any SQL injection risk. The " itself in an URL will be encoded to %22 while the ' will not be encoded whatsoever.

I'm using this logformat for weeks successfully since I found out that an ' in the URL killed the MySQL logging:

LogFormat 'INSERT INTO balusc VALUES ("%{%Y-%m-%d %X}t","%a","%u","%s","%X","%m","%U","%B","%T","%{User-Agent}i","%{Referer}i");' mysql

Posted by Jose Stefan on December 5 2005 7:44am[Delete] [Edit]

In Reply To Bauke Scholtz, I'd like to add the following:
"For security reasons, starting with Apache 2.0.46, non-printable and other special characters are escaped mostly by using \xhh sequences, where hh stands for the hexadecimal representation of the raw byte. Exceptions from this rule are " and \ which are escaped by prepending a backslash, and all whitespace characters which are written in their C-style notation (\n, \t etc)."
http://httpd.apache.org/docs/2.0/mod/mod_log_config.html#formats

So exchanging the quotes is the way to go. Also, I'd like to suggest the use of INSERT DELAYED:
http://dev.mysql.com/doc/refman/5.0/en/insert-delayed.html

Personally I use:
INSERT DELAYED IGNORE INTO ...

Posted by Paul Ilardi on January 29 2006 6:08am[Delete] [Edit]

Bauke's suggestion for using " instead of ' worked for me too, until I got the following log entry:

69.183.8.117 - - [28/Oct/2005:06:51:09 -0400] "GET /become-a-teacher/About_Page1.htm HTTP/1.1" 200 14171 "http://search.yahoo.com/bin/search?p="Grand%20Central%20%20Discovery"" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; YPC 3.0.1;SV1; yplus 4.1.00b)"

Notice the " is preserved in the referer url. I didn't think that was possible.

Add your own comment.