数据库备份与恢复策略：从灾难恢复到零数据丢失

#!/bin/bash
# pg_backup.sh - PostgreSQL逻辑备份脚本

BACKUP_DIR="/backup/postgres"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="production_db"
RETENTION_DAYS=30

# 创建备份目录
mkdir -p ${BACKUP_DIR}/${DATE}

# 全量逻辑备份（自定义格式，支持并行恢复）
pg_dump -h localhost -U postgres \
  -Fc -v \
  -f ${BACKUP_DIR}/${DATE}/${DB_NAME}_full.dump \
  ${DB_NAME}

# 压缩备份
gzip ${BACKUP_DIR}/${DATE}/${DB_NAME}_full.dump

# 备份schema（便于快速恢复结构）
pg_dump -h localhost -U postgres \
  -s -v \
  -f ${BACKUP_DIR}/${DATE}/${DB_NAME}_schema.sql \
  ${DB_NAME}

# 备份全局对象（角色、表空间）
pg_dumpall -h localhost -U postgres \
  --globals-only \
  -f ${BACKUP_DIR}/${DATE}/globals.sql

# 清理过期备份
find ${BACKUP_DIR} -maxdepth 1 -type d -mtime +${RETENTION_DAYS} -exec rm -rf {} \;

echo "Backup completed: ${BACKUP_DIR}/${DATE}"

#!/bin/bash
# pg_basebackup.sh - PostgreSQL物理备份脚本

BACKUP_DIR="/backup/postgres/basebackup"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_PATH="${BACKUP_DIR}/${DATE}"

# 创建基础备份（带WAL）
pg_basebackup -h localhost -U replication_user \
  -D ${BACKUP_PATH} \
  -Ft -z -P \
  --wal-method=stream \
  --checkpoint=fast \
  --label="basebackup_${DATE}"

# 记录备份信息
cat > ${BACKUP_PATH}/backup_info.txt << EOF
Backup Date: $(date)
Backup Type: Full Physical
Format: Tar + Gzip
WAL Method: Stream
PostgreSQL Version: $(psql --version)
EOF

echo "Base backup completed: ${BACKUP_PATH}"

#!/bin/bash
# wal_archive.sh - WAL归档管理脚本

WAL_ARCHIVE_DIR="/backup/wal"
RETENTION_DAYS=7

# 清理过期的WAL文件
# 注意：只清理早于最新基础备份的WAL
LATEST_BASEBACKUP=$(ls -t /backup/postgres/basebackup | head -1)
if [ -n "$LATEST_BASEBACKUP" ]; then
    # 获取基础备份的起始WAL位置
    START_WAL=$(cat /backup/postgres/basebackup/${LATEST_BASEBACKUP}/backup_label | grep "START WAL" | awk '{print $3}')
    
    # 删除早于起始WAL的归档
    for wal_file in $(ls ${WAL_ARCHIVE_DIR} | sort); do
        if [[ "$wal_file" < "$START_WAL" ]]; then
            rm -f ${WAL_ARCHIVE_DIR}/${wal_file}
            echo "Removed old WAL: ${wal_file}"
        fi
    done
fi

# 清理超过保留期的WAL
find ${WAL_ARCHIVE_DIR} -type f -mtime +${RETENTION_DAYS} -delete

#!/bin/bash
# pitr_restore.sh - 时间点恢复脚本

TARGET_TIME="2026-09-25 14:30:00"
BACKUP_DIR="/backup/postgres/basebackup"
WAL_ARCHIVE="/backup/wal"
DATA_DIR="/var/lib/postgresql/14/main"

# 1. 停止PostgreSQL
systemctl stop postgresql

# 2. 备份当前数据目录（以防万一）
mv ${DATA_DIR} ${DATA_DIR}.old

# 3. 恢复基础备份
LATEST_BACKUP=$(ls -t ${BACKUP_DIR} | head -1)
tar -xzf ${BACKUP_DIR}/${LATEST_BACKUP}/base.tar.gz -C ${DATA_DIR}

# 4. 配置恢复参数
cat > ${DATA_DIR}/postgresql.auto.conf << EOF
restore_command = 'cp ${WAL_ARCHIVE}/%f %p'
recovery_target_time = '${TARGET_TIME}'
recovery_target_action = 'promote'
EOF

# 5. 创建恢复信号文件
touch ${DATA_DIR}/recovery.signal

# 6. 设置正确的权限
chown -R postgres:postgres ${DATA_DIR}
chmod 700 ${DATA_DIR}

# 7. 启动PostgreSQL（自动进入恢复模式）
systemctl start postgresql

# 8. 验证恢复
echo "Waiting for recovery to complete..."
sleep 10

