MySQL与PostgreSQL备份恢复性能调优实战

场景

生产数据库备份缓慢或恢复时间过长，影响SLA。例如，MySQL使用mysqldump备份1TB数据库耗时超过8小时，或PostgreSQL使用pg_dump恢复时进度卡住。

症状

• 备份期间CPU使用率低但I/O等待高（iostat显示%util接近100%）
• 恢复时WAL（PostgreSQL）或redo log（MySQL）增长异常
• 备份进程消耗大量内存导致OOM
• 恢复后数据不一致或索引损坏

诊断

MySQL

SHOW ENGINE INNODB STATUS\G
-- 检查History list length和Log sequence number
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
-- 高Buffer pool reads表示缓存未命中

使用pt-query-digest分析慢查询日志，找出备份期间的热点查询。

PostgreSQL

SELECT * FROM pg_stat_progress_vacuum;
-- 查看vacuum进度，可能阻塞备份
SELECT * FROM pg_stat_replication WHERE state = 'streaming';
-- 检查复制延迟

使用pg_stat_statements找出高频查询。

命令

MySQL优化备份

• 使用mysqldump --single-transaction --innodb-optimize-keys --compress减少锁和网络开销。
• 并行备份：mydumper 或 mysqlpump（8.0+）--parallel-workers=4。
• 物理备份：xtrabackup --parallel=4 --compress --compress-threads=4。

恢复加速：

mysql -e 'SET GLOBAL innodb_flush_log_at_trx_commit=2;'
# 仅限恢复期间，风险控制见下文

PostgreSQL优化备份

• pg_dump -j 4 --format=directory --compress=9并行压缩。
• 使用pgbackrest或barman进行物理备份：

pgbackrest --stanza=my_stanza --type=full --process-max=4 backup

恢复时跳过WAL：

pg_restore -j 4 --disable-triggers --exit-on-error --dbname=mydb mydump.dmp

风险控制

• 备份期间限制IO：使用ionice -c 2 -n 7降低优先级。
• MySQL恢复：设置innodb_flush_log_at_trx_commit=2可在崩溃时丢失1秒数据，仅用于非关键恢复。
• PostgreSQL恢复：使用--single-transaction或--no-owner避免权限问题。
• 始终在恢复前创建快照或保留原始备份文件。

回滚

• 备份进程卡住：使用kill -9终止（MySQL）或pg_terminate_backend（PostgreSQL），然后清理残余表。
• 恢复失败：从快照回滚或使用上次成功备份从头恢复。

验证

• 备份完整性：mysqlcheck -A（MySQL）或pg_amcheck（PostgreSQL）。
• 恢复测试：在隔离环境执行恢复，并运行SELECT count(*)与业务查询。
• 性能基准：对比备份前后生产环境的QPS。

何时提交OpsGlobal工单

• 性能问题在调优后仍未解决。
• 备份占用超过50%系统资源且影响业务。
• 恢复后数据损坏或部分丢失。
• 需要设计自动化备份策略或灾备方案。