第17章：数据恢复实战

MySQL教程 / 备份与恢复

第17章：数据恢复实战

数据恢复是DBA的核心技能，关键时刻能救命

17.1 数据恢复概述

17.1.1 常见的数据丢失场景

人为误操作（最常见）：

❌ 误删数据：DELETE FROM users WHERE ...
❌ 误删表：DROP TABLE users
❌ 误删库：DROP DATABASE mydb
❌ 误更新：UPDATE users SET password = '123456' （忘记WHERE）
❌ 误TRUNCATE：TRUNCATE TABLE orders

系统故障：

❌ 硬件故障（磁盘损坏）
❌ 软件Bug
❌ 主从同步错误

恶意攻击：

❌ SQL注入
❌ 勒索病毒
❌ 删库跑路

17.1.2 恢复目标

RTO（Recovery Time Objective）：

恢复时间目标
系统可以容忍的最长停机时间
例如：RTO = 1小时

RPO（Recovery Point Objective）：

恢复点目标
系统可以容忍的最大数据丢失量
例如：RPO = 5分钟

恢复策略：

RTO越小，成本越高
RPO越小，备份频率越高
需要在成本和需求之间平衡

17.2 基于mysqldump的恢复

17.2.1 完整恢复

# 场景：数据库完全损坏，需要完整恢复

# 1. 停止应用访问数据库

# 2. 删除损坏的数据库
mysql -u root -p -e "DROP DATABASE IF EXISTS mydb"

# 3. 创建新数据库
mysql -u root -p -e "CREATE DATABASE mydb DEFAULT CHARSET utf8mb4"

# 4. 恢复备份
mysql -u root -p mydb < /backup/mydb_20241110.sql

# 5. 验证数据
mysql -u root -p mydb -e "SELECT COUNT(*) FROM users"

# 6. 恢复应用访问

17.2.2 恢复单个表

# 场景：误删除了users表

# 方法1：从完整备份中提取单表
# 1. 从备份文件中提取建表语句和数据
sed -n '/CREATE TABLE.*users/,/UNLOCK TABLES/p' backup.sql > users_only.sql

# 2. 恢复单表
mysql -u root -p mydb < users_only.sql

# 方法2：恢复到临时数据库，再导出
# 1. 恢复到临时库
mysql -u root -p -e "CREATE DATABASE temp_restore"
mysql -u root -p temp_restore < backup.sql

# 2. 导出单表
mysqldump -u root -p temp_restore users > users_only.sql

# 3. 恢复到原库
mysql -u root -p mydb < users_only.sql

# 4. 删除临时库
mysql -u root -p -e "DROP DATABASE temp_restore"

17.2.3 恢复部分数据

# 场景：只需要恢复某些记录

# 1. 恢复到临时库
mysql -u root -p -e "CREATE DATABASE temp_restore"
mysql -u root -p temp_restore < backup.sql

# 2. 导出需要的数据
mysqldump -u root -p temp_restore users \
  --where="id BETWEEN 1000 AND 2000" > partial_users.sql

# 3. 恢复到原库
mysql -u root -p mydb < partial_users.sql

# 4. 清理
mysql -u root -p -e "DROP DATABASE temp_restore"

17.3 基于binlog的时间点恢复 ⭐⭐⭐⭐⭐

17.3.1 binlog恢复原理

完整备份 + binlog = 任意时间点恢复

时间线：
[全量备份]----[binlog]----[误操作]----[现在]
  2:00        2:00-10:00    10:00      10:30

恢复步骤：
1. 恢复全量备份（2:00的数据）
2. 应用binlog（2:00-9:59的变更）
3. 跳过误操作（10:00的DELETE）
4. 继续应用binlog（10:01-10:30的变更）

17.3.2 基于时间点恢复

# 场景：10:00误删了数据，需要恢复到9:59

# 1. 停止MySQL（防止新的写入）
systemctl stop mysqld

# 2. 恢复全量备份
mysql -u root -p mydb < /backup/mydb_20241110_0200.sql

