Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
HOWTO: db logging for Apache cluster and stats
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks
View previous topic :: View next topic  
Author Message
fnjordy
n00b
n00b


Joined: 17 Feb 2005
Posts: 13

PostPosted: Mon Oct 03, 2005 12:32 pm    Post subject: HOWTO: db logging for Apache cluster and stats Reply with quote

This is going to be a bit brief, assuming you have Apache, MySql, and awstats setup and working (mini howto).

Tested software:
    Apache 2.0.54-r9
    MySQL 4.0.25-r2
    mod_log_sql 1.97
    mls2clf 0.07
    awstats 6.5


Extra recommended packages:

    geoip 1.3.12
    Geo-IP 1.25 (install via Gentoo cpan: g-cpan -i Geo::IP)
    Net-XWhois (: g-cpan -i Net::XWhois)
    Storable (: g-cpan -i Storable)


Software on web server: apache, mysql, mod_log_sql
Software on database: mysql
Software on machine to generate stats: awstats, mls2clf

Step 1: install software
The easy bit

mls2clf can be picked up from bugzilla

Step 2: create tables in MySql
mod_log_sql can be configured to automagically create tables for ISP setups, however this how to covers a simple configuration.

>> documentation for mod_log_sql

Create a new database, and run the SQL:
Code:

# mysql -uadmin -pmypassword
Enter password:
mysql> create database apachelogs;
mysql> use apachelogs
Database changed
mysql> source create_tables.sql

Optimise the tables for retrieval using the following two SQL:
Code:

alter table access_log add index(time_stamp)
alter table access_log add index(virtual_host)

It is recommended to create two users, one with permissions to insert into the database for mod_log_sql, and another with select permissions to read from it for awstats. You can create the user and setup using phpmyadmin.

Step 3: configure mod_log_sql
The ebuild places a module config file in /etc/apache2/conf/modules.d/10_mod_log_sql.conf edit this and change the ISP features and database login as appropriate.
Code:

LogSQLMassVirtualHosting off
LogSQLCreateTables off

LogSQLLoginInfo mysql://apachelogs:apachelogspassword@db/apachelogs

Restart Apache and confirm its all working
Code:

# /etc/init.d/apache restart


Step 4: configure mls2clf
A binary version of make_combined_log.pl that ships with mod_log_sql, that outputs in Apache log format:

>> sf project page for mls2clf

The ebuild places a configuration file in /etc/mls2clf.conf, change the values for your database:
Code:

# /etc/mls2clf.conf
# mls2clf database information
#
# note that this file should not be world readable!

username="awstats"
password="awstatspassword"
hostname="db"
database="apachelogs"
tablename="access_log"

If you run ml2clf on the command line you should see some log output.

Step 5: configure awstats
Now we re-configure awstats so that it takes the log file data direct from the mls2clf program, for a setup that runs on crontab we can create a script /etc/cron.daily/awstats that pulls the log for each hosted domain.
Code:

#!/bin/sh

/usr/share/webapps/awstats/6.5/hostroot/cgi-bin/awstats.pl -config=www.oranges.com -update
/usr/share/webapps/awstats/6.5/hostroot/cgi-bin/awstats.pl -config=www.apples.com -update
/usr/share/webapps/awstats/6.5/hostroot/cgi-bin/awstats.pl -config=www.pears.com -update

In /etc/awstats we copy awstats.model.conf to awstats.www.oranges.com.conf and update similarly:
Code:

LogFile="/usr/local/bin/mls2clf --vhost=www.oranges.com --time=86400 |"
LogFormat="%virtualname %host %other %logname %time1 %methodurl %code %bytesd %refererquot %uaquot"
DNSLookup=1
SiteDomain="www.oranges.com"
DirData="/www/awstats/www.oranges.com"

Some extra tweaking to support UTF-8 URLs, whois links, and country codes:
Code:

LoadPlugin="decodeutfkeys"
LoadPlugin="geoip GEOIP_STANDARD /usr/share/GeoIP/GeoIP.dat"
LoadPlugin="hostinfo"
LoadPlugin="hashfiles"

And if you want to ignore internal hosts, modify the SkipHosts with regular expressions as needed:
Code:

SkipHosts="REGEX[^10\.]"
Back to top
View user's profile Send private message
fnjordy
n00b
n00b


Joined: 17 Feb 2005
Posts: 13

PostPosted: Tue Oct 18, 2005 9:55 am    Post subject: Reply with quote

oops, fixed the LogFormat, mls2clf adds the virtual host as the first field.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum