用shell 实现 apache日志入mysql oracle库

最新推荐文章于 2022-11-11 20:30:00 发布

xiaoyuezhong

最新推荐文章于 2022-11-11 20:30:00 发布

阅读量799

点赞数

文章标签： mysql apache oracle shell date table

本文链接：https://blog.csdn.net/xiaoyuezhong/article/details/3807403

版权

#!/bin/sh
################################################################
#
# Filename    : logParser.sh
# Author    : xiaocong
# Date        : 2009-01-15
# modifier     : xiaocong
# Date          :
#
#################################################################

#################################################################
# Config Area

home='pwd'
# LOG
HOME=######
HOMELOG=######
HOMELOGNAME=######
HOMESQL=#####

# DB
DBHOST=1####
DBUSER=####
DBPASS=####
DBPORT=######

#################################################################
# ASSIST
RUN_INTERVAL=300

function writeLog()
{
    str=$1;
    echo "[`date '+%Y-%m-%d %H:%M:%S'`] ${str}"
}
sign="','";
sigl="'";
siga="');/n";

#while [ 1 -eq 1 ]
#do
echo "-------------------- Information-----------------------------"
    echo $home
    BACKHOME="${HOME}/bak_sql"
    if [ ! -x ${BACKHOME} ];then
        echo " EXSIT ${BACKHOME} "
        echo " CREATE IT "
        mkdir ${BACKHOME}
    fi

    if [ -f ${HOME}/${HOMESQL} ];then
        echo " EXSIT ${HOME}/${HOMESQL}"
        echo " MOVE IT "
            TEMP_DATE=`date '+%Y-%m-%d%H:%M:%S'`
             BACKHOMESQL=${HOMESQL}${TEMP_DATE}
        mv -v ${HOME}/${HOMESQL} ${BACKHOME}/${BACKHOMESQL}
    fi
#    echo "--at `date`"
#    echo "-->>home=${HOME}"
#    echo "-->>backhome=${BACKHOME}"
#    echo "-->>backhomesql=${BACKHOME}${BACKHOMESQL}"
echo "-------------------------- end ----------------------------------------------"
echo "Please waiting minutes..."
####################################################################
# PARSER
    dx="'";
    ex="*";

    awk '{
    ax="insert into t_#####_logdata (guid,src_ip,src_href,start_time,user_agent) values(";
    bx="&";
    cx="~";
    gsub("'$dx'","'$ex'");
    gsub(bx,cx);
    if (length($0)<=2000)
    if ($7 !~/^.script|^.images|^.css|^.question|^.js|^.user|^.circle|^.swfs|^.options/)

    print substr(ax,0),"'$sigl'"$2"'$sign'"$1"'$sign'"$7"'$sign'"substr($4,2)"'$sign'"substr($8,1,length($8)-1)"'$siga'"}' ${HOMELOG}/${HOMELOGNAME} >> ${HOME}/${HOMESQL}
#####################################################################
#MYSQL

#    mysql -uroot -pbjdb@/)/)^ /
#         -e "use test; /
#    drop index host on log; /
#    drop index f2 on log; /
#    load data infile '/tmp/apache_visit_log' into table log fields terminated by ','; /
#    alter table log add index host (host);/
#    alter table log add index f2 (f2)"
#    writeLog "-->>program is running normal"

####################################################################
echo " Truncate table t_qeesoo_logdata"
# ORCALE SENTENCE
    sqlplus qeesoo_wh/qeesoo_wh <<EOF
    set feedback off;
    set pagesize 0;
#    select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
    truncate table t_qeesoo_logdata;
    commit;
    EOF

# ORCALE SQL FILE
echo " Parser Successed! "
echo " SQL file : ${HOME}/${HOMESQL} "
echo " Insert into DB "
echo " Please waiting minutes..."

    sqlplus qeesoo_wh/qeesoo_wh <<EOF
    set feedback off;
    set pagesize 0;
    @${HOME}/${HOMESQL}
    EOF

####################################################################

#done