# 3. 查看备份时的binlog位置
# 从备份文件中查找：
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=154;
grep "CHANGE MASTER" /backup/mydb_20241110_0200.sql

# 4. 应用binlog（从备份点到误操作前）
mysqlbinlog --start-position=154 \
  --stop-datetime="2024-11-10 09:59:59" \
  /var/lib/mysql/mysql-bin.000010 | mysql -u root -p mydb

# 5. 跳过误操作，继续应用后续binlog
mysqlbinlog --start-datetime="2024-11-10 10:01:00" \
  /var/lib/mysql/mysql-bin.000010 \
  /var/lib/mysql/mysql-bin.000011 | mysql -u root -p mydb

# 6. 启动MySQL
systemctl start mysqld

# 7. 验证数据
mysql -u root -p mydb -e "SELECT COUNT(*) FROM users"

17.3.3 基于位置点恢复

# 场景：知道误操作的binlog位置

# 1. 查找误操作的位置
mysqlbinlog -vv /var/lib/mysql/mysql-bin.000010 | grep -A 10 "DELETE FROM users"

# 输出示例：
# #241110 10:00:00 server id 1  end_log_pos 1234
# DELETE FROM users WHERE id = 100

# 2. 恢复全量备份
mysql -u root -p mydb < /backup/mydb_20241110_0200.sql

# 3. 应用binlog到误操作前（position 1233）
mysqlbinlog --start-position=154 \
  --stop-position=1233 \
  /var/lib/mysql/mysql-bin.000010 | mysql -u root -p mydb

# 4. 跳过误操作，继续应用后续binlog（从position 1500开始）
mysqlbinlog --start-position=1500 \
  /var/lib/mysql/mysql-bin.000010 | mysql -u root -p mydb

17.3.4 恢复误删除的数据

# 场景：误执行了 DELETE FROM users WHERE city = '北京'

# 方法1：从binlog中提取被删除的数据

# 1. 找到DELETE语句的binlog位置
mysqlbinlog -vv --base64-output=DECODE-ROWS \
  /var/lib/mysql/mysql-bin.000010 | grep -B 5 "DELETE FROM users"

# 2. 提取DELETE语句前的数据（ROW格式的binlog）
# binlog中记录了被删除行的完整数据
# 可以手动构造INSERT语句恢复

# 方法2：使用binlog2sql工具（推荐）
# 安装binlog2sql
git clone https://github.com/danfengcao/binlog2sql.git
cd binlog2sql

# 生成回滚SQL
python binlog2sql.py \
  -h127.0.0.1 -P3306 -uroot -p'password' \
  --start-file='mysql-bin.000010' \
  --start-datetime='2024-11-10 09:00:00' \
  --stop-datetime='2024-11-10 11:00:00' \
  -d mydb -t users \
  --flashback > rollback.sql

# 执行回滚
mysql -u root -p mydb < rollback.sql

17.4 基于XtraBackup的恢复

17.4.1 全量恢复

# 场景：服务器故障，需要完整恢复

# 1. 准备备份
xtrabackup --prepare --target-dir=/backup/full_20241110

# 2. 停止MySQL
systemctl stop mysqld

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

# 4. 恢复数据
xtrabackup --copy-back --target-dir=/backup/full_20241110

# 5. 修改权限
chown -R mysql:mysql /var/lib/mysql

# 6. 启动MySQL
systemctl start mysqld

# 7. 验证
mysql -u root -p -e "SHOW DATABASES"

17.4.2 增量恢复

# 场景：恢复全量+增量备份

# 1. 准备全量备份（不回滚）
xtrabackup --prepare --apply-log-only \
  --target-dir=/backup/full

# 2. 应用第一次增量
xtrabackup --prepare --apply-log-only \
  --target-dir=/backup/full \
  --incremental-dir=/backup/inc1

# 3. 应用第二次增量（最后一次）
xtrabackup --prepare \
  --target-dir=/backup/full \
  --incremental-dir=/backup/inc2

# 4. 停止MySQL并恢复
systemctl stop mysqld
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

