第 39 章 MySQL踩坑：备份恢复与迁移篇

📖 本篇简介：本篇是《MySQL 从入门到精通》踩坑系列的第 7 篇，聚焦 DBA 最核心也最容易翻车的备份恢复与迁移场景。前置知识建议先复习第 16 章备份与恢复、第 22 章主从复制（迁移常依赖复制）。学完本篇你将能写出靠谱的 mysqldump 备份命令、正确执行恢复顺序、避免字符集乱码、搭建全量+增量备份链路。本示例基于 MySQL 8.0，命令行参数按 8.0 版本书写。

目录

• 坑 1：mysqldump 漏加一致性参数
• 坑 2：恢复顺序错误违反外键约束
• 坑 3：字符集不匹配导致恢复后乱码
• 坑 4：只做全量备份漏掉 binlog 增量
• 坑 5：备份从未做恢复演练
• 坑 6：漏备份存储过程触发器事件
• 坑 7：跨大版本迁移数据字典报错
• 坑 8：恢复前未清库导致新旧数据混杂
• 坑 9：SELECT INTO OUTFILE 权限陷阱
• 坑 10：备份期间未保一致性导致表损坏
• 坑 11：my.cnf 配置文件遗忘备份
• 坑 12：导入时漏指定字符集乱码
• 本篇速查表
• 运维注意事项
• 动手练习

坑 1：mysqldump 漏加一致性参数

场景 / 错误示范：

上线备份脚本，只写了最朴素的 mysqldump -uroot -p db > db.sql，凌晨跑批时备份出来的数据是"撕裂"的。

# 错误：没有一致性保证，备份过程中业务还在写
mysqldump -uroot -p123456 appdb > /backup/appdb_full.sql
# InnoDB 表没有 --single-transaction，会拿到不一致的快照

为什么是坑：不加 --single-transaction，mysqldump 默认对 MyISAM 锁表、对 InnoDB 也会按表加锁；备份期间业务写入会让导出文件里的多张表处于不同时间点，恢复出来外键对不上、订单与流水对不上。

正确做法：

# 正确：InnoDB 一致性快照 + 记录位点 + 导出存储过程事件
mysqldump -uroot -p123456 \
  --single-transaction \      # 用事务快照保证一致性，不锁表
  --master-data=2 \           # 记录备份时刻 binlog 位点（注释形式）
  --routines \                # 导出存储过程与函数
  --triggers \                # 导出触发器
  --events \                  # 导出事件调度
  --default-character-set=utf8mb4 \
  --set-gtid-purged=AUTO \
  appdb > /backup/appdb_full.sql

加分点：--master-data=2 比 =1 好，位点被注释在文件里，恢复时不会误执行 CHANGE MASTER；从库备份时改用 --dump-slave 避免锁主库。

坑 2：恢复顺序错误违反外键约束

场景 / 错误示范：

用 dump 文件恢复时，外键约束导致"先恢复了子表、后恢复父表"，大量报错。

# 错误：关掉外键检查图省事，结果半恢复状态
mysql -uroot -p appdb < appdb_full.sql
# 报错：Cannot add or update a child row: a foreign key constraint fails

为什么是坑：mysqldump 导出顺序是按表名字母序，不按外键依赖序。如果显式关了 FOREIGN_KEY_CHECKS，又在恢复中途中断，库会停在"一半数据"状态，比不恢复更糟。

正确做法：

-- 正确：在恢复会话首尾临时关/开外键检查（安全做法）
SET FOREIGN_KEY_CHECKS = 0;
-- 然后再 source 整个 dump 文件
SOURCE /backup/appdb_full.sql;
-- 恢复完成后务必重新打开
SET FOREIGN_KEY_CHECKS = 1;

# 或者恢复时在命令行一层包裹
mysql -uroot -p -e "SET FOREIGN_KEY_CHECKS=0; SOURCE /backup/appdb_full.sql; SET FOREIGN_KEY_CHECKS=1;"

加分点：mysqldump 默认会在文件头尾自动加 /*!40014 SET FOREIGN_KEY_CHECKS=0 */，只要你用完整 dump 文件整体导入，一般不用手工关；手工拆库恢复时才需要自己补。

坑 3：字符集不匹配导致恢复后乱码

场景 / 错误示范：

老库是 latin1 存的中文，新库建库时默认 latin1，导进去中文全变成 ???。

