技术 · 思考 · 实践 · 成长

首页/MySQL8专栏/正文

MySQL 8 数据库备份的三种方式:逻辑备份、并行备份与物理备份

MySQL8专栏1214 次阅读

数据库备份的目标不是生成一个文件,而是在发生误删除、硬件故障或升级失败后,能够在规定时间内恢复业务。MySQL 8 常用备份方案可以分为三类:mysqldump 逻辑备份、MySQL Shell 并行逻辑备份,以及专业工具或一致性快照完成的物理备份。

一、先确定 RPO 和 RTO

  • RPO(恢复点目标):最多允许丢失多长时间的数据,例如 5 分钟。
  • RTO(恢复时间目标):发生故障后,业务最多允许中断多长时间。

如果只做每天一次全量备份,理论上可能丢失接近一天的数据。需要更小 RPO 时,应在全量备份基础上保存 binlog,用于时间点恢复。

二、三种备份方式对比

方式适用场景优点主要限制
mysqldump中小型数据库、单库或单表备份通用、易查看、跨环境恢复方便大库导出和恢复速度较慢
MySQL Shell Dump较大数据库、需要并行导出和加载支持分块、并行和压缩需要安装 MySQL Shell
物理备份/一致性快照大型重要数据库、要求快速恢复备份和恢复通常更快对工具、版本、存储和操作流程要求较高

三、方式一:使用 mysqldump 逻辑备份

mysqldump 将数据库对象和数据导出为 SQL 文件,适合中小规模数据。密码参数使用单独的 -p,执行时交互输入,不要把密码直接写在命令中。

1. 备份单个数据库

mysqldump \
  -h 127.0.0.1 \
  -u backup_user \
  -p \
  --single-transaction \
  --quick \
  --routines \
  --events \
  --triggers \
  --hex-blob \
  --default-character-set=utf8mb4 \
  --set-gtid-purged=OFF \
  --databases booksdb \
  > booksdb_$(date +%F_%H%M%S).sql
  • --single-transaction:为 InnoDB 表创建一致性快照,减少锁表影响。
  • --quick:逐行读取数据,降低客户端内存占用。
  • --routines、--events:包含存储程序和事件;触发器默认包含,这里显式写出便于检查。
  • --hex-blob:以十六进制形式导出二进制列。
  • --set-gtid-purged:应按复制拓扑决定;示例关闭 GTID 信息,不能机械用于所有恢复场景。

--single-transaction 只保证 InnoDB 等事务表的一致性。备份期间应避免对相关表执行 ALTER TABLE、DROP TABLE、RENAME TABLE 或 TRUNCATE TABLE。

2. 备份指定数据表

mysqldump \
  -h 127.0.0.1 \
  -u backup_user \
  -p \
  --single-transaction \
  booksdb books authors \
  > books_and_authors.sql

数据库名后面依次填写表名,多个表名使用空格分隔。

3. 只备份表结构或数据

# 只备份结构,不导出数据
mysqldump -u backup_user -p --no-data booksdb > booksdb_schema.sql

# 只备份数据,不包含建表语句
mysqldump -u backup_user -p --no-create-info booksdb > booksdb_data.sql

4. 备份多个数据库

mysqldump \
  -u backup_user \
  -p \
  --single-transaction \
  --routines \
  --events \
  --databases booksdb app_db \
  > booksdb_app_db.sql

5. 压缩备份

mysqldump \
  -u backup_user \
  -p \
  --single-transaction \
  --quick \
  --databases booksdb \
  | gzip > booksdb.sql.gz

6. 恢复 mysqldump 备份

使用 --databases booksdb 导出的文件通常包含创建和选择数据库的语句:

mysql -h 127.0.0.1 -u restore_user -p < booksdb.sql

恢复压缩文件:

gzip -dc booksdb.sql.gz \
  | mysql -h 127.0.0.1 -u restore_user -p

不要直接覆盖生产数据库进行首次恢复测试。应先恢复到隔离实例,确认数据和对象完整后再执行正式恢复。

四、方式二:使用 MySQL Shell 并行备份

MySQL Shell 的 Dump 和 Load 工具支持多线程、数据分块和压缩,通常比单文件 mysqldump 更适合较大数据库。

1. 备份指定数据库

连接 MySQL Shell:

mysqlsh --js \
  --host=127.0.0.1 \
  --user=backup_user \
  --password

在 JavaScript 模式中执行:

util.dumpSchemas(
  ["booksdb"],
  "/backup/booksdb_dump",
  {
    threads: 4,
    consistent: true,
    compression: "zstd"
  }
);

2. 备份整个实例

util.dumpInstance(
  "/backup/mysql_instance_dump",
  {
    threads: 4,
    consistent: true,
    compression: "zstd"
  }
);

实例备份涉及更多对象和权限,正式使用前应确认所选 MySQL Shell 版本、选项和目标恢复环境。线程数应根据数据库负载、磁盘和网络能力设置。

3. 恢复备份

使用 MySQL Shell 连接隔离的目标实例后执行:

