分析Nginx日志并存入MySQL

发表于 分类于 Waline： 本文字数： 2k 阅读时长 ≈ 7 分钟

需求

我的网站托管在VPS上，使用Nginx提供服务。Nginx的日志中大多数是搜索引擎爬虫和DNS服务器的访问记录，真实用户的只占一小部分。我想把真实用户的访问记录提取出来，这样便能获得比Google Analytics更详细的统计信息。

为了便于管理和检索，不能像以前那样用文本保存了，必须要把它存放在数据库中。本文介绍如何用Python分析Nginx日志，从文本中提取感兴趣的信息，然后存入到MySQL数据库中。

日志过滤

日志格式

Nginx的日志默认存放在/var/log/nginx目录下，access.log文件是当天的访问记录。Nginx每天定时将access.log压缩，文件名加上日期信息，这便是目录下.gz后缀的文件，然后access.log文件会归零，重新记录。为了防止日志占用太多空间，会定期清理旧的日志文件，在我的服务器上，只保存最近十天的日志。

日志的每一行是一次访问的记录，遵从特定的格式，默认的格式配置如下：

1
2
3
log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                  '$status $body_bytes_sent "$http_referer" '
                  '"$http_user_agent" "$http_x_forwarded_for"';

下面是一次访问在日志中的记录内容（为保护用户隐私，已修改IP），请把各字段对号入座：

1
1.1.1.1 - - [18/Apr/2019:01:57:50 +0800] "GET / HTTP/1.1" 200 65567 "-" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/45.0.2454.93 Safari/537.36" "-"

下面是我感兴趣的内容：

  • remote_addr：访问者的IP地址
  • time_local：访问时间
  • request：HTTP请求类型
  • status：HTTP状态码
  • http_user_agent：简称UA，包含用户信息

我用Python语言进行文本处理，从一行访问记录中提取出上述感兴趣的信息。接下来是重要的内容过滤。

内容过滤

真实用户的有效访问有如下几个特征：

  1. HTTP状态码为200，判断status字段即可
  2. HTTP方法为GET，从request字段中提取HTTP方法即可
  3. User-Agent表明为浏览器访问

HTTP状态码和HTTP方法这两个过滤条件非常简单，这里就不多说。User-Agent主要用来过滤搜索引擎的爬虫和DNS服务器的访问，主要通过识别关键字完成，需用到正则表达式。正规爬虫的Agent一般都包含Bot、Spider、Crawler、Fetcher等关键词。下面是这个部分的实现代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
def agent_filter(agent):
    # 排除掉网络蜘蛛
    if re.search(r"[Bb]ot", agent):
        return False
    if re.search(r"[Ss]pider", agent):
        return False
    if re.search(r"[Cc]rawler", agent):
        return False
    if re.search(r"[Ff]etcher", agent):
        return False
    # 排除掉DNS服务器
    if re.search(r"DNS", agent):
        return False
    # 排除掉空的UA
    if re.search(r"^\-", agent):
        return False
        
    return True

上面的方法只能过滤掉知名的、容易辨别的网络爬虫，其它的诸如RSS订阅器的零星爬取，需要仔细甄别，并添加额外的规则。为了减小工作量，我用IP过滤的方法排除它们。一般来讲，爬虫有个特点，只抓取HTML，不抓取JavaScript代码。我的实现方法是，将通过前面过滤条件的记录保存在一个list中，遍历整个表，将抓取JavaScript的IP看作是正常的浏览器用户，保存到一个set中。再将list遍历一次，只保留set中的IP地址的访问记录。部分代码如下：

1
2
3
4
5
6
7
8
9
valid_user = set()
for i in range(len(rlist)):
    url = rlist[i][2]
    addr = rlist[i][0]
    runjs = re.search(r"^\/js\/.*\.js", url) or \
            re.search(r"^\/lib\/.*\.js", url)
if runjs:
    if addr not in valid_user:
        valid_user.add(addr)

通过上面UserAgent和IP过滤的方法，几乎能排除全部的爬虫访问，但也有漏网之鱼。我在日志中发现，偶尔有来自同一网段的多个IP的访问，而且都请求了JavaScript文件。在ipip.net上查询它们的地理位置，发现一般都是国内的机房。要排除这种类型的用户，要用更高级的方法。考虑到它们伪装得这么辛苦，就暂时先放它们一马，这点误差可以容忍，毕竟连Google Analytics都经常识别不出爬虫。

一般来讲，还要排除特定IP的访问，比如自己的IP，方法和User-Agent的类似，就不多说了。

我将这部分代码命名为filter.py，输入为Nginx的日志，输出打印格式化的访问记录。完整代码在这里，这部分代码的实现需根据自己的情况进行适配。

数据库

数据库软件我选择最常用的MySQL。首先用如下的SQL语句在数据库中创建如下的数据表（Table）：

