# -*- coding: utf-8 -*- """ 水利智学系统 - 自动备份守护(start.bat 用 pythonw 拉起,常驻每小时一次) 备份对象:只备份不可再生的数据库 backend/data/shuili_exam.db(SQLite 在线一致备份 .backup(), 不锁库、不会热拷损坏)。前端代码可由交付包/git 恢复,不再每小时整拷(旧版连 .bak 垃圾一起拷, 10 份占 2.1GB)。 分层保留(GFS 简化,对齐交付验收"每天 1 份、保留 30 天、旧备份自动清理"): hourly/ 每次运行 1 份未压缩快照,保留最近 KEEP_HOURLY=12 份(约 12 小时,防当天误操作); daily/ 每天首次运行产出 1 份 gzip 压缩日备份 YYYYMMDD.db.gz,保留 KEEP_DAILY=30 份; safety/ 一键恢复前自动生成的"恢复前安全网",保留最近 KEEP_SAFETY=10 份。 清单:backups/auto/manifest.json(供后端 /api/v1/backup/auto_list 发现、前端一键恢复)。 手动跑一次:python auto_backup.py once 常驻每小时:python auto_backup.py (内置单实例端口锁,重复拉起会自行退出) 本文件同时被后端 import 复用 online_backup / restore_online(顶层无副作用,日志仅在 __main__ 配置)。 """ import os import sys import gzip import shutil import socket import sqlite3 import logging from datetime import datetime ROOT = os.path.dirname(os.path.abspath(__file__)) BACKUP_DIR = os.path.join(ROOT, "backups", "auto") HOURLY_DIR = os.path.join(BACKUP_DIR, "hourly") DAILY_DIR = os.path.join(BACKUP_DIR, "daily") SAFETY_DIR = os.path.join(BACKUP_DIR, "safety") MANIFEST = os.path.join(BACKUP_DIR, "manifest.json") DB_SRC = os.path.join(ROOT, "backend", "data", "shuili_exam.db") LOG_FILE = os.path.join(ROOT, "backups", "auto_backup.log") KEEP_HOURLY = 12 KEEP_DAILY = 30 KEEP_SAFETY = 10 LOOP_SECONDS = 3600 _LOCK_PORT = 63187 # 单实例锁端口(仅本机) log = logging.getLogger("auto_backup") # B3 备份加密(AES-256-GCM 认证加密)。后端 import 与守护进程独立运行都要可用,显式补 backend 到 path。 _BACKEND = os.path.join(ROOT, "backend") for _p in (_BACKEND, ROOT): if _p not in sys.path: sys.path.insert(0, _p) try: from core.backup_crypto import ( encrypt_file as _encrypt_file, decrypt_file as _decrypt_file, looks_encrypted_file as _looks_encrypted, decrypt_bytes as _decrypt_bytes, looks_encrypted_bytes as _looks_encrypted_bytes, ) _CRYPTO_OK = True _CRYPTO_ERR = "" except Exception as _e: # 加密依赖缺失时降级为不加密,保证备份不中断 _CRYPTO_OK = False _CRYPTO_ERR = repr(_e) # ---------------- 基础原语(参数化,便于回归用临时目录/临时库测试) ---------------- def online_backup(db_src, db_dst): """用 SQLite online backup API 做在线一致备份(可在库被使用时调用,不锁库、不损坏)。""" os.makedirs(os.path.dirname(os.path.abspath(db_dst)), exist_ok=True) s = sqlite3.connect(db_src, timeout=30) d = sqlite3.connect(db_dst, timeout=30) try: with d: s.backup(d) finally: s.close() d.close() return db_dst def gzip_file(src, dst_gz): """把文件 gzip 压缩为 dst_gz。""" os.makedirs(os.path.dirname(os.path.abspath(dst_gz)), exist_ok=True) with open(src, "rb") as fi, gzip.open(dst_gz, "wb", compresslevel=6) as fo: shutil.copyfileobj(fi, fo, length=1024 * 1024) return dst_gz def _prune_dir(directory, keep, suffix): """按文件名(含时间戳,可字典序)只保留最新 keep 个指定后缀文件,旧的删除。""" if not os.path.isdir(directory): return 0 names = sorted((n for n in os.listdir(directory) if n.endswith(suffix)), reverse=True) removed = 0 for n in names[keep:]: try: os.remove(os.path.join(directory, n)) removed += 1 except OSError: pass return removed def _list_dir(directory, suffix): if not os.path.isdir(directory): return [] out = [] for n in sorted(os.listdir(directory), reverse=True): if not n.endswith(suffix): continue p = os.path.join(directory, n) out.append({"name": n, "size": os.path.getsize(p), "mtime": datetime.fromtimestamp(os.path.getmtime(p)).strftime("%Y-%m-%d %H:%M:%S")}) return out def write_manifest(base_dir=BACKUP_DIR): hourly = _list_dir(os.path.join(base_dir, "hourly"), ".db.gz") daily = _list_dir(os.path.join(base_dir, "daily"), ".db.gz") safety = _list_dir(os.path.join(base_dir, "safety"), ".db") manifest = { "generated_at": datetime.now().strftime("%Y-%m-%d %H:%M:%S"), "retention": {"hourly": KEEP_HOURLY, "daily": KEEP_DAILY, "safety": KEEP_SAFETY}, "counts": {"hourly": len(hourly), "daily": len(daily), "safety": len(safety)}, "hourly": hourly, "daily": daily, "safety": safety, } os.makedirs(base_dir, exist_ok=True) tmp = os.path.join(base_dir, "manifest.json.tmp") import json with open(tmp, "w", encoding="utf-8") as f: json.dump(manifest, f, ensure_ascii=False, indent=2) os.replace(tmp, os.path.join(base_dir, "manifest.json")) return manifest def run_cycle(base_dir=BACKUP_DIR, db_src=DB_SRC, now=None, keep_hourly=KEEP_HOURLY, keep_daily=KEEP_DAILY, keep_safety=KEEP_SAFETY): """执行一次备份:1 份小时快照 + 当天首份日备份(gzip),再分层清理、写清单。""" now = now or datetime.now() hourly_dir = os.path.join(base_dir, "hourly") daily_dir = os.path.join(base_dir, "daily") os.makedirs(hourly_dir, exist_ok=True) os.makedirs(daily_dir, exist_ok=True) result = {"hourly": None, "daily": None, "pruned_hourly": 0, "pruned_daily": 0} if not os.path.exists(db_src): raise FileNotFoundError("数据库不存在: %s" % db_src) # 1) 小时快照:在线备份到临时文件后 gzip(压缩到约 1/3,近线 12 份也不占空间) stamp = now.strftime("%Y%m%d_%H%M") tmp_db = os.path.join(base_dir, ".tmp_hourly.db") online_backup(db_src, tmp_db) hourly_path = os.path.join(hourly_dir, stamp + ".db.gz") gzip_file(tmp_db, hourly_path) # B3:gzip 之外再做 AES-256-GCM 认证加密(文件名仍 .db.gz,内容为加密容器)。 # 加密故障时保留 gzip 明文并标记,绝不因加密异常导致没有备份。 result["encrypted"] = False if _CRYPTO_OK: try: _enc_tmp = hourly_path + ".enc" _encrypt_file(hourly_path, _enc_tmp) os.replace(_enc_tmp, hourly_path) result["encrypted"] = True except Exception as _e: log.warning("自动备份加密失败,保留 gzip 明文: %s", _e) else: log.warning("备份加密模块不可用,产出未加密 gzip: %s", _CRYPTO_ERR) try: os.remove(tmp_db) except OSError: pass result["hourly"] = os.path.basename(hourly_path) # 2) 当天首份日备份(与小时快照内容一致,直接复用压缩产物,每天仅 1 份,留 30 天) day = now.strftime("%Y%m%d") daily_path = os.path.join(daily_dir, day + ".db.gz") if not os.path.exists(daily_path): shutil.copy2(hourly_path, daily_path) result["daily"] = os.path.basename(daily_path) # 3) 分层清理 result["pruned_hourly"] = _prune_dir(hourly_dir, keep_hourly, ".db.gz") result["pruned_daily"] = _prune_dir(daily_dir, keep_daily, ".db.gz") result["pruned_safety"] = _prune_dir(os.path.join(base_dir, "safety"), keep_safety, ".db") # 4) 清单 result["manifest"] = write_manifest(base_dir) return result def restore_online(backup_file, live_db, safety_dir, compressed=None): """从备份在线恢复到 live_db(不覆盖被占用文件,不需重启后端)。 返回 {"success", "safety_net", "integrity", "questions"}。 步骤:当前活库先 online backup 成安全网 -> (gzip 先解压到临时文件)-> 用 SQLite backup API 把备份页级覆盖进活库 -> integrity_check + 题数校验。 调用方(后端)应在前后 engine.dispose() 释放连接池。 """ if compressed is None: compressed = str(backup_file).lower().endswith(".gz") os.makedirs(safety_dir, exist_ok=True) if not os.path.exists(backup_file): raise FileNotFoundError("备份文件不存在: %s" % backup_file) if not os.path.exists(live_db): raise FileNotFoundError("活库不存在: %s" % live_db) tmp_files = [] src_path = backup_file try: # B3:若为加密容器先解密(历史明文 gz/db 自动透传兼容),再按需 gzip 解压 if _CRYPTO_OK and _looks_encrypted(backup_file): import tempfile fd, tmp_dec = tempfile.mkstemp(suffix=".bin") os.close(fd) _decrypt_file(backup_file, tmp_dec) src_path = tmp_dec tmp_files.append(tmp_dec) if compressed: import tempfile fd, tmp_gun = tempfile.mkstemp(suffix=".db") os.close(fd) with gzip.open(src_path, "rb") as fi, open(tmp_gun, "wb") as fo: shutil.copyfileobj(fi, fo, length=1024 * 1024) src_path = tmp_gun tmp_files.append(tmp_gun) # 1) 恢复前安全网(当前活库) stamp = datetime.now().strftime("%Y%m%d_%H%M%S_%f") safety = os.path.join(safety_dir, "pre_restore_%s.db" % stamp) online_backup(live_db, safety) _prune_dir(safety_dir, KEEP_SAFETY, ".db") # 2) 在线页级恢复:src.backup(live) 会用备份内容完整替换活库 src = sqlite3.connect(src_path, timeout=30) live = sqlite3.connect(live_db, timeout=30) try: src.backup(live) finally: src.close() live.close() # 3) 校验 chk = sqlite3.connect(live_db, timeout=30) try: integrity = chk.execute("PRAGMA integrity_check").fetchone()[0] try: questions = chk.execute("SELECT COUNT(*) FROM questions").fetchone()[0] except sqlite3.Error: questions = -1 finally: chk.close() return {"success": integrity == "ok", "safety_net": os.path.basename(safety), "integrity": integrity, "questions": questions} finally: for _t in tmp_files: if os.path.exists(_t): try: os.remove(_t) except OSError: pass def normalize_backup_bytes(data): """把上传/外部备份字节归一化为【明文 SQLite 字节】(B3 上传恢复统一入口)。 依次识别:本系统 AES-GCM 加密容器(SLBKENC1) -> gzip(含加密后再 gz) -> 明文 SQLite。 任何无法还原为有效 SQLite 头的输入抛 ValueError,调用方据此中止、绝不覆盖活库。 """ if not data: raise ValueError("文件为空") data = bytes(data) if _CRYPTO_OK and _looks_encrypted_bytes(data[:8]): data = _decrypt_bytes(data) if data[:2] == b"\x1f\x8b": data = gzip.decompress(data) if _CRYPTO_OK and _looks_encrypted_bytes(data[:8]): data = _decrypt_bytes(data) if data[:16] != b"SQLite format 3\x00": raise ValueError("内容不是有效的 SQLite 数据库(已尝试解密/解压),已拒绝恢复") return data def restore_from_bytes(data, live_db, safety_dir): """从上传字节安全在线恢复:归一化 -> 临时文件 -> restore_online(含安全网+完整性校验)。 输入非法抛 ValueError(此时活库完全未动);返回 restore_online 的结果 dict。 """ norm = normalize_backup_bytes(data) import tempfile fd, tmp = tempfile.mkstemp(suffix=".db") os.close(fd) try: with open(tmp, "wb") as f: f.write(norm) return restore_online(tmp, live_db, safety_dir, compressed=False) finally: try: os.remove(tmp) except OSError: pass def _acquire_single_instance(): """本机端口单实例锁;已有守护在跑则返回 None。""" try: s = socket.socket(socket.AF_INET, socket.SOCK_STREAM) s.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 0) s.bind(("127.0.0.1", _LOCK_PORT)) s.listen(1) return s except OSError: return None def main(): logging.basicConfig(filename=LOG_FILE, level=logging.INFO, format="%(asctime)s %(message)s") os.makedirs(BACKUP_DIR, exist_ok=True) if len(sys.argv) > 1 and sys.argv[1] == "once": r = run_cycle() log.info("backup ok(once): hourly=%s daily=%s pruned(h=%s,d=%s)", r["hourly"], r["daily"], r["pruned_hourly"], r["pruned_daily"]) return lock = _acquire_single_instance() if lock is None: log.info("auto_backup 已有实例在运行,本进程退出") return while True: try: r = run_cycle() log.info("backup ok: hourly=%s daily=%s pruned(h=%s,d=%s)", r["hourly"], r["daily"], r["pruned_hourly"], r["pruned_daily"]) except Exception as e: log.warning("loop error: %s", e) # 对齐到下一个整点附近:固定间隔即可(start.bat 开机/启动时也会补一次) import time time.sleep(LOOP_SECONDS) if __name__ == "__main__": main()