Apache Access log mods : Redirecting logs to a MySql Database

Purpose

There are times when you require that your Apache server logs the request made to it, in a customized location. In my case, i wanted it to log all the incoming requests in a MySql database, so i made some experiments based on the things told by many people and yes, finally i got it. Read the full article for more details. I have tried to keep this article as short as possible, still have tried to make it understandable.

Objective

Redirect all the output to a user defined database (here MySql database) which is logged in "access.log" file by default.

Testing System Configuration

The test machine was running Ubuntu 9.10 with Apache2 and MySql5.

Requirements

1.) For this example, you'll need-
mod_log_sql
2.) Second thing,you'll need to define a database that will hold the table having access logs. I'll show you how.
3.) Lastly, what you need, is a modification to the default configuration file of Apache. I'll show you how to do it.

How Things Are Done : Step by Step


Step1: Installing mod_log_sql

If you don't have this installed by default, which i didnt have, use these procedures to get it installed
Open a terminal, and type-
sudo apt-get install libapache-mod-log-sql-mysql
this will install mod_log_sql.we need this in order to get things done.
now restart the apache server-
sudo /etc/init.d/apache2 reload

Step2: MySql part

you can use database creation from phpMyadmin as well, but i prefer using terminal, since so many things will be done using only terminal.
So open a terminal, type-
mysql -u root -p
and give your root password.
Now we'll create a user called "loguser" which will be updating tables, and we are going to grant CREATE, INSERT permissions to this user, sinse this user will be doing these things. Fire this command-
grant insert,create on apachelogs.* to loguser@localhost identified by 'loguser_password';
grant insert,create on apachelogs.* to loguser@localhost.localdomain identified by 'loguser_password';
FLUSH PRIVILEGES;
where 'loguser_password' can be anything that you like the password to be.

Step3: Apache part

Do the following things to create a virtual host of apache, a new virtual host actually.
This involves creating a separate default config file.
So first, we backup our own default config file.
Fire these commands-

Backing up old config file

sudo mv /etc/apache2/sites-available/default /etc/apache2/sites-available/default_orig
We renamed our old file to default-orig'

Creating new config file

Open a new file-
sudo gedit /etc/apache2/sites-available/default
After this, paste the following text in it-
LogSQLLoginInfo mysql://loguser:loguser_password@localhost/apachelogs
LogSQLCreateTables on
LogSQLDBParam socketfile /var/run/mysqld/mysqld.sock
LogSQLTransferLogFormat AbHhmRSsTUuvI

NameVirtualHost *
<VirtualHost *>
ServerAdmin webmaster@localhost

DocumentRoot /var/www/web1/web/
<Directory /var/www/web1/web/>
Options Indexes MultiViews
AllowOverride None
Order allow,deny
allow from all
</Directory>

LogSQLTransferLogTable web1_access_log
</VirtualHost> 
 
Update: I noticed, that if you edit your default config file and put these lines in it in the way that it doesn't create a problem, 
then you'll end up with apache logging on two places: one is the default log file, and the second will be your MySql database.
Cool, isn't it? 
 
after doing this, restart your apache-

sudo /etc/init.d/apache2 reload 
 
After this, make some requests to apache, and open the particular database that you had created, to see the changes.
We're done!

Conclusion/Known Issues

Well, there are some things you should be careful about-
1.) You'll end up creating a NEW virtual host, so any settings particular to the old virtual host, may not work
2.) I got several warnings, which meant that apache couldn't map the name localhost to the ip 127.0.0.1, so it used the real ip. means, i couldn't use localhost in my URL anymore.
3.) You should have root permissions to perform most of the things, so i prefixed a sudo before commands.
4.) This thing may not be suitable if your database isn't dedicated to the sole task you want this feature to work for, So if you want logging to be done in a file too, then try another methods available on the Internet.
Update: I devised another method. please look at the update of step3 Creating New Config File

For more information, you can refer to this page at How to Forge. But this article is optimized for Ubuntu, while How to Forge article focuses on Debian Etch Linux.
Happy moding!