数据库基础体系 · 第 89/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

MySQL 备份恢复：逻辑备份、物理备份、Binlog 与 PITR 演练

在 MySQL 中，“备份成功”不等于“能够恢复到业务需要的时间点”。备份恢复至少包含四个不同问题：

  1. 如何保存某一时刻已经提交的数据；
  2. 如何保存表结构、账号、存储过程等数据库对象；
  3. 如何保存全量备份之后发生的增量变化；
  4. 发生误删、误更新或实例损坏后，如何把数据恢复到指定时间点。

逻辑备份、物理备份和 Binlog 分别解决不同层次的问题。PITR（Point-in-Time Recovery，时间点恢复）则把全量备份与 Binlog 组合起来，恢复到某个历史时刻。

本文以 MySQL 8.4、主要使用 InnoDB、单实例部署为基础说明。涉及复制、云存储、容器卷和非 InnoDB 引擎时，会明确额外边界。

一、先建立恢复模型：备份保存的到底是什么

设数据库在时间 tt 的已提交状态为：

S(t)S(t)

如果在时间 t0t_0 做了一份一致的全量备份，记为：

Bt0B_{t_0}

之后 Binlog 记录了一系列提交事件：

Et0→t1E_{t_0 \rightarrow t_1}

那么时间点恢复的目标是：

S(t1)=Apply(Bt0,Et0→t1)S(t_1) = Apply(B_{t_0}, E_{t_0 \rightarrow t_1})

这个公式成立需要同时满足以下条件：

  1. B_t0 确实代表某个一致的数据库状态；
  2. Binlog 覆盖了从备份一致性点到目标时间点的全部提交；
  3. Binlog 文件没有损坏、丢失或被错误过滤；
  4. 恢复过程按正确顺序重放；
  5. 目标实例的版本、字符集、表结构和运行条件足以解释这些事件；
  6. 恢复时没有把目标时间点之后的事件也重放进去。

因此，PITR 不是“把几个文件复制回来”，而是一个有起点、有连续日志、有终点的重放过程。

1. 备份的一致性点

“一致性备份”表示备份中的多个表能够共同描述某一时刻的数据库状态。例如转账事务：

START TRANSACTION;

UPDATE account
SET balance = balance - 100
WHERE id = 1;

UPDATE account
SET balance = balance + 100
WHERE id = 2;

COMMIT;

如果备份读到了第一条 UPDATE，却没有读到第二条 UPDATE，恢复后的数据就不是任何一个已提交时刻的状态。

对于 InnoDB，事务型一致性通常依赖一致性读和 MVCC；对于 MyISAM 等非事务引擎，不能仅靠 --single-transaction 获得同样的保证。

2. Binlog、Redo 和 Undo 的职责不同

这三个概念经常被混淆：

组件 主要内容 主要用途
InnoDB Redo Log 数据页修改对应的重做信息 崩溃恢复、保证已提交修改能够重新应用
InnoDB Undo Log 旧版本和回滚信息 事务回滚、MVCC 一致性读
MySQL Binlog 逻辑层的事件或事务变化 复制、审计式变更记录、PITR

一次事务提交的大致关系是：

  1. 事务修改 InnoDB 页，同时产生 Undo 和 Redo；
  2. 提交时，MySQL 将事务的 Binlog 写入 Binlog 缓冲区并落盘；
  3. InnoDB 的提交状态与 Redo 持久化配合完成；
  4. 事务对其他会话可见；
  5. 后续崩溃恢复使用 Redo 恢复数据页，PITR 使用 Binlog 在备份之上重放逻辑变化。

Redo 不是长期备份。它的生命周期受日志空间和检查点控制，旧 Redo 会被循环复用。Undo 也不是用于重建整个数据库的增量日志。要实现跨越较长时间的恢复，必须保存全量备份和连续 Binlog。

二、逻辑备份：把数据库导出为可执行内容

1. 逻辑备份的含义

逻辑备份保存的是数据库对象和数据的逻辑表示，例如：

CREATE TABLE ...
INSERT INTO ...
CREATE VIEW ...
CREATE PROCEDURE ...

恢复时，MySQL 客户端重新执行这些 SQL，重新创建表并插入数据。

