Hey
Let me share with you what I wrote for writing ErrorLog to both rotated file
and mysql database:
I didn't have a chance to test it on a life system yet but it looks fine. It
uses sh/perl/sed and rotatelogs apache binary.
------------------------------------------------------------------------------
httpd.conf:
ErrorLog "|/opt/CollabNet_Subversion/bin/logtomysql"
------------------------------------------------------------------------------
/opt/CollabNet_Subversion/bin/logtomysql:
#!/bin/sh
######################################################################
# Following settings are defined in .my.cnf file:
# bash-3.2$ ls -l /opt/apache2/sudoconf/.my.cnf
# -r-------- 1 root root 56 Jul 24 08:45 /opt/apache2/sudoconf/.my.cnf
######################################################################
#[client]
#user=loadlog
#host=127.0.0.1
#password=<password>
#####################################
# EDIT SETTINGS BELOW:
#####################################
#<EDIT>
MYCNF="/opt/apache2/sudoconf/.my.cnf"
MYSQL_HOST="localhost"
MYSQL_DB="svnlogs"
#Edit below to reflect user naming in your system:
HTTP_USER_REGEXP='([A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4})'
#</EDIT>
while read input
do
echo $input |/opt/CollabNet_Subversion/bin/rotatelogs -l
/var/log/httpd/error_log.%Y-%m-%d 86400
USR=`echo -n "$input"|perl -ne 'if(m/'"$HTTP_USER_REGEXP"'/) {print
"$1"}'`
INS=`echo -n "$input"|sed -e s/\'//g|perl -ne 'print if
s/\[(.*)\]\s\[(\w+)\]\s\[client\s([0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3})\]\s(.*)$/insert
into errorlog values(0, str_to_date('\''\1'\'','\''%a %b %d %H:%i:%s
%Y'\''), '\''\3'\'', '\''\2'\'', '\'''"$USR"''\'', '\''\4'\'');/'`
echo $INS |mysql --defaults-extra-file=$MYCNF -h $MYSQL_HOST
$MYSQL_DB
done
-----------------------------------------------------------------------------------------------------------------------------------------
Here is the sample DB:
DROP TABLE IF EXISTS `errorlog`;
CREATE TABLE IF NOT EXISTS `errorlog` (
`log_id` bigint(20) unsigned NOT NULL auto_increment,
`log_dtime` datetime default NULL,
`log_client` varchar(25) default NULL,
`log_level` varchar(12) default NULL,
`log_user` varchar(50) default NULL,
`log_entry` varchar(400) default NULL,
PRIMARY KEY (`log_id`),
KEY `log_dtime` (`log_dtime`,`log_client`,`log_user`),
KEY `log_level` (`log_level`),
FULLTEXT KEY `log_entry` (`log_entry`)
) ENGINE=MyISAM ;
Regards,
Leszek Szarlej
Received on 2010-07-24 16:23:40 CEST