util.loadDump(
  "/backup/booksdb_dump",
  {
    threads: 4,
    progressFile: "/backup/booksdb-load-progress.json"
  }
);

转储目录中包含元数据和分块文件。不要只复制其中部分文件,也不要在备份尚未完成时把目录当作有效备份。

五、方式三:物理备份或一致性快照

物理备份直接保存数据库文件,通常适合数据量大、恢复时间要求高的场景。常用方案包括 MySQL Enterprise Backup、经过验证的第三方热备工具,或与数据库一致性流程配合的存储快照。

不建议在 MySQL 正常写入时直接复制 /var/lib/mysql。InnoDB 数据字典、表空间、重做日志和撤销日志必须保持一致,普通文件复制可能得到无法恢复或数据不一致的备份。

物理备份方案必须明确以下事项:

  • 是否支持当前 MySQL 精确版本和操作系统。
  • 备份是在线、温备还是需要停机。
  • 如何处理加密、密钥、表空间和 binlog。
  • 恢复前是否需要准备、合并增量或应用日志。
  • 备份文件是否可以恢复到不同主机及不同硬件。
  • 如何校验备份完整性并定期演练恢复。

文件系统或云盘快照速度快,但必须先满足数据库一致性要求。不能把“创建了一份磁盘快照”等同于“拥有可恢复的数据库备份”。

六、binlog 时间点恢复

全量备份只能恢复到备份时刻。如果启用了二进制日志并妥善保留,可以先恢复全量备份,再使用 mysqlbinlog 重放之后的变更,恢复到误操作之前的时间或日志位置。

查看 binlog 配置和文件:

SHOW VARIABLES LIKE 'log_bin';
SHOW BINARY LOGS;

查看指定日志内容:

mysqlbinlog \
  --start-datetime="2026-11-14 10:00:00" \
  --stop-datetime="2026-11-14 10:30:00" \
  binlog.000123 \
  > binlog_replay.sql

检查生成的文件后,在隔离环境重放:

mysql --binary-mode \
  -h 127.0.0.1 \
  -u restore_user \
  -p \
  < binlog_replay.sql

时间点恢复对时区、日志边界、GTID 和目标实例状态非常敏感。正式恢复前应确认起止位置,避免重复应用事务或跨过误操作事件。binlog 是全量备份的补充,不能单独替代基础备份。

七、备份账号的最小权限

不要长期使用业务账号或 root 执行自动备份。所需权限取决于备份工具、选项和 MySQL 版本;mysqldump 常见需求包括 SELECT、SHOW VIEW、TRIGGER、EVENT 等,某些选项还需要额外权限。

SHOW GRANTS FOR 'backup_user'@'localhost';

应根据实际命令和官方文档授予最小权限,并保护备份账号凭据。不要把密码直接写进脚本或提交到代码仓库。

八、备份后的验证

每次备份至少完成以下检查:

  1. 命令退出状态为成功,日志中没有错误。
  2. 备份文件或目录存在,大小符合预期。
  3. 计算并保存哈希值,传输后再次核对。
  4. 在隔离实例完成实际恢复。
  5. 校验表、视图、触发器、存储程序和事件。
  6. 对关键表比较行数、主键范围和业务汇总。
  7. 执行应用的核心读写流程。
sha256sum booksdb.sql.gz

仅仅看到备份文件存在,并不能证明它可以恢复。

九、备份保留与安全

  • 遵循 3-2-1 原则:至少 3 份数据、2 种介质、1 份异地保存。
  • 根据 RPO 制定全量、增量和 binlog 保留周期。
  • 对备份进行加密,并单独管理加密密钥。
  • 限制备份目录和对象存储权限,记录下载和删除操作。
  • 避免备份文件与数据库只存放在同一台服务器或同一块磁盘。
  • 设置容量、备份失败和恢复演练告警。
  • 定期清理过期备份,但删除前确认保留策略和合规要求。

十、为什么不再推荐 mysqlhotcopy

mysqlhotcopy 是面向旧式 MyISAM/ARCHIVE 文件复制的历史工具,不适合作为 MySQL 8 InnoDB 数据库的现代备份方案。也不能因为 MySQL 把数据保存在文件中,就直接复制数据库目录。

十一、推荐备份方案

  • 小型项目:每日 mysqldump,持续保存 binlog,并定期恢复测试。
  • 中大型项目:MySQL Shell 并行全量备份,加 binlog 时间点恢复。
  • 大型关键系统:经过验证的热物理备份、增量备份、binlog 和异地副本,并持续演练灾难恢复。

复制和高可用副本可以减少故障切换时间,但误删除会同步到副本,因此不能替代独立备份。

十二、官方文档

十三、总结

mysqldump 适合中小型逻辑备份,MySQL Shell 适合并行备份和恢复,专业物理备份适合大型数据库和较短 RTO。无论使用哪种方式,都必须配套安全存储、binlog、监控和恢复演练;未经恢复验证的文件,不能视为可靠备份。

所谓坚持,就是从种子种下到开花结果需要等待的时间。

评论交流

0 条评论 · 支持多级回复
验证码
暂无评论,来说说你的想法吧

相关文章

继续阅读