Search the MySQL manual:
Subscribe to the monthly
MySQL Newsletter!

3.8 Использование MySQL совместно с Apache

Существуют программы, позволяющие проводить идентификацию пользователей с помощью базы данных MySQL, а также записывать журналы в таблицу MySQL.

Формат записи журналов Apache можно привести в легко понятную MySQL форму, введя в файл настроек Apache следующие строки: 

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

В MySQL же можно сделать примерно следующее: 

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

User Comments

Posted by Adrian Ariza on December 18 2002 5: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 September 5 2002 4:03am[Delete] [Edit]

very nice,
there's a spelling mistake: should be %{Content-Type}

Posted by [name withheld] on May 20 2003 1:38am[Delete] [Edit]

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

/thomas

Posted by Mike Nix on June 10 2003 3:02am[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 23 2003 3:59am[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 10: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.

Add your own comment.