常见工具包括：

  • mysqldump：单实例、单库或单表逻辑导出；
  • MySQL Shell 的 util.dumpInstance()、util.dumpSchemas()：支持并行导出和更适合大规模逻辑迁移的格式；
  • mysql：执行 SQL 导出文件；
  • mysqlbinlog：读取或重放 Binlog，不是全量逻辑备份工具。

逻辑备份与物理备份的根本差别是：逻辑备份保存“如何表示数据”，物理备份保存“数据页和相关存储文件”。

2. 一个可执行的 InnoDB 逻辑备份示例

先准备测试数据：

CREATE DATABASE IF NOT EXISTS shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

USE shop;

CREATE TABLE account (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    balance DECIMAL(12, 2) NOT NULL
) ENGINE = InnoDB;

INSERT INTO account(id, name, balance)
VALUES
    (1, 'Alice', 1000.00),
    (2, 'Bob',   500.00);

CREATE TABLE order_info (
    id BIGINT PRIMARY KEY,
    account_id BIGINT NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    created_at TIMESTAMP NOT NULL
) ENGINE = InnoDB;

导出一个数据库：

mysqldump \
  --user=backup \
  --password \
  --host=127.0.0.1 \
  --single-transaction \
  --routines \
  --events \
  --triggers \
  --databases shop \
  > shop.sql

参数含义：

  • --single-transaction：对事务型表使用一致性读，通常不需要长时间锁表；
  • --routines：导出存储过程和函数；
  • --events：导出 Event Scheduler 事件；
  • --triggers：导出触发器；
  • --databases shop：在文件中包含 CREATE DATABASE 和 USE 语句；
  • --password：交互式输入密码，避免密码直接出现在 shell 历史中。

查看导出文件的开头：

head -n 30 shop.sql

通常可以看到类似以下内容：

-- MySQL dump ...
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;
CREATE DATABASE ...
USE `shop`;
CREATE TABLE `account` (...);

实际输出会随版本、选项和对象内容变化，不应依赖某一行注释的固定格式。

恢复到一个已经创建好的目标实例：

mysql \
  --user=restore \
  --password \
  --host=127.0.0.1 \
  < shop.sql

如果导出文件包含 --databases shop 生成的建库语句，目标端不需要提前手工创建 shop。

3. --single-transaction 的真正边界

--single-transaction 并不是“对所有表自动实现完全无锁一致性”。

对于 InnoDB，典型过程是：

  1. 导出程序建立连接；
  2. 设置适合一致性读取的事务隔离行为；
  3. 开始事务并建立一致性视图；
  4. 后续查询按照这个视图读取已提交数据；
  5. 其他事务通常可以继续修改 InnoDB 表。

因此，导出过程中提交的新事务通常不会出现在这份快照中，但快照中的事务不会因为后续修改而改变。

然而有几个重要边界：

非事务表

MyISAM 表不支持 InnoDB 的 MVCC。一份导出可能读到不一致的内容。若数据库中存在非事务表，需要考虑：

  • 业务停写；
  • 锁表；
  • 使用全局读锁；
  • 将非事务表单独处理；
  • 或迁移到事务型引擎。

DDL

在导出期间执行 ALTER TABLE、DROP TABLE、TRUNCATE TABLE 等 DDL，可能与导出产生锁冲突、元数据锁等待，或者导致导出结果与预期不一致。

--single-transaction 不能把并发 DDL 变成事务型快照。

长事务和历史版本

导出事务持续时间较长时，InnoDB 需要保留旧版本以服务一致性读。大量写入可能导致 Undo 增长、历史版本堆积，甚至引发性能和空间问题。

可以观察长事务：

SELECT
    trx_id,
    trx_started,
    trx_state,
    trx_mysql_thread_id,
    trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

外键和对象依赖

导出文件通常会临时关闭外键检查，以便按照导出顺序重建对象。恢复时如果数据本身已经不满足外键约束，不能因为导入成功就认为数据正确。恢复完成后应重新验证约束和业务不变量。

4. 导出 Binlog 坐标：让逻辑备份可以用于 PITR

如果逻辑备份要作为 PITR 的全量起点，必须知道备份一致性点对应的 Binlog 位置。

常见示例：

