精读笔记 · 第23章 SQL 数据备份管理
大约 5 分钟
精读笔记 · 第23章 SQL 数据备份管理
精读整理自 RHCE9 随堂讲义（原文字版 19 页）｜同名视频（第02套，随课笔记对应课程） 关联知识文件：
分章笔记/02-Oracle（RMAN/expdp 对照）、命令手册/02-Oracle命令手册.md
一、备份基础概念（面试常考）
- 备份原因：数据丢/删（误操作）；目标：数据一致性 + 服务可用性。
- 物理备份/冷备份：直接复制数据库文件（tar/cp/scp），适合大库、不受存储引擎限制、速度快，但需停服务、不能恢复到不同 MySQL 版本。
- 逻辑备份/热备份：备份建库建表插数据等 SQL 语句（mysqldump/mydumper），适合中小库，不停服务但效率较低。
- 备份模式：
- 完全备份：全部数据。
- 增量备份：只备“自上一次备份以来”变化（体积小、速度快；恢复要按时间顺序逐版本回放，恢复慢）。
- 差异备份：每次相对“第一次完全备份”的变化（体积居中；恢复只需 完全备份 + 最后一次差异，速度居中）。
二、xtrabackup 物理热备（percona 出品，InnoDB 非阻塞热备）
- 版本对应：xtrabackup-24 配 MySQL5.7（命令 innobackupex）；xtrabackup-80 配 MySQL8.0+（命令 xtrabackup）。
- 安装（5.7 例）：配 MySQL 官方源 + yum-utils，
yum-config-manager --disable mysql80-community --enable mysql57-community→ 装mysql-community-libs-compat→ 装 percona-release →yum -y install percona-xtrabackup-24。 - 全备：
innobackupex --user=root --password='密码' /xtrabackup/full→ 目录按时间生成，含数据/配置/日志；cat .../xtrabackup_binlog_info记录 binlog 位置。 - 全备恢复：
systemctl stop mysqld→ 清空/var/lib/mysql/*（模拟损坏）→innobackupex --apply-log 备份目录（重演回滚，使备份一致）→innobackupex --copy-back 备份目录→chown -R mysql.mysql /var/lib/mysql→systemctl start mysqld。 - 增量备份：每次
--incremental /xtrabackup --incremental-basedir=上一份备份目录（周一夜全备、周二基于周一、周三基于周二…）。 - 增量恢复：
--apply-log --redo-only 全备后逐份--apply-log --redo-only 全备 --incremental-dir=增量目录合并（想恢复到周三就把周一/周二/周三都合进去）→--copy-back→ 授权重启。 - 差异备份：增量备份的
--incremental-basedir每次都指周一全备；恢复 = 全备 + 最后一次差异。 - xtrabackup-80 命令风格：全备
xtrabackup --backup --target-dir=/data/backup/base -uroot -p密码 -H localhost -P3306 --no-server-version-check；--prepare（末次增量不加 --apply-log-only）；--copy-back；增量--incremental-basedir=...。压缩备份加--compress（可--compress-threads=4），解压需装qpress再--decompress（--remove-original清原文件）。
三、mysqldump + binlog 逻辑备份恢复（经典方案）
- 语法：
mysqldump -h 主机 -u用户 -p密码 库名 > 备份.sql。 - 常用选项： | 选项 | 作用 | | --- | --- | |
-A/--all-databases| 所有库 | |-B 库1 库2| 多个指定库 | |--single-transaction| InnoDB 一致性+不停服务（热备） | |--master-data=1/2| 记录 binlog 文件名与位置（1=执行语句，2=注释） | |-F/--flush-logs| 备份前刷新/截断日志 | |-R| 备份存储过程/函数；--triggers触发器 | |--opt| 开启多种高级选项 | - 备份文件内容：建库建表 +
LOCK TABLES ... WRITE（一致性锁）+ 第 22 行CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=154;（binlog 截断位置）。 - 恢复实战：先备份 binlog（
cp /var/lib/mysql/*bin* ~）→ 停库清目录重启（拿到新临时密码，改为“备份时的密码”）→mysql -p'密码' < 备份.sql（恢复的是备份时刻的数据）→ 再回放备份之后的 binlog：mysqlbinlog localhost-bin.000002 ... --start-position=154 | mysql -p'密码'（有多个日志就全列上）→ 数据完整。 - 误删某个库/跳过误操作：把
mysqlbinlog全量输出到 1.txt，手工删掉误操作对应的at N段再cat 1.txt | mysql；或研究 start/stop-position 精确定位（课后题）。 - 恢复产生的日志也会进 binlog 占空间：恢复前
set sql_log_bin=0;再source 备份.sql（或备份文件内加关闭 binlog 语句）。
四、记录的导出与导入（文本方式）
- 前提：my.cnf 设
secure-file-priv=/backup（MySQL 只信任该目录写文件）→ 重启 →chown mysql.mysql /backup。 - 导出：
SELECT * FROM 库.表 INTO OUTFILE '/backup/x.txt' [FIELDS TERMINATED BY '---'];（文件不能已存在；报 1290=未配 secure-file-priv、1064=漏 into 关键字、1086=文件已存在）。 - 命令行导出：
mysql -e 'select * from 表' > 文件；--xml、--html可导指定格式。 - 导入：
DELETE FROM 表;后LOAD DATA INFILE '/backup/x.txt' INTO TABLE 库.表;。 - 注意：文本导入导出只搬记录不搬表结构；要先用 mysqldump 备好结构，先恢复结构再导数据。
五、命令速查表
| 场景 | 命令 |
|---|---|
| 逻辑备份 | mysqldump -uroot -p密码 --all-databases --single-transaction --master-data=2 -F > /backup/x.sql |
| 逻辑恢复 | mysql -p密码 < /backup/x.sql |
| binlog 回放 | mysqlbinlog bin.000002 --start-position=154 | mysql -p密码 |
| xtrabackup 全备 | innobackupex --user=root --password=... /backup |
| apply/copy-back | innobackupex --apply-log [--redo-only] [--incremental-dir=...] 全备、--copy-back 全备 |
| xtrabackup80 | xtrabackup --backup/--prepare/--copy-back --target-dir=... |
| 记录导出 | select * into outfile '/backup/x.txt' from 表 |
| 记录导入 | load data infile '/backup/x.txt' into table 表 |
| 恢复期免日志 | set sql_log_bin=0; |
六、易错点
- 冷备 vs 热备、逻辑 vs 物理别混：xtrabackup 是物理热备；mysqldump 是逻辑热备（锁表保证一致）。
- 增量备份的 basedir＝上一份（含上次增量）；差异备份的 basedir＝第一次全备。
- 增量恢复顺序不能乱：先全备
--redo-only，再按时间逐份合并；最后一份不能加 --apply-log-only。 --master-data记录的是备份时刻 binlog 位置，漏掉它就无法精确补回后续数据。- 全库恢复先停库并清空 datadir；恢复后必须
chown -R mysql.mysql，否则起不来。 - secure-file-priv 不配/目录不对 → INTO OUTFILE 报 1290；文件已存在报 1086。
七、实操清单
mysqldump 全备含 master-data → 造数据→ 停库清目录 → 从备份恢复 + mysqlbinlog 补增量 → 验证完整 → xtrabackup-80 全备/增量/差异各一轮恢复演练 → SELECT INTO OUTFILE + LOAD DATA INFILE 文本搬记录实验。
八、本章自测
- 物理/逻辑备份区别与代表工具？2. 增量与差异备份区别（体积/恢复方式）？3. xtrabackup 恢复三步？4. 增量恢复时最后一份为什么不能加 --apply-log-only？5. mysqldump 保证一致性的两个选项？6. --master-data=2 记录什么？7. INTO OUTFILE 报 1290 是什么原因？8. 文本导出导入与表结构的关系？