258 lines
8.4 KiB
Bash
Executable File
258 lines
8.4 KiB
Bash
Executable File
#!/bin/bash
|
||
# ============================================
|
||
# 数据库恢复脚本
|
||
#
|
||
# 用法:
|
||
# 恢复本地数据库: bash scripts/restore-db.sh local <服务名> <备份文件名>
|
||
# 恢复云端数据库: bash scripts/restore-db.sh cloud <服务名> <备份文件名>
|
||
#
|
||
# 服务名: issue / assets / ldap
|
||
#
|
||
# 示例:
|
||
# bash scripts/restore-db.sh local issue issue-2026-04-29_1753.db
|
||
# bash scripts/restore-db.sh cloud assets assets-2026-04-29_1753.db
|
||
# bash scripts/restore-db.sh cloud ldap ldap-2026-05-15_1200.db
|
||
# ============================================
|
||
|
||
set -e
|
||
|
||
# ---- 参数检查 ----
|
||
if [ $# -lt 3 ]; then
|
||
echo "用法: $0 <local|cloud> <issue|assets|ldap> <备份文件名>"
|
||
echo ""
|
||
echo "示例:"
|
||
echo " $0 local issue issue-2026-04-29_1753.db"
|
||
echo " $0 cloud ldap ldap-2026-05-15_1200.db"
|
||
echo " $0 cloud assets assets-2026-04-29_1753.db"
|
||
echo ""
|
||
echo "可用备份文件:"
|
||
echo " 本地:"
|
||
ls -1 /Users/niuniu/programs/docker/db-backups/ 2>/dev/null || echo " (无)"
|
||
echo " 云端:"
|
||
ssh txjp "ls -1 /root/docker/db-backups/" 2>/dev/null || echo " (无)"
|
||
exit 1
|
||
fi
|
||
|
||
TARGET=$1
|
||
SERVICE=$2
|
||
BACKUP_FILE=$3
|
||
|
||
# ---- 路径定义 ----
|
||
LOCAL_BACKUP_DIR="/Users/niuniu/programs/docker/db-backups"
|
||
CLOUD_BACKUP_DIR="/root/docker/db-backups"
|
||
|
||
case $SERVICE in
|
||
issue)
|
||
PROJECT_DIR="issue-ai"
|
||
CONTAINER="issue-ai"
|
||
DB_PATH_IN_CONTAINER="/app/data/issue.db"
|
||
LOCAL_DB_PATH="/Users/niuniu/programs/docker/issue-ai/data/issue.db"
|
||
;;
|
||
assets)
|
||
PROJECT_DIR="assets-ai"
|
||
CONTAINER="assets-ai"
|
||
DB_PATH_IN_CONTAINER="/app/data/assets.db"
|
||
LOCAL_DB_PATH="/Users/niuniu/programs/docker/assets-ai/data/assets.db"
|
||
;;
|
||
ldap)
|
||
PROJECT_DIR="ldap-ai"
|
||
CONTAINER="lldap"
|
||
DB_PATH_IN_CONTAINER="/data/users.db"
|
||
LOCAL_DB_PATH="/Users/niuniu/programs/docker/ldap-ai/data/lldap/users.db"
|
||
;;
|
||
*)
|
||
echo "错误: 服务名必须是 issue、assets 或 ldap"
|
||
exit 1
|
||
;;
|
||
esac
|
||
|
||
# ============================================
|
||
# 本地恢复
|
||
# ============================================
|
||
restore_local() {
|
||
local BACKUP_PATH="${LOCAL_BACKUP_DIR}/${BACKUP_FILE}"
|
||
|
||
echo "========================================"
|
||
echo " 本地数据库恢复"
|
||
echo "========================================"
|
||
echo " 服务: ${SERVICE} (${CONTAINER})"
|
||
echo " 备份文件: ${BACKUP_PATH}"
|
||
echo " 目标数据库: ${LOCAL_DB_PATH}"
|
||
echo "========================================"
|
||
echo ""
|
||
|
||
# 1. 检查备份文件
|
||
if [ ! -f "$BACKUP_PATH" ]; then
|
||
echo "[错误] 备份文件不存在: ${BACKUP_PATH}"
|
||
exit 1
|
||
fi
|
||
|
||
# 2. 验证备份文件是有效的 SQLite 数据库
|
||
if ! sqlite3 "$BACKUP_PATH" "SELECT count(*) FROM sqlite_master" > /dev/null 2>&1; then
|
||
echo "[错误] 备份文件不是有效的 SQLite 数据库"
|
||
exit 1
|
||
fi
|
||
|
||
# 3. 确认操作
|
||
echo "[警告] 这将覆盖本地 ${CONTAINER} 数据库!"
|
||
echo " 当前数据库: ${LOCAL_DB_PATH}"
|
||
echo " 将替换为: ${BACKUP_PATH}"
|
||
echo ""
|
||
read -p "确认恢复? (输入 yes 继续): " CONFIRM
|
||
if [ "$CONFIRM" != "yes" ]; then
|
||
echo "已取消"
|
||
exit 0
|
||
fi
|
||
|
||
# 4. 备份当前数据库（安全起见）
|
||
local SAFE_BACKUP="${LOCAL_DB_PATH}.before-restore-$(date +%Y%m%d_%H%M%S)"
|
||
if [ -f "$LOCAL_DB_PATH" ]; then
|
||
cp "$LOCAL_DB_PATH" "$SAFE_BACKUP"
|
||
echo "[备份] 当前数据库已备份至: ${SAFE_BACKUP}"
|
||
fi
|
||
|
||
# 5. WAL checkpoint on backup (确保数据完整)
|
||
sqlite3 "$BACKUP_PATH" "PRAGMA wal_checkpoint(TRUNCATE)" 2>/dev/null || true
|
||
|
||
# 6. 停止本地容器（如果在运行）
|
||
if docker ps --format '{{.Names}}' | grep -q "^${CONTAINER}$"; then
|
||
echo "[操作] 停止容器 ${CONTAINER}..."
|
||
docker stop "$CONTAINER"
|
||
fi
|
||
|
||
# 7. 替换数据库
|
||
cp "$BACKUP_PATH" "$LOCAL_DB_PATH"
|
||
rm -f "${LOCAL_DB_PATH}-shm" "${LOCAL_DB_PATH}-wal"
|
||
echo "[完成] 数据库已替换"
|
||
|
||
# 8. 启动容器
|
||
echo "[操作] 启动容器 ${CONTAINER}..."
|
||
docker start "$CONTAINER"
|
||
|
||
# 9. 验证
|
||
sleep 2
|
||
echo ""
|
||
echo "[验证] 恢复后数据:"
|
||
if [ "$SERVICE" = "issue" ]; then
|
||
echo " 工单数: $(sqlite3 "$LOCAL_DB_PATH" "SELECT COUNT(*) FROM tickets")"
|
||
echo " 配件名称: $(sqlite3 "$LOCAL_DB_PATH" "SELECT COUNT(*) FROM tickets WHERE parts_name IS NOT NULL AND parts_name != ''") 条"
|
||
else
|
||
echo " 设备数: $(sqlite3 "$LOCAL_DB_PATH" "SELECT COUNT(*) FROM assets")"
|
||
fi
|
||
echo ""
|
||
echo "恢复完成!"
|
||
}
|
||
|
||
# ============================================
|
||
# 云端恢复
|
||
# ============================================
|
||
restore_cloud() {
|
||
local BACKUP_PATH="${CLOUD_BACKUP_DIR}/${BACKUP_FILE}"
|
||
|
||
echo "========================================"
|
||
echo " 云端数据库恢复 (txjp)"
|
||
echo "========================================"
|
||
echo " 服务: ${SERVICE} (${CONTAINER})"
|
||
echo " 备份文件: ${BACKUP_PATH}"
|
||
echo "========================================"
|
||
echo ""
|
||
|
||
# 1. 检查备份文件
|
||
if ! ssh txjp "test -f ${BACKUP_PATH}"; then
|
||
echo "[错误] 云端备份文件不存在: ${BACKUP_PATH}"
|
||
exit 1
|
||
fi
|
||
|
||
# 2. 验证备份文件（通过容器内 better-sqlite3，避免依赖宿主机或容器内 sqlite3 CLI）
|
||
if ! ssh txjp "docker cp ${BACKUP_PATH} ${CONTAINER}:/tmp/restore-check.db 2>/dev/null && \
|
||
docker exec ${CONTAINER} node -e \"const D=require('better-sqlite3');const db=new D('/tmp/restore-check.db',{readonly:true});console.log(db.prepare('SELECT count(*) as c FROM sqlite_master').get().c);db.close();\" > /dev/null 2>&1 && \
|
||
docker exec ${CONTAINER} rm /tmp/restore-check.db"; then
|
||
echo "[错误] 备份文件不是有效的 SQLite 数据库"
|
||
exit 1
|
||
fi
|
||
|
||
# 3. 显示备份和当前状态
|
||
echo "[信息] 备份文件信息:"
|
||
ssh txjp "ls -lh ${BACKUP_PATH}"
|
||
echo ""
|
||
echo "[信息] 当前云端数据库状态:"
|
||
if [ "$SERVICE" = "issue" ]; then
|
||
ssh txjp "docker exec ${CONTAINER} node -e \"
|
||
const D=require('better-sqlite3');
|
||
const db=new D('/app/data/issue.db',{readonly:true});
|
||
console.log(' 工单数:', db.prepare('SELECT COUNT(*) as c FROM tickets').get().c);
|
||
db.close();
|
||
\""
|
||
else
|
||
ssh txjp "docker exec ${CONTAINER} node -e \"
|
||
const D=require('better-sqlite3');
|
||
const db=new D('/app/data/assets.db',{readonly:true});
|
||
console.log(' 设备数:', db.prepare('SELECT COUNT(*) as c FROM assets').get().c);
|
||
db.close();
|
||
\""
|
||
fi
|
||
|
||
# 4. 确认
|
||
echo ""
|
||
echo "[警告] 这将覆盖云端 ${CONTAINER} 数据库!"
|
||
read -p "确认恢复? (输入 yes 继续): " CONFIRM
|
||
if [ "$CONFIRM" != "yes" ]; then
|
||
echo "已取消"
|
||
exit 0
|
||
fi
|
||
|
||
# 5. 停止容器（确保数据库不被写入，安全替换）
|
||
echo "[操作] 停止容器 ${CONTAINER}..."
|
||
ssh txjp "cd /root/docker/${PROJECT_DIR} && docker compose stop ${CONTAINER}"
|
||
|
||
# 6. 备份当前数据库（容器已停止，安全复制）
|
||
local SAFE_NAME="before-restore-$(date +%Y%m%d_%H%M%S).db"
|
||
echo "[操作] 备份云端当前数据库..."
|
||
ssh txjp "docker cp ${CONTAINER}:${DB_PATH_IN_CONTAINER} /root/docker/db-backups/${SAFE_NAME}"
|
||
echo "[备份] 当前数据库已保存至云端: /root/docker/db-backups/${SAFE_NAME}"
|
||
|
||
# 7. 替换数据库
|
||
echo "[操作] 复制备份文件到容器..."
|
||
ssh txjp "docker cp ${BACKUP_PATH} ${CONTAINER}:${DB_PATH_IN_CONTAINER}"
|
||
|
||
# 8. 删除残留 WAL/SHM 文件，防止旧日志与新数据库不匹配导致损坏
|
||
echo "[操作] 清理残留 WAL/SHM 文件..."
|
||
ssh txjp "docker exec ${CONTAINER} rm -f ${DB_PATH_IN_CONTAINER}-shm ${DB_PATH_IN_CONTAINER}-wal" 2>/dev/null || true
|
||
|
||
# 9. 启动容器
|
||
echo "[操作] 启动容器 ${CONTAINER}..."
|
||
ssh txjp "cd /root/docker/${PROJECT_DIR} && docker compose start ${CONTAINER}"
|
||
|
||
# 10. 验证
|
||
sleep 3
|
||
echo ""
|
||
echo "[验证] 恢复后数据:"
|
||
if [ "$SERVICE" = "issue" ]; then
|
||
ssh txjp "docker exec ${CONTAINER} node -e \"
|
||
const D=require('better-sqlite3');
|
||
const db=new D('/app/data/issue.db',{readonly:true});
|
||
console.log(' 工单数:', db.prepare('SELECT COUNT(*) as c FROM tickets').get().c);
|
||
db.close();
|
||
\""
|
||
else
|
||
ssh txjp "docker exec ${CONTAINER} node -e \"
|
||
const D=require('better-sqlite3');
|
||
const db=new D('/app/data/assets.db',{readonly:true});
|
||
console.log(' 设备数:', db.prepare('SELECT COUNT(*) as c FROM assets').get().c);
|
||
db.close();
|
||
\""
|
||
fi
|
||
echo ""
|
||
echo "恢复完成!"
|
||
}
|
||
|
||
# ---- 执行 ----
|
||
case $TARGET in
|
||
local) restore_local ;;
|
||
cloud) restore_cloud ;;
|
||
*)
|
||
echo "错误: 目标必须是 local 或 cloud"
|
||
exit 1
|
||
;;
|
||
esac
|