mysqldump \
  --user=backup \
  --password \
  --host=127.0.0.1 \
  --single-transaction \
  --source-data=2 \
  --routines \
  --events \
  --triggers \
  --all-databases \
  > full.sql

--source-data=2 会把复制源的 Binlog 文件和位置以注释形式写入导出文件，而不是在恢复时直接执行。这样可以查看：

grep -E "CHANGE REPLICATION SOURCE TO|CHANGE MASTER TO" full.sql

不同版本和兼容模式下，注释中的语句名称可能不同。关键不是语句文本，而是其中的 Binlog 文件和位置。

这里的“位置”必须对应导出的一致性点，而不是随意在导出完成后执行一次：

SHOW BINARY LOG STATUS;

后者得到的可能已经是更晚的位置。使用 --source-data 的价值，就是让工具在建立备份一致性范围时同时记录对应的日志坐标。

执行该选项通常需要读取源状态和执行一致性协调所需的权限。生产环境应使用具有最小必要权限的备份账号，并先在测试实例验证权限组合。

三、物理备份：保存数据文件和存储状态

1. 物理备份的含义

物理备份保存数据库的底层文件，例如：

  • InnoDB 表空间；
  • 系统表空间；
  • 独立表空间；
  • Redo Log；
  • Undo 相关文件；
  • 数据字典相关内容；
  • 实例配置中需要配套恢复的文件；
  • Binlog 文件。

恢复时通常不是重新执行每条 INSERT，而是将文件放回兼容的 MySQL 数据目录，随后由 InnoDB 进行必要的崩溃恢复或备份恢复处理。

物理备份通常具有以下特征：

  • 对大数据量恢复更快；
  • 更依赖版本、平台、文件布局和存储引擎；
  • 不天然适合跨版本迁移；
  • 不能只复制几个 .ibd 文件就宣称完成了实例备份。

2. 为什么不能直接复制运行中的数据目录

假设直接执行：

cp -a /var/lib/mysql /backup/mysql

如果 MySQL 正在运行，这个目录可能处于以下状态：

  • 某些数据页已经写入，相关 Redo 尚未复制；
  • 某些 Redo 已复制，数据页尚未复制；
  • 表空间文件之间的复制时间不同；
  • 文件复制期间发生页修改；
  • Binlog 文件只复制了前半部分；
  • 数据字典、表空间和表文件不匹配。

这不是一个受 MySQL 语义保证的热备份。

停机后复制属于冷物理备份。示例：

sudo systemctl stop mysqld

sudo rsync -aHAX --numeric-ids \
  /var/lib/mysql/ \
  /backup/mysql-full/

sudo systemctl start mysqld

这里的关键不是 rsync，而是 MySQL 已经停止，文件不再变化。恢复时还需要确认：

  • 目标 MySQL 版本兼容；
  • 文件权限和所有者正确；
  • 配置中的 datadir、端口、字符集和插件环境匹配；
  • 数据目录不是被另一个运行中的实例同时使用；
  • Binlog 文件和索引文件完整。

3. 热物理备份工具和存储快照

生产环境常见三类方案：

MySQL Enterprise Backup

这是 MySQL 官方商业备份工具，支持 InnoDB 热备份，并处理在线备份期间产生的变化。具体功能和授权取决于产品版本。

Percona XtraBackup

这是常见的第三方 InnoDB 物理热备工具，不属于 MySQL Server 本身。使用时必须按其支持的 MySQL 版本、Redo 格式和工具版本选择，不能将其语义直接等同于 MySQL 官方工具。

文件系统或云盘快照

存储快照是否可恢复，取决于快照语义：

  • 只是单卷崩溃一致性，还是多卷原子一致性；
  • 是否同时覆盖数据目录和 Binlog；
  • 快照前是否暂停写入；
  • 是否需要依赖 Redo 完成恢复；
  • 云盘快照是否保证块级快照的一致性。

“云盘有快照”不自动意味着“数据库有可验证的备份”。

4. Clone 的边界

MySQL Clone 是用于克隆 MySQL 数据实例的能力，适合初始化副本、搭建新实例或某些集群场景。它不等同于长期归档备份：

  • Clone 通常复制当前实例状态；
  • 它不能替代长期保留的全量备份；
  • 不能单独提供任意历史时间点恢复；
  • 仍需要考虑版本、权限、网络和空间要求。

