SFTP用户登录日志实时过滤与MySQL入库的技术实现求助

嗨，我看你已经开启了SFTP登录日志到/var/log/syslog，但在过滤目标日志行、提取关键信息以及实时入库MySQL这些环节遇到了麻烦，别担心，咱们一步步来搞定：

一、精准过滤目标登录日志行 #

你需要匹配的是类似Started Session 11907571 of user ftpuser1的行，这里会话号是随机数字，用户名也不固定，咱们用正则表达式配合grep就能精准捕获：

grep -E "Started Session [0-9]+ of user [a-zA-Z0-9_]+" /var/log/syslog

解释下这个命令：

• -E 启用扩展正则表达式
• [0-9]+ 匹配任意长度的数字（会话号）
• [a-zA-Z0-9_]+ 匹配合法的用户名（字母、数字、下划线）

这样就能把所有用户登录的会话启动日志筛选出来了。

二、提取并格式化关键信息（日期时间、用户名） #

筛选出目标行后，咱们需要提取日期时间和用户名，还要把日期转成MySQL兼容的格式（YYYY-MM-DD HH:MM:SS），用awk和date命令就能搞定：

grep -E "Started Session [0-9]+ of user [a-zA-Z0-9_]+" /var/log/syslog | while read -r line; do
    # 提取原始日期时间（比如 Jun 23 15:47:13）
    raw_datetime=$(echo "$line" | awk '{print $1" "$2" "$3}')
    # 提取用户名（行的最后一个字段）
    username=$(echo "$line" | awk '{print $NF}')
    # 转成MySQL支持的日期格式
    mysql_datetime=$(date -d "$raw_datetime" +"%Y-%m-%d %H:%M:%S")
    # 这里可以先打印测试，确认格式正确
    echo "登录时间：$mysql_datetime，用户名：$username"
done

三、将数据插入MySQL数据库 #

首先得先在MySQL里创建对应的存储表，比如：

CREATE TABLE sftp_logins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(20) NOT NULL UNIQUE, -- 会话号作为唯一键，避免重复插入
    login_time DATETIME NOT NULL,
    username VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

注意：加上session_id并设为唯一键，可以防止重复插入同一条日志（比如日志轮转时重复处理）

然后修改上面的脚本，加入插入数据库的逻辑。为了安全，建议把MySQL的认证信息放在~/.my.cnf文件里（权限设为600，避免其他用户读取）：

[client]
user=你的数据库用户名
password=你的数据库密码
database=sftp_db -- 替换成你的数据库名

修改后的入库脚本：

tail -F /var/log/syslog | grep -E "Started Session ([0-9]+) of user ([a-zA-Z0-9_]+)" | while read -r line; do
    raw_datetime=$(echo "$line" | awk '{print $1" "$2" "$3}')
    username=$(echo "$line" | awk '{print $NF}')
    session_id=$(echo "$line" | awk '{print $5}') -- 提取会话号
    mysql_datetime=$(date -d "$raw_datetime" +"%Y-%m-%d %H:%M:%S")
    
    # 插入数据库，用INSERT IGNORE避免重复键错误
    mysql -e "INSERT IGNORE INTO sftp_logins (session_id, login_time, username) VALUES ('$session_id', '$mysql_datetime', '$username');"
done

四、做成守护进程，实现实时监控 #

要让脚本一直运行，实时处理新产生的日志，咱们可以把它做成systemd服务，实现开机自启：

1. 把上面的脚本保存为/usr/local/bin/sftp-logger.sh，并赋予执行权限：

chmod +x /usr/local/bin/sftp-logger.sh

2. 创建systemd服务文件/etc/systemd/system/sftp-logger.service：

[Unit]
Description=实时监控SFTP登录日志并写入MySQL
After=mysql.service network.target

[Service]
User=root -- 需要读取syslog的权限，一般用root就行
ExecStart=/usr/local/bin/sftp-logger.sh
Restart=always -- 进程意外退出时自动重启
RestartSec=5

[Install]
WantedBy=multi-user.target

3. 启动并启用服务：

systemctl daemon-reload
systemctl enable sftp-logger.service
systemctl start sftp-logger.service

这样脚本就会作为守护进程一直运行，新的登录日志产生时会自动提取并插入数据库啦！

一些额外的小提醒 #

• 确保脚本有读取/var/log/syslog的权限，一般root用户没问题
• 如果syslog有轮转（比如用logrotate），用tail -F而不是tail -f，它会自动跟踪日志文件的变化
• 可以在脚本里加入错误日志记录，比如把错误信息写入/var/log/sftp-logger-error.log，方便排查问题

备注：内容来源于stack exchange，提问作者Amal P Ramesh