-- 错误：建库字符集与实际数据不符
CREATE DATABASE appdb DEFAULT CHARACTER SET latin1;

-- 导入时客户端连接也没指定字符集
mysql -uroot -p appdb < appdb_full.sql;
-- 中文列变成 ？？？

为什么是坑：字符集问题是"三重门"：建库字符集、表字符集、连接字符集必须一致，任一环节不一致就乱码。老系统历史遗留 latin1 存中文（字节本来是 utf8 字节流）是重灾区。

正确做法：

-- 正确：统一建库为 utf8mb4
DROP DATABASE IF EXISTS appdb;
CREATE DATABASE appdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

# 导入时显式指定客户端字符集
mysql -uroot -p --default-character-set=utf8mb4 appdb < appdb_full.sql

-- 验证实际存储字符集
SHOW VARIABLES LIKE 'character_set%';
SELECT table_name, table_collation FROM information_schema.tables
 WHERE table_schema = 'appdb';

加分点：迁移老 latin1 中文库时，先用 CONVERT(column USING utf8mb4) 把历史字节流转码，别直接换库字符集；先在测试库验证一页数据肉眼看无乱码再全量迁。

坑 4：只做全量备份漏掉 binlog 增量

场景 / 错误示范：

每周日凌晨做一次全量备份，周一晚上数据库被误删表，运维发现"最新备份是 6 天前的"，中间 6 天数据全丢。

# 错误：crontab 里只有全量备份，没有 binlog 归档
# 0 3 * * 0  mysqldump ... > /backup/full.sql
# 没有任何 binlog 同步任务

为什么是坑：全量备份只能恢复到"备份那一刻"，备份之后到故障之间的所有变更都写在 binlog 里。不持续归档 binlog，就等于没有任意时间点恢复（PITR）能力。

正确做法：

-- 正确 1：主库必须开 binlog，且保留足够长
SET GLOBAL binlog_expire_logs_seconds = 604800;  -- 保留 7 天

-- 正确 2：用脚本定期把 binlog 远端备份（伪代码）
-- mysqlbinlog --read-from-remote-server --raw \
--   --host=主库 --user=repl --password=xxx \
--   --stop-never mysql-bin.000001 &

-- 正确 3：恢复时先恢复全量，再重放 binlog 到故障前
-- mysql> SOURCE full.sql;
-- shell> mysqlbinlog mysql-bin.000100 mysql-bin.000101 | mysql -uroot -p

加分点：全量 + binlog 才能做到"恢复到误删前一秒"；binlog 一定要存到异地，本地磁盘跟主库一起挂就全完了。

坑 5：备份从未做恢复演练

场景 / 错误示范：

备份脚本跑了两年没出过问题，直到真出事那天，恢复时才发现 dump 文件早就因为磁盘满而截断了。

# 错误：只看备份"有没有生成文件"，不校验内容
ls -lh /backup/
# appdb_full.sql 看起来 2G，但其实是 0 字节或截断文件

为什么是坑：备份的唯一目的是恢复。从来没恢复过的备份等于没有备份——磁盘满、权限错、mysqldump 中途崩溃、文件传输失败，都会让备份文件"存在但不可用"。

正确做法：

# 正确 1：备份后立即做基本校验
ls -lh /backup/appdb_full.sql          # 大小非 0
tail -1 /backup/appdb_full.sql         # 结尾应是 -- Dump completed
grep -c "CREATE TABLE" /backup/appdb_full.sql   # 表数量合理

-- 正确 2：定期（如每月）在测试库真实恢复一次
-- 1. 起一个干净的 MySQL 8.0 实例
-- 2. 导入全量 + 最近 binlog
-- 3. 跑关键业务 SQL 抽样校验行数

# 伪代码：备份脚本结尾做完整性校验，失败立即告警
import os  # 标准库
size = os.path.getsize('/backup/appdb_full.sql')
if size < 1024 * 1024:   # 小于 1MB 视为异常
    raise RuntimeError('备份文件过小，疑似失败')

加分点：把"每月一次恢复演练"写进运维日历，恢复时长也记录下来，真出事时才知道要等多久、会不会影响 SLA。

坑 6：漏备份存储过程触发器事件

场景 / 错误示范：

mysqldump 备份只导了表和数据，恢复后业务报错"PROCEDURE xxx does not exist"。

