📘 PostgreSQL 与 MySQL 备份恢复终极指南（生产臻享版）

一、核心原则与避坑指南

核心原则 详细说明
3-2-1 备份规则 至少 3 份数据副本，存储在 2 种不同介质（如磁盘+对象存储），至少 1 份异地（防机房级灾难）。
备份 ≠ 可恢复 必须定期进行恢复演练（建议每季度一次），验证备份文件的完整性和可用性。
监控与告警 监控备份脚本的退出码（Exit Code）和日志，不仅要关注“是否执行”，更要关注“是否成功”。
锁表误区 PG 利用 MVCC 机制，备份过程不锁表；MySQL InnoDB 配合 --single-transaction 也可实现无锁热备。
GTID 与位点 开启 GTID 的 MySQL 集群，恢复时需特别注意 GTID_PURGED 参数，否则可能导致复制报错。

二、PostgreSQL 备份与恢复

2.1 逻辑备份：pg_dump / pg_dumpall

🔹 适用场景

  • 数据量中小型（< 100GB）
  • 跨版本迁移或升级
  • 仅需迁移部分表数据

🔹 生产级命令（带详解）

# 1. 备份全局对象（角色、表空间、权限）
# 必须先执行此步，因为 pg_dump 不包含角色定义
pg_dumpall -U postgres -g > globals.sql
# 2. 备份单个数据库（推荐目录格式，支持并行）
pg_dump -U postgres -d mydb \
  -Fd \              # 目录格式，利于并行和压缩
  -j 4 \             # 使用 4 个并发进程加速备份
  -Z6 \              # 压缩级别（0-9，6为推荐值）
  -f /backup/mydb_dir
# 3. 备份单表（SQL格式）
pg_dump -U postgres -t schema_name.table_name mydb > table.sql

🔹 恢复命令

# 1. 恢复全局对象（先创建角色）
psql -U postgres -f globals.sql
# 2. 恢复数据库（并行恢复）
pg_restore -U postgres -d mydb \
  -j 4 \             # 并行恢复
  --clean \          # 恢复前清理已存在的对象
  --if-exists \      # 清理时避免报错
  /backup/mydb_dir

⚠️ 统计信息警告：pg_dump 不会备份统计信息（pg_statistic）。恢复后必须执行 ANALYZE，否则查询计划可能极其低效。

2.2 物理备份：pg_basebackup

🔹 前置配置 (postgresql.conf)

wal_level = replica               # 必须开启
archive_mode = on                 # 开启归档
archive_command = 'test ! -f /wal_archive/%f && cp %p /wal_archive/%f'
max_wal_senders = 5               # 允许复制的连接数
wal_keep_size = 1GB               # 保留 WAL 防止备库拉取失败

🔹 备份命令（生产标准）

pg_basebackup \
  -h 192.168.1.101 \
  -U replicator \
  -D /backup/pg_full_$(date +%Y%m%d) \
  -Ft \               # 输出为 tar 格式
  -z \                # 启用 gzip 压缩
  -X stream \         # 流式传输 WAL，确保备份自包含
  -Pv \               # 显示进度和详细信息
  -S backup_slot      # 使用复制槽（需提前创建），防止备份期间 WAL 被清理

注意：-R 参数仅用于搭建流复制从库。做数据恢复备份时不要加 -R。

2.3 时间点恢复（PITR）全流程

1. 解压基础备份

tar -xzf base.tar.gz -C /var/lib/postgresql/16/main

2. 配置恢复参数

在 postgresql.auto.conf 中添加：

restore_command = 'cp /wal_archive/%f %p'
# recovery_target = 'immediate'      # 关键：仅恢复基础备份，不回放日志
# recovery_target_xid = '123456'     # 恢复到指定事务 ID,需配合事务日志分析工具（如 pg_waldump）找到准确 XID。
recovery_target_time = '2025-10-27 14:30:00'  # 目标恢复时间
recovery_target_action = 'promote'            # 恢复后自动提升为主库

3. 创建恢复信号

touch /var/lib/postgresql/16/main/recovery.signal

4. 启动恢复

pg_ctl -D /var/lib/postgresql/16/main start

2.4 恢复后必做事项（关键）

恢复成功后，数据库可能处于只读状态或性能异常，需执行以下步骤：

  1. 提升为主库（如果未自动提升）
    -- 检查是否处于恢复模式
SELECT pg_is_in_recovery();
-- 如果返回 't'，手动提升
SELECT pg_promote();
  2. 更新统计信息（必须执行）
    vacuumdb -U postgres -a -Z -j 4
  3. 清理残留配置
    rm -f /var/lib/postgresql/16/main/recovery.signal
# 清理 postgresql.auto.conf 中的 restore_command 等配置

三、MySQL 备份与恢复

3.1 逻辑备份：mysqldump 最佳实践

注意：mysqlpump 在 MySQL 8.0.31+ 已被移除，请使用 mysqldump 或第三方工具 mydumper。

