nginx日志收集储存到mysql

有个需求就是收集日志并分析就搞了个这个首先必须要python3以及 pymysql库
我这边是centos7就用yum安装python3 以及 pymysql库

yum install python3
pip3 install pymysql

然后就是数据库了

这个数据表需要复制了存为sql导入创建好的数据库里面

-- phpMyAdmin SQL Dump
-- version 5.0.4
-- https://www.phpmyadmin.net/
--
-- 主机： localhost
-- 生成日期： 2021-03-27 11:53:10
-- 服务器版本： 5.7.33-log
-- PHP 版本： 7.1.33

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- 数据库： `nginxlog`
--

-- --------------------------------------------------------

--
-- 表的结构 `nginxlog`
--

CREATE TABLE `nginxlog` (
  `id` int(10) NOT NULL,
  `ip` varchar(128) NOT NULL DEFAULT '',
  `time` datetime DEFAULT NULL,
  `methods` varchar(10) NOT NULL DEFAULT '',
  `source` varchar(400) DEFAULT NULL,
  `protocol` varchar(10) NOT NULL DEFAULT '',
  `status` varchar(10) NOT NULL DEFAULT '',
  `user_agent` varchar(128) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- 转储表的索引
--

--
-- 表的索引 `nginxlog`
--
ALTER TABLE `nginxlog`
  ADD PRIMARY KEY (`id`);

--
-- 在导出的表使用AUTO_INCREMENT
--

--
-- 使用表AUTO_INCREMENT `nginxlog`
--
ALTER TABLE `nginxlog`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=211;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

python源码

#/usr/bin/env python
#-*-coding:UTF-8 -*-
from  datetime  import  datetime

stat_days = []
import   pymysql
#print(datetime.now().strftime("%Y-%m-%d %H:%M:%S")) #2018-05-25 22:23:44
#print(datetime.now().strftime("%d/%b-%Y %H:%M:%S")) #25/May-2018 22:23:44格式
#print(datetime.strptime('17/Jun/2017:12:11:16',"%d/%b/%Y:%H:%M:%S")) #格式转换
connect=pymysql.connect(
   host='127.0.0.1',
   port=3306,
   user='root',
   password='61a14abbb5fac222',
   database='nginxlog',
   charset="utf8"

)
cur = connect.cursor()
sql= "insert into nginxlog(ip,time,methods,source,protocol,status,user_agent) values(%s,%s,%s,%s,%s,%s,%s)"
def ng(line):
      _nodes = line.split()
      IP= _nodes[0]
      Time= _nodes[3][1:-1].replace(":"," ",1) #将时间转换为17/Jun/2017 12:43:4格式
      Time = datetime.strptime(Time,"%d/%b/%Y %H:%M:%S")#将时间格式化为2017-06-17 12:43:04
      Methods = _nodes[5][1:]
      Source = _nodes[6]
      Protocol = _nodes[7][:-1]
      Status = _nodes[8]
      user_agent = _nodes[11][1:]+_nodes[12][1:]+_nodes[13]+_nodes[14]+_nodes[15]+_nodes[16]
      print(IP,Time,Methods,Source,Protocol,Status,user_agent)
      cur.execute(sql,(IP,Time,Methods,Source,Protocol,Status,user_agent))
      connect.commit()

with open("/www/wwwroot/lingling/chifan.cb.log","r")  as  ngfile:
   for line in ngfile:
        try:
            ng(line)
        except:
            print("出现错误")
   connect.close()

执行的话就是