python过滤nginx access日志存入数据库中

1. 将nginx的access日志文件发送至日志服务器的514端口，在access_log下添加一行

    access_log  syslog:server=192.168.230.1,facility=local7,tag=nginx_access_log,severity=info;
   复制代码

2. 重启nginx与rsyslog

3. 编写python文件

    import socket
    import pymysql
    from  datetime import  datetime
    #连接数据库
    connect = pymysql.connect(
        host='192.168.230.119',
        port=3306,
        user='root',
        password='hc193955',
        database='nginxlog',
        charset="utf8"
    )
    #创建游标对象
    cursor = connect.cursor()
    print('连接数据库成功')
    sql = '''CREATE TABLE nginxlog(
        id int(10) primary key auto_increment,
        hostname varchar(40) not null default '',
        log_type varchar(40) not null default '',
        ip varchar(40) not null default '',
        time datetime,
        methods varchar(10) not null default '',
        sourceurl varchar(200) not null  default '',
        protocol varchar(20) not null default '',
        status varchar(10) not null default '',
        user_agent varchar(300) not null default''
    )'''
    
    
    try:
        #执行SQL语句
        cursor.execute(sql)
        #提交到数据库执行
        connect.commit()
        print("数据库表创建成功")
    except:
        print("数据库表创建失败")
    # 实例化
    sk = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    # 定义IP和端口
    ip_port = ("0.0.0.0", 514)
    #绑定监听
    sk.bind(ip_port)
    print("等待数据传入")
    
    while True:
        data = sk.recv(1024).decode()
        print(data)
        data_list = data.split()
        hostname = data_list[3]
        log_type = data_list[4]
        ip = data_list[5]
        time1 = data_list[8]
        time = time1.lstrip('[').replace(":", " ", 1)
        time = datetime.strptime(time, '%d/%b/%Y %H:%M:%S')
        methods = data_list[10]
        sourceurl  = data_list[11]
        protocol = data_list[12]
        status = data_list[13]
        user_agent = data_list[17:]
        user_agent = ' '.join(user_agent)
        print(user_agent)
        sql = '''insert into nginxlog(hostname, log_type, ip, time, methods, sourceurl, protocol, status, user_agent)
        values(%s, %s, %s, %s, %s, %s, %s, %s, %s);'''
        cursor.execute(sql,(hostname, log_type, ip, time, methods, sourceurl, protocol, status, user_agent))
        connect.commit()
   复制代码

4.结果如下