PurposeThere 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.
ObjectiveRedirect all the output to a user defined database (here MySql database) which is logged in "access.log" file by default.
Testing System ConfigurationThe test machine was running Ubuntu 9.10 with Apache2 and MySql5.
Requirements1.) For this example, you'll need-
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_sqlIf 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-mysqlthis 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 partyou 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 -pand 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 email@example.com identified by 'loguser_password'; FLUSH PRIVILEGES;where 'loguser_password' can be anything that you like the password to be.
Step3: Apache partDo 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_origWe renamed our old file to default-orig'
Creating new config fileOpen a new file-
sudo gedit /etc/apache2/sites-available/defaultAfter 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.
Conclusion/Known IssuesWell, 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.