1. MySQL数据恢复实战从删库到跑路的救赎之路刚接手数据库管理那会儿我最怕听到开发说不小心执行了DELETE不带WHERE。直到经历过三次完整的数据恢复实战才真正理解MySQL数据恢复不是玄学而是一套有章可循的急救方案。今天分享的这套方法曾帮我们从误删30万用户数据的事故中全身而退也适用于表损坏、磁盘故障等常见灾难场景。2. 数据恢复前的必要准备2.1 识别灾难类型MySQL数据丢失通常分为三类逻辑删除误执行DELETE/UPDATE/DROP语句物理损坏ibd文件损坏、磁盘故障系统级故障实例崩溃、主从切换异常上周我们遇到的典型案例是运维同学用pt-online-schema-change改表结构时网络中断导致表空间文件损坏。这种情况就需要物理恢复方案。2.2 必须立即执行的止损操作发现数据异常后的黄金30分钟立即停止应用连接建议在负载均衡层操作对当前实例做内存快照gcore -o /tmp/mysqldump $(pidof mysqld)备份当前所有日志文件包括binlog/relaylog/errorlog用只读模式挂载数据目录mount -o remount,ro /var/lib/mysql重要提示千万不要尝试重启MySQL服务这可能导致InnoDB执行自动恢复时二次损坏数据。3. 逻辑删除恢复方案3.1 使用binlog回滚当明确知道误操作时间点时比如开发说10:15执行了误删# 先解析binlog确认位置点 mysqlbinlog --start-datetime2023-08-20 10:14:00 \ --stop-datetime2023-08-20 10:16:00 \ /var/lib/mysql/mysql-bin.000123 /tmp/bad_query.sql # 提取反向SQL语句 awk /### DELETE FROM users/{flag1} flag{print} /# at/{flag0} /tmp/bad_query.sql | sed s/### DELETE FROM/INSERT INTO/; s/### WHERE/VALUES(/; s/### 1/)/; s/### 2/,/g /tmp/recovery.sql3.2 全量备份增量恢复我们的标准恢复流程适用于无binlog场景从最近的xtrabackup全量备份恢复基础数据用mysqlbinlog应用后续所有binlog在测试环境验证数据一致性# 恢复基础备份 innobackupex --copy-back /backups/full_20230801 chown -R mysql:mysql /var/lib/mysql # 应用增量日志 mysqlbinlog --start-position107 \ /var/lib/mysql/mysql-bin.000123 | mysql -u recovery -p4. 物理损坏恢复方案4.1 InnoDB表空间修复当遇到Tablespace is missing错误时创建同名空表获取表结构丢弃损坏的表空间导入原始ibd文件-- 步骤1获取表结构 CREATE TABLE users_bak LIKE users; -- 步骤2解除关联 ALTER TABLE users DISCARD TABLESPACE; -- 步骤3替换文件 cp /backups/users.ibd /var/lib/mysql/dbname/ chown mysql:mysql /var/lib/mysql/dbname/users.ibd -- 步骤4重新关联 ALTER TABLE users IMPORT TABLESPACE;4.2 使用undrop-for-innodb工具对于严重损坏的情况推荐使用这个神器git clone https://github.com/twindb/undrop-for-innodb.git cd undrop-for-innodb make # 提取表结构 ./stream_parser -f /var/lib/mysql/ibdata1 # 恢复特定表数据 ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page \ -t dictionary/SYS_TABLES.sql recovered_data.sql5. 预防胜于治疗我们的数据安全方案5.1 备份策略优化当前生产环境采用的备份矩阵备份类型频率保留周期存储位置验证方式XtraBackup全量每日7天异地SSD每周restore测试Binlog增量实时30天对象存储每月时间点恢复演练逻辑备份每周4周磁带库抽样数据比对5.2 自动化监控体系我们在Zabbix中配置的关键指标告警数据库写流量突降可能发生只读挂载大事务检测超过5000行的DML操作备份文件校验失败磁盘剩余空间不足24小时用量6. 血泪教训我们踩过的坑6.1 字符集导致的恢复失败某次用binlog恢复时发现中文全部变成问号。原因是备份时用了--default-character-setlatin1而实际数据是utf8mb4。现在我们的恢复命令永远带这个参数mysqlbinlog --default-character-setutf8mb4 [...]6.2 大表恢复的OOM问题恢复200GB的订单表时因为默认配置导致OOM崩溃。现在对于大表恢复必调参数[mysqld] bulk_insert_buffer_size256M max_allowed_packet1G innodb_buffer_pool_size12G7. 终极救命方案延迟复制从库我们在核心数据库配置的延迟复制架构CHANGE MASTER TO MASTER_DELAY 86400; -- 24小时延迟当主库发生误操作时只需停止从库SQL线程解析relaylog找到正确位置将从库提升为新主库这套方案去年在电商大促期间成功拦截了误删促销配置表的操作成为我们的后悔药方案。