mysql数据库备份的基本方法与工具使用

mysqldump适用于中小规模逻辑备份，mydumper适合大库并行高效导出，XtraBackup用于TB级物理热备；三者均需注意系统库备份缺失、权限补充及备份验证、密码安全、异地存储等关键细节。

mysqldump 备份单库或全库最常用

绝大多数 MySQL 备份场景下，mysqldump 是首选工具，它生成的是可读的 SQL 文本，兼容性好、恢复直观，适合中小规模数据（通常

关键点在于权限和参数组合：

• 执行用户需有 select、LOCK TABLES（非 –single-transaction 时）、SHOW VIEW、TRIGGER 权限
• 加 --single-transaction 可避免锁表（仅对 InnoDB 有效），生产环境几乎必加
• 加 --routines 和 --events 才能导出存储过程和事件调度器
• 不加 --databases 时，第一个参数是数据库名；加了则可一次导多个库

mysqldump -u root -p --single-transaction --routines --events myapp_db > myapp_db_$(date +%F).sql

注意：mysqldump 不备份 MySQL 系统库（如 mysql、information_schema），若需备份用户权限，要单独导出 mysql.user 表或用 --all-databases + 排除过滤。

mydumper 备份大库更高效（并行 + 无锁）

当单表超千万行或总数据量超 100GB，mysqldump 明显变慢且容易超时，mydumper 是更优选择——它是 C 写的多线程导出工具，支持按表并发、一致性快照（依赖 Percona Server 或 MySQL 5.6+ 的 GTID/FLUSH TABLES WITH READ LOCK）。

常见误用点：

• 未安装 myloader（配套恢复工具），导致导出后不会还原
• 忽略 --trx-consistency-only 和 --no-locks 的区别：前者靠事务一致性，后者仍可能锁表
• 未用 --compress 导致临时文件巨大，磁盘爆满

mydumper -u root -p password -B myapp_db -o /backup/myapp_db/ --single-transaction --compress

导出结果是多个 .sql 文件（按表拆分）+ metadata 记录 binlog 位置，这对后续基于时间点恢复很关键。

物理备份用 Percona XtraBackup（适合热备 + 快速恢复）

当数据库达 TB 级、RTO 要求分钟级，必须用物理备份。Percona XtraBackup（简称 xtrabackup）是目前唯一成熟支持 InnoDB 热备份的开源工具，直接拷贝数据文件 + redo log，速度远超逻辑备份。

但它的使用门槛明显更高：

• 版本强耦合：MySQL 8.0.33+ 需用 xtrabackup 8.0.33+，否则备份失败并报错 Unknown redo log format
• 备份后必须执行 --prepare（回放 redo），否则无法启动 MySQL
• 恢复时不能直接覆盖运行中实例的数据目录，必须停库 + 替换 + chown + 启动

xtrabackup --backup --target-dir=/backup/full_$(date +%F) --user=root --password=xxx

注意：xtrabackup 默认不备份 mysql 系统库中的权限表（如 user、db），若需完整权限恢复，得额外用 mysqldump mysql.user 补充。

备份策略里最容易被忽视的三件事

再好的工具也救不了没设计的策略。以下三点在真实故障中反复踩坑：

• mysqldump 或 mydumper 导出的 SQL 文件，必须定期 gunzip -t 或 head -n 100 检查是否截断（网络中断、磁盘满、权限不足都会静默失败）
• 所有备份命令必须加 --defaults-file 指定配置文件，避免密码明文出现在 ps aux 或 bash history 中
• 备份文件存放在与数据库同盘时，一旦磁盘损坏，备份和原库一起丢——至少一份要异地（如 rsync 到另一台机器或对象存储）

真正决定备份成败的，往往不是工具选型，而是这些看似琐碎却没人盯的执行细节。