1. 文件级备份
├── 直接复制数据库文件
├── 简单快速
├── 需要停止写入
└── 适合小型数据库

2. 在线备份API
├── sqlite3_backup_init()
├── sqlite3_backup_step()
├── sqlite3_backup_finish()
├── 不阻塞读写
└── 推荐生产使用

3. SQL导出备份
├── .dump命令
├── 导出SQL语句
├── 跨版本兼容
└── 适合迁移

4. 命令行备份
├── .backup命令
├── 在线热备
└── 简单易用

备份文件组成：
├── 主数据库文件：fgedudb.db
├── WAL文件：fgedudb.db-wal（WAL模式）
└── 共享内存：fgedudb.db-shm（WAL模式）

1. 文件恢复
├── 复制备份文件覆盖
├── 简单直接
├── 需要停止服务
└── 完整恢复

2. SQL导入恢复
├── 执行导出的SQL
├── 适合跨版本
├── 可选择性恢复
└── 耗时较长

3. WAL恢复
├── 自动恢复机制
├── 崩溃后自动执行
├── 从WAL文件恢复
└── 透明恢复

4. 损坏恢复
├── .recover命令
├── 尽可能恢复数据
├── 跳过损坏部分
└── 最后手段

恢复优先级：
├── 1. WAL自动恢复
├── 2. 完整备份恢复
├── 3. 增量备份恢复
└── 4. 损坏恢复

1. 完整性检查
├── PRAGMA integrity_check
├── PRAGMA quick_check
└── 定期执行

2. 空间回收
├── VACUUM
├── VACUUM INTO
└── 清理碎片

3. 统计信息更新
├── ANALYZE
└── 优化查询计划

4. 索引维护
├── REINDEX
└── 重建碎片化索引

5. WAL维护
├── checkpoint
└── 控制WAL大小

维护频率建议：
┌─────────────────┬──────────────────────────┐
│ 维护项目 │ 频率 │
├─────────────────┼──────────────────────────┤
│ 完整性检查 │ 每日 │
│ 统计信息更新 │ 每周 │
│ 空间回收 │ 每月或大量删除后 │
│ 索引重建 │ 每月 │
│ WAL checkpoint │ 自动或手动 │
└─────────────────┴──────────────────────────┘

1. 备份类型
├── 全量备份：完整数据库
├── 增量备份：变化部分
└── 差异备份：相对上次全量

2. 备份频率
├── 关键数据：每小时
├── 重要数据：每日
└── 一般数据：每周

3. 备份保留
├── 日备份：保留7天
├── 周备份：保留4周
├── 月备份：保留12月
└── 年备份：永久保留

4. 备份验证
├── 定期恢复测试
├── 完整性检查
└── 记录验证结果

5. 异地备份
├── 本地备份
├── 远程备份
└── 云存储备份

备份脚本规划：
├── 备份前检查
├── 执行备份
├── 验证备份
├── 清理旧备份
└── 记录日志

DB_PATH=”/sqlite/fgdata/fgedudb.db”
BACKUP_DIR=”/sqlite/backup”
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE=”${BACKUP_DIR}/fgedudb_${DATE}.db”

mkdir -p $BACKUP_DIR

echo “=== SQLite Backup ===”
echo “Time: $(date)”
echo “Source: $DB_PATH”
echo “Target: $BACKUP_FILE”

# 在线备份
sqlite3 $DB_PATH “.backup ‘${BACKUP_FILE}'”

# 验证备份
if [ -f “$BACKUP_FILE” ]; then
sqlite3 $BACKUP_FILE “PRAGMA integrity_check;”
echo “Backup completed successfully”
echo “Size: $(ls -lh $BACKUP_FILE | awk ‘{print $5}’)”
else
echo “Backup failed!”
exit 1
fi

# 清理旧备份（保留7天）
find $BACKUP_DIR -name “fgedudb_*.db” -mtime +7 -delete
echo “Old backups cleaned”

执行结果：
$ ./backup.sh
=== SQLite Backup ===
Time: Mon Apr 8 12:00:00 CST 2024
Source: /sqlite/fgdata/fgedudb.db
Target: /sqlite/backup/fgedudb_20240408_120000.db
ok
Backup completed successfully
Size: 512K
Old backups cleaned

DB_PATH=”/sqlite/fgdata/fgedudb.db”
BACKUP_FILE=$1

if [ -z “$BACKUP_FILE” ]; then
echo “Usage: $0 ”
exit 1
fi

echo “=== SQLite Restore ===”
echo “Time: $(date)”
echo “Backup: $BACKUP_FILE”
echo “Target: $DB_PATH”

# 验证备份文件
if [ ! -f “$BACKUP_FILE” ]; then
echo “Backup file not found!”
exit 1
fi

# 验证备份完整性
echo “Verifying backup…”
sqlite3 $BACKUP_FILE “PRAGMA integrity_check;”
if [ $? -ne 0 ]; then
echo “Backup file is corrupted!”
exit 1
fi

# 停止应用（示例）
# systemctl stop fgedu-app

