一、逻辑备份与物理备份：两条路的分水岭

先说清楚两个词，后面一直要用。

逻辑备份读的是「数据」，物理备份拷的是「文件」。 这一句差别决定了后面所有的取舍：

四行里最要紧的是最后一行。「能不能不停机」这一条，往往直接决定了选哪个。 线上库停不了，就只能选慢的那条路——这不是技术偏好，是现实约束。 反过来，一个半夜能停十分钟的内部系统，拷文件是最省事的：快、简单、不用装工具。 所以「备份方案」这件事，先问「能停多久」，再问「多大的库」。

二、mysqldump：备份的正统写法

教材给的第一种办法是 mysqldump，它是 MySQL 自带的逻辑备份工具。

mysqldump -u root -p --databases shop > shop.sql
mysqldump -u root -p shop tb_g tb_c > two_tables.sql
mysqldump -u root -p --all-databases > all.sql

三行的区别在「备份的粒度」：整台实例、一个库、几张表。 但真正要留意的是第一行那个 --databases——它决定脚本里有没有建库语句。 带 --databases：脚本里有 CREATE DATABASE shop 和 USE shop。 不带：只有 CREATE TABLE 和 INSERT，还原时你得自己建库、自己 USE。 这个差别在 18.2 会变成一个具体的错误：还原到了错误的库里，而且不报错。 原因是「表建成功了」——只是建在了 mysql 库或者别的什么当前库里。

三、沙箱里的等价物：iterdump 打出 SQL

iterdump() 干的就是 mysqldump 那件事：把库读一遍，逐条写成 SQL 语句。 打出来的文本长这样：

BEGIN TRANSACTION;
CREATE TABLE tb_c (id INTEGER PRIMARY KEY, cname TEXT);
INSERT INTO "tb_c" VALUES(1,'外设');
INSERT INTO "tb_c" VALUES(2,'存储');
CREATE TABLE tb_g (id INTEGER PRIMARY KEY, name TEXT, ...);
INSERT INTO "tb_g" VALUES(1,'机械键盘',399.0,12);
...
COMMIT;

整个库的东西全在里面，包括 BEGIN TRANSACTION 和 COMMIT。 外面套一层事务这件事很关键：回放时要么全成、要么全不成，不会留下半个库。 注意它的排列：先 tb_c 后 tb_g——按表名字母序，不是按建表顺序。 这个细节现在看着无害，18.2 会变成一个坑。 还有一点：INSERT 是一行一条，不是批量。 十万行的表，脚本就是十万条 INSERT——这也是逻辑备份慢的直接原因。

四、直接拷数据文件：快，但要停

物理备份就是 cp 一下数据文件。它比 dump 快得多，但前提是「拷的时候库是静的」。 先在最干净的状态下拷一次，结果是一致的：

这一次一致，是因为写操作早就落盘了。换一个时机就不一样。 把库切到 WAL 模式，写一行但不等 checkpoint，再只拷主文件：

第一行丢了一行。因为 WAL 模式下「已经提交」的数据还在 -wal 文件里，主文件里没有。 这就是这条路的核心风险：你要拷的不是一个文件，是一组文件。 MySQL 完全一样：InnoDB 有 redo log，只拷 .ibd 不拷日志，最近的事务就没了。 所以教材写「复制前先停服务」，不是保守，是必要条件。 「先停服务」这一步的实际含义是：停掉之后，所有缓存都刷盘了，日志和主文件对齐了。 不停就直接拷，等于在一个正在写的文件上动手——拷到一半的数据页，还原时就是坏页。

五、VACUUM INTO：带上收缩的备份

第三种做法：VACUUM INTO 把库重写一份到新文件。它同时干了三件事——备份、压缩、整理。 演示里先建两万行、再删掉九成，然后做一次 VACUUM INTO：

文件缩小了九成，行数一行不少。 原因不难想：删行只是把页标成空闲，文件不会自己缩；VACUUM 是整库重写一遍。 这一点对备份策略的影响很实际：天天 cp 的库会越来越大，因为空闲页一直留着。 定期做一次 VACUUM INTO，备份体积和还原速度都会好看很多。 但它本质还是「拷文件」，所以「不能跨版本」这条限制一样适用。 别把它当成逻辑备份——它只是把物理备份整理了一遍。

六、三种做法怎么选

