mysqldump 全库备份

导学

mysqldump 是 MySQL 自带的逻辑备份工具，它将数据库结构和数据导出为可执行的 SQL 文本。全库备份是最常用的备份场景——每天凌晨跑一次，生成一个 .sql 文件，既是灾难恢复的底线，也是迁移环境的利器。

定义

mysqldump：MySQL 官方提供的逻辑备份客户端程序，通过执行 SELECT 将表数据转为 INSERT 语句，同时导出 CREATE TABLE 等结构语句，最终生成纯文本 SQL 文件。

核心参数速查

参数	作用	使用建议
`--single-transaction`	对 InnoDB 开启一致性读事务，不锁表	InnoDB 必加
`--master-data=2`	记录 binlog 文件名和位置（注释形式）	搭建从库或 PITR 必加
`--all-databases`	备份所有数据库	全库备份用
`--databases db1 db2`	备份指定数据库	多库备份用
`--routines`	包含存储过程和函数	完整备份必加
`--triggers`	包含触发器	完整备份必加
`--events`	包含事件调度器	完整备份必加
`--hex-blob`	二进制字段用十六进制导出	避免 BLOB 数据损坏
`--set-gtid-purged=OFF`	不输出 GTID 相关语句	跨实例恢复时避免 GTID 冲突
`--skip-lock-tables`	跳过锁表（配合 `--single-transaction`）	避免与事务选项冲突

场景一：全库一致性备份

当前数据状态：company 库有员工表和绩效表，业务正在运行。

USE company;
SELECT emp_name, dept FROM employees;

emp_name	dept
大翔	技术部
白歌	技术部

操作语句（在服务器命令行执行）：

mysqldump -u root -p \
  --single-transaction \
  --master-data=2 \
  --routines \
  --triggers \
  --events \
  --all-databases \
  --hex-blob \
  > /backup/full_backup_$(date +%Y%m%d_%H%M%S).sql

输入 root 密码后，命令执行期间：

--single-transaction 在 InnoDB 内部开启一个 REPEATABLE READ 事务，备份期间看到的数据是命令开始时的快照，不阻塞业务写入
--master-data=2 在 SQL 文件头部写入 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=1234;
备份文件大小取决于数据量，可用 gzip 压缩：... | gzip > backup.sql.gz

验证备份文件：

head -n 20 /backup/full_backup_*.sql

输出中应包含：

-- MySQL dump 10.13  Distrib 5.7.44, for Linux (x86_64)
--
-- Host: localhost    Database:
-- ------------------------------------------------------
-- Server version	5.7.44-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
-- ...
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=1234;

结果解读：-- CHANGE MASTER TO 这行注释记录了备份时刻的 binlog 坐标，是 point-in-time 恢复和搭建从库的关键锚点。

场景二：只备份结构（不备份数据）

操作语句：

mysqldump -u root -p --no-data --databases company > company_schema.sql

验证：

grep -c "INSERT INTO" company_schema.sql

预期输出：0

grep "CREATE TABLE" company_schema.sql | head -n 3

预期输出包含 CREATE TABLE 语句。

结果解读：--no-data 只导出 CREATE DATABASE、CREATE TABLE、CREATE INDEX 等结构语句，不导出 INSERT。适合快速复制表结构到新环境，或做 schema 版本管理。

场景三：只备份数据（不备份结构）

操作语句：

mysqldump -u root -p --no-create-info --databases company > company_data_only.sql

验证：

grep -c "CREATE TABLE" company_data_only.sql

预期输出：0

grep "INSERT INTO" company_data_only.sql | head -n 2

预期输出包含 INSERT INTO 语句。

结果解读：--no-create-info 跳过所有 CREATE 语句，只导出 INSERT。适合表结构已存在、只需导入数据的场景（如测试环境刷新数据）。

常见误区

误区	正解
"不加 `--single-transaction` 也能备份"	可以，但默认会锁表（`FLUSH TABLES WITH READ LOCK`），阻塞写入。InnoDB 生产环境必须加。
"`--master-data=2` 会锁表"	会短暂加全局读锁（获取 binlog 坐标），但配合 `--single-transaction` 时锁的时间极短（通常 < 1 秒）。
"mysqldump 备份文件可以直接用编辑器打开"	小库可以；大库（> 1GB）用 `less`、`head`、`grep` 查看，不要直接用记事本打开。
"备份时加 `--quick` 没用"	大表必加。默认 mysqldump 将整张表读入内存再输出，`--quick` 逐行读取，避免内存溢出。

面试考点

Q：--single-transaction 的原理是什么？

对 InnoDB 开启一个 REPEATABLE READ 事务，利用 MVCC 获得一致性快照。备份期间业务可以正常读写，不阻塞。但仅对 InnoDB 有效，MyISAM 仍需锁表。

Q：--master-data=1 和 =2 的区别？

=1 写入非注释的 CHANGE MASTER TO 语句，恢复时会自动执行；=2 写入注释形式（前面加 --），恢复时不会自动执行，需手动提取坐标。推荐 =2，避免误恢复时自动改主库配置。

Q：mysqldump 备份 100GB 的库有什么问题？

生成 SQL 文件体积大（通常比数据文件大 2~3 倍）；2. 恢复极慢（逐行 INSERT）；3. 内存消耗高（不加 --quick 时）。大库应改用 xtrabackup 物理备份。

小结

mysqldump 是逻辑备份，生成可执行的 SQL 文本
InnoDB 全库备份必加 --single-transaction（不锁表）和 --master-data=2（记录 binlog 坐标）
--no-data 只备份结构，--no-create-info 只备份数据
大表备份加 --quick 避免内存溢出，大库整体应考虑 xtrabackup