MySQL在Ubuntu中的备份策略有哪些

MySQL在Ubuntu中的备份策略

一 策略总览与选型

• 逻辑备份：使用 mysqldump / mysqlpump 导出为 SQL 脚本，适合中小型库、跨版本迁移、按库/表灵活恢复。可配合 gzip 压缩节省空间。
• 物理备份：使用 Percona XtraBackup（xtrabackup/innobackupex） 直接拷贝 InnoDB 数据文件，支持热备、增量备份，恢复速度快，适合大库与严格 RPO/RTO 场景。
• 文件系统快照：基于 LVM/ZFS 的快照可做瞬时一致性备份，需与数据库一致性手段配合（如冻结表空间/暂停写入）。
• 复制与云备份：通过主从复制构建 只读副本 作为在线热备；或将备份文件同步至 对象存储（如 MinIO） 提升异地容灾能力。

二 常用备份策略与适用场景

三 推荐备份方案与频率

• 方案A 逻辑备份 + 日常增量（通用）

  • 每日全量：使用 mysqldump/mysqlpump 导出所有库或关键库，配合 gzip 压缩与 cron 定时；保留 7–30 天。
  • 增量方式一：开启 binlog，每日在固定时点执行 mysqladmin flush-logs，备份当日新产生的 mysql-bin.* 文件；保留 7–14 天。
  • 增量方式二：使用 rsync 对数据目录做增量同步（适合物理文件快速复制，注意一致性窗口）。
  • 异地/云：用 rsync/scp 或 MinIO 客户端 将备份同步至远程主机/对象存储。

• 方案B 物理热备 + 增量（大库/严格RPO）

  • 每周一次 XtraBackup 全量，每日一次 增量（基于上次备份）；保留 2–4 周。
  • 恢复流程要点：准备（prepare）全量→依次应用增量→停库→copy-back→启动并校验。

• 方案C 快照增强（配合A或B）

  • 在关键时点（如全量前后）创建 LVM/ZFS 快照，用于快速回滚或克隆测试环境；快照不等同于备份，需与逻辑/物理备份结合。

四 快速实施步骤示例

• 逻辑全量备份脚本（示例）

  • 创建脚本 /usr/local/bin/mysql_backup.sh：

    #!/usr/bin/env bash
    BACKUP_DIR="/var/backups/mysql"
    MYSQL_USER="backup"
    MYSQL_PASS="YourStrongPass"
    DATE=$(date +%F)
    mkdir -p "$BACKUP_DIR"
    mysqldump -u"$MYSQL_USER" -p"$MYSQL_PASS" --single-transaction --routines --triggers --default-character-set=utf8mb4 --all-databases \
      | gzip > "$BACKUP_DIR/all_databases_$DATE.sql.gz"
    # 可选：同步到远程
    # rsync -avz "$BACKUP_DIR/" user@remote:/backup/mysql/
    

  • 定时任务（每天 02:00）：

    0 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1
    

  • 恢复示例：

    gunzip < /var/backups/mysql/all_databases_2025-11-14.sql.gz | mysql -u root -p
    

  • 要点：使用 –single-transaction 获取一致性快照（InnoDB），开启 –routines/–triggers 保留对象定义。

• 二进制日志增量备份（示例）

  • 开启 binlog（my.cnf）：

    [mysqld]
    log_bin=/var/log/mysql/mysql-bin
    server_id=1
    binlog_format=ROW
    

  • 每日轮转并备份新日志：

    #!/usr/bin/env bash
    BINLOG_DIR="/var/log/mysql"
    BACKUP_DIR="/var/backups/mysql/binlog"
    mkdir -p "$BACKUP_DIR"
    mysqladmin --user=backup --password=YourStrongPass flush-logs
    cp "$BINLOG_DIR"/mysql-bin.0* "$BACKUP_DIR/"
    

  • 时间点恢复（按时间或位置）：

    mysqlbinlog --start-datetime="2025-11-14 10:00:00" --stop-datetime="2025-11-14 10:30:00" \
      /var/log/mysql/mysql-bin.00000X | mysql -u root -p
    

  • 要点：执行 flush-logs 后，新的写入会进入新编号的 binlog，便于按日归档与回滚。

• XtraBackup 物理增量（示例）

  • 首次全量：

    innobackupex --user=backup --password=YourStrongPass --no-timestamp /backup/mysql/full_20251114
    

  • 每日增量（基于上次备份）：

    innobackupex --user=backup --password=YourStrongPass --no-timestamp \
      --incremental /backup/mysql/incr_20251115 \
      --incremental-basedir=/backup/mysql/full_20251114
    

  • 恢复（概要）：准备全量→依次 apply 增量→停库→copy-back→启动并校验。

五 备份可靠性与运维要点

• 保留与异地：至少保留 7–30 天；关键备份同步至 远程主机/对象存储，避免单点。
• 加密与权限：备份文件设置 600 权限；凭据使用 ~/.my.cnf 或凭据文件，避免在命令行暴露密码。
• 校验与演练：定期做 恢复演练 与 校验和/行数 对比，确保可用。
• 监控与告警：监控 cron 退出码、备份文件大小与延迟；异常即时告警。
• 一致性：逻辑备份 InnoDB 建议 –single-transaction；MyISAM 需全局读锁（会短暂阻塞写入）。
• 空间管理：对 binlog 与历史备份设置 自动清理策略，避免磁盘被占满。