# 备份当前数据库
if [ -f “$DB_PATH” ]; then
cp $DB_PATH “${DB_PATH}.bak.$(date +%Y%m%d%H%M%S)”
echo “Current database backed up”
fi

# 恢复数据库
cp $BACKUP_FILE $DB_PATH
echo “Database restored”

# 验证恢复
sqlite3 $DB_PATH “PRAGMA integrity_check;”
sqlite3 $DB_PATH “SELECT COUNT(*) FROM fgedu_users;”

echo “Restore completed successfully”

执行结果：
$ ./restore.sh /sqlite/backup/fgedudb_20240408_120000.db
=== SQLite Restore ===
Time: Mon Apr 8 12:05:00 CST 2024
Backup: /sqlite/backup/fgedudb_20240408_120000.db
Target: /sqlite/fgdata/fgedudb.db
Verifying backup…
ok
Current database backed up
Database restored
ok
10004
Restore completed successfully

DB_PATH=”/sqlite/fgdata/fgedudb.db”
LOG_FILE=”/sqlite/logs/maintenance.log”

echo “=== SQLite Maintenance ===” | tee -a $LOG_FILE
echo “Time: $(date)” | tee -a $LOG_FILE

# 1. 完整性检查
echo “1. Integrity Check…” | tee -a $LOG_FILE
sqlite3 $DB_PATH “PRAGMA integrity_check;” | tee -a $LOG_FILE

# 2. 更新统计信息
echo “2. Analyze…” | tee -a $LOG_FILE
sqlite3 $DB_PATH “ANALYZE;” | tee -a $LOG_FILE

# 3. 优化索引
echo “3. Optimize…” | tee -a $LOG_FILE
sqlite3 $DB_PATH “PRAGMA optimize;” | tee -a $LOG_FILE

# 4. WAL checkpoint
echo “4. Checkpoint…” | tee -a $LOG_FILE
sqlite3 $DB_PATH “PRAGMA wal_checkpoint(TRUNCATE);” | tee -a $LOG_FILE

# 5. 数据库信息
echo “5. Database Info…” | tee -a $LOG_FILE
sqlite3 $DB_PATH “SELECT ‘Page Count: ‘ || page_count FROM pragma_page_count;” | tee -a $LOG_FILE
sqlite3 $DB_PATH “SELECT ‘Free Pages: ‘ || freelist_count FROM pragma_freelist_count;” | tee -a $LOG_FILE
sqlite3 $DB_PATH “SELECT ‘DB Size: ‘ || (page_count * page_size) / 1024.0 / 1024.0 || ‘ MB’ FROM pragma_page_count, pragma_page_size;” | tee -a $LOG_FILE

echo “Maintenance completed” | tee -a $LOG_FILE

执行结果：
$ ./maintenance.sh
=== SQLite Maintenance ===
Time: Mon Apr 8 12:10:00 CST 2024
1. Integrity Check…
ok
2. Analyze…
3. Optimize…
ok
4. Checkpoint…
0 0 0
5. Database Info…
Page Count: 128
Free Pages: 0
DB Size: 0.5 MB
Maintenance completed

# 每小时备份
0 * * * * /sqlite/scripts/backup.sh >> /sqlite/logs/backup.log 2>&1

# 每日完整备份
0 2 * * * /sqlite/scripts/full_backup.sh >> /sqlite/logs/backup.log 2>&1

# 每周备份清理
0 3 * * 0 /sqlite/scripts/cleanup_backup.sh >> /sqlite/logs/backup.log 2>&1

备份监控脚本：
#!/bin/bash
# backup_monitor.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

BACKUP_DIR=”/sqlite/backup”
ALERT_EMAIL=”admin@fgedu.net.cn”

# 检查最新备份
LATEST=$(ls -t ${BACKUP_DIR}/fgedudb_*.db 2>/dev/null | head -1)

if [ -z “$LATEST” ]; then
echo “ERROR: No backup found!” | mail -s “SQLite Backup Alert” $ALERT_EMAIL
exit 1
fi

# 检查备份时间
BACKUP_TIME=$(stat -c %Y “$LATEST”)
CURRENT_TIME=$(date +%s)
AGE=$((CURRENT_TIME – BACKUP_TIME))

if [ $AGE -gt 86400 ]; then
echo “WARNING: Backup is older than 24 hours” | mail -s “SQLite Backup Alert” $ALERT_EMAIL
fi

# 检查备份完整性
sqlite3 $LATEST “PRAGMA quick_check;” > /dev/null 2>&1
if [ $? -ne 0 ]; then
echo “ERROR: Backup is corrupted!” | mail -s “SQLite Backup Alert” $ALERT_EMAIL
fi

echo “Backup check passed: $LATEST”

1. 备份策略
├── 定期自动备份
├── 多份备份保留
├── 异地备份存储
└── 定期恢复测试

2. 备份验证
├── 完整性检查
├── 数据一致性验证
├── 恢复测试
└── 记录验证结果

3. 备份监控
├── 监控备份执行
├── 监控备份大小
├── 监控备份时间
└── 异常告警

4. 备份安全
├── 加密敏感数据
├── 访问权限控制
├── 传输加密
└── 审计日志