要实现 PITR，仍然需要全量基线和连续 Binlog。

四、Binlog：PITR 的增量变化来源

1. Binlog 记录什么

Binlog 位于 MySQL Server 层，记录会影响数据或数据库状态的事件。记录格式主要有：

  • STATEMENT：记录 SQL 语句；
  • ROW：记录行变化；
  • MIXED：由服务器在语句和行格式之间选择。

生产环境通常优先使用 ROW，因为某些非确定性语句在语句格式下可能在不同环境得到不同结果。但 ROW 也会带来事件体积增大、显示内容不直观等代价。

查看当前配置：

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'binlog_row_image';
SHOW VARIABLES LIKE 'gtid_mode';
SHOW VARIABLES LIKE 'enforce_gtid_consistency';

修改 Binlog 相关配置通常需要重启或按变量支持情况动态调整，不能仅凭 SET GLOBAL 认为配置已永久生效。

2. 查看当前 Binlog 文件和位置

SHOW BINARY LOG STATUS;

典型结果包含：

File              Position
binlog.000123     456789

含义是当前 Binlog 文件和写入位置。位置是字节偏移，不是事务 ID，也不是时间戳。

查看 Binlog 文件：

mysqlbinlog \
  --base64-output=DECODE-ROWS \
  -vv \
  /var/lib/mysql/binlog.000123 \
  | less
  • -v、-vv：尝试将行事件显示为更易读的形式；
  • --base64-output=DECODE-ROWS：配合详细模式显示行事件；
  • 这只是查看方式，不改变 Binlog 本身。

如果启用 Binlog 校验和，mysqlbinlog 会验证事件内容。遇到损坏时，读取可能报错，不能把截断文件直接当作完整日志。

3. Binlog 保留策略决定 PITR 窗口

PITR 依赖从全量备份时刻开始的连续 Binlog。如果备份完成后 Binlog 很快被清理，那么备份即使完整，也无法恢复到清理范围内的时间点。

需要同时管理：

  • Binlog 自动过期时间；
  • 外部归档；
  • Binlog 文件是否已上传成功；
  • 上传校验；
  • 本地清理顺序；
  • 备份保留周期。

例如，MySQL 8.4 中可以查看：

SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

不要简单地把保留时间设置为“备份周期”。如果全量备份每周一次，而某次全量备份失败，至少需要保留上一份成功全量备份之后的全部 Binlog，并留出检测和恢复时间。

4. 持久化参数影响已提交事务的可恢复性

常见配置：

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'sync_binlog';

典型的强持久化配置是：

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

直觉上：

  • innodb_flush_log_at_trx_commit=1：每次事务提交都将 InnoDB Redo 刷到持久存储；
  • sync_binlog=1：每次事务提交相关的 Binlog 都同步到持久存储。

这有助于降低主机崩溃后的丢失窗口，但不是对所有故障的绝对保证。磁盘控制器、电源保护、虚拟化存储和文件系统仍可能影响持久性。

如果为了性能放宽这些配置，必须明确 RPO：主机突然断电时，最近若干已返回成功的事务可能尚未持久化，Redo 和 Binlog 之间也可能出现更复杂的恢复边界。

五、PITR 的完整条件和故障边界

设全量备份记录的 Binlog 起点为：

binlog.000123:456789

目标时间为：

2025-03-08 14:35:00

理想恢复流程是：

恢复全量备份→从 binlog.000123:456789 开始重放→在目标事务之前停止\text{恢复全量备份} \rightarrow \text{从 } binlog.000123:456789 \text{ 开始重放} \rightarrow \text{在目标事务之前停止}

1. 必须有“全量状态 + 连续日志”

以下组合不能完成可靠 PITR：

现状 问题
只有全量备份，没有 Binlog 只能恢复到全量备份时刻
只有 Binlog，没有全量备份 缺少重放起点
Binlog 中间缺文件 后续事件无法形成完整历史
只保留部分表的逻辑备份，却重放全实例 Binlog 可能缺少其他表和对象
备份坐标不准确 可能漏放或重复放事务
恢复到错误的时间边界 误删或误更新仍然被重放

2. 时间点不是足够精确的唯一标识

