Advanced Logging from Apache – using MySQL and mod_log_sql
The previous example describes how to log access requests to a file, in
this case, the access_ howtohostawebsite_net.log. There are many tools available on the net
that can allow you to analyse these logs.
However, I would recommend using a SQL database to log records. SQL is a language that is easy to use and
extremely powerful for creating queries and data mining.
If you plan to have any sort of web site that generates content based
on user requests, or other variables, then you will need some sort of database
anyway, and it makes sense to leverage that infrastructure for logging.
I would recommend using MySQL. Again, it’s a free database that is used all
over the internet by many web hosts.
Go for the Community Edition, that can be
downloaded from here:
http://www.mysql.com/products/database/mysql/community_edition.html
Ensure that you download the MySQL Administrator and MySQL Query Browser too. These
make it very easy to use the database and are essential utilities.
After installing MySQL to your web host use
the MySQL Query Browser to create a schema called
“apache”. A schema is a fancy name for
a subset of tables and information that makes up a database. You can have many schemas per database
server. In fact, MySQL
uses a schema to manage itself and its user accounts.
After you have created the apache schema, use MySQL
Administrator to create a user called Apache.
Then go to the “Schema Privileges” tab and ensure that this user only
has INSERT privileges allowed. Again,
this is to ensure that there are no security issues.
The next step is to extend Apache using a freely available module,
which will allow Apache to directly log to the SQL database. Luckily some nice people at http://www.outoforder.cc/projects/apache/mod_log_sql/
have provided this functionality and you can download this module in binary
form for Windows from http://opensource.tdc-llc.com/Projects/mod_log_sql/mod_log_sql.php
.
Download this and install it to your Apache programs directory.
Usually C:\Program Files\Apache Group\Apache2\modules
Then update your httpd.conf file with the
following:
# Put these
with the other LoadModule directives
LoadModule log_sql_module modules/mod_log_sql.dll
LoadModule log_sql_mysql_module modules/mod_log_sql_mysql.dll
# Add these
with the other logging directives. You
will want to comment out the logging to file directives.
LogSQLLoginInfo mysql://Apache:apple@localhost/apache
LogSQLCreateTables Off
LogSQLTransferLogFormat AbHhmRSsTUuv
# Update your virtual hosts
to include:
<VirtualHost
*:80>
…
LogSQLTransferLogFormat AbfHhmpRrSsTtUuV
LogSQLTransferLogTable access_log
</VirtualHost>
Remove or comment out (with a ‘#’) any
references to logging to a file.
Once you have updated this file, ensure that you stop and start the
Apache service.
You should now see access requests being logged to your MySQL databases.
Some basic SQL to generate statistics –
How many unique people (assume unique IP addresses are unique people) have
accessed each of my virtual hosts?
SELECT virtual_host,
COUNT(DISTINCT remote_host) FROM access_log a
GROUP BY virtual_host;
Estimate of bytes sent (does not take into account HTTP headers etc)
SELECT virtual_host,
SUM(bytes_sent) FROM access_log a
GROUP BY virtual_host;
How many people have come to each of my websites after a Google search?;
SELECT virtual_host,
COUNT(referer) FROM access_log WHERE referer LIKE '%google%' GROUP BY virtual_host;
As you can see, it is easy to extract information from logs using
SELECT queries once the logs are in an SQL database. Highly recommended.
|