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.結果如下