MySQL备份自动验证脚本

功能：验证备份文件的完整性和可恢复性

版本：v1.0

BACKUP_DIR="/backup/mysql"
LOG_FILE="/var/log/backup-verify.log"
MYSQL_USER="root"
MYSQL_PASSWORD="password"

日志函数

log() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

第1层：文件完整性验证

verify_file_integrity() {
local backup_file="$1"
log "验证文件完整性: $backup_file"

# 检查文件是否存在
if [[ ! -f "$backup_file" ]]; then
log "错误: 备份文件不存在"
return 1
fi

# 检查文件大小
local file_size=$(stat -f%z "$backup_file" 2>/dev/null || stat -c%s "$backup_file")
if [[ $file_size -lt 1024 ]]; then
log "错误: 备份文件太小 (${file_size} bytes)"
return 1
fi

# 检查MD5
local md5_file="${backup_file}.md5"
if [[ -f "$md5_file" ]]; then
if ! md5sum -c "$md5_file"; then
log "错误: MD5校验失败"
return 1
fi
log "MD5校验通过"
fi

# 检查gzip完整性（如果是压缩文件）
if [[ "$backup_file" =~ \.gz$ ]]; then
if ! gzip -t "$backup_file" 2>/dev/null; then
log "错误: gzip文件损坏"
return 1
fi
log "gzip完整性检查通过"
fi

log "文件完整性验证通过"
return 0
}

第2层：内容格式验证

verify_content_format() {
local backup_file="$1"
log "验证内容格式: $backup_file"

# 解压并检查SQL格式
local temp_sql="/tmp/verify-$$.sql"
if [[ "$backup_file" =~ \.gz$ ]]; then
gunzip -c "$backup_file" > "$temp_sql"
else
cp "$backup_file" "$temp_sql"
fi

# 检查是否包含基本的SQL语句
if ! grep -q "CREATE TABLE" "$temp_sql"; then
log "警告: 备份中未找到CREATE TABLE语句"
fi

if ! grep -q "INSERT INTO" "$temp_sql"; then
log "警告: 备份中未找到INSERT INTO语句"
fi

# 统计表数量
local table_count=$(grep -c "CREATE TABLE" "$temp_sql" || echo "0")
log "备份包含 ${table_count} 个表"

# 统计数据行数
local row_count=$(grep -c "^INSERT INTO" "$temp_sql" || echo "0")
log "备份包含 ${row_count} 条INSERT语句"

# 清理临时文件
rm -f "$temp_sql"

log "内容格式验证通过"
return 0
}

第3层：恢复测试验证

verify_restore_test() {
local backup_file="$1"
local test_db="test_restore_$(date +%s)"
log "执行恢复测试: $backup_file -> $test_db"

# 创建测试数据库
mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "CREATE DATABASE $test_db"

# 恢复备份
local start_time=$(date +%s)
if [[ "$backup_file" =~ \.gz$ ]]; then
gunzip -c "$backup_file" | mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" "$test_db"
else
mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" "$test_db" < "$backup_file"
fi
local restore_result=$?
local end_time=$(date +%s)
local duration=$((end_time - start_time))

if [[ $restore_result -ne 0 ]]; then
log "错误: 恢复失败"
mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "DROP DATABASE $test_db"
return 1
fi

log "恢复成功，耗时: ${duration}秒"

# 验证表数量
local restored_tables=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" "$test_db" -e "SHOW TABLES" | wc -l)
log "恢复的表数量: $((restored_tables - 1))"

# 清理测试数据库
mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "DROP DATABASE $test_db"

log "恢复测试通过"
return 0
}

主验证函数

verify_backup() {
local backup_file="$1"
local verify_level="${2:-3}" # 默认执行到第3层验证

log "========================================"
log "开始验证备份: $backup_file"
log "验证级别: $verify_level"
log "========================================"

# 第1层：文件完整性
if ! verify_file_integrity "$backup_file"; then
log "文件完整性验证失败"
return 1
fi

# 第2层：内容格式
if [[ $verify_level -ge 2 ]]; then
if ! verify_content_format "$backup_file"; then
log "内容格式验证失败"
return 1
fi
fi

# 第3层：恢复测试
if [[ $verify_level -ge 3 ]]; then
if ! verify_restore_test "$backup_file"; then
log "恢复测试失败"
return 1
fi
fi

log "========================================"
log "备份验证通过！"
log "========================================"
return 0
}

验证所有备份

verify_all_backups() {
log "开始验证所有备份..."

local backup_count=0
local success_count=0
local fail_count=0

# 查找所有备份文件
for backup_file in "$BACKUP_DIR"/full/*.sql.gz; do
if [[ -f "$backup_file" ]]; then
((backup_count++))
log "处理备份文件: $backup_file"

if verify_backup "$backup_file" 2; then
((success_count++))
else
((fail_count++))
# 发送告警
send_alert "备份验证失败: $backup_file"
fi
fi
done

log "========================================"
log "验证完成: 总数=${backup_count}, 成功=${success_count}, 失败=${fail_count}"
log "========================================"

return $fail_count
}

发送告警

send_alert() {
local message="$1"
log "告警: $message"

# 发送邮件
if command -v mail &>/dev/null; then
echo "$message" | mail -s "[备份验证告警] $message" "admin@your-domain.com"
fi

# 发送飞书通知
if command -v curl &>/dev/null; then
curl -X POST "http://localhost:18789/webhook/alert" -H "Content-Type: application/json" -d "{"text": "$message"}" 2>/dev/null || true
fi
}

主函数

main() {
local action="${1:-verify-all}"

case "$action" in
verify-all)
verify_all_backups
;;
verify-single)
local backup_file="$2"
verify_backup "$backup_file" 3
;;
*)
echo "用法: $0 {verify-all|verify-single }"
exit 1
;;
esac
}