# 错误：没加 --routines --triggers --events
mysqldump -uroot -p appdb > /backup/appdb.sql
# 文件里只有表结构 + 数据，没有存储过程、触发器、事件

为什么是坑：mysqldump 默认只导表结构和数据，存储过程、自定义函数、触发器、事件调度器都需要显式参数才会导出。漏了它们，恢复出来的库"能查不能算"，业务逻辑缺失。

正确做法：

# 正确：四个参数一个都不能少
mysqldump -uroot -p \
  --single-transaction \
  --routines \        # 存储过程 + 函数
  --triggers \        # 触发器（默认其实开了，显式写上更稳）
  --events \          # 事件调度器
  --databases appdb > /backup/appdb_full.sql

-- 恢复后验证对象数量
SELECT ROUTINE_TYPE, COUNT(*) FROM information_schema.ROUTINES
 WHERE ROUTINE_SCHEMA = 'appdb' GROUP BY ROUTINE_TYPE;
SELECT TRIGGER_NAME FROM information_schema.TRIGGERS
 WHERE TRIGGER_SCHEMA = 'appdb';

加分点：MySQL 8.0 导出存储过程需要 PROCESS 权限，备份账号要单独授权；视图的 DEFINER 在迁移后可能失效，记得用 --set-gtid-purged=AUTO 配合检查。

坑 7：跨大版本迁移数据字典报错

场景 / 错误示范：

从 MySQL 5.7 直接 dump 导入 8.0，建表时报"Unknown collation: utf8mb4_general_ci"或数据字典损坏。

# 错误：5.7 dump 直接往 8.0 灌
mysql -uroot -p --version   # 新库是 8.0.36
mysql -uroot -p appdb < appdb_57.sql
# 报错：Unknown collation: 'utf8mb4_0900_ai_ci' 反向也可能报错

为什么是坑：MySQL 8.0 把数据字典从 MyISAM 搬到了 InnoDB，字符集排序规则也换了（utf8mb4_0900_ai_ci）。5.7 与 8.0 之间不是简单 dump 就能互导，直接升大版本极易触发数据字典不兼容。

正确做法：

# 正确路径 1：原地升级（推荐）
# 1. 先 mysqldump 全量备份
# 2. 停业务，用 mysqlshell upgrade 或官方升级路径原地升到 8.0
mysql shell > util.upgradeCheck()   # 升级前兼容性检查

# 正确路径 2：逻辑迁移走"同版本对齐"
# 5.7 dump -> 先导入 5.7 临时库 -> 再用主从复制同步到 8.0 -> 切流量

-- 迁移前先跑官方检查
USE appdb;
CHECK TABLE orders;
-- 8.0 升级前务必跑 mysqlcheck --check-upgrade

加分点：大版本迁移必须先在预发完整演练一遍，记录所有 warning；生产切换选在业务低峰，备好"回退方案"（旧库保持只读可回切）。

坑 8：恢复前未清库导致新旧数据混杂

场景 / 错误示范：

恢复备份时目标库已经有一周的新数据，直接 source dump 文件，主键冲突、新旧数据混在一起。

-- 错误：直接往已有数据的库里导
USE appdb;
SOURCE /backup/appdb_full.sql;
-- 一堆 Duplicate entry，部分表覆盖、部分表追加，库已经乱了

为什么是坑：mysqldump 默认导出的建表语句是 CREATE TABLE（没 DROP），遇到已存在的表会报错跳过，或者数据往里追加，导致新旧数据并存。恢复是"覆盖"语义，必须先有一个干净目标。

正确做法：

-- 正确：先删库重建，保证干净起点
DROP DATABASE IF EXISTS appdb;
CREATE DATABASE appdb DEFAULT CHARACTER SET utf8mb4;

# 恢复命令整库导入
mysql -uroot -p < /backup/appdb_full.sql

-- 如果只想恢复单张表，用 --no-create-db + 指定表
-- mysqldump ... appdb orders > orders.sql
-- 恢复前：DROP TABLE orders; 再 source

加分点：恢复操作前一定先在另一个实例把 dump 验一遍，确认无误再动生产；生产恢复前先把"当前有问题的库"另存一份，别一上来就 DROP。

坑 9：SELECT INTO OUTFILE 权限陷阱

场景 / 错误示范：

想快速导出一张大表，用 SELECT ... INTO OUTFILE，结果报错"MySQL server is running with the --secure-file-priv option"。

