Apache Access Logs

Centralising Apache logs can be useful in many scenarios. Let’s get the basics 1st. So Apache logs can be defined as

In order to effectively manage a web server, it is necessary to get feedback about the activity and performance of the server as well as any problems that may be occuring. The Apache HTTP Server provides very comprehensive and flexible logging capabilities.
Today we’ll setup Apache to log each access request to MySQL database rather than storing it in a flat file. Unlike logging to a flat text file, a SQL-based log exhibits tremendous flexibility and power of data extraction. Let’s dive in.

and Apache access logs can be defined as

The server access log records all requests processed by the server. The location and content of the access log are controlled by the CustomLog directive.

Today let’s see how to setup Apache to use MySQL to store the all the logs. Apache access logs will be sent to MySQL database through the Apache log module.

Prerequisites

MySQL Server and Apache should be installed on the Ubuntu instance.

Step 1

To setup Apache access logs to MySQL there is an Apache library to be installed which can be done via apt-get.

apt-get install libapache2-mod-log-sql-mysql

Step 2

Now let’s enable unique_id module.

a2enmod unique_id

You may ask why? Answer is it’s not necessary, but without it, mod_log_sql will leave the id column in the MySQL table for the access log empty. The id column will contain the unique request ID supplied by the standard Apache module mod_unique_id.

Step 3

Now log in the to MySQL console and create a database to store the Apache access logs.

create database apachelogs;

Step 4

Let’s create a user and grant privileges to store the Apache access logs to the database.

grant insert,create on apachelogs.* to loguser@localhost identified by 'loguser_pw';
flush privileges;

Step 5

Now add the below configuration to the VirtualHost where you need put the Apache access logs to the database. If you don’t know about VirtualHosts refer this post. Also note that if you put these lines as global it will affect all the VirtualHosts available in your Apache.

LogSQLTransferLogTable web_access_log
LogSQLLoginInfo mysql://loguser:loguser_pw@localhost/apachelogs
LogSQLCreateTables on
LogSQLDBParam socketfile /var/run/mysqld/mysqld.sock
LogSQLTransferLogFormat AbHhmRSsTUuvI

Finally restart Apache.

/etc/init.d/httpd restart

Tips and Tricks

If you want to log to a remote MySQL instance change the localhost to the particular IP address. LogSQLLoginInfo should be changed. Format is as follows

mysql:[user[!password]][@[host][:port]][/database]

Also make sure that the MySQL instance accepts remote connections as shown in this. Plus grant privileges to the MySQL user in the step 4 for remote access as shown here.

As given below in the usual scenario logs are stored in a scattered way. You can’t run analytics about the application since the logs are stored in 3 different places. But with the below setup logs are saved in a single MySQL database. Analytical queries such as how many requests came from what application, how the application load is distributed so on and so forth. All you have to do is use the above configuration in all three Apache instances.

So that’s it. If you have any questions let me know in the comments below. Your feedback is highly appreciated(happy-face).