🔹 生产级命令

mysqldump -u root -p \
  --single-transaction \      # InnoDB 热备，不锁表
  --routines \                # 备份存储过程和函数
  --triggers \                # 备份触发器
  --events \                  # 备份事件
  --master-data=2 \           # 记录 binlog 位点（注释形式）
  --set-gtid-purged=OFF \     # 关键：恢复到原集群时必须设为 OFF
  --flush-logs \              # 切割日志，便于管理
  --source-data=2 \           # MySQL 8.0+ 替代 --master-data
  mydb > mydb.sql

🔹 GTID 陷阱解析

  • 恢复到新环境：保留 GTID 信息（不加参数或 =ON），以便搭建从库。
  • 恢复到原集群：必须设为 OFF，否则执行 SQL 时会因 GTID 已存在而报错。

3.2 物理备份：Percona XtraBackup

🔹 全量备份与准备

# 1. 备份
xtrabackup --backup \
  --target-dir=/backup/full \
  --user=root --password=secret
# 2. 准备阶段
xtrabackup --prepare \
  --target-dir=/backup/full
# 必须看到 "completed OK!" 字样才算成功

🔹 恢复流程

systemctl stop mysqld
rm -rf /var/lib/mysql/*   # 清空数据目录
# 恢复数据
xtrabackup --copy-back \
  --target-dir=/backup/full
# 修正权限（关键）
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

3.3 时间点恢复（PITR）与 GTID

  1. 恢复全量备份（如上节所示）。
  2. 提取 Binlog：
    • 从全量备份的 xtrabackup_binlog_info 或 dump 文件的头部找到起始位点。
    • 应用 Binlog 到目标时间：
    mysqlbinlog \
  --start-datetime="2025-10-27 10:00:00" \
  --stop-datetime="2025-10-27 10:05:00" \
  /var/log/mysql/mysql-bin.000003 | mysql -u root -p

3.4 增量备份与合成全量

🔹 增量备份流程

# 增量基于全量
xtrabackup --backup --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full

🔹 合并流程（恢复前必须操作）

# 1. 准备全量（只应用 redo，不回滚）
xtrabackup --prepare --apply-log-only --target-dir=/backup/full
# 2. 合并增量 inc1
xtrabackup --prepare --apply-log-only \
  --incremental-dir=/backup/inc1 --target-dir=/backup/full
# 3. 最后一个增量合并（完整 prepare，执行回滚）
xtrabackup --prepare \
  --incremental-dir=/backup/inc2 --target-dir=/backup/full

核心规则：只有合并最后一个增量时，才去掉 --apply-log-only。

四、常见故障排查手册

故障现象 可能原因 解决方案
PG 恢复后查询极慢 缺失统计信息 执行 ANALYZE VERBOSE; 或 vacuumdb -Z。
PG 恢复后只读不可写 仍处于 Recovery 模式 删除 recovery.signal；执行 SELECT pg_promote();。
MySQL 恢复报错 GTID already exists GTID 冲突 备份时加 --set-gtid-purged=OFF；或恢复前执行 RESET MASTER;。
XtraBackup 恢复后启动失败 权限问题或未 Prepare 检查是否执行 chown -R mysql:mysql；确认 prepare 步骤成功。
mysqldump 卡住不动 存在 MyISAM 表或大查询 确认使用 --single-transaction；检查是否有元数据锁。

五、附录：生产级自动化脚本

PostgreSQL 恢复后自动化处理脚本

#!/bin/bash
PGDATA="/var/lib/postgresql/16/main"
echo "Start PostgreSQL recovery post-process..."
# 1. 清理信号文件
rm -f $PGDATA/recovery.signal $PGDATA/standby.signal
# 2. 启动数据库
pg_ctl -D $PGDATA start -w -t 300
# 3. 确保提升为主库
psql -U postgres -c "SELECT pg_promote();"
# 4. 重建统计信息（关键步骤）
echo "Analyzing database..."
vacuumdb -U postgres -a -Z -j 4
echo "Recovery completed. Database is ready for read/write."

MySQL 安全备份脚本（GTID 兼容）

#!/bin/bash
DATE=$(date +%Y%m%d)
DIR="/backup/mysql/$DATE"
mkdir -p $DIR
echo "Starting MySQL backup..."
# 数据备份（安全模式，不包含 GTID 信息）
mysqldump -u root -p$MYSQL_PASS \
  --single-transaction \
  --routines --triggers --events \
  --set-gtid-purged=OFF \
  --all-databases | gzip > $DIR/full_data.sql.gz
# 权限备份（单独备份，避免风险）
pt-show-grants -u root -p$MYSQL_PASS > $DIR/grants.sql
echo "Backup finished at $DIR"

运维金句：
备份是最后的救命稻草，恢复能力才是真正的运维底气。
请务必在非生产环境定期演练上述流程。