Ubuntu MySQL如何备份和恢复

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 -avz /opt/backup/mysql user@remote:/backup/mysql/
  • 或 scp /opt/backup/mysql/mydb_*.sql.gz user@remote:/backup/mysql/
• 备份管理与验证
  • 定期做恢复演练/自动化恢复测试，验证备份可用。
  • 将备份存于远程/云存储，并做好访问控制与加密。