第30章备份与恢复

本章目标

理解备份策略类型：全量备份、增量备份、差异备份的区别和适用场景
精通 mysqldump 逻辑备份的各种用法和关键参数
掌握使用 mysql 客户端恢复数据的方法
理解物理备份（XtraBackup）与逻辑备份（mysqldump）的区别
掌握基于二进制日志的 Point-in-Time Recovery (PITR)
了解 LOAD DATA INFILE 和 SELECT INTO OUTFILE 的数据导入导出
建立"备份 + 验证恢复"的完整数据保护意识

前置知识

熟悉 mysql 命令行客户端的使用
了解 MySQL 数据文件的基本结构
理解二进制日志（binlog）的基本概念
具有操作系统命令行操作经验

30.1 备份策略概述

30.1.1 三种备份类型

+------------------+     +-----------------------+     +----------------------+
|    Full Backup   |     |   Incremental Backup  |     |  Differential Backup |
|   (全量备份)      |     |   (增量备份)           |     |  (差异备份)           |
+------------------+     +-----------------------+     +----------------------+
| 备份所有数据       |     | 仅备份自上次备份以来的   |     | 仅备份自上次全量备份   |
| 恢复速度最快       |     | 变化（基于 binlog）      |     | 以来的所有变化         |
| 占用空间最大       |     | 占用空间最小             |     | 占用空间中等           |
| 备份时间最长       |     | 恢复需要全量+所有增量    |     | 恢复需要全量+最新差异  |
+------------------+     +-----------------------+     +----------------------+

推荐策略（混合使用）：

周一：全量备份
周二～周日：增量备份（binlog）
// 或
周日：全量备份
周一～周六：增量备份

恢复步骤：
1. 恢复最近的全量备份
2. 依次应用之后的所有增量备份（或差异备份）
3. 到达目标时间点

30.1.2 备份验证的重要性

一条没有经过恢复测试的备份，不是真正的备份。

bash

# 备份后必须进行的验证步骤：
# 1. 在独立的测试环境中恢复备份
# 2. 检查关键表的数据行数
# 3. 执行应用冒烟测试（关键查询）
# 4. 检查存储过程和函数是否正常
# 5. 验证触发器和事件
# 6. 验证表结构一致性（对比 SHOW CREATE TABLE）

30.2 mysqldump 逻辑备份

mysqldump 是 MySQL 自带的逻辑备份工具，将数据库结构和数据导出为 SQL 语句文件。

30.2.1 基本用法

bash

# 备份单个数据库
mysqldump -u root -p database_name > backup.sql

# 备份多个数据库
mysqldump -u root -p --databases db1 db2 db3 > multi_backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_backup.sql

# 备份特定表
mysqldump -u root -p database_name table1 table2 > tables_backup.sql

# 带端口和主机
mysqldump -h 192.168.1.100 -P 3306 -u root -p database_name > remote_backup.sql

30.2.2 关键参数详解

结构 vs 数据控制：

bash

# 仅备份表结构（不要数据）- 用于重建空表
mysqldump -u root -p --no-data database_name > schema_only.sql
mysqldump -u root -p -d database_name > schema_only.sql  # -d 是 --no-data 的简写

# 仅备份数据（不要建表语句）- 用于向已有表导入数据
mysqldump -u root -p --no-create-info database_name > data_only.sql
mysqldump -u root -p -t database_name > data_only.sql  # -t 是 --no-create-info 的简写

# 同时备份结构和数据（默认行为）
mysqldump -u root -p database_name > full_backup.sql

一致性保证（InnoDB）：

bash

# --single-transaction: 使用事务快照保证一致性
# 发送 START TRANSACTION WITH CONSISTENT SNAPSHOT
# 整个备份过程在一个事务中完成，不锁表！
# 适用于 InnoDB 表（默认存储引擎）
mysqldump -u root -p --single-transaction database_name > consistent_backup.sql

# --single-transaction 的工作原理：
# 1. 开始事务（REPEATABLE READ 隔离级别）
# 2. 获取一致性快照
# 3. 逐表导出数据（所有读取基于同一个快照）
# 4. 提交事务
# 期间其他连接可以正常读写！