Binlog 事件包含时间信息，但时间戳通常只能精确到秒，而且事件时间可能受事务提交和记录方式影响。多个事务可以具有相同时间戳。

因此：

  • --stop-datetime 适合粗粒度恢复；
  • 对误操作恢复，最好根据 Binlog 事件、事务边界和位置确定停止点；
  • 目标时间必须转换成“某个事务之前”或“某个事务之后”，而不是只凭应用日志中的秒级时间。

3. GTID 与文件位置

GTID 为事务提供逻辑标识，例如：

source_uuid:12345

GTID 集合可以表达一组已执行或需要执行的事务，比单纯文件位置更适合复制拓扑变化和缺少连续文件编号的情况。

查看 GTID 配置：

SHOW VARIABLES LIKE 'gtid_mode';
SHOW VARIABLES LIKE 'enforce_gtid_consistency';
SELECT @@GLOBAL.gtid_executed;

但 GTID 不会自动解决所有恢复问题：

  • 目标实例可能已经执行过部分 GTID；
  • 目标实例可能带有不应存在的 gtid_executed 状态；
  • 恢复到新实例和恢复到原实例的处理方式不同；
  • 使用 --skip-gtids 或手工修改 GTID 相关行为可能导致重复执行或错误跳过。

文件位置适合明确的单实例 Binlog 重放；GTID 适合需要识别事务集合的环境。实际选择应与备份工具、复制拓扑和恢复目标保持一致。

六、PITR 演练：从全量备份恢复到误操作前

下面演练一个典型场景：

  1. 先做一份 shop 数据库全量逻辑备份；
  2. 之后插入一笔订单；
  3. 随后执行错误的删除；
  4. 恢复到删除之前。

1. 演练前提

检查：

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW BINARY LOG STATUS;

要求：

  • log_bin=ON；
  • 备份账号具备导出所需权限；
  • shop 主要使用 InnoDB；
  • 备份文件和从备份点开始的 Binlog 都可读取；
  • 恢复目标最好是隔离的新实例，不要直接覆盖生产实例。

2. 做带坐标的全量备份

mysqldump \
  --user=backup \
  --password \
  --host=127.0.0.1 \
  --single-transaction \
  --source-data=2 \
  --routines \
  --events \
  --triggers \
  --databases shop \
  > shop-full.sql

保存导出文件后，记录其摘要：

sha256sum shop-full.sql > shop-full.sql.sha256

同时保存：

  • 执行时间；
  • MySQL 版本；
  • 导出命令；
  • SHOW BINARY LOG STATUS 或 --source-data 中的坐标；
  • 账号和权限说明；
  • 文件校验值。

3. 产生一笔正常事务

USE shop;

START TRANSACTION;

INSERT INTO order_info(id, account_id, amount, created_at)
VALUES (1001, 1, 88.00, CURRENT_TIMESTAMP);

UPDATE account
SET balance = balance - 88.00
WHERE id = 1;

COMMIT;

由于使用了 InnoDB，这两个修改作为一个事务提交。以行格式 Binlog 为例，Binlog 中会出现事务开始、行变化和提交相关事件。

可以在当前日志文件中查找该事务：

mysqlbinlog \
  --base64-output=DECODE-ROWS \
  -vv \
  /var/lib/mysql/binlog.000123 \
  | grep -n -A 30 -B 10 "1001"

实际生产环境不应仅用 grep 作为解析器。更可靠的做法是查看完整事务，确认：

  • 事务的 GTID（如果启用）；
  • 事务开始位置；
  • 行事件；
  • COMMIT；
  • 下一个事务的起始位置。

4. 产生错误操作

例如应用错误地执行：

USE shop;

DELETE FROM order_info
WHERE account_id = 1;

如果没有 WHERE 限制到正确订单，可能删除了账户 1 的全部订单。

此时不要继续在原库尝试“反向写 SQL”修复。反向 SQL 可能缺少被删除行的完整原值，也可能与并发写入互相覆盖。正确方式是使用备份和 Binlog 重建隔离恢复实例。

5. 准备恢复实例

恢复实例应满足：

  • MySQL 版本与备份来源兼容；
  • 数据目录为空或为专用实例；
  • 不接收业务流量；
  • 应用账号不能连接；
  • 如不希望恢复过程继续写入 Binlog，应按恢复环境的权限和拓扑设计处理。

