Piped logs, writing error log to the mysql db and rotated file. another solution.

[Leszek Szarlej]

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