Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.8Mb
PDF (A4) - 37.8Mb
PDF (RPM) - 36.4Mb
HTML Download (TGZ) - 9.8Mb
HTML Download (Zip) - 9.9Mb
HTML Download (RPM) - 8.6Mb
Man Pages (TGZ) - 209.5Kb
Man Pages (Zip) - 318.7Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  Tutorial  /  Using MySQL with Apache

3.7 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\",  \

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

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

What do you thing of doing that:

Change httpd.conf:

LogFormat "INSERT INTO access_log
ested,referer,user_agent) VALUES ('%h','%l','%v','%
u',%{%Y%m%d%H%M%S}t,%>s,'%B', '%

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

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,
bytes_sent INT,
content_type VARCHAR(50),
url_requested VARCHAR(250),
referer VARCHAR(250),
user_agent VARCHAR(250),
INDEX (datetime))

  Posted by on May 19, 2003
Also, the mysql password wil show up in the process list (ps -aux), bad idea

  Posted by Mike Nix on June 9, 2003
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 on October 22, 2003
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 on December 6, 2003
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
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
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
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)."

So exchanging the quotes is the way to go. Also, I'd like to suggest the use of INSERT DELAYED:

Personally I use:

  Posted by Paul Ilardi on January 29, 2006
Bauke's suggestion for using " instead of ' worked for me too, until I got the following log entry: - - [28/Oct/2005:06:51:09 -0400] "GET /become-a-teacher/About_Page1.htm HTTP/1.1" 200 14171 ""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.
  Posted by Vitold S on July 23, 2010
David I Fletcher write pipeline port for adding logs to MySQL:

look this ...
Sign Up Login You must be logged in to post a comment.