导入全量备份：

mysql \
  --user=root \
  --password \
  --host=127.0.0.1 \
  < shop-full.sql

导入后检查：

SELECT COUNT(*) FROM shop.order_info;
SELECT * FROM shop.account ORDER BY id;

此时状态应该是全量备份时刻的状态：已经提交到备份快照的内容存在，备份开始或结束之后才发生的事务不一定存在。

6. 从备份坐标开始读取 Binlog

如果备份文件中的坐标是：

binlog.000123:456789

可以先导出从该位置开始的 SQL 文本进行审查：

mysqlbinlog \
  --start-position=456789 \
  /var/lib/mysql/binlog.000123 \
  > replay.sql

如果后续已经切换到多个文件，需要按顺序读取：

mysqlbinlog \
  --start-position=456789 \
  /var/lib/mysql/binlog.000123 \
  /var/lib/mysql/binlog.000124 \
  /var/lib/mysql/binlog.000125 \
  > replay.sql

注意文件必须按 Binlog 顺序排列，不能按文件修改时间、目录显示顺序或上传完成顺序随意拼接。

审查 replay.sql 时，应定位错误 DELETE 所在事务。目标是：

重放备份点之后的正常事务
但不重放错误 DELETE 事务及其之后不应恢复的内容

7. 按事务边界停止重放

如果已经确定错误事务开始位置为 P_bad，可以把停止位置设为该事务开始位置之前的边界。实际使用时，必须根据 mysqlbinlog 输出中的事件位置确认语义，不能直接把“看到的任意 end_log_pos”当作停止位置。

常见做法是先生成一个不包含错误事务的文件，例如：

mysqlbinlog \
  --start-position=456789 \
  --stop-position=789012 \
  /var/lib/mysql/binlog.000123 \
  /var/lib/mysql/binlog.000124 \
  > replay-before-delete.sql

这里的 789012 不是固定值，而应当是经过 Binlog 检查后确定的边界：它应位于错误事务开始之前，并且不要截断前一个事务。生产操作记录中必须保存“为什么选择这个位置”的证据，例如事务 GTID、事务起止位置和人工复核结果。

随后导入：

mysql \
  --user=root \
  --password \
  --host=127.0.0.1 \
  < replay-before-delete.sql

如果使用 --stop-datetime：

mysqlbinlog \
  --start-position=456789 \
  --stop-datetime='2025-03-08 14:35:00' \
  /var/lib/mysql/binlog.000123 \
  /var/lib/mysql/binlog.000124 \
  | mysql --user=root --password --host=127.0.0.1

这个方式只能在目标时间和 Binlog 时间语义足够明确时使用。对同一秒内存在多个事务的情况，位置或 GTID 通常更可靠。

8. 恢复后的验证

恢复完成不能只看命令退出码为 0。至少检查：

SELECT COUNT(*) FROM shop.order_info;

SELECT
    id,
    balance
FROM shop.account
ORDER BY id;

SELECT
    SUM(amount) AS total_amount
FROM shop.order_info
WHERE account_id = 1;

还应验证：

  • 被误删的订单存在；
  • 错误删除之后不应恢复的事务没有被重放；
  • 账户余额与订单业务规则一致；
  • 外键、唯一键和非空约束没有异常；
  • 存储过程、触发器、事件和视图存在；
  • 字符集、排序规则和时区符合原实例；
  • 应用以只读方式执行关键查询；
  • 必要时对源库和恢复库做按主键范围的校验或抽样校验。

七、如何从远程服务器读取和归档 Binlog

Binlog 不应只留在数据库主机本地。磁盘损坏、误删文件、主机丢失都可能同时摧毁数据和 Binlog。

查看远程 Binlog 可以使用：

mysqlbinlog \
  --read-from-remote-server \
  --host=db.example.com \
  --port=3306 \
  --user=binlog_reader \
  --password \
  --raw \
  --result-file=/backup/binlog/ \
  binlog.000123

这里的重点是：

  • --read-from-remote-server：从远程 MySQL 读取；
  • --raw：保存原始 Binlog 文件，而不是转换为 SQL 文本；
  • --result-file：指定保存目录；
  • 账号需要读取 Binlog 所需权限；
  • 归档完成后应校验文件大小、校验和和文件序列。