-- 错误：直接导出，被 secure_file_priv 拦住
SELECT * FROM orders
 INTO OUTFILE '/tmp/orders.csv'
 FIELDS TERMINATED BY ',';
-- 报错：The MySQL server is running with the --secure-file-priv option

为什么是坑：8.0 默认开启 secure_file_priv，只允许在指定目录读写文件，这是安全加固。直接写 /tmp 会被拒；且 INTO OUTFILE 写出的文件在数据库服务器上，不是在你客户端机器上。

正确做法：

-- 正确 1：先看允许导出的目录
SHOW VARIABLES LIKE 'secure_file_priv';
-- 假设返回 /var/lib/mysql-files/

-- 正确 2：导出到该目录
SELECT * FROM orders
 INTO OUTFILE '/var/lib/mysql-files/orders.csv'
 FIELDS TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n';

# 再在服务器上把文件拉到本地
# scp user@db:/var/lib/mysql-files/orders.csv ./

加分点：运维导出大表更推荐用 mysqldump --tab 或 SELECT ... INTO OUTFILE，但注意这是服务器端文件；客户端侧导出用 Navicat / DBeaver 更直观。

坑 10：备份期间未保一致性导致表损坏

场景 / 错误示范：

直接 cp 了 InnoDB 的 .ibd 文件做"物理备份"，恢复时报表已损坏。

# 错误：数据库运行时直接拷数据文件
cp -r /var/lib/mysql/appdb /backup/appdb_cp/
# InnoDB 内存里还有脏页未落盘，拷出来的文件是撕裂的

为什么是坑：InnoDB 有 buffer pool、double write、redo log，运行时数据文件不是"某一时刻的完整快照"。直接拷文件拷到一半页面，恢复时校验失败。物理备份要用专门工具（Percona XtraBackup），它会做 crash-consistent。

正确做法：

# 正确 1：逻辑备份用 mysqldump --single-transaction（前面已述）

# 正确 2：物理热备用 Percona XtraBackup（企业级）
# xtrabackup --backup --user=root --password=xxx --target-dir=/backup/xtra
# xtrabackup --prepare --target-dir=/backup/xtra

# 正确 3：云环境直接用存储快照（前提是先触发 redo 落盘）
mysql -e "SET GLOBAL innodb_fast_shutdown=0;"

加分点：小库用 mysqldump 逻辑备份足够；TB 级大库必须上 XtraBackup 物理备份，否则备份窗口长到不可接受。

坑 11：my.cnf 配置文件遗忘备份

场景 / 错误示范：

数据库服务器磁盘故障，换新机后只恢复了数据，却发现没备份 my.cnf，一堆参数全靠回忆重配。

# 错误：备份脚本只备了数据目录，没备配置
# 0 3 * * *  mysqldump ... > /backup/data.sql
# /etc/my.cnf 从来没进过备份清单

为什么是坑：数据能恢复，参数配错（buffer pool size、字符集、隔离级别、binlog 格式）会让性能崩、行为变。配置文件是数据库"人格"的一部分，丢了等于裸奔。

正确做法：

# 正确：把配置文件一起纳入备份
# my.cnf 常见路径
cp /etc/my.cnf /backup/config/
cp -r /etc/mysql/ /backup/config/

# 同时把当前运行时参数也导出一份
mysql -uroot -p -e "SHOW VARIABLES;" > /backup/config/variables.txt
mysql -uroot -p -e "SHOW GLOBAL STATUS;" > /backup/config/status.txt

-- 恢复后对比参数是否一致
SELECT variable_name, variable_value FROM performance_schema.global_variables
 WHERE variable_name IN ('innodb_buffer_pool_size','character_set_server','binlog_format');

加分点：用配置管理工具（Ansible / 数据库上云）管理 my.cnf，比靠备份文件恢复更可靠；配置变更全部走 git 评审，禁止线上手改。

坑 12：导入时漏指定字符集乱码

场景 / 错误示范：

dump 文件本身是 utf8mb4，但导入时客户端连接是 latin1，中文进去就变成了"涓枃"。

# 错误：导入命令没带 --default-character-set
mysql -uroot -p appdb < appdb_full.sql
# 中文列显示为乱码（典型如"涓枃"这种 utf8 字节被当 latin1 读）

