mysqldump —— 逻辑备份与迁移

分类：08 数据库工具适用：MySQL 5.7 / 8.0 / 8.4，MariaDB 10.x（参数略有差异）一句话：生成可读 SQL 的逻辑备份，跨版本迁移、单库导出、小库日常备份的首选。

1. 它解决什么问题

每日全量备份，能恢复到任意一天。
把单个库/表迁到另一台机器或另一个版本。
导出结构给开发 review，或导出部分数据做测试环境。
升级前留一份"能直接 source"的兜底。

不适合：TB 级大库（慢且锁风险高）、需要物理级热备（用 XtraBackup）。

2. 最小可用命令

# 单库
mysqldump -u root -p --single-transaction --routines --triggers mydb > mydb.sql

# 多库（含 create database）
mysqldump -u root -p --databases db1 db2 > dbs.sql

# 全库
mysqldump -u root -p --all-databases --single-transaction > all.sql

# 只导结构
mysqldump -u root -p --no-data mydb > schema.sql

# 只导数据
mysqldump -u root -p --no-create-info mydb > data.sql

# 单表
mysqldump -u root -p mydb orders > orders.sql

# 压缩
mysqldump -u root -p --single-transaction mydb | gzip > mydb-$(date +%F).sql.gz

3. 关键参数（按重要性排序）

参数	作用	为什么重要
`--single-transaction`	在一个事务里导出，保证一致性且不锁表	InnoDB 必加，否则可能锁表或数据不一致
`--routines`	导出存储过程与函数	默认不导，恢复后逻辑丢失
`--triggers`	导出触发器	默认导出，但配 `--skip-triggers` 可关闭
`--events`	导出事件调度器任务	默认不导
`--source-data=2`	记录 binlog 位置（注释形式）	搭建从库/时间点恢复用；8.0.26+ 替代 `--master-data`
`--set-gtid-purged=OFF`	不写 GTID 信息	导入到非 GTID 环境必须关，否则报错
`--default-character-set=utf8mb4`	指定字符集	避免乱码
`--hex-blob`	二进制以十六进制导出	含 blob/bit 字段时必加
`--quick`	逐行读取，不缓存整个结果	大表防 OOM（默认已开启）
`--skip-extended-insert`	每行一条 INSERT	便于 diff 和部分恢复，文件更大
`--complete-insert`	INSERT 带列名	表结构顺序变化时更安全
`--no-tablespaces`	不导表空间信息	8.0 普通用户无 `PROCESS` 权限时必加
`--where="..."`	条件导出	只导部分数据
`--ignore-table=db.t`	排除表	可多次
`--max-allowed-packet=1G`	大包上限	大字段导出报错时调
`--compress`	传输压缩	跨网络导出有效
`--tab=/path`	每表导出 `.sql` + `.txt`	快速导入用 `LOAD DATA`

4. 一致性与锁

引擎	推荐参数	影响
InnoDB	`--single-transaction`	不锁表，一致性快照
MyISAM / MEMORY	`--lock-tables`（默认）	会锁表，业务受影响
混合	`--single-transaction --lock-tables=false`	MyISAM 表可能不一致

--single-transaction 只对 InnoDB 有效，且要求隔离级别为 REPEATABLE READ（默认）。

导出期间不要执行 DDL（ALTER/DROP），否则可能报 Table definition has changed 或拿到不一致结构。

5. 恢复

# 普通 SQL 文件
mysql -u root -p mydb < mydb.sql

# 压缩文件
gunzip -c mydb.sql.gz | mysql -u root -p mydb

# 含 CREATE DATABASE 的备份
mysql -u root -p < dbs.sql

# 导入前建议
mysql -u root -p -e "SET GLOBAL max_allowed_packet=1073741824;"
mysql -u root -p -e "SET GLOBAL net_buffer_length=1048576;"

恢复加速技巧：

# 临时关双写与唯一性检查（仅恢复期间）
mysql -u root -p mydb -e "
SET unique_checks=0;
SET foreign_key_checks=0;
SET sql_log_bin=0;
source /tmp/mydb.sql;
"

导入完成后记得恢复 foreign_key_checks=1。

6. 实战场景

6.1 每日备份脚本（带保留与校验）

#!/bin/bash
set -euo pipefail

BACKUP_DIR=/backup/mysql
DAY=$(date +%F_%H%M)
FILE="$BACKUP_DIR/mydb-$DAY.sql.gz"

mkdir -p "$BACKUP_DIR"

mysqldump \
  --defaults-extra-file=/etc/mysql/backup.cnf \
  --single-transaction --quick \
  --routines --triggers --events \
  --source-data=2 \
  --set-gtid-purged=OFF \
  --default-character-set=utf8mb4 \
  --hex-blob \
  --no-tablespaces \
  mydb | gzip -6 > "$FILE"

