日志分析脚本

analysis.php

<?php
$file = fopen('a.log', 'r');
$i = 0;
$mysqli = new mysqli("ip", "username", "password", "database");

if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}
while(! feof($file)) {

        $a = [];
        $str = fgets($file);
        for($j=0; $j < 11; $j++) {
                $string = getNext($str);
                $a[] = $string;
        }
        $time = date('Y-m-d H:i:s', strtotime($a[3]));
        $requestArr = explode(' ', $a[4]);
        $upsteamTime = $a[6] == '-' ? 0 : $a[6];
        $insertSql = "INSERT INTO log (ip, host, server_addr, method, request_uri, http_v, request_time, upstream_time, ua, time, status, size) VALUES ('{$a[0]}', '{$a[1]}', '{$a[2]}', '{$requestArr[0]}', '{$requestArr[1]}', '{$requestArr[2]}', '{$a[5]}', '{$a[6]}', '{$a[10]}', '{$time}', '{$a[7]}', '{$a[8]}')";
        $mysqli->query($insertSql);
        $i++;
}
echo $i;
$mysqli->close();
fclose($file);

function getNext(&$string) {
        if($string === '') {
                return false;
        }
        if(preg_match('/^"/', $string)){
                $string = ltrim($string, '"');
                $arr = explode('" ', $string, 2);
                if(count($arr) < 2){
                        $arr = explode('"', $string, 2);
                }
                if(isset($arr[1])){
                        $string = $arr[1];
                }
                if(isset($arr[0])) {
                        return $arr[0];
                }
                return false;
        }
        if(preg_match('/^\[/', $string)){
                $string = ltrim($string, '[');
                $arr = explode('] ', $string, 2);
                if(isset($arr[1])){
                        $string = $arr[1];
                }
                if(isset($arr[0])) {
                        return $arr[0];
                }
                return false;
        }

        $arr = explode(' ', $string, 2);
        if(isset($arr[1])){
                $string = $arr[1];
        }
        if(isset($arr[0])) {
                return $arr[0];
        }
        return false;
}

log.sql

CREATE TABLE `log` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ip` varchar(15) DEFAULT NULL,
  `host` varchar(256) DEFAULT NULL,
  `server_addr` varchar(512) DEFAULT NULL,
  `method` varchar(10) DEFAULT NULL,
  `request_uri` varchar(512) DEFAULT NULL,
  `http_v` varchar(20) DEFAULT NULL,
  `request_time` decimal(10,3) DEFAULT NULL,
  `upstream_time` decimal(20,3) DEFAULT NULL,
  `ua` varchar(512) DEFAULT NULL,
  `time` timestamp NULL DEFAULT NULL,
  `status` int(15) DEFAULT NULL,
  `size` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3990464 DEFAULT CHARSET=utf8mb4;

log_format.conf

log_format  wlb_log  '$remote_addr $host $server_addr [$time_local] "$request" $request_time "$upstream_response_time" $status $body_bytes_sent "$htt
p_referer" "$http_user_agent"';