Unifix knowledge database - The original one

Dashboard - News - Search - List - About - Admin


Apache logfiles in MySQL

Author:mike - Wed Feb 11 12:20:57 2004

The database:
Database changed
mysql> describe weblog;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| ID          | int(11)      |      | PRI | NULL    | auto_increment |
| remotehost  | varchar(255) |      |     |         |                |
| virtualhost | varchar(255) |      | MUL |         |                |
| path        | varchar(255) |      |     |         |                |
| agent       | varchar(255) |      |     |         |                |
| referer     | varchar(255) |      |     |         |                |
| status      | int(11)      |      |     | 0       |                |
| size        | int(11)      |      |     | 0       |                |
| request     | varchar(255) |      |     |         |                |
| time        | int(11)      |      |     | 0       |                |
+-------------+--------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)

mysql>


in httpd.conf where the CustomLog and LogFormat entries resides you need to add the followin:

LogFormat "%h#%v#%U#%{User-agent}i#%{Referer}i#%s#%b#%r" mysql
CustomLog "| /usr/bin/mysqllog.pl" mysql

and /usr/bin/mysql.log should be like this:

#!/usr/bin/perl
# script: mysqllog
use DBI;
use constant DSN => 'dbi:mysql:database';
use constant DB_TABLE => 'access_log';
use constant DB_USER => 'username';
use constant DB_PASSWD => 'password';

$db = DBI->connect(DSN,DB_USER,DB_PASSWD) || die DBI->errstr;
$sth = $db->prepare("INSERT INTO weblog (`remotehost`,`virtualhost`,`path`,`agent`,
`referer`,`status`,`size`,`request`,`time`) VALUES(?,?,?,?,?,?,?,?,?)")
|| die $db->errstr;

while (<>) {
chomp;
@dat=split("#");
$sth->execute(@dat[0],@dat[1],@dat[2],@dat[3],@dat[4],@dat[5],@dat[6],@dat[7],time(
));
}
$sth->finish;
$db->disconnect;
Copyright(c) Unifix.org 2002-2011


Dashboard - News - Search - List - About - Admin
 
Powered by Mikjaer Consulting Solutions - Hosted by Specialhosting.dk