psql -U postgres -c "SELECT pg_is_in_recovery();"

echo "PITR recovery initiated. Monitor logs for progress."

#!/bin/bash
# mysql_backup.sh - MySQL逻辑备份脚本

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
MYSQL_USER="backup_user"
MYSQL_PASS="backup_password"
DATABASES="db1 db2 db3"
RETENTION_DAYS=30

mkdir -p ${BACKUP_DIR}/${DATE}

# 全量备份所有数据库
mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --master-data=2 \
  --flush-logs \
  | gzip > ${BACKUP_DIR}/${DATE}/all_databases.sql.gz

# 单独备份每个数据库
for DB in ${DATABASES}; do
    mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} \
      --single-transaction \
      --routines \
      --triggers \
      ${DB} \
      | gzip > ${BACKUP_DIR}/${DATE}/${DB}.sql.gz
done

# 记录binlog位置
mysql -u${MYSQL_USER} -p${MYSQL_PASS} \
  -e "SHOW MASTER STATUS\G" \
  > ${BACKUP_DIR}/${DATE}/binlog_position.txt

# 清理过期备份
find ${BACKUP_DIR} -maxdepth 1 -type d -mtime +${RETENTION_DAYS} -exec rm -rf {} \;

echo "MySQL backup completed: ${BACKUP_DIR}/${DATE}"

#!/bin/bash
# xtrabackup.sh - Percona XtraBackup物理备份

BACKUP_DIR="/backup/mysql/xtrabackup"
DATE=$(date +%Y%m%d_%H%M%S)
FULL_BACKUP_DIR="${BACKUP_DIR}/full"
INCR_BACKUP_DIR="${BACKUP_DIR}/incr/${DATE}"

# 全量备份（每周一次）
if [ "$(date +%u)" = "1" ]; then
    echo "Performing full backup..."
    
    xtrabackup --backup \
      --user=root \
      --password=password \
      --target-dir=${FULL_BACKUP_DIR} \
      --compress \
      --compress-threads=4 \
      --parallel=4
    
    # 准备备份
    xtrabackup --prepare \
      --target-dir=${FULL_BACKUP_DIR}
    
    echo "Full backup completed: ${FULL_BACKUP_DIR}"
else
    # 增量备份（每天）
    echo "Performing incremental backup..."
    
    xtrabackup --backup \
      --user=root \
      --password=password \
      --target-dir=${INCR_BACKUP_DIR} \
      --incremental-basedir=${FULL_BACKUP_DIR} \
      --compress \
      --compress-threads=4
    
    echo "Incremental backup completed: ${INCR_BACKUP_DIR}"
fi

#!/bin/bash
# binlog_restore.sh - MySQL Binlog恢复

BINLOG_DIR="/var/lib/mysql"
START_TIME="2026-09-25 10:00:00"
END_TIME="2026-09-25 14:30:00"
OUTPUT_FILE="/tmp/binlog_restore.sql"

# 找到时间范围内的binlog文件
mysqlbinlog \
  --start-datetime="${START_TIME}" \
  --stop-datetime="${END_TIME}" \
  ${BINLOG_DIR}/mysql-bin.000* \
  > ${OUTPUT_FILE}

# 查看生成的SQL（验证）
head -100 ${OUTPUT_FILE}

# 应用恢复（谨慎操作）
# mysql -uroot -p < ${OUTPUT_FILE}

echo "Binlog recovery SQL generated: ${OUTPUT_FILE}"

# /etc/cron.d/db-backup
# PostgreSQL备份
0 2 * * * root /scripts/pg_backup.sh >> /var/log/pg_backup.log 2>&1
0 */6 * * * root /scripts/pg_basebackup.sh >> /var/log/pg_basebackup.log 2>&1

# MySQL备份
0 3 * * * root /scripts/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1
0 4 * * 1 root /scripts/xtrabackup.sh >> /var/log/xtrabackup.log 2>&1

# 备份验证（每周）
0 6 * * 0 root /scripts/verify_backup.sh >> /var/log/verify_backup.log 2>&1

# 清理旧备份（每天）
0 5 * * * root /scripts/cleanup_backups.sh >> /var/log/cleanup.log 2>&1

package main

import (
    "fmt"
    "os"
    "time"
    
    "github.com/prometheus/client_golang/prometheus"
)

