使用sh格式化nginx访问日志并存入mysql

 

说明:记录的日志格式(字段有:ip,time,method,uri,http,code,datasize,head,postdata)

步骤:

1.设置nginx日志格式

2.使用sh脚本格式化日志,并存入mysql

3.新建mysql表格

4.验证

一.设置nginx日志格式:

a.打开nginx.conf

在http模块里面添加自定义日式格式:

$remote_addr>>>>$time_local>>>>$request>>>>$status>>>>$body_bytes_sent>>>>$http_referer>>>>$http_user_agent>>>>$request_body

b.设置vhost目录中对应的网站conf参数(引入以上log_format):

c.重启nginx

service nginx restart

二:使用sh脚本格式化日志,并存入mysql

a.新建sh脚本:

vi nginxlog.sh

#!/bin/sh


#获取nigin日志

log_path='/data/wwwlogs/kefu.xfz178.com.nginx_access_log'

cat $log_path | while read line
do
        if [ -n "$line" ]
        then
                        ip=${line%%>>>>*}
                        #echo $ip
                        leavestr=${line#*>>>>}

                        time=${leavestr% +0800>>>>*}

                        day=${time%%/*}
                        time=${time#*/}

                        month=${time%%/*}
                        time=${time#*/}

                        year=${time%%:*}
                        time=${time#*:}

                        time=${month}' '${day},' '${year}' '${time}
                        time=`date -d "$time" "+%Y-%m-%d %H:%M:%S"`
                        #echo $time
                        leavestr=${leavestr#*>>>>}

                        method=${leavestr% /*}
                        #echo $method
                        leavestr=${leavestr#*' '}

                        uri=${leavestr%% *}
                        #echo $uri
                        leavestr=${leavestr#*' '}

                        http=${leavestr%%>>>>*}
                        #echo $http
                        leavestr=${leavestr#*>>>>}

                        code=${leavestr%%>>>>*}
                        #echo $code 
                        leavestr=${leavestr#*>>>>}

                        datasize=${leavestr%%>>>>*}
                        #echo $datasize
                        leavestr=${leavestr#*>>>>}

                        url=${leavestr%%>>>>*}
                        #echo $url
                        leavestr=${leavestr#*>>>>}

                        head=${leavestr%%>>>>*}
                        #echo $head
                        leavestr=${leavestr#*>>>>}

                        postdata=${leavestr%%>>>>*}
                        #echo $leavestr

                        #插入数据库信息
                        HOSTNAME="127.0.0.1"                                           #数据库信息
                        PORT="3306"
                        USERNAME="toor"
                        PASSWORD="123456"

                        DBNAME="request_logs"                                       #数据库名称
                        TABLENAME="test"                                            #数据库中表的名称

                        insert_sql="insert into ${TABLENAME}(ip,time,method,uri,http,code,datasize,head,postdata) values('${ip}','${time}','${method}','${uri}','${http}','${code}','${datasize}','${head}','${postdata}')"
                        #insert_sql="insert into ${TABLENAME}(ip) values('${ip}')"
                        #echo $insert_sql   
                        mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} ${DBNAME} -e  "${insert_sql}"  2>/dev/null
    fi
done
#日志清除
echo  > "${log_path}"

b.设置定时任务

crontab -e

*/5 * * * * sh /sh/nginxlog.sh #日志转存

三:新建mysql表格

sql:

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ip` varchar(255) DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  `method` varchar(10) DEFAULT NULL,
  `uri` varchar(255) DEFAULT NULL,
  `http` varchar(50) DEFAULT NULL,
  `code` varchar(10) DEFAULT NULL,
  `datasize` int(5) DEFAULT NULL,
  `head` varchar(255) DEFAULT NULL,
  `postdata` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `ip` (`ip`) USING HASH,
  KEY `code` (`code`) USING HASH,
  KEY `usetime` (`datasize`) USING HASH,
  KEY `time` (`time`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=1823 DEFAULT CHARSET=utf8;

四:验证结果

备注(相关说明):

nginx log格式:

https://blog.csdn.net/feiniao8651/article/details/55670040

sh字符串截取(本文主要的sh格式化方式):

https://www.cnblogs.com/gaochsh/p/6901809.html

有任何问题联系:411200871