# MyISAM 表的一致性（需要锁表）：
mysqldump -u root -p --lock-tables database_name > locked_backup.sql
# 或
mysqldump -u root -p --lock-all-tables --all-databases > global_locked_backup.sql
# 注意：--single-transaction 和 --lock-tables 是互斥的

Drop/Replace 控制：

bash

# 在 CREATE 前加入 DROP（方便干净恢复）
mysqldump -u root -p --add-drop-database database_name > backup.sql
mysqldump -u root -p --add-drop-table database_name > backup.sql
# 生成的 SQL：
# DROP TABLE IF EXISTS `users`;
# CREATE TABLE `users` (...);

# 跳过 --add-drop-table（恢复时不删除已有表）
mysqldump -u root -p --skip-add-drop-table database_name > backup.sql

其他重要对象：

bash

# 备份存储过程和函数
mysqldump -u root -p --routines database_name > backup_with_routines.sql

# 备份触发器（默认已包含）
mysqldump -u root -p --triggers database_name > backup_with_triggers.sql

# 备份定时事件
mysqldump -u root -p --events database_name > backup_with_events.sql

# 完整备份（包含所有对象）
mysqldump -u root -p --single-transaction --routines --events --triggers \
  --all-databases > complete_backup.sql

条件导出：

bash

# 仅导出符合 WHERE 条件的行
mysqldump -u root -p database_name orders --where="created_at >= '2024-01-01'" > recent_orders.sql

# 仅导出部分数据行（结合 LIMIT）
mysqldump -u root -p database_name users --where="1=1 LIMIT 1000" > sample_users.sql

复制相关：

bash

# --master-data: 在输出中包含 CHANGE MASTER TO 语句（用于复制设置）
# --master-data=1: 不注释 CHANGE MASTER TO（自动生效）
# --master-data=2: 注释掉 CHANGE MASTER TO（仅供参考）
mysqldump -u root -p --master-data=2 --single-transaction database_name > backup.sql
# 输出中包含：
# -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=1234;

# --set-gtid-purged: 用于 GTID 复制
mysqldump -u root -p --set-gtid-purged=ON database_name > backup.sql
mysqldump -u root -p --set-gtid-purged=OFF database_name > backup.sql  # 非复制环境

压缩和性能：

bash

# 结合 gzip 压缩（减少磁盘占用和网络传输）
mysqldump -u root -p database_name | gzip > backup.sql.gz

# 解压恢复
gunzip < backup.sql.gz | mysql -u root -p database_name

# 使用 --compress 压缩客户端-服务器通信（MySQL 8.0.18+）
mysqldump -u root -p --compress database_name > backup.sql

# 扩展插入（多行合并为一条 INSERT，加快恢复速度）
mysqldump -u root -p --extended-insert database_name > backup.sql
# INSERT INTO users VALUES (1,'a'),(2,'b'),(3,'c'),...;

# 单行插入（每条 INSERT 一行，增大文件但更安全）
mysqldump -u root -p --skip-extended-insert database_name > backup.sql

30.2.3 mysqldump 生产级完整命令

bash

#!/bin/bash
# MySQL 生产环境备份脚本模板

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_USER="backup_user"
DB_PASS="backup_password"
DB_HOST="localhost"
DB_NAME="production_db"

mkdir -p $BACKUP_DIR