生产归档通常还需要处理：

  1. 新文件不断生成；
  2. 归档端重启后从断点继续；
  3. 文件上传成功后再允许源端清理；
  4. 源端已经清理但归档不完整时报警；
  5. 归档文件加密和访问控制；
  6. 防止归档文件被篡改。

Binlog 中可能包含业务敏感数据，尤其是行事件解码后可能直接暴露列值，因此归档权限必须按敏感数据处理。

八、物理恢复与逻辑恢复的取舍

维度 逻辑备份 物理备份
保存内容 SQL、对象和逻辑数据 表空间、日志和实例文件
恢复速度 数据量大时通常较慢 通常更快
跨版本迁移 通常更灵活 兼容约束更强
跨平台 通常较容易 受文件格式和环境影响
单表恢复 方便 通常需要额外流程
可读性 人工可审查 不适合直接阅读
PITR 配合 需要准确记录 Binlog 坐标 需要保存物理备份一致性元数据
对非 InnoDB 的处理 需要额外锁和一致性设计 需要确认引擎文件是否被正确纳入
空间和速度 可能占用更多 CPU 和时间 通常更适合大实例

这不是“逻辑一定好”或“物理一定好”的选择。

常见组合是：

  • 使用物理热备作为快速灾难恢复基线；
  • 使用逻辑备份作为跨版本迁移、单库恢复和对象审查手段；
  • 两者都保留 Binlog，以提供 PITR；
  • 定期把备份恢复到隔离实例进行验证。

九、常见误解和失败表现

误解一：mysqldump 命令成功就说明备份一致

不一定。需要继续确认：

  • 是否中途连接断开；
  • 输出文件是否被截断；
  • 是否包含需要的 routines、events 和 triggers；
  • 是否存在非事务表；
  • 是否发生了并发 DDL；
  • 是否记录了正确的 Binlog 坐标；
  • 恢复测试是否成功。

检查文件末尾：

tail -n 20 shop-full.sql

导出文件通常会有结束标记或恢复状态语句，但不能只依赖某个固定文本判断成功。更可靠的是查看工具退出码、文件大小变化、日志和实际导入结果。

误解二：复制 .ibd 文件就是物理备份

独立表空间并不是孤立数据库。表空间 ID、数据字典、系统表空间、Redo、Undo、配置和表定义可能共同决定其可用性。脱离完整备份体系复制单个 .ibd 文件，通常不能直接恢复整张表。

误解三：Binlog 就是数据库的完整历史快照

Binlog 只记录发生过的变化，不包含全量状态。它依赖一个正确的基线，而且 Binlog 可能受格式、过滤规则、DDL、非确定性语句和保留周期影响。

误解四：用时间戳恢复到某秒就一定准确

多个事务可能共享同一秒的时间戳。应用日志时间、数据库服务器时间、客户端时间和 Binlog 事件时间也可能不同。精确恢复应结合事务内容、位置和 GTID，必要时先在隔离环境演练多次。

误解五：恢复进程返回成功就表示恢复正确

恢复程序成功只说明输入事件被接受。它不说明：

  • 恢复到了正确时间点；
  • 应用数据没有被误过滤；
  • 触发器和事件符合预期；
  • 业务约束仍然成立；
  • 所有 Binlog 文件都已覆盖。

恢复验证必须包含数据库层检查和业务层检查。

十、恢复失败时的诊断路径

1. 逻辑导入报错

常见错误包括：

  • Unknown collation：目标版本不支持源端排序规则；
  • Table already exists：目标实例不是空实例，或恢复顺序不正确；
  • Access denied：账号缺少创建对象、触发器、事件或例程权限；
  • Duplicate entry：重复导入或恢复边界错误；
  • 外键错误：对象顺序、数据完整性或关闭外键检查后的数据问题；
  • 字符集错误：客户端字符集、连接字符集和表定义不一致。

诊断命令：

mysql --version
mysqldump --version

目标端检查：

SELECT VERSION();
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
SHOW PLUGINS;

2. Binlog 重放报错