# 校验：文件非空且能读出结尾标记
gzip -t "$FILE"
tail -c 200 <(zcat "$FILE") | grep -q "Dump completed" || { echo "备份不完整"; exit 1; }

# 保留 14 天
find "$BACKUP_DIR" -name '*.sql.gz' -mtime +14 -delete

echo "$(date) backup ok: $FILE $(du -h "$FILE" | cut -f1)"

--defaults-extra-file 避免密码出现在 ps。

6.2 跨版本迁移（5.7 → 8.0）

# 源端（5.7）
mysqldump -u root -p \
  --single-transaction --routines --triggers --events \
  --set-gtid-purged=OFF \
  --default-character-set=utf8mb4 \
  --no-tablespaces \
  --all-databases > all-57.sql

# 目标端（8.0）
mysql -u root -p < all-57.sql
mysql_upgrade -u root -p      # 8.0.16 之后通常不需要

注意：

5.7 的 utf8 在 8.0 里仍是 utf8mb3，建议迁移后统一改 utf8mb4。
8.0 默认认证插件 caching_sha2_password，老客户端连不上，需评估。
sql_mode 更严格，含零日期/非法值的数据会导入失败。

6.3 只导一部分数据

mysqldump -u root -p mydb orders \
  --where="created_at >= '2026-01-01' AND created_at < '2026-02-01'" \
  > orders-jan.sql

--where 不能与 --databases/--all-databases 同用。

6.4 排除大表/日志表

mysqldump -u root -p --single-transaction mydb \
  --ignore-table=mydb.audit_log \
  --ignore-table=mydb.session_data \
  > mydb-noaudit.sql

6.5 只恢复一张表

# 从全库备份中提取单表
sed -n '/CREATE TABLE `orders`/,/Dump completed/p' all.sql > orders.sql
# 更可靠：用 awk 按表边界提取，或用 mydumper

正规做法是用 mydumper/myloader，支持并行与按表恢复。

6.6 验证备份可用（重要）

# 恢复到临时实例验证，而不是只看文件大小
mysql -u root -p -e "create database verify_tmp"
gunzip -c /backup/mysql/mydb-latest.sql.gz | mysql -u root -p verify_tmp
mysql -u root -p -e "select count(*) from verify_tmp.orders"
mysql -u root -p -e "drop database verify_tmp"

没验证过的备份等于没有备份。

7. 常见坑

忘加 --single-transaction：InnoDB 表被 LOCK TABLES，业务写入阻塞。
忘加 --routines --triggers --events：恢复后发现存储过程、触发器、定时事件全没了。
--set-gtid-purged 未关：导入到非 GTID 库报 ERROR 1840。
8.0 的 --no-tablespaces：不加会报 Access denied; you need PROCESS privilege。
--master-data 在 8.0.26+ 已改名 --source-data，老参数仍兼容但会警告。
字符集不指定：客户端默认字符集与库不同，中文变 ???。
有 blob 字段没加 --hex-blob：二进制被破坏。
大库导致 mysqldump: Error 2013：连接超时，调 --max-allowed-packet、net_read_timeout。
备份文件不完整：磁盘满、管道中断都会产生"看似正常"的半截文件，检查结尾 -- Dump completed。
恢复到生产前没测：DROP TABLE 语句会直接删表，先看文件头。
触发器 DEFINER 用户不存在：导入报错，用 --skip-definer 或先建用户。
--all-databases 包含 mysql 库：覆盖系统表风险高，迁移时慎用。
时区问题：TIMESTAMP 字段按会话时区转换，导出与导入时区不一致会偏移。
MyISAM 表在 --single-transaction 下仍可能不一致：需要停写或单独处理。

8. 速查表

# 单库（推荐模板）
mysqldump -u root -p --single-transaction --quick \
  --routines --triggers --events \
  --set-gtid-purged=OFF --default-character-set=utf8mb4 --hex-blob \
  mydb | gzip > mydb.sql.gz

# 多库 / 全库
mysqldump -u root -p --databases db1 db2 > dbs.sql
mysqldump -u root -p --all-databases --single-transaction > all.sql

# 结构 / 数据
mysqldump -u root -p --no-data mydb > schema.sql
mysqldump -u root -p --no-create-info mydb > data.sql

# 单表 / 条件
mysqldump -u root -p mydb orders > orders.sql
mysqldump -u root -p mydb orders --where="id<1000" > part.sql

# 恢复
gunzip -c mydb.sql.gz | mysql -u root -p mydb
mysql -u root -p < dbs.sql

# 校验
gzip -t mydb.sql.gz
zcat mydb.sql.gz | tail -3 | grep 'Dump completed'