# 执行备份
mysqldump \
  -h $DB_HOST \
  -u $DB_USER \
  -p"$DB_PASS" \
  --single-transaction \
  --routines \
  --events \
  --triggers \
  --add-drop-database \
  --master-data=2 \
  --set-gtid-purged=OFF \
  --databases $DB_NAME \
  | gzip > ${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz

# 保留最近 7 天的备份
find $BACKUP_DIR -name "${DB_NAME}_*.sql.gz" -mtime +7 -delete

echo "Backup completed: ${DB_NAME}_${DATE}.sql.gz"

30.2.4 mysqldump 的局限性

速度慢：逻辑备份逐行导出，对于大型数据库（>100GB）速度非常慢
不是增量：每次都导出全部数据（虽然可以通过 --where 导出增量数据，但不基于 binlog）
恢复慢：恢复时需要逐条执行 SQL，重建索引
锁问题：虽然 --single-transaction 不锁 InnoDB 表，但 MyISAM 仍需锁表
大事务：整个备份在一个事务中，可能产生非常大的 undo log

30.3 数据恢复

30.3.1 使用 mysql 客户端恢复

bash

# 恢复到指定数据库
mysql -u root -p database_name < backup.sql

# 恢复包含 USE 语句的备份（使用 --databases 或 --all-databases 导出的）
mysql -u root -p < backup_with_use.sql

# 恢复时指定字符集
mysql -u root -p --default-character-set=utf8mb4 database_name < backup.sql

# 通过管道恢复压缩的备份
gunzip < backup.sql.gz | mysql -u root -p database_name

# 恢复时显示详细进度（MySQL 8.0.16+）
mysql -u root -p --show-progress database_name < large_backup.sql

30.3.2 使用 source 命令恢复

sql

-- 登录 MySQL 后执行
mysql> USE target_database;
mysql> source /path/to/backup.sql;

-- 或
mysql> source /path/to/backup_with_use.sql;  -- 文件中包含 USE 语句

30.3.3 部分恢复

bash

# 仅提取并恢复特定表的创建语句
grep -A 1000 "CREATE TABLE \`users\`" backup.sql | mysql -u root -p target_db

# 仅提取并恢复特定表的数据
sed -n '/INSERT INTO `users`/,/;/p' backup.sql | mysql -u root -p target_db

# 跳过某些表的恢复
grep -v "INSERT INTO \`large_logs\`" backup.sql | mysql -u root -p target_db

30.4 物理备份

30.4.1 冷备份（停机备份）

bash

# 冷备份步骤：
# 1. 停止 MySQL 服务
systemctl stop mysqld

# 2. 复制整个数据目录
tar -czf /backup/mysql_cold_$(date +%Y%m%d).tar.gz /var/lib/mysql/

# 3. 同时备份配置文件
cp /etc/my.cnf /backup/my_$(date +%Y%m%d).cnf

# 4. 启动 MySQL 服务
systemctl start mysqld

# 优点：简单可靠，数据完全一致
# 缺点：需要停机，不适合 7x24 服务

30.4.2 Percona XtraBackup（热物理备份）

XtraBackup 是目前最流行的开源 MySQL 物理热备份工具，可在不锁表的情况下备份 InnoDB 数据。

bash

# 安装 XtraBackup
# yum install percona-xtrabackup-80  # CentOS/RHEL
# apt install percona-xtrabackup-80  # Debian/Ubuntu

# 全量备份
xtrabackup --backup \
  --user=backup_user \
  --password=backup_pass \
  --target-dir=/backup/xtrabackup/full_$(date +%Y%m%d)

# 增量备份
xtrabackup --backup \
  --user=backup_user \
  --password=backup_pass \
  --target-dir=/backup/xtrabackup/inc_$(date +%Y%m%d) \
  --incremental-basedir=/backup/xtrabackup/full_20240101

# 准备备份（恢复前必需步骤 - 应用 redo log）
xtrabackup --prepare --target-dir=/backup/xtrabackup/full_20240101

# 准备增量备份恢复
xtrabackup --prepare --apply-log-only --target-dir=/backup/xtrabackup/full_20240101
xtrabackup --prepare --target-dir=/backup/xtrabackup/full_20240101 \
  --incremental-dir=/backup/xtrabackup/inc_20240102

# 恢复
# 1. 停止 MySQL
systemctl stop mysqld
# 2. 清空数据目录
rm -rf /var/lib/mysql/*
# 3. 复制恢复
xtrabackup --copy-back --target-dir=/backup/xtrabackup/full_20240101
# 4. 修复权限
chown -R mysql:mysql /var/lib/mysql
# 5. 启动 MySQL
systemctl start mysqld

30.4.3 逻辑备份 vs 物理备份对比

特性	mysqldump（逻辑）	XtraBackup（物理）
速度	慢（逐行导出）	快（直接复制数据文件）
恢复速度	慢（逐行插入+重建索引）	快（直接恢复数据文件）
大小	较小（纯数据，不含索引碎片）	较大（含索引+碎片）
跨版本恢复	兼容性好	需要相同或更高版本
跨引擎恢复	通用	主要针对 InnoDB
部分恢复	容易（过滤表/行）	较困难
热备份	支持（--single-transaction）	原生支持
增量备份	不支持（需自己实现）	原生支持
费用	免费	免费（开源版）
输出	SQL 文件（可读可编辑）	二进制文件

30.5 数据导入导出

30.5.1 SELECT INTO OUTFILE

将查询结果导出为纯文本文件（CSV 等格式），文件写入到 MySQL 服务器端的磁盘：

sql

-- 导出为 CSV 格式
SELECT id, name, email, created_at
INTO OUTFILE '/tmp/users_export.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM users
WHERE status = 'active';

-- 导出为 TSV（制表符分隔）
SELECT *
INTO OUTFILE '/tmp/products.tsv'
FIELDS TERMINATED BY '\t'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM products;

-- 仅导出部分列
SELECT order_id, customer_name, total_amount
INTO OUTFILE '/tmp/orders_summary.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE created_at >= '2024-01-01';

重要限制：

sql

-- 1. 需要 FILE 权限
GRANT FILE ON *.* TO 'export_user'@'%';

-- 2. 必须确保 secure_file_priv 设置允许
SHOW VARIABLES LIKE 'secure_file_priv';
-- 如果返回空字符串：可以写入任何目录
-- 如果返回路径（如 /var/lib/mysql-files/）：只能写入该目录
-- 如果返回 NULL：禁止文件写入（默认安全设置）

-- 3. 文件必须不存在（不会覆盖已有文件）

-- 4. 文件写入服务器磁盘，不是客户端！

30.5.2 LOAD DATA INFILE

从文本文件快速批量导入数据，比逐行 INSERT 快 10-100 倍：

sql

-- 基本用法
LOAD DATA INFILE '/tmp/users_import.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;  -- 跳过 CSV 标题行

-- 指定列对应关系
LOAD DATA INFILE '/tmp/users_import.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id, name, email, @created_str)  -- @ 表示用户变量
SET created_at = STR_TO_DATE(@created_str, '%Y-%m-%d %H:%i:%s');

-- 从客户端文件加载（非服务器文件）
LOAD DATA LOCAL INFILE 'C:/data/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- LOCAL 需要启用
-- 服务端：SET GLOBAL local_infile = ON;
-- 客户端：mysql --local-infile=1 -u root -p

-- LOAD DATA INFILE 性能优化技巧：
-- 1. 使用 LOCAL 时文件在客户端
-- 2. 非 LOCAL 时文件在服务器端（更快，避免网络传输）
-- 3. 考虑暂时禁用索引和约束，导入后重建（大导入时）
SET unique_checks = 0;
SET foreign_key_checks = 0;
-- ... LOAD DATA ...
SET unique_checks = 1;
SET foreign_key_checks = 1;

30.5.3 mysqlimport 命令行工具

mysqlimport 是 LOAD DATA INFILE 的命令行封装：

bash

# 文件名必须与表名相同
# 导入 data/users.csv 到 users 表
mysqlimport -u root -p \
  --fields-terminated-by=',' \
  --fields-enclosed-by='"' \
  --lines-terminated-by='\n' \
  --ignore-lines=1 \
  --local \
  mydatabase /path/to/data/users.csv

# 注意：文件名 users.csv → 表名 users
#       文件名 products.csv → 表名 products

30.6 二进制日志与 Point-in-Time Recovery (PITR)

30.6.1 二进制日志基础

sql

-- 查看所有二进制日志文件
SHOW BINARY LOGS;

-- 查看当前正在写入的日志文件及位置
SHOW MASTER STATUS;

-- 查看 binlog 事件内容
SHOW BINLOG EVENTS IN 'binlog.000001' LIMIT 10;
SHOW BINLOG EVENTS IN 'binlog.000001' FROM 1234 LIMIT 50;

-- 二进制日志配置（my.cnf / my.ini）
-- [mysqld]
-- log_bin = /var/log/mysql/binlog
-- binlog_format = ROW
-- binlog_expire_logs_seconds = 604800  # 7 天自动清理
-- max_binlog_size = 104857600  # 100MB 自动切换

-- 手动清理旧日志
PURGE BINARY LOGS TO 'binlog.000010';     -- 删除指定文件之前的所有日志
PURGE BINARY LOGS BEFORE '2024-06-01';    -- 删除指定日期之前的所有日志

30.6.2 mysqlbinlog 工具

bash

# 查看 binlog 内容（转换为可读 SQL）
mysqlbinlog /var/log/mysql/binlog.000001

# 查看指定时间范围
mysqlbinlog \
  --start-datetime="2024-01-15 09:00:00" \
  --stop-datetime="2024-01-15 12:00:00" \
  /var/log/mysql/binlog.000001

# 查看指定位置范围
mysqlbinlog \
  --start-position=1234 \
  --stop-position=5678 \
  /var/log/mysql/binlog.000001

# 排除特定数据库
mysqlbinlog --database=mydb /var/log/mysql/binlog.000001

# 导出为 SQL 文件
mysqlbinlog /var/log/mysql/binlog.000001 > binlog_output.sql

# 直接导入 MySQL（重放事件）
mysqlbinlog /var/log/mysql/binlog.000001 | mysql -u root -p

# 多个 binlog 文件连续处理
mysqlbinlog /var/log/mysql/binlog.000003 /var/log/mysql/binlog.000004 | mysql -u root -p

# 基于 GTID 的处理
mysqlbinlog --include-gtids='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1-100' \
  /var/log/mysql/binlog.000001

30.6.3 完整的 PITR 流程

场景：2024-01-15 11:30:00 有人误删了 important_table。
     需要恢复到 2024-01-15 11:29:00 的数据状态。

假设：
  - 最近全量备份：2024-01-15 02:00:00（备份时 binlog 位置：binlog.000010, pos 1234）
  - binlog 文件：binlog.000010, binlog.000011

步骤 1：准备环境

bash

# 在独立的恢复服务器上操作（不要在生产环境直接恢复！）

# 停止 MySQL
systemctl stop mysqld

# 清空数据目录
rm -rf /var/lib/mysql/*

# 重新初始化（或使用干净的备份实例）

步骤 2：恢复全量备份

bash

mysql -u root -p < /backup/mysql/full_backup_20240115_020000.sql

步骤 3：应用 binlog 到事故前

bash

# 从备份位置（binlog.000010, pos 1234）恢复到事故前一刻
mysqlbinlog \
  --start-position=1234 \
  --stop-datetime="2024-01-15 11:29:00" \
  /var/log/mysql/binlog.000010 \
  /var/log/mysql/binlog.000011 \
  | mysql -u root -p

步骤 4：验证恢复结果

sql

-- 检查目标表是否恢复到预期状态
SELECT COUNT(*) FROM important_table;
SELECT MAX(updated_at) FROM important_table;
-- 应该看到在 11:29:00 之前的数据，11:30:00 的操作不会出现

步骤 5：将恢复的数据导回生产环境

bash

# 导出恢复的表
mysqldump -u root -p recovery_db important_table > recovered_table.sql

# 导回生产环境
mysql -u root -p production_db < recovered_table.sql

30.6.4 更精确的 PITR（基于 position）

bash

# 先找到误操作的精确位置
mysqlbinlog --start-datetime="2024-01-15 11:28:00" \
  --stop-datetime="2024-01-15 11:31:00" \
  -v /var/log/mysql/binlog.000011 | grep -A 20 "DROP TABLE"

# 输出类似：
# # at 98765
# #240115 11:30:00 server id 1  end_log_pos 98999
# DROP TABLE `important_table`

# 精确恢复：停在前一个位置前
mysqlbinlog \
  --start-position=1234 \
  --stop-position=98765 \  # 刚好在 DROP TABLE 之前
  /var/log/mysql/binlog.000010 \
  /var/log/mysql/binlog.000011 \
  | mysql -u root -p

常见错误

错误 1：备份了但没有 --single-transaction

问题：在生产环境执行 mysqldump 时没有加 --single-transaction，
     导致备份期间表被锁，服务响应缓慢甚至超时。

解决：
  - InnoDB 表：必须使用 --single-transaction
  - 混合引擎：InnoDB 用 --single-transaction，MyISAM 用 --lock-tables
  - 最安全：在从库上执行备份，完全不影响主库

错误 2：恢复了错误的数据库

问题：使用 mysql db_name < backup.sql 恢复了备份，
     但 backup.sql 中包含 USE other_db 语句，覆盖了错误的数据库。

解决：
  - 恢复前检查备份文件头部：head -30 backup.sql
  - 使用 grep -i "^USE " backup.sql 查看文件中的 USE 语句
  - 在独立数据库中先恢复测试
  - 使用 sed 移除 USE 语句：
    sed '/^USE /d' backup.sql | mysql -u root -p target_db

错误 3：binlog 过期导致的 PITR 失败

问题：需要恢复到 7 天前的状态，但 binlog 只保留了最近 3 天。

解决：
  - 设置合理的 binlog 保留时间：binlog_expire_logs_seconds = 2592000（30天）
  - 确保备份频率高于 binlog 保留时间
  - 将 binlog 放置在独立于数据目录的存储上
  - 定期将 binlog 备份到远程存储

错误 4：LOAD DATA INFILE 权限问题

问题：执行 LOAD DATA INFILE 时报错：
     "The MySQL server is running with the --secure-file-priv option"

解决：
  1. 将文件放到 secure_file_priv 指定的目录
  2. 使用 LOAD DATA LOCAL INFILE（启用 local_infile）
  3. 在 my.cnf 中设置 secure_file_priv=""（不推荐，有安全风险）
  4. 确保用户有 FILE 权限

错误 5：恢复时外键约束失败

问题：恢复备份时，表的恢复顺序与外键依赖不匹配，导致错误。

解决：
  -- 恢复前暂时禁用外键检查
  SET foreign_key_checks = 0;
  source backup.sql;
  SET foreign_key_checks = 1;

本章练习

练习 1：全量备份与恢复

创建一个测试数据库（至少 5 个表，插入测试数据），然后：

使用 mysqldump 进行全量备份（包含所有参数）
记录备份文件大小
模拟数据灾难：删除数据库中的所有表
从备份文件恢复数据
验证恢复结果（行数一致性检查）
对比直接 COPY 数据文件的冷备份方式

练习 2：基于条件的部分备份

仅备份表结构（--no-data）
仅备份最近 30 天的订单数据（--where）
备份时排除某些大表
将备份文件压缩并分割为 100MB 的小文件
比较不同备份方式的文件大小

练习 3：Point-in-Time Recovery 模拟

创建测试表并插入初始数据（记录时间 T0）
执行全量备份
插入更多数据（记录时间 T1）
删除某些数据（记录时间 T2，模拟误操作）
再插入正常数据（记录时间 T3）
使用 PITR 恢复到 T2 之前的状态
验证：T2 的删除操作被撤销，但 T3 的数据丢失（符合预期）

练习 4：SELECT INTO OUTFILE 和 LOAD DATA INFILE

创建一个包含 10000 行数据的测试表
使用 SELECT INTO OUTFILE 导出为 CSV
使用 TRUNCATE 清空表
使用 LOAD DATA INFILE 重新导入
对比 LOAD DATA INFILE 和逐行 INSERT 的性能差异
测试 LOCAL 和非 LOCAL 方式的区别

练习 5：备份策略设计

为一个电商系统设计完整的备份策略，要求：

数据总量 500GB，日增量 5GB
要求 RPO（恢复点目标）< 1小时，RTO（恢复时间目标）< 4小时
给出备份方案（工具、频率、保留策略）
编写自动化备份脚本（含错误处理和告警）
编写恢复演练计划
计算存储成本

练习 6：灾难恢复综合演练

模拟以下场景并编写恢复计划：

场景A：单表误删 → 从备份恢复该表
场景B：整个数据库误删 → 全量恢复 + binlog PITR
场景C：服务器硬件故障 → 在新服务器上恢复
场景D：主从复制中断 → 重新建立复制
每个场景包含：恢复步骤、预估时间、验证方法、回滚方案

第30章 备份与恢复 ​

本章目标 ​

前置知识 ​

30.1 备份策略概述 ​

30.1.1 三种备份类型 ​

30.1.2 备份验证的重要性 ​

30.2 mysqldump 逻辑备份 ​

30.2.1 基本用法 ​

30.2.2 关键参数详解 ​

30.2.3 mysqldump 生产级完整命令 ​

30.2.4 mysqldump 的局限性 ​

30.3 数据恢复 ​

30.3.1 使用 mysql 客户端恢复 ​

30.3.2 使用 source 命令恢复 ​

30.3.3 部分恢复 ​

30.4 物理备份 ​

30.4.1 冷备份（停机备份） ​

30.4.2 Percona XtraBackup（热物理备份） ​

30.4.3 逻辑备份 vs 物理备份 对比 ​

30.5 数据导入导出 ​

30.5.1 SELECT INTO OUTFILE ​

30.5.2 LOAD DATA INFILE ​

30.5.3 mysqlimport 命令行工具 ​

30.6 二进制日志与 Point-in-Time Recovery (PITR) ​

30.6.1 二进制日志基础 ​

30.6.2 mysqlbinlog 工具 ​

30.6.3 完整的 PITR 流程 ​

30.6.4 更精确的 PITR（基于 position） ​

常见错误 ​

错误 1：备份了但没有 --single-transaction ​

错误 2：恢复了错误的数据库 ​

错误 3：binlog 过期导致的 PITR 失败 ​

错误 4：LOAD DATA INFILE 权限问题 ​

错误 5：恢复时外键约束失败 ​

本章练习 ​

练习 1：全量备份与恢复 ​

练习 2：基于条件的部分备份 ​

练习 3：Point-in-Time Recovery 模拟 ​

练习 4：SELECT INTO OUTFILE 和 LOAD DATA INFILE ​

练习 5：备份策略设计 ​

练习 6：灾难恢复综合演练 ​

第30章备份与恢复

本章目标

前置知识

30.1 备份策略概述

30.1.1 三种备份类型

30.1.2 备份验证的重要性

30.2 mysqldump 逻辑备份

30.2.1 基本用法

30.2.2 关键参数详解

30.2.3 mysqldump 生产级完整命令

30.2.4 mysqldump 的局限性

30.3 数据恢复

30.3.1 使用 mysql 客户端恢复

30.3.2 使用 source 命令恢复

30.3.3 部分恢复

30.4 物理备份

30.4.1 冷备份（停机备份）

30.4.2 Percona XtraBackup（热物理备份）

30.4.3 逻辑备份 vs 物理备份对比

30.5 数据导入导出

30.5.1 SELECT INTO OUTFILE

30.5.2 LOAD DATA INFILE

30.5.3 mysqlimport 命令行工具

30.6 二进制日志与 Point-in-Time Recovery (PITR)

30.6.1 二进制日志基础

30.6.2 mysqlbinlog 工具

30.6.3 完整的 PITR 流程

30.6.4 更精确的 PITR（基于 position）

常见错误

错误 1：备份了但没有 --single-transaction

错误 2：恢复了错误的数据库

错误 3：binlog 过期导致的 PITR 失败

错误 4：LOAD DATA INFILE 权限问题

错误 5：恢复时外键约束失败

本章练习

练习 1：全量备份与恢复

练习 2：基于条件的部分备份

练习 3：Point-in-Time Recovery 模拟

练习 4：SELECT INTO OUTFILE 和 LOAD DATA INFILE

练习 5：备份策略设计

练习 6：灾难恢复综合演练