技术

HFish 蜜罐数据库迁移实录:从 SQLite 到 MySQL 的踩坑与总结

目录
  1. 一、背景
  2. 二、问题定位
    1. SQLite 锁的本质
    2. 为什么 WAL 模式没根治
  3. 三、换数据库:为什么选 MySQL
  4. 四、迁移过程踩坑
    1. 1. 内存炸了(fetchall)
    2. 2. 时区格式不兼容
    3. 3. INSERT IGNORE 跳了 admin 密码
    4. 4. 建索引时表锁死锁
  5. 五、加索引后的性能对比
  6. 六、最终数据完整性
  7. 七、经验总结

一、背景

一台跑在京东云上的 HFish 蜜罐(2核 2GB),跑了大约 40 天,某天登录面板时提示「数据库错误」。SSH 上去一看,日志里铺天盖地的 database is locked 和 context deadline exceeded。

数据库是 HFish 默认的 SQLite,文件体积 1.5GB,里面 file_attack 表 211 万行。蜜罐 7×24 接收全球扫描,每秒几十条写入需求,全部怼在 SQLite 那唯一一个写入槽上,崩盘是迟早的事。

二、问题定位

SQLite 锁的本质

SQLite 的写操作是独占锁。即使开了 WAL 模式(Write-Ahead Logging),同一时刻仍然只有一个 writer。WAL 的好处是读不阻塞写、写不阻塞读,但当多个 goroutine 同时抢占写锁时:

  1. 先拿到锁的写入执行
  2. 后来的排队等待
  3. Go 代码里默认的 context deadline 是 5 秒
  4. 排队超过 5 秒 → context deadline exceeded

日志中第一次 database is locked 出现在 6 月 22 日 00:52,也就是数据库达到临界体积的时间点。到 6 月 30 日干预时,累计 3,525 条锁错误。

为什么 WAL 模式没根治

第一时间给 SQLite 开了 WAL:

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;

数据库锁错误从高频变成了偶发(35 条/h),但 context deadline exceeded 依然存在。原因不在锁,在全表扫描——1.5GB 的 SQLite 做一次 SELECT * FROM ip_profile WHERE ip=? 要扫几十万行。

三、换数据库:为什么选 MySQL

三个考量:

  1. HFish 原生支持 MySQL(配置文件里就有 [[database.mysql]] 连接串)
  2. 并发写入能力:MySQL/MariaDB 的行级锁 + MVCC 彻底解决写冲突
  3. 索引效率:InnoDB 的 B+ 树索引比 SQLite 在大表上快一个数量级

服务器 2GB 内存,装完整 MySQL 会吃 500MB+,用 MariaDB(Debian 12 默认源)内存占用控制在 200MB 左右。

四、迁移过程踩坑

1. 内存炸了(fetchall)

第一次写迁移脚本时用了 fetchall():

rows = bak.execute("SELECT * FROM file_attack").fetchall()  # 211万行!

Python 进程直接吃掉 1.38GB 内存,CPU 打满,SSH 连接断开,服务器假死。

教训:大表迁移必须用流式游标:

c2 = bak.cursor()
c2.execute("SELECT * FROM file_attack")
for row in c2:   # 逐行迭代,内存恒定
    ...

2. 时区格式不兼容

SQLite 存的是 2026-05-21 02:11:15+08:00,MySQL 只认 2026-05-21 02:11:15。直接 INSERT 会报 Incorrect datetime value。

修复:正则去掉时区后缀。

3. INSERT IGNORE 跳了 admin 密码

HFish 启动时自动在 MySQL 建了空库,创建了默认 admin(id=1,随机密码)。迁移脚本用 INSERT IGNORE 时遇到同 id 就跳过——用户的原始密码哈希被跳了。登录时一直「用户名密码不匹配」。

修复:

UPDATE users SET password = '原始bcrypt哈希' WHERE id = 1;

4. 建索引时表锁死锁

迁移完想给 211 万行的大表加索引,但 HFish 面板正在跑 GROUP BY 查询占着 metadata lock,ALTER TABLE 无限等待。必须先停 HFish 再加索引。

五、加索引后的性能对比

场景 SQLite MySQL(无索引) MySQL(加索引后)
今日攻击数 COUNT 超时失败 21s 0.02s
TOP 10 攻击源 超时失败 N/A 0.85s(含 GROUP BY)
首页加载 打不开 ~30s 1-3s

加的索引:

ALTER TABLE infos ADD INDEX idx_ct (create_time);
ALTER TABLE scans ADD INDEX idx_scantime (scan_time);
ALTER TABLE file_attack ADD INDEX idx_ct (create_time);
ALTER TABLE passwords ADD INDEX idx_ct (create_time);
ALTER TABLE ipaddress ADD INDEX idx_ip (ip(64));
ALTER TABLE ip_profile ADD INDEX idx_ip_dt (ip(64), date);
ALTER TABLE rule_alert ADD INDEX idx_ct (create_time);
ALTER TABLE scanners ADD INDEX idx_srcip (source_ip(64));

六、最终数据完整性

迁移覆盖所有 26 张表,核心数据零丢失:

表 行数 说明
file_attack 2,115,915 攻击文件记录
scans 386,683 扫描记录
infos 374,059 攻击信息
passwords 198,878 捕获密码
ipaddress 43,637 IP画像
users 2 管理员账号

七、经验总结

  1. SQLite 的边界:单文件数据库适合嵌入式、移动端、个人工具。7×24 高并发写入的服务器场景请用 MySQL/PostgreSQL,不要犹豫。

  2. 2GB 服务器也能跑 MySQL:MariaDB 默认 buffer pool 128MB,实际占用 ~200MB。加上 HFish 500MB,总共 700MB,1.3GB 留给系统,完全够用。

  3. 大表迁移用流式游标:fetchall() 会瞬间撑爆内存,游标迭代才是正确姿势。

  4. INSERT IGNORE 谨慎用于主键冲突的表:会默默跳过数据,不如先清空目标表再全量同步。

  5. 建索引先停服务:生产环境的表锁问题比想象中容易触发,ALTER TABLE 拿到锁之前可能等很久。