3.8 Using MySQL with Apache

There are programs that let you authenticate your users from a MySQL database and also let you log 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\""

In MySQL you can do a variation of:

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

User Comments

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))

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

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

/thomas

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

Add your own comment.