第一行是「通用答案」，第二行是「能停机时的最优解」，第三行是「想要小就用它」。 注意第二、三行的「不能跨版本」：文件格式和引擎版本绑死，这是物理备份的硬伤。 所以「备份」和「迁移」是两件事：备份可以挑最快的，迁移只能走文本。 把它们混为一谈，就会遇到「备份能还原、但升不了级」这种尴尬。 最后补一句工程上的常识：备份文件要和数据库分开存。 放同一个盘上，盘坏了两份一起没；放同一台机器上，机器没了也是一起没。 这一条听着像废话，但真出事的时候，它是最要命的那一条。

七、动手 Demo：iterdump、拷文件、VACUUM，三种备份各跑一遍

这一段按教材 18.1 的两条路走，再加一条：先用 iterdump() 打出一份 SQL（相当于 mysqldump）， 再在「干净」和「WAL 有未刷盘数据」两种时机各拷一次文件，最后看 VACUUM INTO 能把文件缩到多小。

import os, shutil, sqlite3

p = u"shop.db"
c = sqlite3.connect(p, isolation_level=None)
c.executescript(u"""
CREATE TABLE tb_c (id INTEGER PRIMARY KEY, cname TEXT);
CREATE TABLE tb_g (id INTEGER PRIMARY KEY, name TEXT, price REAL, stock INTEGER);
INSERT INTO tb_c VALUES (1,'外设'), (2,'存储');
INSERT INTO tb_g VALUES (1,'机械键盘',399.0,12), (2,'无线鼠标',129.0,30);
""")

# ① 逻辑备份：等效于 mysqldump
dump = u"\n".join(c.iterdump())
print(dump)

# ② 物理备份：WAL 模式下只拷主文件会丢数据
c.execute(u"PRAGMA journal_mode = WAL")
c.execute(u"INSERT INTO tb_g VALUES (3,'移动硬盘',459.0,8)")
shutil.copyfile(p, u"copy1.db")          # 只拷主文件
print(sqlite3.connect(u"copy1.db").execute(
      u"SELECT count(*) FROM tb_g").fetchone())   # 2，丢了一行

c.execute(u"PRAGMA wal_checkpoint(TRUNCATE)")     # 先把 WAL 刷进主文件
shutil.copyfile(p, u"copy2.db")
print(sqlite3.connect(u"copy2.db").execute(
      u"SELECT count(*) FROM tb_g").fetchone())   # 3，齐了

# ③ VACUUM INTO：备份 + 收缩
c.execute(u"VACUUM INTO 'shop_vac.db'")
print(os.path.getsize(p), os.path.getsize(u"shop_vac.db"))

实跑输出：

--------------------------------------------------------------------
数据备份：三种做法，各有前提
--------------------------------------------------------------------


  mysqldump：备份的正统写法
  --------------------------------------------------------------------
**教材 18.1 讲两种备份：`mysqldump` 导成 SQL，或者直接复制整个数据目录。**
**前者是逻辑备份，后者是物理备份——这两个词后面一直要用。**

  [教材原码 · 本沙箱不执行]
  | mysqldump -u root -p --databases shop > shop.sql
  | mysqldump -u root -p shop tb_g tb_c > two_tables.sql
  | mysqldump -u root -p --all-databases > all.sql
  沙箱里没有 mysqldump，这三行是教材原码，不执行。

**带 `--databases` 和不带，差的是「脚本里有没有 `CREATE DATABASE` 和 `USE`」。**
**这一点在 18.2 还原时是决定性的：不带的话，还原前必须先自己建库、自己 `USE`。**

  沙箱里的等价物：iterdump 打出一份 SQL
  --------------------------------------------------------------------
**`iterdump()` 干的就是 `mysqldump` 那件事：把库读一遍，逐条写成 SQL 语句。**
**打出来的文本是这样的：**
  | BEGIN TRANSACTION;
  | CREATE TABLE tb_c (id INTEGER PRIMARY KEY, cname TEXT);
  | INSERT INTO "tb_c" VALUES(1,'外设');
  | INSERT INTO "tb_c" VALUES(2,'存储');
  | CREATE TABLE tb_g (
  |     id    INTEGER PRIMARY KEY,
  |     name  TEXT,
  |     price REAL,
  |     stock INTEGER
  | );
  | INSERT INTO "tb_g" VALUES(1,'机械键盘',399.0,12);
  | INSERT INTO "tb_g" VALUES(2,'无线鼠标',129.0,30);
  | INSERT INTO "tb_g" VALUES(3,'移动硬盘',459.0,8);
  | COMMIT;

**整个库的东西全在里面，包括 `BEGIN TRANSACTION` 和 `COMMIT`。**
**注意它的排列：先 `tb_c` 后 `tb_g`——按表名字母序，不是按建表顺序。**
**这个细节在 18.2 会变成一个坑。**
**整份脚本 14 行、393 个字符，对应这个 12 KB 的库。**

  直接拷文件：快，但有前提
  --------------------------------------------------------------------
