nginx日志导入mysql

http {
    log_format main '$remote_addr - $remote_user [$time_local] "$request" '
                    '$status $body_bytes_sent "$http_referer" '
                    '"$http_user_agent" "$http_x_forwarded_for"';
    access_log /var/log/nginx/access.log main;
}

import mysql.connector
import re

# 连接 MySQL 数据库
db = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

cursor = db.cursor()

# 定义日志格式正则表达式
log_pattern = re.compile(r'(\d+\.\d+\.\d+\.\d+) - (.+) \[(.+)\] "(GET|POST) (.+) HTTP/1\.1" (\d+) (\d+) "(.+)" "(.+)"')

# 读取 Nginx 日志文件
with open('/var/log/nginx/access.log', 'r') as log_file:
    for line in log_file:
        match = log_pattern.match(line)
        if match:
            ip, user, time, method, path, status, size, referer, user_agent = match.groups()
            query = "INSERT INTO nginx_logs (ip, user, time, method, path, status, size, referer, user_agent) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
            cursor.execute(query, (ip, user, time, method, path, status, size, referer, user_agent))

# 提交事务并关闭连接
db.commit()
cursor.close()
db.close()

CREATE TABLE nginx_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip VARCHAR(15),
    user VARCHAR(255),
    time DATETIME,
    method VARCHAR(10),
    path VARCHAR(255),
    status INT,
    size INT,
    referer VARCHAR(255),
    user_agent VARCHAR(255)
);