MySQL 入门（八）：备份恢复与日常维护

MySQL 入门指南 · 第八章：把数据保存、恢复和日常排查纳入使用流程。

数据库能正常启动，不代表数据不会被误删；事务能够回滚，也不能撤销已经提交的所有历史操作。备份需要从恢复需求设计，再通过实际恢复确认其用途。

本章以 MySQL 8.4 的命令行工具为例。备份与恢复需要已经分配好的相应权限；所有地址、端口、账号和目录都要与实际环境核对。

一、先确定要恢复什么

副本、复制和备份的能力不同。复制可能快速传播误删；同机副本无法覆盖整机故障；存储快照是否具备数据库一致性，要看创建方式和恢复流程。

二、逻辑备份、物理备份与日志

不要直接复制正在运行的 MySQL 数据目录，就认为已经获得可用物理备份。InnoDB 文件、日志和其他状态之间需要保持一致。

redo、undo 与 binlog

恢复能力取决于完整方案，不能只看到某个日志文件存在就假定能回到任何时间点。

三、使用 mysqldump 创建逻辑备份

以下以 mysql_learn 为对象，假设 backup_user 已具备本次范围所需权限。命令在操作系统终端执行，不在 SQL 提示符内执行。

先准备仅供当前用户访问的新目录：

umask 077
backup_dir=$(mktemp -d "$HOME/mysql-backup-XXXXXXXX")

确认上一步成功后执行：

if mysqldump \
  --host=127.0.0.1 --port=3306 \
  --user=backup_user --password \
  --single-transaction --quick \
  --routines --events --triggers \
  --no-tablespaces --set-gtid-purged=OFF \
  --databases mysql_learn \
  --result-file="$backup_dir/mysql_learn.sql.partial" \
  && mv -- "$backup_dir/mysql_learn.sql.partial" "$backup_dir/mysql_learn.sql"; then
    printf '导出完成：%s\n' "$backup_dir/mysql_learn.sql"
else
    printf '导出或文件整理失败，检查错误并保留不完整文件用于排查\n' >&2
fi

这个写法只在导出成功且文件整理成功后报告完成，不把失败途中创建的文件当作最终备份。用于自动化脚本时，还应让失败分支返回非零状态，并记录必要元数据。

关键选项

--single-transaction 不为 MyISAM 等非事务表提供同样的一致性保证。备份期间也应避免并发的表结构变更，不能把它理解为“不加任何限制就能无影响地备份一切”。

账号权限与对象范围

通常涉及 SELECT、SHOW VIEW、TRIGGER、EVENT，以及获取存储程序定义等对应权限，具体以版本、工具选项和对象为准。不要为了让一条命令不报错就给备份账号所有管理权限。

单个数据库导出不等于完整实例备份。账号、授权、配置、证书、外部依赖和其他数据库对象，要纳入各自的保存与恢复计划。

工具应与服务端版本相容。导出成功也不保证可以直接向更旧版本导入，排序规则、语法和功能差异都可能阻止恢复。

四、备份完成后还要确认什么

ls -lh "$backup_dir/mysql_learn.sql"
shasum -a 256 "$backup_dir/mysql_learn.sql"

在提供 sha256sum 的系统中也可以用对应命令。校验值用于之后检测文件是否变化，应与备份记录一起保存。

SQL 备份包含数据，可能也包含对象定义中的敏感信息。应限制访问，并按需要加密与保存解密材料。若密钥只保存在故障主机上，备份文件仍可能无法使用。

五、恢复到独立实例

本节假设已经准备好一个独立的恢复实例，监听本机 3307，与原实例的数据目录完全分开，并由 restore_admin 执行恢复。必须先确认连接目标，不能把端口数字本身当作隔离保证。

检查目标身份：

mysql --protocol=TCP -h 127.0.0.1 -P 3307 -u restore_admin -p \
  -e 'SELECT @@hostname, @@port, @@server_uuid;'

确认这是可用于恢复的目标后，再导入：

mysql --protocol=TCP -h 127.0.0.1 -P 3307 -u restore_admin -p \
  < "$backup_dir/mysql_learn.sql"

上面的导出使用了 --databases，文件中包含数据库名和 USE。不能只在导入命令末尾换一个数据库名，就假定所有内容都会改到新库。

恢复文件还可能包含 DROP、定义者、触发器和事件等语句。应检查内容、账号条件和目标调度策略，避免导入后立即启动不应执行的任务。

导入后检查

在恢复实例连接中执行：

USE mysql_learn;
SHOW FULL TABLES;
SHOW CREATE TABLE emp;
SELECT COUNT(*) AS employee_count, SUM(salary) AS total_salary FROM emp;

如果源数据始终保持本指南第二章的固定版本，应为 6 位员工、总薪资 58500.00。实际备份如果已经包含其他修改，应与备份时的记录比较，不能固定套用这个值。

再按范围确认约束、索引、视图、程序对象、中文内容、关键查询和应用功能。只比较 COUNT 并不足以证明每一行内容正确。

六、按时间点恢复的基本流程

仅恢复一次全量逻辑备份，只能回到它所覆盖的状态。需要恢复到之后的某一时刻，通常要有与基础备份一致的起点和后续连续二进制日志。

确认可用基础备份及对应日志起点
                   ↓
在独立目标恢复基础数据
                   ↓
按事务边界回放所需日志，止于错误变更之前
                   ↓
核对数据、业务不变量与恢复后的应用行为

时间戳、日志位置、GTID 和事务边界不是可以随意互换的信息。本章示例关闭了 GTID_PURGED 输出，也没有保存完整的恢复起点，因此不能直接把它当作已经准备好的复制或时间点恢复方案。

二进制日志通常也不能简单地从错误变更“反向执行”来撤销历史。实际恢复应使用经过验证的工具和明确边界，原始备份与日志副本应保留。

七、日常状态与常见故障

查看连接和事务线索

SHOW FULL PROCESSLIST;
SELECT * FROM information_schema.innodb_trx;
SELECT * FROM performance_schema.data_lock_waits;

可见范围与权限有关，输出可能含业务 SQL。发现等待后，先区分执行慢、被锁阻塞、空闲连接和空闲但事务未结束等状态。

终止连接可能引发回滚并影响应用，不能只凭“运行时间长”就执行 KILL。应确认所属业务、阻塞关系和可接受影响。

查看连接容量与活动

SHOW GLOBAL STATUS
WHERE Variable_name IN ('Threads_connected', 'Threads_running', 'Connections');

Connections 是累计尝试次数，Threads_connected 是当前连接数，Threads_running 反映当前活动线程情况。累计值和瞬时值不能直接混成同一种指标。

数据库空间与日志

SELECT table_schema,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS estimated_mb
FROM information_schema.tables
WHERE table_schema = 'mysql_learn'
GROUP BY table_schema;

这是表与索引空间的近似汇总，不等于实例全部磁盘使用。redo、binlog、临时文件、备份和其他目录还需要从操作系统层面检查。

错误日志、慢查询记录和监控数据应与问题发生时间对应。慢查询记录本身也有配置、权限、存储与敏感数据管理要求。

八、维护顺序与速查

维护过程先明确实例和目标，再保存必要状态与备份，安排影响范围，执行变更并核对结果。升级之前确认兼容路径与回退条件，不能把旧数据目录随意交给另一个大版本启动。

上一篇：应用连接与 SQL 安全 · 返回指南目录。