通过goaccess分析出nginx的访问量保存到数据库

两个文件在同一个目录

[root@minion246 242]# ls
cut-nginx.sh insert-mysql.sh

执行sh insert-mysql.sh

 

1、访问goaccess将结果进行分行

[root@minion246 242]# cat cut-nginx.sh

#!/bin/bash

 

#string=$(cat $1)

string=$(curl -s -u user:password  http://100.98.100.242:9999/index.html)

#对IFS变量 进行替换处理

OLD_IFS="$IFS" #保存当前shell默认的分割符，一会要恢复回去

IFS="," #将shell的分割符号改为，“”

array=($string) #分割符是“，”，"hello,shell,split,test" 赋值给array 就成了数组赋值

IFS="$OLD_IFS" #恢复shell默认分割符配置

 

for var in ${array[@]}

do

echo $var

done

 

2、对切分的文件处理获取需要的字段值并保存到数据库

[root@minion246 242]# more insert-mysql.sh

#!/bin/bash

id=$(date +%Y%m%d%H%M%S)

total_requests=$(sh cut-nginx.sh $1|grep -C 1 total_requests|tail -n 1)

valid_requests=$(sh cut-nginx.sh $1|grep -C 1 valid_requests|tail -n 1)

Unique_visitors=$(sh cut-nginx.sh $1|grep -C 1 unique_visitors|tail -n 1)

d=$(sh cut-nginx.sh $1|grep last-updated|awk -F \> '{print $2}')

 

echo "insert into nginx_table values("$id","$total_requests","$valid_requests","$Unique_visitors",'$d')"

mysql -uroot -h100.98.100.100 -P 3306 -ppwd -e "insert into nginx_info.nginx_table values($id,$total_requests,$valid_requests,$Unique_visitors,'$d')"

 

3、mysql表结构

CREATE TABLE `nginx_table` (

`id` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

`total_requests` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`valid_requests` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`unique_visitors` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`count_date` datetime(0) DEFAULT NULL

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

 

SET FOREIGN_KEY_CHECKS = 1;

 

4、数据结果显示