1
2
3
4
5
6
7
8
9
10
CREATE DATABASE IF NOT EXISTS website;

USE website;

CREATE TABLE IF NOT EXISTS record (
    count INT(32) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    addr VARCHAR(20) NOT NULL,
    time DATETIME(0) NOT NULL,
    page VARCHAR(128) NOT NULL
);

插入记录的SQL语句如下：

1
2
3
INSERT INTO record (addr, time, page) 
VALUES ('1.2.3.4', NOW(), '/about/'),
       ('5.6.7.8', NOW(), '/series/');

现在要用Python操作数据库，我用的是MySQLdb模块。由于Nginx的时间格式和MySQL的默认格式不一样，要做格式转换。该过程的代码文件命名为mysql.py，将filter.py的输出作为输入，将数据插入到数据库中。代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
#!/usr/bin/python3
 
import sys
import datetime
import MySQLdb
 
def time_format(t):
    dt = datetime.datetime.strptime(t, '%d/%b/%Y:%H:%M:%S')
    return str(dt)

def insert_record(db, cursor, addr, time, page):
    sql = "INSERT INTO record (addr, time, page) \
           VALUES ('%s', '%s', '%s')" % (addr, time, page)
    try:
        cursor.execute(sql)
        db.commit()
    except:
        db.rollback()

def run():
    db = MySQLdb.connect('localhost', 'username', 'password', 'dbname', charset = 'utf8')
    cursor = db.cursor()

    if len(sys.argv) != 2:
        print("Usage: ./mysql.py record.log")
        sys.exit()
    fin = open(sys.argv[1], 'r')

    while True:
        line = fin.readline()
        if not line:
            break
        splits = line.split()
        addr = splits[0]
        time = time_format(splits[1])
        page = splits[2]
        insert_record(db, cursor, addr, time, page)

    db.close();

if __name__ == "__main__":
    run()

定时任务

最好让整个过程自动化，步骤依次为：

  1. Nginx每天的特定时刻将access.log打包为.gz文件，文件名中包含当天的日期，以今天为例，20190425
  2. 将压缩日志从Nginx的目录拷贝到当前目录，例如文件名为access.log-20190425.gz
  3. 解压文件，得到access.log-20190425
  4. 过滤日志，得到格式化输出，运行./filter.py access.log-20190425 > record.log-20190425
  5. 将访问记录插入到MySQL中，运行./mysql.py record.log-20190425
  6. 删除处理完的文本文件

由于Shell的语法过于恶心，就不奉陪了。我用Python的os模块运行命令，文件命名为cmd.py，并将其他的脚本文件和cmd.py放到同一个目录中。cmd.py的内容如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
#!/usr/bin/python3
 
import os
import datetime

log_dir = '/var/log/nginx/'
current_dir = '/root/script/traffic/'

today = datetime.datetime.now().strftime("%Y%m%d")

gzfile = log_dir + 'access.log-' + today + '.gz'

# cp here
cp_cmd = 'cp ' + gzfile + ' ' + current_dir
# unzip
nowgzfile = current_dir + 'access.log-' + today + '.gz'
unzip_cmd = 'gunzip ' + nowgzfile
# filter
logfile = current_dir + 'access.log-' + today
recordfile = 'record.log-' + today
filter_cmd = current_dir + 'filter.py ' + logfile + ' > ' + recordfile
# save to mysql
sql_cmd = current_dir + 'mysql.py ' + recordfile
# rm files
rm_cmd = 'rm ' + recordfile + ' ' + logfile

os.system(cp_cmd)
os.system(unzip_cmd)
os.system(filter_cmd)
os.system(sql_cmd)
os.system(rm_cmd)

在我的服务器上，Nginx每天凌晨04:00左右打包access.log文件。我需要在每天凌晨04:00自动运行上面的cmd.py脚本，运行crontab -e命令添加如下的定时任务：

1
00 04 * * * /root/script/traffic/cmd.py

这样，网站每天的访问记录就会自动添加到MySQL数据库中了。我用下面的SQL语句查询了昨天的访问记录：

1
2
3
4
SELECT addr, time, page FROM record
WHERE time BETWEEN
STR_TO_DATE('2019-04-24 00:00:00', '%Y-%m-%d %H:%i:%s') AND
STR_TO_DATE('2019-04-24 23:59:59', '%Y-%m-%d %H:%i:%s');

页面访问量和Google Analytics的统计相差不大。

总结

其实本文就是用简单的技术实现了自己的需求。数据库是非常重要的技术，而且实践性强，以前在工作中没机会接触，自己又找不到合适的数据库练手。从今天起，我的网站访问记录不仅会自动保存下来，还给我提供了一份非常不错的数据用来练手，这应该是最大的收获吧。