After using Apache for a while, you are probably tired of “less’ing”/”grep’ing” your log files. You might want something a little more flexible and efficient. How about having the logs in a database table, one field per column? Now that would be an improvement. Several of your Virtual Hosts already use MySQL, and it’s about time it does something useful for you.

The answer is called mod_log_sql.

Here are some instructions you need to get it running on a freshly installed VPSLink CentOS-4-LAMP VPS.

1) VPSLink OS templates are updated regularly, but do it too, it can’t hurt:
yum update

2) Install the required software:
yum install httpd-devel mysql-devel make bzip2

3) Download the latest stable version of mod_log_sql (was released on 11/15/2006):
wget http://www.outoforder.cc/downloads/mod_log_sql/mod_log_sql-1.101.tar.bz2

4) Untar the archive and enter the directory:
tar xjf mod_log_sql-1.101.tar.bz2
cd mod_log_sql-1.101

5) Run the configure script:
./configure

6) Compile
gmake

7) Install
gmake install

8 ) Make sure MySQL is started
/etc/init.d/mysqld start

9) Connect to MySQL, create the apachelogs database, and use it (do I need to mention that you should set a MySQL root password?)
mysql
create database apachelogs;
use apachelogs;

10) Run the create_table.sql script (located in mod_log_sql/contrib)
source /path/to/mod_log_sql/contrib/create_tables.sql

11) Setup a user to access the apachelogs database;
grant insert,create on apachelogs.* to 'loguser'@'localhost' identified by 'password';
flush privileges;

12) Now configure Apache (/etc/httpd/conf/httpd.conf):
Add the following lines to the file (the loadmodule ones where the other modules are loaded, the logsql* ones where logformat directives are)
LoadModule log_sql_module modules/mod_log_sql.so
LoadModule log_sql_mysql_module modules/mod_log_sql_mysql.so

LogSQLLoginInfo mysql://loguser:password@localhost/apachelogs
LogSQLDBParam socketfile /var/lib/mysql/mysql.sock
LogSQLCreateTables on

For the sake of the example, you need to configure a virtualhost. The commented lines at the end of httpd.conf will do. Uncomment them, and set document root to /var/www/html.
Comment out ErrorLog and CustomLog lines, and add:
LogSQLTransferLogTable access_log
LogSQLTransferLogFormat AabcfHhIlMmPpRrSsTtUuvio

This last line sets what to log. For more info, refer to the project documentation: http://www.outoforder.cc/projects/apache/mod_log_sql/docs-2.0/

It should look like this:

ServerAdmin webmaster@dummy-host.example.com
DocumentRoot /var/www/html/
ServerName dummy-host.example.com
#ErrorLog logs/dummy-host.example.com-error_log
#CustomLog logs/dummy-host.example.com-access_log common
LogSQLTransferLogTable access_log
LogSQLTransferLogFormat AabcfHhIlMmPpRrSsTtUuvio

13) Finally restart apache.
/etc/init.d/httpd restart

14) Now access your IP from a web browser. It will default to your single virtualhost.

Here’s an example of what you will get in the table

select * from access_log \G

*************************** 1. row ***************************
id: -
agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.1.6) Gecko/20061201 Firefox/2.0.0.6 (Ubuntu-feisty)
bytes_sent: 5044
child_pid: 26239
cookie: -
machine_id: -
request_file: /var/www/error/noindex.html
referer: -
remote_host: 64.XXX.XXX.XXX
remote_logname: NULL
remote_user: -
request_duration: 0
request_line: GET / HTTP/1.1
request_method: GET
request_protocol: HTTP/1.1
request_time: [16/Aug/2007:13:23:48 -0700]
request_uri: /
request_args: NULL
server_port: 80
ssl_cipher: NULL
ssl_keysize: NULL
ssl_maxkeysize: NULL
status: 403
time_stamp: 1187295828
virtual_host: dummy-host.example.com
1 row in set (0.00 sec)

More information on the project page:
http://www.outoforder.cc/projects/apache/mod_log_sql/

Digg It! del.icio.us Bookmarks Stumble It! Google Bookmarks