var (
    backupLastSuccess = prometheus.NewGaugeVec(
        prometheus.GaugeOpts{
            Name: "backup_last_success_timestamp",
            Help: "Timestamp of last successful backup",
        },
        []string{"database", "type"},
    )
    
    backupDuration = prometheus.NewHistogramVec(
        prometheus.HistogramOpts{
            Name:    "backup_duration_seconds",
            Help:    "Duration of backup operation",
            Buckets: prometheus.ExponentialBuckets(60, 2, 10),
        },
        []string{"database", "type"},
    )
    
    backupSize = prometheus.NewGaugeVec(
        prometheus.GaugeOpts{
            Name: "backup_size_bytes",
            Help: "Size of backup file",
        },
        []string{"database", "type"},
    )
)

func init() {
    prometheus.MustRegister(backupLastSuccess, backupDuration, backupSize)
}

func recordBackupSuccess(database, backupType string, duration time.Duration, size int64) {
    backupLastSuccess.WithLabelValues(database, backupType).Set(float64(time.Now().Unix()))
    backupDuration.WithLabelValues(database, backupType).Observe(duration.Seconds())
    backupSize.WithLabelValues(database, backupType).Set(float64(size))
}

func checkBackupFreshness(database, backupType string, maxAge time.Duration) error {
    // 检查最后一次成功备份的时间
    // 如果超过maxAge，发送告警
    return nil
}

#!/bin/bash
# verify_backup.sh - 备份验证脚本

VERIFY_DIR="/tmp/backup_verify"
PG_BACKUP=$(ls -t /backup/postgres/*/production_db_full.dump.gz 2>/dev/null | head -1)
MYSQL_BACKUP=$(ls -t /backup/mysql/*/all_databases.sql.gz 2>/dev/null | head -1)

mkdir -p ${VERIFY_DIR}

# 验证PostgreSQL备份
if [ -n "$PG_BACKUP" ]; then
    echo "Verifying PostgreSQL backup: ${PG_BACKUP}"
    
    # 解压并检查完整性
    gunzip -c ${PG_BACKUP} > ${VERIFY_DIR}/pg_backup.dump
    
    if pg_restore -l ${VERIFY_DIR}/pg_backup.dump > /dev/null 2>&1; then
        echo "✅ PostgreSQL backup is valid"
        
        # 恢复到测试数据库
        createdb -U postgres verify_db
        pg_restore -U postgres -d verify_db ${VERIFY_DIR}/pg_backup.dump
        
        # 验证数据
        TABLE_COUNT=$(psql -U postgres -d verify_db -t -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';")
        echo "✅ Restored ${TABLE_COUNT} tables successfully"
        
        # 清理
        dropdb -U postgres verify_db
    else
        echo "❌ PostgreSQL backup is corrupted!"
        # 发送告警
    fi
    
    rm -f ${VERIFY_DIR}/pg_backup.dump
fi

# 验证MySQL备份
if [ -n "$MYSQL_BACKUP" ]; then
    echo "Verifying MySQL backup: ${MYSQL_BACKUP}"
    
    gunzip -c ${MYSQL_BACKUP} > ${VERIFY_DIR}/mysql_backup.sql
    
    # 检查SQL语法
    if mysql -uroot -ppassword --help > /dev/null 2>&1; then
        echo "✅ MySQL backup file is readable"
        
        # 恢复到测试数据库
        mysql -uroot -ppassword -e "CREATE DATABASE IF NOT EXISTS verify_db;"
        mysql -uroot -ppassword verify_db < ${VERIFY_DIR}/mysql_backup.sql
        
        TABLE_COUNT=$(mysql -uroot -ppassword -N -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'verify_db';")
        echo "✅ Restored ${TABLE_COUNT} tables successfully"
        
        mysql -uroot -ppassword -e "DROP DATABASE verify_db;"
    else
        echo "❌ MySQL backup verification failed!"
    fi
    
    rm -f ${VERIFY_DIR}/mysql_backup.sql
fi

rm -rf ${VERIFY_DIR}
echo "Backup verification completed"

# 季度灾难恢复演练计划

## 演练目标
- 验证备份的完整性和可恢复性
- 测试RTO和RPO是否满足要求
- 提升团队的应急响应能力

## 演练场景

### 场景1：单库恢复
- 模拟数据库损坏
- 从最新备份恢复
- 验证数据完整性
- 记录恢复时间

### 场景2：时间点恢复
- 模拟误删数据
- 恢复到指定时间点
- 验证数据一致性
- 记录RPO

### 场景3：全站恢复
- 模拟机房故障
- 在备用环境完整恢复
- 验证所有服务可用性
- 记录完整RTO

## 演练步骤
1. 通知相关人员（不告知具体时间）
2. 启动演练计时
3. 执行恢复操作
4. 验证服务可用性
5. 记录关键指标
6. 总结改进点

## 演练报告
- 实际RTO vs 目标RTO
- 实际RPO vs 目标RPO
- 遇到的问题和解决方案
- 改进建议和行动计划