场景

一位数据库管理员报告备份和恢复操作耗时过长，影响生产可用性。例如，一个200GB的MySQL数据库使用默认mysqldump需要4小时，而使用pg_dump备份同等大小的PostgreSQL数据库需要3小时。同时，恢复操作也频繁超时。

症状

• 备份命令执行时间远超预期（例如，备份速度低于50MB/s）
• 恢复过程中磁盘I/O等待时间高（iostat显示%util>90%）
• 复制延迟增加（特别是在备份期间）
• 备份文件体积过大（未压缩时接近原始数据大小）

诊断

MySQL

1. 检查当前备份命令：mysqldump --single-transaction --routines --triggers --events --all-databases > backup.sql
2. 监控备份期间的性能：pt-query-digest --processlist --interval=1 或 SHOW FULL PROCESSLIST;
3. 分析磁盘性能：iostat -x 1 查看磁盘利用率
4. 检查InnoDB缓冲池大小：SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';

PostgreSQL

1. 检查备份命令：pg_dump -Fc -j 4 -f backup.dump dbname （-j 指定并行作业数）
2. 使用pg_stat_statements分析查询：SELECT query, calls, total_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;
3. 检查WAL生成速率：pg_waldump -p /path/to/pg_wal/ 或 SELECT * FROM pg_stat_wal;
4. 监控I/O：iostat -x 1

命令

性能优化的备份命令

MySQL

• 使用压缩：mysqldump --single-transaction --compress --all-databases > backup.sql.gz
• 并行导出（通过mydumper）：mydumper -h localhost -u root -p password -o /backup --threads=4
• 使用物理备份（如XtraBackup）：xtrabackup --backup --parallel=4 --target-dir=/backup

PostgreSQL

• 使用pg_dump并行：pg_dump -Fd -j 8 -f /backup/dumpdir dbname （目录格式）
• 使用pg_basebackup进行物理备份：pg_basebackup -D /backup -X fetch -P -v
• 调整并行度：pg_dump -Fc -j $(nproc) -f backup.dump dbname

恢复优化

MySQL

• 使用mysqlbinlog实现时间点恢复：mysqlbinlog --start-datetime="2023-01-01 00:00:00" binlog.000001 | mysql -u root
• 恢复时禁用外键检查：SET FOREIGN_KEY_CHECKS=0; 然后导入，完成后重新启用。

PostgreSQL

• 并行恢复（使用pg_restore）：pg_restore -d dbname -j 4 -Fc backup.dump
• 使用流复制进行快速恢复：配置standby并启用hot standby。

风险控制

• 备份前：确保有足够的磁盘空间（建议为数据大小的1.5倍）。使用--single-transaction避免锁表（MySQL InnoDB）。设置lock_wait_timeout防止长时间锁等待。
• 备份中：监控系统负载，避免在高峰时段进行完整备份。使用nice降低备份进程优先级：nice -n 19 mysqldump ...
• 恢复前：备份目标数据库（如果存在）。在非生产环境测试恢复过程。
• 恢复中：禁用自动提交和索引维护（如可能）以加速恢复。

回滚

如果备份或恢复失败，立即终止操作： - MySQL：kill备份进程，检查文件完整性，必要时从上次成功备份恢复。 - PostgreSQL：终止pg_dump或pg_restore进程，清理部分文件。使用pg_ctl重启服务前确认数据一致性。

确保有最近的完整备份可用。如果损坏，使用快照或物理备份回滚。

验证

备份验证

• MySQL：mysqldump --all-databases | gzip > backup.sql.gz 后，解压并检查表：gunzip -c backup.sql.gz | grep -i "CREATE TABLE"。使用mysqlcheck检查表完整性。
• PostgreSQL：使用pg_restore -l backup.dump列出内容，或使用zcat backup.sql.gz | head -n 50检查。使用psql -f backup.sql在测试库上还原并运行ANALYZE。

恢复验证

• 比较行数：SELECT COUNT(*) FROM table; 对比原始和恢复后的数据库。
• 计算校验和：mysqldump --all-databases --order-by-primary | md5sum 比较备份与恢复后的输出。
• 运行应用测试：确保应用连接正常且查询返回正确结果。

何时提交OpsGlobal工单

• 经过优化后备份或恢复仍无法满足SLA（例如，超过4小时）
• 频繁出现备份损坏或恢复失败
• 需要专家协助配置物理备份（如XtraBackup或pg_basebackup）或高可用架构
• 怀疑底层存储或网络问题，需要基础设施层面分析

通过遵循本指南，您可以显著提升数据库备份与恢复的性能，确保业务连续性。