MySQL教程 / 第 170 节

第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优化实战