17.4.3 XtraBackup + binlog恢复

# 场景：恢复到最新状态

# 1. 恢复XtraBackup备份
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql

# 2. 查看备份时的binlog位置
cat /backup/full/xtrabackup_binlog_info
# 输出：mysql-bin.000015  12345

# 3. 应用binlog（从备份点到现在）
mysqlbinlog --start-position=12345 \
  /var/lib/mysql/mysql-bin.000015 \
  /var/lib/mysql/mysql-bin.000016 | mysql -u root -p

# 4. 启动MySQL
systemctl start mysqld

17.5 实战案例

17.5.1 案例1：误删除表

# 场景：10:30误执行了 DROP TABLE orders

# 恢复步骤：

# 1. 立即停止应用，防止新数据写入

# 2. 确认最新的全量备份
ls -lh /backup/mysql/
# full_20241110_0200.sql.gz

# 3. 恢复全量备份到临时库
mysql -u root -p -e "CREATE DATABASE temp_restore"
gunzip < /backup/mysql/full_20241110_0200.sql.gz | \
  mysql -u root -p temp_restore

# 4. 从备份中提取orders表结构
mysqldump -u root -p temp_restore orders \
  --no-data > orders_schema.sql

# 5. 在原库中重建表
mysql -u root -p mydb < orders_schema.sql

# 6. 应用binlog恢复数据（从备份点到DROP TABLE前）
# 从备份文件查找binlog位置
grep "CHANGE MASTER" /backup/mysql/full_20241110_0200.sql.gz

# 应用binlog
mysqlbinlog --start-position=154 \
  --stop-datetime="2024-11-10 10:29:59" \
  --database=mydb \
  /var/lib/mysql/mysql-bin.* | mysql -u root -p mydb

# 7. 继续应用DROP TABLE之后的binlog
mysqlbinlog --start-datetime="2024-11-10 10:31:00" \
  --database=mydb \
  /var/lib/mysql/mysql-bin.* | mysql -u root -p mydb

# 8. 验证数据
mysql -u root -p mydb -e "SELECT COUNT(*) FROM orders"

# 9. 清理临时库
mysql -u root -p -e "DROP DATABASE temp_restore"

# 10. 恢复应用访问

17.5.2 案例2：误更新数据

# 场景：11:00误执行了 UPDATE users SET password = '123456'
# 忘记加WHERE条件，所有用户密码都被改了

# 恢复步骤：

# 1. 立即停止应用

# 2. 使用binlog2sql生成回滚SQL
python binlog2sql.py \
  -h127.0.0.1 -P3306 -uroot -p'password' \
  --start-file='mysql-bin.000020' \
  --start-datetime='2024-11-10 10:59:00' \
  --stop-datetime='2024-11-10 11:01:00' \
  -d mydb -t users \
  --flashback > rollback_users.sql

# 3. 查看回滚SQL
head -20 rollback_users.sql
# 会生成UPDATE语句，将password改回原值

# 4. 执行回滚
mysql -u root -p mydb < rollback_users.sql

# 5. 验证
mysql -u root -p mydb -e "SELECT id, username, password FROM users LIMIT 10"

# 6. 恢复应用

17.5.3 案例3：误删除数据库

# 场景：12:00误执行了 DROP DATABASE mydb

# 恢复步骤：

# 1. 立即停止MySQL（防止binlog被覆盖）
systemctl stop mysqld

# 2. 备份当前的binlog
cp -r /var/lib/mysql/mysql-bin.* /backup/binlog_emergency/

# 3. 启动MySQL
systemctl start mysqld

# 4. 重建数据库
mysql -u root -p -e "CREATE DATABASE mydb DEFAULT CHARSET utf8mb4"

# 5. 恢复最新的全量备份
gunzip < /backup/mysql/full_20241110_0200.sql.gz | \
  mysql -u root -p mydb

# 6. 应用binlog（从备份点到DROP DATABASE前）
mysqlbinlog --start-position=154 \
  --stop-datetime="2024-11-10 11:59:59" \
  --database=mydb \
  /backup/binlog_emergency/mysql-bin.* | mysql -u root -p mydb

