shell mysql 日志分析_shell分析日志导入数据库

1:mycomm.sh -> 数据库连接

#!/bin/sh

test_db=test

optest="mysql -hlocalhost -uroot -ptyu!@!*T2 --default-character-set=utf8 -N "

now_time=`date +%s`

p_list=`$optest -e "select concat(id,value) from forlist where status =0"` #shell中反引号里的是命令,shell首先执行该命令行，并以它的标准输出结果

for p in $p_list

do

echo 'p_id =' $p

done

2.mystat.sh ->读取日志awk分析后导入数据库

#!/bin/sh

source /usr/local/stat/shell/mycomm.sh

stime=`date +%F' '%H:%M -d "1 minute ago"`

etime=`date +%F' '%H:%M`

tmp_list="$0.list"

filename(){

if [ "`date +%M`" == "00" ]

then

day=`date +%F'-'%H -d '-1 hour'`

if test -e $log_path/sys.log-$day

then

sys_log="$log_path/sys.log-$day"

else

sys_log="$log_path/sys.log"

fi

else

sys_log="$log_path/sys.log"

fip

}

p_log(){

$optest stat -e "truncate table p_tmp"

$optest stat -e "truncate table p3_tmp"

log_path="/usr/local/stat/log"

filenamel

#1.写入

cat $log|awk -v vtime="$stime" -v wtime="$etime" -F '|' '{if(($1>=vtime)&&($1 $tmp_list

$optest stat -e "load data local infile '$tmp_list' into table xxx fields terminated by '|' (create_time,p_id,item_id,c_id,status)"

cat $tmp_list

#2.修改

cat $log|awk -v vtime="$stime" -v wtime="$etime" -F '|' '{if(($1>=vtime)&&($1 $tmp_list

$optest stat -e "load data local infile '$tmp_list' into table p_tmp fields terminated by '|' (update_time,item_id)"

$optest stat -e "update xxx a, yyy b set a.status=2, a.update_time=b.update_time where a.item_id=b.item_id"

cat $tmp_list

#3.修改

cat $log|awk -v vtime="$stime" -v wtime="$etime" -F '|' '{if(($1>=vtime)&&($1 $tmp_list

$optest stat -e "load data local infile '$tmp_list' into tablexxx fields terminated by '|' (update_time,item_id)"

$optest stat -e "update xxx a, yyy b set a.status=3, a.update_time=b.update_time where a.item_id=b.item_id"

cat $tmp_list

}

p_log  #执行

date