**物理备份就是 `cp` 一下整个 `.db` 文件。它比 dump 快得多，但前提是「拷的时候库是静的」。**
**先在最干净的状态下拷一次：**
  原库 3 行，拷出来的库 3 行，文件 12.0 KB

**这一次一致，是因为写操作早就落盘了。换一个时机就不一样了。**
**把库切到 WAL 模式，写一行但不等 checkpoint，再只拷主文件：**
  journal_mode = wal，-wal 文件 4.1 KB
  原库 4 行，只拷主文件得到 3 行

**丢了一行。因为 WAL 模式下「已经提交」的数据还在 `-wal` 文件里，主文件里没有。**
**`PRAGMA wal_checkpoint(TRUNCATE)` 之后重新拷，四行就齐了：**
  checkpoint 之后再拷，得到 4 行

**这就是「直接复制数据目录」这条路的全部风险：你要拷的不是一个文件，是一组文件。**
**MySQL 也一样——InnoDB 有 redo log，只拷 `.ibd` 不拷日志，数据就少一截。**
**所以教材写「复制前先停服务」，不是保守，是必要条件。**

  VACUUM INTO：带上收缩的备份
  --------------------------------------------------------------------
**第三种做法：`VACUUM INTO` 把库重写一份到新文件。它同时干了三件事——**
**备份、压缩、整理。删过大量数据的库用它，备份文件会小很多。**
  删掉九成数据之后：2240.0 KB
  VACUUM INTO 之后：232.0 KB
  还剩 2000 行

**文件缩小了 90%，行数一行不少。**
**原因不难想：删行只是把页标成空闲，文件不会自己缩；`VACUUM` 是整库重写一遍。**
**这一点对备份策略的影响很实际：天天 `cp` 的库会越来越大，因为空闲页一直留着。**
**定期做一次 `VACUUM INTO`，备份体积和还原速度都会好看很多。**

  三种做法对照：选哪个看「能不能停」
  --------------------------------------------------------------------

  做法                 要不要停机        体积            能不能跨版本
  -------------------------------------------------------------------
  iterdump／mysqldump  不用（逻辑一致）  文本，可压缩    能
  拷数据文件           必须停            原样（含空洞）  不能
  VACUUM INTO          不用              最小            不能

**第二行的「不能跨版本」是物理备份的硬伤：文件格式和引擎版本绑死。**
**第三行也一样——它本质还是拷文件，只是拷之前先整理了一遍。**
**所以「备份」和「迁移」是两件事：备份可以挑最快的，迁移只能走文本。**

第一段那个 dump 值得逐行读完。 BEGIN TRANSACTION 在最前面、COMMIT 在最后面，中间是建表和插入。 这个结构说明它是一份「要么全成、要么全不成」的脚本——回放时不用担心中途失败留下半个库。 第二段两个数是这条路的核心：3 行的库，只拷主文件得到 2 行。 少的那一行不是被删了，它在 -wal 里，只是没被拷走。 checkpoint 之后再拷就齐了——所以「停服务」这个动作，等价于「先 checkpoint」。 第三段两个文件大小之差，取决于这个库的空闲页有多少。 刚建的库差别很小；删过大半数据的库差别能到十倍。 所以「VACUUM 有没有用」这个问题，答案取决于「这个库最近删过多少东西」。

30 秒实验

1. 把库里的行删掉一半，再跑一次 VACUUM INTO，记下两个文件大小，算一下缩了多少。
2. 在 WAL 模式下写三行、不 checkpoint，分别拷「主文件」和「主文件加 -wal」两份， 看第二份能不能正常打开、里面有几行。
3. 把 iterdump() 的输出存成文件，再 wc -l 一下； 和库里的行数对比，看「一行数据变成一个字符数」大概是多少倍。

常见误区

1. 以为拷了 .db 文件就是一份完整备份。WAL 模式下还有 -wal， 没 checkpoint 时主文件里没有最新的数据。
2. 以为 mysqldump 会把视图、触发器、存储过程一起导。默认不导， 要加 --routines --triggers --events。
3. 以为 VACUUM INTO 出来的是逻辑备份。它还是物理备份，只是整理过；一样不能跨引擎版本使用。

备份讲完了，下一页讲恢复——重点不在「怎么灌回去」，在「灌完怎么验」。

• ☐ （本篇完，下一篇 18.2 数据恢复）

相关笔记

• 查看触发器
• 事务的隔离级别
• 🛢️ MySQL从入门到精通 · 索引