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:                         

3 thoughts on “Save Apache log in MySQL database

  • September 28, 2020 at 19:11
    Permalink

    This looks like this invokes the mysql client for _each_ log entry (as opposed to the old mod_log_sql_mysql which, I’m assuming, had a single, open connection to the MySQL engine to avoid invocation of the mysql binary and authentication of username/password over and over). Do you have any observations about the extra load this places on the server? I’m wondering if it would be advantageous to make a daemon that just monitored a unix socket or a pipe and sent anything written to it to the DB. That way, Apache could just write log entries to the filesystem pipe.

    Reply
    • September 28, 2020 at 19:19
      Permalink

      You could save apache logs into Mysql database, only if you need to process in some way all the requests. Or you can choose a faster db engine for writing stuff.

      Reply

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.