# 7. 继续应用DROP DATABASE之后的binlog
# 注意：需要过滤掉DROP DATABASE语句
mysqlbinlog --start-datetime="2024-11-10 12:01:00" \
  --database=mydb \
  /backup/binlog_emergency/mysql-bin.* | \
  grep -v "DROP DATABASE" | mysql -u root -p mydb

# 8. 验证数据完整性
mysql -u root -p mydb -e "SHOW TABLES"
mysql -u root -p mydb -e "SELECT COUNT(*) FROM users"

# 9. 恢复应用

17.5.4 案例4：硬盘损坏

# 场景：数据盘完全损坏，需要在新服务器上恢复

# 恢复步骤：

# 1. 准备新服务器，安装MySQL

# 2. 从异地备份获取最新备份
# 假设使用XtraBackup备份
scp -r backup_server:/backup/mysql/full_20241110 /backup/

# 3. 准备备份
xtrabackup --prepare --target-dir=/backup/full_20241110

# 4. 恢复数据
systemctl stop mysqld
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full_20241110
chown -R mysql:mysql /var/lib/mysql

# 5. 如果有binlog备份，继续应用
# 从备份服务器获取binlog
scp backup_server:/backup/binlog/* /backup/binlog/

# 查看XtraBackup备份的binlog位置
cat /backup/full_20241110/xtrabackup_binlog_info

# 应用binlog
mysqlbinlog --start-position=12345 \
  /backup/binlog/mysql-bin.* | mysql -u root -p

# 6. 启动MySQL
systemctl start mysqld

# 7. 验证数据
mysql -u root -p -e "SHOW DATABASES"

# 8. 更新应用配置，指向新服务器

# 9. 恢复业务

17.5.5 案例5：主从同步错误导致数据不一致

# 场景：从库执行了写操作，导致主从数据不一致

# 恢复步骤：

# 1. 停止从库的复制
mysql -u root -p -e "STOP SLAVE"

# 2. 在从库上重新搭建（使用主库备份）

# 方法1：使用mysqldump
# 在主库上备份
mysqldump -u root -p \
  --all-databases \
  --single-transaction \
  --master-data=2 \
  --flush-logs > master_backup.sql

# 传输到从库
scp master_backup.sql slave_server:/backup/

# 在从库上恢复
mysql -u root -p < /backup/master_backup.sql

# 方法2：使用XtraBackup（推荐，大数据库）
# 在主库上备份
xtrabackup --backup \
  --user=root \
  --password=your_password \
  --target-dir=/backup/master_full

# 传输到从库
rsync -avz /backup/master_full slave_server:/backup/

# 在从库上恢复
xtrabackup --prepare --target-dir=/backup/master_full
systemctl stop mysqld
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/master_full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

# 3. 重新配置主从复制
# 从备份中获取binlog位置
grep "CHANGE MASTER" /backup/master_backup.sql
# 或
cat /backup/master_full/xtrabackup_binlog_info

# 配置从库
mysql -u root -p <<EOF
CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_password',
  MASTER_LOG_FILE='mysql-bin.000020',
  MASTER_LOG_POS=12345;
START SLAVE;
EOF

# 4. 检查主从状态
mysql -u root -p -e "SHOW SLAVE STATUS\G"

# 5. 设置从库为只读（防止再次写入）
mysql -u root -p -e "SET GLOBAL read_only = 1"

17.6 数据恢复工具

17.6.1 binlog2sql

# binlog2sql：解析binlog，生成回滚SQL

# 安装
git clone https://github.com/danfengcao/binlog2sql.git
cd binlog2sql
pip install -r requirements.txt

# 查看binlog内容
python binlog2sql.py \
  -h127.0.0.1 -P3306 -uroot -p'password' \
  --start-file='mysql-bin.000010' \
  --start-datetime='2024-11-10 10:00:00' \
  --stop-datetime='2024-11-10 11:00:00' \
  -d mydb -t users

# 生成回滚SQL
python binlog2sql.py \
  -h127.0.0.1 -P3306 -uroot -p'password' \
  --start-file='mysql-bin.000010' \
  --start-datetime='2024-11-10 10:00:00' \
  --stop-datetime='2024-11-10 11:00:00' \
  -d mydb -t users \
  --flashback > rollback.sql

# 只查看DELETE语句
python binlog2sql.py \
  -h127.0.0.1 -P3306 -uroot -p'password' \
  --start-file='mysql-bin.000010' \
  -d mydb -t users \
  --sql-type=DELETE

17.6.2 MyFlash

# MyFlash：美团开源的binlog回滚工具

# 安装
git clone https://github.com/Meituan-Dianping/MyFlash.git
cd MyFlash
gcc -o myflash myflash.c -lmysqlclient -lz -lm -lrt -ldl -lpthread

# 使用
./myflash --binlogFileNames=/var/lib/mysql/mysql-bin.000010 \
  --start-position=154 \
  --stop-position=1234 \
  --databaseNames=mydb \
  --tableNames=users \
  --outputDir=/tmp/flashback

17.6.3 mysqlbinlog

# mysqlbinlog：MySQL官方binlog解析工具

# 查看binlog内容
mysqlbinlog -vv /var/lib/mysql/mysql-bin.000010

# 按时间范围导出
mysqlbinlog --start-datetime="2024-11-10 10:00:00" \
  --stop-datetime="2024-11-10 11:00:00" \
  /var/lib/mysql/mysql-bin.000010 > binlog_extract.sql

# 按位置导出
mysqlbinlog --start-position=154 \
  --stop-position=1234 \
  /var/lib/mysql/mysql-bin.000010 > binlog_extract.sql

# 只查看指定数据库
mysqlbinlog --database=mydb \
  /var/lib/mysql/mysql-bin.000010

# 解码ROW格式（可读性更好）
mysqlbinlog -vv --base64-output=DECODE-ROWS \
  /var/lib/mysql/mysql-bin.000010

17.7 数据恢复最佳实践

17.7.1 预防措施

-- 1. 开启binlog
[mysqld]
log_bin = mysql-bin
binlog_format = ROW  -- ROW格式记录完整数据，便于恢复
expire_logs_days = 7

-- 2. 开启慢查询日志
slow_query_log = 1
long_query_time = 1

-- 3. 设置从库为只读
SET GLOBAL read_only = 1;
SET GLOBAL super_read_only = 1;

-- 4. 使用安全模式（防止误操作）
SET sql_safe_updates = 1;

-- 开启后，没有WHERE或LIMIT的UPDATE/DELETE会报错
UPDATE users SET password = '123456';  -- 报错
UPDATE users SET password = '123456' WHERE id = 1;  -- 正常

-- 5. 重要操作前先备份
mysqldump -u root -p mydb users > users_backup_before_update.sql

-- 6. 使用事务（可以回滚）
START TRANSACTION;
UPDATE users SET status = 0 WHERE city = '北京';
-- 检查影响的行数
SELECT ROW_COUNT();
-- 如果不对，回滚
ROLLBACK;
-- 如果正确，提交
COMMIT;

-- 7. 先SELECT再UPDATE/DELETE
SELECT * FROM users WHERE city = '北京';  -- 先查看
UPDATE users SET status = 0 WHERE city = '北京';  -- 再更新

17.7.2 恢复流程规范

1. 发现问题
   ↓
2. 立即停止应用（防止新数据写入）
   ↓
3. 评估影响范围
   ↓
4. 制定恢复方案
   ↓
5. 在测试环境验证
   ↓
6. 执行恢复
   ↓
7. 验证数据完整性
   ↓
8. 恢复应用访问
   ↓
9. 监控系统状态
   ↓
10. 编写事故报告

17.7.3 恢复检查清单

✅ 确认问题类型（误删、误更新、硬件故障等）
✅ 确认影响范围（哪些表、多少数据）
✅ 确认最新可用备份
✅ 确认binlog是否完整
✅ 停止应用访问
✅ 制定恢复方案
✅ 在测试环境验证
✅ 记录恢复步骤
✅ 执行恢复
✅ 验证数据完整性
✅ 验证数据一致性
✅ 恢复应用访问
✅ 监控系统
✅ 编写事故报告
✅ 总结经验教训

17.7.4 常见错误

# ❌ 错误1：没有立即停止应用
# 后果：新数据写入，恢复更复杂

# ❌ 错误2：直接在生产环境操作
# 后果：可能造成二次伤害

# ❌ 错误3：没有验证就恢复应用
# 后果：数据不完整，影响业务

# ❌ 错误4：binlog格式为STATEMENT
# 后果：无法准确恢复（ROW格式更好）

# ❌ 错误5：没有测试恢复流程
# 后果：紧急时手忙脚乱

# ❌ 错误6：恢复后没有分析原因
# 后果：同样的错误再次发生

17.8 本章总结

本章学习内容：

✅ 数据恢复概述（RTO、RPO）
✅ 基于mysqldump的恢复
✅ 基于binlog的时间点恢复 ⭐⭐⭐⭐⭐
✅ 基于XtraBackup的恢复
✅ 实战案例（误删表、误更新、误删库等）⭐⭐⭐⭐⭐
✅ 数据恢复工具（binlog2sql、MyFlash）
✅ 数据恢复最佳实践 ⭐⭐⭐⭐⭐

重点掌握：

完整备份 + binlog = 任意时间点恢复
误操作后立即停止应用
使用binlog2sql生成回滚SQL
恢复前先在测试环境验证
binlog格式使用ROW（便于恢复）
重要操作前先备份

恢复方法选择：

完整恢复：mysqldump或XtraBackup
时间点恢复：备份 + binlog
误操作恢复：binlog2sql回滚

恢复工具：

mysqlbinlog：官方工具
binlog2sql：生成回滚SQL（推荐）
MyFlash：美团开源工具

预防措施：

开启binlog（ROW格式）
定期备份
从库只读
使用sql_safe_updates
重要操作使用事务

面试重点：

如何进行时间点恢复
binlog的作用
误删数据如何恢复
RTO和RPO的区别
数据恢复的最佳实践

下一章预告： SQL优化实战

练习题

什么是RTO和RPO？
如何进行基于时间点的恢复？
误删除表如何恢复？
误更新数据如何恢复？
binlog2sql的作用是什么？
为什么binlog格式推荐使用ROW？
数据恢复前为什么要停止应用？
如何预防误操作？
sql_safe_updates的作用是什么？
编写一个误删除数据的恢复方案

继续学习： 第18章：SQL优化实战

第17章：数据恢复实战 - MySQL教程 - jackssybin 的个人博客

jackssybin 的个人博客

第17章：数据恢复实战

第17章：数据恢复实战

第17章：数据恢复实战

17.1 数据恢复概述

17.1.1 常见的数据丢失场景

17.1.2 恢复目标

17.2 基于mysqldump的恢复

17.2.1 完整恢复

17.2.2 恢复单个表

17.2.3 恢复部分数据

17.3 基于binlog的时间点恢复 ⭐⭐⭐⭐⭐

17.3.1 binlog恢复原理

17.3.2 基于时间点恢复

17.3.3 基于位置点恢复

17.3.4 恢复误删除的数据

17.4 基于XtraBackup的恢复

17.4.1 全量恢复

17.4.2 增量恢复

17.4.3 XtraBackup + binlog恢复

17.5 实战案例

17.5.1 案例1：误删除表

17.5.2 案例2：误更新数据

17.5.3 案例3：误删除数据库

17.5.4 案例4：硬盘损坏

17.5.5 案例5：主从同步错误导致数据不一致

17.6 数据恢复工具

17.6.1 binlog2sql

17.6.2 MyFlash

17.6.3 mysqlbinlog

17.7 数据恢复最佳实践

17.7.1 预防措施

17.7.2 恢复流程规范

17.7.3 恢复检查清单

17.7.4 常见错误

17.8 本章总结

练习题