MySQL 与 PostgreSQL 备份恢复性能优化实战指南

场景描述

某电商平台数据库（MySQL 5.7）每日全量备份耗时超过 8 小时，且每周的恢复演练耗时超过 12 小时，严重影响备份窗口和 RTO（恢复时间目标）。同样，PostgreSQL 12 数据库在流复制基础上，执行 pg_dump 逻辑备份时 CPU 飙升，导致主库响应延迟。

常见症状

• 备份慢：mysqldump 或 pg_dump 执行时间远超预期，磁盘 I/O 利用率接近 100%，CPU 负载高。
• 恢复慢：从备份文件恢复时，导入速度远低于预期，尤其是大表或存在大量索引时。
• 日志暴增：二进制日志（MySQL）或 wal 日志（PostgreSQL）增长异常，导致磁盘空间不足。

诊断方法

MySQL

1. 检查备份命令的选项：mysqldump --single-transaction --quick --compress --skip-lock-tables 可加速逻辑备份。
2. 使用 SHOW ENGINE INNODB STATUS 查看是否有长事务阻塞备份。
3. 监控磁盘性能：iostat -x 1 查看 %util 和 await 值，判断是否达到瓶颈。
4. 分析备份日志：开启 general log 或使用 pt-query-digest 分析备份查询。

PostgreSQL

1. 检查 pg_dump 是否使用了并行选项：pg_dump -j 4 可并行转储。
2. 验证 wal 归档是否正常：pg_current_wal_lsn() 与归档位置对比。
3. 使用 pg_stat_activity 查看备份进程是否被其他查询阻塞。
4. 调整参数：wal_buffers, max_wal_size, checkpoint_completion_target 影响写入性能。

命令示例

MySQL 优化备份

# 使用 innobackupex 进行物理备份（更快）
innobackupex --user=backup --password --parallel=4 --no-timestamp /backup/

# 限制备份速率避免影响业务
pvb -i 300m -r 100m | mysqldump ... > dump.sql

PostgreSQL 优化备份

# 并行 pg_dump（仅 PG 9.4+）
pg_dump -j 4 -Fd -f /backup/dump_dir dbname

# 使用 pg_basebackup 进行物理备份
pg_basebackup -D /backup -X stream -P -v -z -Z 6

安全管控

• 备份期间避免 DDL 操作（MySQL 使用 lock_wait_timeout，PG 使用 lock_timeout）。
• 设置 IOPS 限制：ionice -c2 -n7 降低备份进程优先级。
• 监控备份进度：MySQL 用 PROCESSLIST 查看 Time 列；PG 用 pg_stat_progress_* 视图。
• 使用事务快照隔离：MySQL --single-transaction，PG 默认使用可重复读隔离级别。

回滚方案

始终保留上一个完整备份和增量备份。若恢复失败： 1. 停止恢复进程，防止数据覆盖。 2. 从冷备或复制节点重新加载。 3. 验证备份文件完整性：mysqlcheck --all-databases 或 pg_checksums。

验证方法

• 恢复后执行 SELECT COUNT(*) 对比原表行数。
• 使用 pt-table-checksum (MySQL) 或 pg_verifybackup (PG) 校验一致性。
• 检查最近事务时间戳是否合理。

何时提交 OpsGlobal 工单

• 备份/恢复性能问题持续超过 2 小时。
• 发生数据损坏或丢失风险。
• 需要专家调优数据库参数或备份策略。
• 备份工具版本不兼容导致错误。