Ubuntu 上 MySQL 备份与恢复完整指南

一 常见备份方案概览

• 逻辑备份：使用 mysqldump 导出为 .sql 脚本，适用于中小型数据库、跨版本迁移以及时间点恢复（结合二进制日志使用）。
• 并行逻辑备份：使用 mysqlpump（MySQL 5.7.12+）提高导出效率，适合多库或较大的数据库实例。
• 物理热备：使用 Percona XtraBackup 在不停机的情况下备份 InnoDB，适合大型数据库和快速恢复场景。
• 时间点恢复 PITR：启用 binlog 后，通过 mysqlbinlog 回放指定时间段或指定位置的事务日志。

二、mysqldump的逻辑备份与恢复应用

• 备份
  • 单库：mysqldump -u root -p --single-transaction --quick mydb > mydb.sql
  • 多库：mysqldump -u root -p --databases db1 db2 > dbs.sql
  • 全库：mysqldump -u root -p --all-databases > all.sql
  • 压缩：mysqldump -u root -p mydb | gzip > mydb.sql.gz
  • 远程：mysqldump -h 10.0.0.9 -P 3306 -u root -p mydb > mydb.sql
  • 常用选项说明：
    • –single-transaction：为 InnoDB 创建一致性快照，通常可避免锁表。
    • –quick：边读取边输出，降低内存占用，更适合较大的表。
    • 主从/复制场景可加 –master-data=2（记录 binlog 位点，方便后续 PITR 恢复）。
• 恢复
  • 准备库：mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS mydb;"
  • 导入：mysql -u root -p mydb < mydb.sql
  • 压缩包：gunzip < mydb.sql.gz | mysql -u root -p mydb
  • 全库导入：mysql -u root -p < all.sql
• 要点
  • 逻辑备份文件本质上是文本格式的 SQL，便于审计、修改、迁移和版本留存。
  • 建议在业务低峰期执行备份任务，并保留多份历史备份文件以降低风险。

三 使用 mysqlpump 并行备份与恢复

• 备份
  • 单库：mysqlpump -u root -p mydb > mydb_pump.sql
  • 多库：mysqlpump -u root -p --databases db1 db2 > dbs_pump.sql
  • 全库：mysqlpump -u root -p --all-databases > all_pump.sql
• 恢复
  • 与 mysqldump 相同：mysql -u root -p mydb < mydb_pump.sql
• 要点
  • mysqlpump 支持并行导出，备份速度通常快于 mysqldump；但恢复时仍然使用 mysql 客户端进行导入。

四、运用Percona XtraBackup执行物理热备与恢复

• 安装
  • Ubuntu：sudo apt-get update && sudo apt-get install percona-xtrabackup-24
• 备份
  • 全量：sudo xtrabackup --backup --target-dir=/opt/backup/2025-12-10
• 准备（使备份一致性）
  • sudo xtrabackup --prepare --target-dir=/opt/backup/2025-12-10
• 恢复
  • 建议先停库并清空数据目录：sudo systemctl stop mysql && sudo rm -rf /var/lib/mysql/*
  • 拷贝回数据：sudo xtrabackup --copy-back --target-dir=/opt/backup/2025-12-10
  • 修正权限：sudo chown -R mysql:mysql /var/lib/mysql
  • 启动：sudo systemctl start mysql
• 要点
  • 适合大库、高可用以及零停机备份需求，恢复效率通常更高。
  • 仅适用于 InnoDB/XtraDB；如果存在 MyISAM 表，在准备阶段通常会发生锁表。

五 时间点恢复与自动化运维

• 时间点恢复 PITR
  • 确认开启 binlog：mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin';"
  • 按时间回放：mysqlbinlog --start-datetime="2025-12-10 10:00:00" --stop-datetime="2025-12-10 10:05:00" /var/log/mysql/mysql-bin.000001 | mysql -u root -p
  • 按位置回放：mysqlbinlog --start-position=154 --stop-position=1056 /var/log/mysql/mysql-bin.000001 | mysql -u root -p
• 自动化备份脚本与定时任务
  • 示例脚本 /opt/backup/mysql_backup.sh：

    #!/bin/bash
    DATE=$(date +%F_%H-%M-%S)
    BACKUP_DIR="/opt/backup/mysql"
    DB="mydb"
    USER="root"
    mkdir -p "$BACKUP_DIR"
    mysqldump -u "$USER" -p --single-transaction --quick "$DB" 
      | gzip > "$BACKUP_DIR/${DB}_${DATE}.sql.gz"
    find "$BACKUP_DIR" -name "*.sql.gz" -mtime +7 -delete

  • 赋权：chmod +x /opt/backup/mysql_backup.sh
  • 每天 02:00 执行：0 2 * * * /opt/backup/mysql_backup.sh
• 远程备份与传输
  • rsync -a vz /opt/backup/mysql user@remote:/backup/mysql/
  • 或 scp /opt/backup/mysql/mydb_*.sql.gz user@remote:/backup/mysql/
• 备份管理与验证
  • 定期进行恢复演练/自动化恢复测试，确保 MySQL 备份文件真实可用。
  • 建议将备份保存到远程/云存储，同时做好访问控制与加密保护。