Apache webserver can be configured to save access logs into database via Apache Module mod_log_config.

This module provides for flexible logging of client requests. Logs are written in a customizable format, and may be written directly to a file, or to an external program. Conditional logging is provided so that individual requests may be included or excluded from the logs based on characteristics of the request.

“LogFormat” directive specifies the format of the access log file.

Syntax:

CustomLog file|pipe format|nickname [env=[!]environment-variable]

Example:

<IfModule mod_log_config.c>
LogFormat "INSERT INTO apache_logs \
set ip='%h',\
datetime='%{%Y-%m-%d %H:%M:%S}t',\
status='%>s',\
bytes_sent='%B',\
content_type='%{Content-Type}o',\
url_requested='%r',\
user_agent='%{User-Agent}i',\
referer='%{Referer}i';" \
mysql_custom_log

# execute queries
CustomLog "|/usr/bin/mysql -h 127.0.0.1 -u log_user -plog_pass apache_logs" \
mysql_custom_log
# save queries to log file
CustomLog logs/mysql_custom_log mysql_custom_log
</IfModule>
# End of Log directives.

This example save into database: IP, date, http status, sent bytes, content type, requested url, user agent, referer;

Mysql table:

mysql> desc apache_logs;
+---------------+--------------+------+-----+---------------------+-------+
| Field         | Type         | Null | Key | Default             | Extra |
+---------------+--------------+------+-----+---------------------+-------+
| ip     | varchar(20)  | YES  |     | NULL                |       |
| datetime      | datetime     | NO   | MUL | 0000-00-00 00:00:00 |       |
| status        | smallint(6)  | NO   | MUL | 0                   |       |
| bytes_sent    | int(11)      | YES  |     | 0                   |       |
| content_type  | varchar(20)  | YES  | MUL | NULL                |       |
| url_requested | varchar(255) | YES  |     | NULL                |       |
| user_agent    | varchar(100) | YES  | MUL | NULL                |       |
| referer       | varchar(255) | YES  |     | NULL                |       |
+---------------+--------------+------+-----+---------------------+-------+
8 rows in set (0.00 sec)

mysql>

 

Save Apache log in MySQL database
Tagged on:                         

Leave a Reply

Your email address will not be published. Required fields are marked *