重点区分：

  • 文件找不到：归档不完整或路径错误；
  • Event read failed：Binlog 截断或损坏；
  • Duplicate entry：恢复起点重复，或目标库并非干净基线；
  • Unknown database：全量备份没有包含对应数据库，或 Binlog 中存在该库的变化；
  • GTID 已执行：目标实例的 GTID 状态与恢复计划不匹配；
  • 语句依赖外部状态：语句格式 Binlog 可能依赖原实例变量、当前数据库、临时表或非确定性函数。

每次 PITR 都应保存：

  • 全量备份文件校验值；
  • 全量备份对应的 Binlog 文件和位置；
  • 使用过的 Binlog 文件列表；
  • mysqlbinlog 参数；
  • 停止位置或目标 GTID；
  • 失败时的错误行和目标实例日志；
  • 恢复前后关键数据校验结果。

3. 物理恢复无法启动

常见原因：

  • 直接复制运行中数据目录导致文件不一致；
  • 目标 MySQL 版本不兼容；
  • 文件所有者和权限错误；
  • 配置的 datadir 与实际目录不同；
  • 只复制了部分表空间；
  • 缺少系统表空间、Redo、Undo 或相关元数据；
  • 恢复实例与原实例使用了不同的插件或加密密钥；
  • 加密表空间缺少密钥管理配置。

首先查看错误日志：

journalctl -u mysqld --no-pager -n 200

不要在原始备份目录上反复尝试启动。应复制出工作副本，保留原始证据，避免一次失败操作破坏后续恢复可能性。

十一、恢复设计中的 RPO、RTO 和验证闭环

两个指标需要先区分：

  • RPO（Recovery Point Objective）：最多能接受丢失多长时间的数据；
  • RTO（Recovery Time Objective）：最多能接受恢复花费多长时间。

举例：

  • 每周一次全量逻辑备份，Binlog 只保留三天：无法保证七天内任意时点恢复；
  • 每天一次物理备份、Binlog 实时归档：通常可以把恢复点推进到备份后的某个 Binlog 事务；
  • 只有备份没有定期恢复演练：RTO 实际上是未知的。

一个可验证的闭环应当是：

  1. 生成全量备份；
  2. 记录一致性点和 Binlog 坐标；
  3. 归档连续 Binlog；
  4. 校验备份文件和日志文件；
  5. 在隔离实例恢复；
  6. 按目标时间重放；
  7. 验证数据库和业务数据；
  8. 测量实际恢复耗时；
  9. 记录缺陷并修正备份流程。

尤其要验证以下故障路径：

  • 误删一张表；
  • 误更新大量行；
  • 主机磁盘损坏；
  • 全量备份文件损坏；
  • 中间 Binlog 文件缺失；
  • 主实例不可用但远程 Binlog 仍可获取；
  • 恢复后需要将业务切换到新实例；
  • 恢复完成后如何重新建立复制或重新开始 Binlog 归档。

十二、一个可落地的备份恢复组合

对于主要使用 InnoDB 的单实例 MySQL，可以采用如下结构：

定期全量备份
    ├── 逻辑备份：用于迁移、单库恢复、对象审查
    └── 物理备份：用于大实例快速恢复

持续 Binlog 归档
    └── 覆盖最近一次成功全量备份之后的全部日志

恢复流程
    ├── 选择全量备份
    ├── 验证备份校验值
    ├── 恢复到隔离实例
    ├── 从一致性坐标开始读取 Binlog
    ├── 按事务边界停止
    ├── 执行数据库和业务校验
    └── 决定数据导出、实例切换或重新建立复制

其中最容易被忽略的是“从一致性坐标开始”。如果起点过早，可能重复执行事务；如果起点过晚，可能丢失事务。使用逻辑备份时，应让备份工具记录坐标；使用物理备份时，应保存备份工具生成的元数据，而不是凭人工记忆执行 SHOW BINARY LOG STATUS。

备份恢复系统最终要证明的不是“文件存在”，而是：

可读取的全量备份+连续的 Binlog+明确的恢复边界+经过验证的恢复结果\text{可读取的全量备份} + \text{连续的 Binlog} + \text{明确的恢复边界} + \text{经过验证的恢复结果}

只有这四部分同时成立，逻辑备份、物理备份和 Binlog 才真正构成了可执行的 PITR 能力。

系列导航与关联阅读

官方资料

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。