Log Apache to MySQL

From INIwiki
Jump to: navigation, search

Digg this!

Contents

[edit] For-word

Log Apache to MySql (mod_log_sql) This example is built on a base install on RHEL4 -UPDATE- These steps have been used on CentOS 5x successfully

To get started first see what we have installed

rpm -qa |grep mysql
rpm -qa |grep httpd

Make sure you see httpd-devel-* this allows you to use apache's apsx abilities (this rpm can be found on the install CD under RPMS or by yum install httpd-devel)

[edit] Download and Install

Check here for most current version

cd /usr/local/src
wget http://www.outoforder.cc/downloads/mod_log_sql/mod_log_sql-1.101.tar.bz2
tar fxjv mod_log_sql-1.101.tar.bz2
cd mod_log_sql-1.101
./configure
make
make install

[edit] apxs Error

This is the APache eXtenSion tool that is needed, on a Redhat based System (ie: CentOS) you need httpd development package. To install:

yum install httpd-devel

[edit] RPM Style Install

Add the DAG wieers repository

yum install mod_log_sql

[edit] Configure

Edit the httpd.conf file

vi /etc/httpd/conf/httpd.conf

Search for LoadModule insert the following underneath

LoadModule log_sql_module modules/mod_log_sql.so
LoadModule log_sql_mysql_module modules/mod_log_sql_mysql.so
<IfModule mod_ssl.c>
LoadModule log_sql_ssl_module moduels/mod_log_sql_ssl.so
</IfModule>

[edit] Setup MySQL for logging

In this example...
super user = root/password = supasskey
Apache Log User = alogger/password = apasskey
cd /usr/local/src/mod_log_sql-1.101/contrib/
mysql -uroot -p

Enter password: supasskey

mysql>

create database apachelogs;

mysql>

use apachelogs

Database changed mysql>

source create_tables.sql

mysql>

grant insert on apachelogs.* to alogger@localhost identified by 'apasskey';
flush privileges;

quit

Enable full logging of your MySQL daemon for trouble shooting

vi /etc/my.cnf
under the [mysqld] section add the following
log=/var/log/mysqld.log
 
service mysqld restart

Finish the httpd.conf configuration Insert the following under the LoadModule Rules you entered from above..

  • Change user name and password as needed
<IfModule mod_ssl.c>
LoadModule log_sql_ssl_module moduels/mod_log_sql_ssl.so
</IfModule>
LogSQLLoginInfo mysql://alogger:apasskey@/apachelogs
#LogSQLCreateTables on
LogSQLDBParam socketfile /var/lib/mysql/mysql.sock
#LogSQLDBParam port 3306

[edit] httpd.conf example

Here is an excerpt of my httpd.conf

#####
# Custom Modules ###
####
### START log_sql ###
LoadModule log_sql_module modules/mod_log_sql.so
LoadModule log_sql_mysql_module modules/mod_log_sql_mysql.so
### Use log_sql modules ###
<IfModule mod_ssl.c>
LoadModule log_sql_ssl_module moduels/mod_log_sql_ssl.so
</IfModule>
LogSQLLoginInfo mysql://alogger:apasskey@/apachelogs
#LogSQLCreateTables on
LogSQLDBParam socketfile /var/lib/mysql/mysql.sock
#LogSQLDBParam port 3306
### END log_sql ###

[edit] Virtual Hosts

Now apply logging to the virtual hosts -- this is just an example. (pay special attention to the two "LogSQLTransferLog...." lines.)

<VirtualHost *:80>
ServerAdmin sa@inisec.com
DocumentRoot /path/to/the/webpage
ServerName webpage.inisec.com
ErrorLog logs/webpage.inisec.com-error_log
CustomLog logs/webpage.inisec.com-access_log combined
LogSQLTransferLogTable access_log
LogSQLTransferLogFormat AbfHhlMmpRrSsTtUuv
</VirtualHost>

Restart Apache

service httpd restart


[edit] Testing

This works best where you can have multiple screens open at once (X)

[edit] Screen1:

tail -f /var/log/httpd/webpage.inisec.com-access_log

[edit] Screen 2:

tail -f /var/log/mysqld.log

[edit] Screen 3:

[edit] OPTION 1:

Simple Script to help check the table count without entering MYSQL Create file

vi /tmp/mysqlcount.sh

Insert the following (update paths if needed)

#! /bin/bash
mysql apachelogs <<EOFMYSQL
SELECT count(*) from access_log;
EOFMYSQL

Allow execute

chmod 777 /tmp/mysqlcount.sh

Execute to see count

/tmp/mysqlcount.sh

[edit] OPTION 2:

mysql -uroot -p

mysql>

use apachelogs;

mysql>

select count(*) from access_log;
+----------+
| count(*) |
+----------+
| 74 |
+----------+
1 row in set (0.00 sec)

When all is good remember to remove the logging out of the my.cnf

 /etc/my.cnf
delete the line of place a # in front to comment it out.
#log=/var/log/mysqld.log

You may also choose to stop the standard logging as well - I left it on for awstats.

[edit] Attachments/Sample Files

SQL modlog version 1.100 (.tar.bz2)

[edit] Additional Notes

Log flag definitions: http://www.outoforder.cc/projects/apache/mod_log_sql/docs-2.0/#Conf.LogSQLTransferLogFormat

Many thanks to Edward Rudd and Paul Querna from www.outoforder.cc

Personal tools