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.

Apache Access Logs to MySQL
Apache Access Logs to MySQL

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.

Step 2

Now let’s enable unique_id module.

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.

Step 4

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

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.

Finally restart Apache.

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

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.

Multiple Apache Servers

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

5 Comments

  1. SocialFeeds.Club September 7, 2015 at 6:20 pm

    Very nice to know! Thanks a lot for this information. Probably it is an idea to get some more information by adding some parameters to the Apache logs, if possible!

    (Used this to created statistics about our Social Feeds at http://SocialFeeds.Club/)

    Reply
  2. Mike February 3, 2016 at 2:00 pm

    Very easy instructions to follow, thank you. I was also wondering if it is possible to log to both the standard Apache log and to MySQL at the same time? I use Fail2Ban to check the logs and look for intrusion attempts, but I would also like to add the logs to a database automatically so I can examine them easier. Thank you.

    Reply
    1. Dasun Hegoda February 4, 2016 at 4:47 am

      Thanks. By the way frankly speaking I have tried this only. If you google I’m pretty sure you would find more resources.

      Reply
  3. Brian Adyatma July 10, 2019 at 8:18 am

    thank you for your knowledge. I have a problem, someone knows how to send the Apache log sent to the telegram. can anyone help me about sending apache logs to the telegram?

    Reply
  4. Brian Adyatma July 10, 2019 at 8:06 am

    thank you for your knowledge. I have a problem, someone knows how to send the Apache log sent to the telegram

    Reply

Leave A Comment

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.