为什么是坑：mysqldump 导出时如果不在连接层指定字符集，导出的中文可能已经"在错误的字符集里转了一次"。导入时再错一次，乱码层层叠加。解决思路是导出导入两端都显式锁死 utf8mb4。

正确做法：

# 正确：导出导入两端都显式指定 utf8mb4
# 导出
mysqldump -uroot -p --default-character-set=utf8mb4 appdb > appdb_full.sql

# 导入
mysql -uroot -p --default-character-set=utf8mb4 appdb < appdb_full.sql

-- 验证导入后实际字符
SELECT HEX(name), name FROM appdb.users LIMIT 5;
-- 正常中文 utf8mb4 的 HEX 应以 E4-E9 开头（常用汉字区段）
SHOW VARIABLES LIKE 'character_set_client';   -- 应为 utf8mb4

加分点：彻底避免乱码的口诀："库、表、连接（client/connection/results）四件套全部 utf8mb4"；建库脚本里永远写 DEFAULT CHARACTER SET utf8mb4。

本篇速查表

| 坑号 | 现象关键词 | 根因一句话 | 一句话解法 |

|---|---|---|---|

| 1 | 备份出来不一致 | 没 --single-transaction | 加一致性快照参数 |

| 2 | 恢复报外键错 | 表顺序乱 | 首尾包 FOREIGN_KEY_CHECKS |

| 3 | 中文变 ??? | 库/表/连接字符集不一致 | 统一 utf8mb4 |

| 4 | 只能恢复到几天前 | 没归档 binlog | 全量 + binlog 异地备份 |

| 5 | 备份文件是空的 | 从不恢复演练 | 定期真实恢复 + 校验 |

| 6 | 缺存储过程 | 没 --routines 等 | 加全四个导出参数 |

| 7 | 5.7→8.0 报错 | 数据字典/排序规则不兼容 | 走官方升级路径 |

| 8 | 新旧数据混杂 | 恢复前没清库 | 先 DROP DATABASE 重建 |

| 9 | OUTFILE 被拒 | secure_file_priv 限制 | 导出到允许目录 |

| 10 | 拷文件恢复损坏 | 运行时数据文件撕裂 | 用 XtraBackup 物理热备 |

| 11 | 新机参数靠回忆 | 没备 my.cnf | 配置文件纳入备份 |

| 12 | 导入后乱码 | 连接字符集没锁死 | 导入两端 --default-character-set=utf8mb4 |

运维注意事项

1. 备份策略必须写明"全量频率、binlog 保留天数、异地副本、恢复 RTO/RPO"，不能只说"每天备份"。

2. 任何恢复操作先在测试库做一遍，确认步骤和时长，再上生产；生产恢复前对当前库另存快照。

3. 备份账号最小权限：只给 LOCK TABLES、SELECT、RELOAD、REPLICATION CLIENT 等，不要用 root。

4. 大版本迁移、大表结构变更，一律先在预发演练，保留一键回退方案。

5. 把"备份完整性校验"做成自动化任务（文件大小、结尾 dump completed、表数量），失败立即告警。

动手练习

练习 1：写出一条完整的、用于生产的 mysqldump 备份命令，要求：InnoDB 一致性、记录位点、导出存储过程触发器事件、utf8mb4 字符集。

<details>

<summary>参考答案</summary>

mysqldump -uroot -p --single-transaction --master-data=2 --routines --triggers --events --default-character-set=utf8mb4 --set-gtid-purged=AUTO appdb > /backup/appdb_full.sql

</details>

练习 2：周一晚 23:00 业务误删了 orders 表，周日凌晨 3:00 做过全备。请描述完整恢复步骤。

<details>

<summary>参考答案</summary>

1. 立即归档当前 binlog 防止被覆盖；2. 在干净实例恢复周日全备；3. 用 mysqlbinlog 重放周日 3:00 之后到 23:00 误删前的 binlog（按位点/时间过滤）；4. 校验数据后把 orders 表导回生产。

</details>

练习 3：为什么不能在数据库运行时直接 cp InnoDB 数据目录做备份？

<details>

<summary>参考答案</summary>

InnoDB buffer pool 里有未落盘的脏页，redo log 与数据文件存在时间差，直接拷贝会拿到撕裂/不一致的页面，恢复时校验失败。应使用 mysqldump 逻辑备份或 XtraBackup 物理热备。

</details>

下一篇预告：第 40 章 MySQL踩坑：日常运维篇