MySQL数据恢复的九把瑞士军刀
作者介绍
李辉，新浪爱彩票运维负责人，常用网名：门牙没了。主导新浪爱彩票的MySQL运维工作。培训合伙人、资深讲师，中国科学院大学在读研究生（大数据方向），擅长大型项目的关系型数据库运维和管理，现在在数据库运维自动化方向研究。
做DBA的朋友可能都遇到过MySQL数据损坏或丢失的问题，比如忘加where条件的update、delete语句，或者MySQL服务器异常宕机导致数据文件损坏等。本文针对在日常运维中由于误操作、数据文件损坏、硬盘损坏、备份失效等情况导致的各种数据丢失或损坏的场景，提供了九种恢复方案，供大家参考。
注：高危操作请勿在没有测试的情况下，直接在生产环境使用。
工具一：完全备份+binlog
恢复数据最常见的做法，只要有这两样东西，无论是误操作还是数据库损坏等，都能恢复数据到指定的时间节点，能覆盖大多数的恢复场景，也是DBA手中最重要的资产。恢复方法比较简单这里就不过多赘述了。
工具二：业务逻辑反推恢复update误操作
这种方法适合做了误操作但停机会造成更大影响的场景，通过逻辑反推可以迅速恢复数据到正常状态。下面我们以用户充值表为例，来看看如何恢复误操作。
充值状态说明：0未充值，1已充值，2充值失败，3充值异常。
示例1：
某开发在处理用户充值故障时漏掉了用户id，导致大面积的用户充值状态被篡改。由于此表中有last_update_time字段，所以我们可以根据最后修改时间恢复这次的误操作。
正确的语句update t1 set status=1 where member_id=10001 and status=0;
误操作语句update t1 set status=1 where status=0;
反向执行即可恢复误操作update t1 set status=0 where status=1 and last_update_time=’2017-03-20 11:30:27’;
示例2：
某开发在处理用户充值状态时，漏掉了where条件，导致全表被更新。
正确的语句update t1 set status=1 where member_id=10001 and status=0;
误操作语句update t set status=1;
执行时丢失了where条件，此时就要根据其它表中记录的用户最后的充值status来进行恢复了，比如用户充值历史表，先从用户充值历史表中取得用户最后一次充值的记录，分析此次充值的status，恢复到用户充值表即可。这种恢复方法和业务逻辑密切相关。
从这里我们也可以看出此方法并不是很严谨，比较适合小规模的恢复。
工具三：MySQL flashback
最早的相关资料是在彭立勋的博客上，随后他提交给了MariaDB，网易等大厂在自己的分支中也实现了该功能。对于仍然在使用官方主流版本的同学来说，业内开源的mysqlbinlog_flashback和binlog2sql这两个闪回工具是个不错的选择，作者已经在Github上开源。
其原理主要是由于binlog中会记录Update和Delete语句在更改前后的所有状态（如下图），对binlog进行解析和处理即可得到原始SQL、回滚SQL、INSERT语句等，可以恢复Update和Delete误操作。
工具四：innodb_force_recovery
MySQL非正常重启或者磁盘故障等原因可能导致MySQL数据文件损坏，损坏后会导致MySQL server无法启动。如果也没有备份文件，可以使用这个选项强制InnoDB启动，阻止一些后台操作的运行，从而dump出数据库中的数据。
innodb_force_recovery可选的值为0-6，默认情况下的值为0，大的数字包含前面所有数字的影响。当设置参数值大于0后，可以对表进行select，create，drop操作，但insert，update或者delete这类操作是不允许的。
SRV_FORCE_IGNORE_CORRUPT:忽略检查到的corrupt页
SRV_FORCE_NO_BACKGROUND:阻止主线程的运行，如主线程需要执行full purge操作，会导致crash
SRV_FORCE_NO_TRX_UNDO:不执行事务回滚操作
SRV_FORCE_NO_IBUF_MERGE:不执行插入缓冲的合并操作
SRV_FORCE_NO_UNDO_LOG_SCAN:不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交
SRV_FORCE_NO_LOG_REDO:不执行前滚的操作。
[mysqld]中加入此参数，尝试启动MySQL，如果启动失败就逐步增加参数的值，直到启动为止，当然其数据一致性也会越来越差。数据库启动后，InnoDB类型的表只能读不能写，此时把表中的数据dump出来，或导入MyISAM表里面，即可恢复损坏的数据。
工具五：DISCARD、IMPORT TABLESPACE
这种方法适用于修复frm文件损坏，或者误操作、ibd损坏但是有物理备份的情况。修复数据要分两种情况讨论：
有物理备份，数据损坏后table没有recreate过
这种情况下恢复是比较简单的，物理备份中的ibd、数据库中ibd的space id和index id，都是和ibdata文件中的space id和index id一致的，所以可以直接拿物理备份中的ibd覆盖数据库中的ibd。
操作过程：
应用物理备份的log：innobackupex --apply-log
备份数据库中的ibd：cp test.ibd test.bak
丢弃数据库中的ibd：alter table test discard tablespace;
复制物理备份中的ibd到数据库目录：cp /bak/test.ibd /data/test/; chown mysql:mysql /data/test/test.ibd
导入ibd：alter table test import tablespace;
有物理备份，但是数据库中表结构已经被drop。
这种情况有点复杂，因为表被drop后元数据中的space id和index id已经被删除。但space id和index id会留空，不会被新创建的table占用，给我们留下了恢复的机会。只需要重建表结构，然后在ibdata中还原该表的space id即可，还原过程需要percona recovery tool的协助。
操作过程：
应用物理备份的log：innobackupex --apply-log
数据库中重建表：create table test(id int);
关闭数据库
用物理备份中的ibd覆盖数据库中的ibd
使用percona recovery tool修改ibdata：~/percona-data-recovery-tool-for-innodb-0.5/ibdconnect -o /data/ibdata1 -f /data/test/test.ibd -d test -t test
使用percona recovery tool对ibdata做checksum：~/percona-data-recovery-tool-for-innodb-0.5/innochecksum -f /data/ibdata1
重复执行执行步骤6，直到没有任何输出为止
启动MySQL
工具六：手工修改ibd
这种方法适用于只有ibd文件和表结构了，frm和ibdata全部损坏的情况。其原理是在新数据库上创建表，然后修改待恢复的ibd的文件头，使之适应新表的space id和index id，从而读取出ibd中的数据。
操作过程：
1、新建数据库，创建需要恢复的数据库的表结构。
2、使用vim打开此表的ibd文件，16进制查看。
[root@localhost test]# vim -b tmp.ibd
:%!xxd
3、使用vim打开要恢复的ibd文件，16进制查看
4、修改要恢复的ibd文件，将红方框中的值修改的和刚刚创建的新表的ibd文件一致。看到后面大段的0000没，我们只需要修改文件头就可以了。00000c0偏移量以后的不用修改。
[root@localhost test]# vim -b tmp.ibd
:%!xxd -r #一定要先执行这一步
:wq
5、把待恢复的ibd文件覆盖刚刚创建的新表的ibd文件。修改文件权限为MySQL用户。
6、重启MySQL，重启时加上参数innodb_force_recovery。
7、将数据dump出来，找回数据成功。
工具七：extundelete
这个工具是基于Linux的文件恢复工具，可以用来恢复误删除的表，对于DML和truncate操作无能为力。其主要原理是在Linux文件系统中，删除文件只是删除了文件系统的inode信息，物理文件仍然在磁盘上，通过此工具即可将误删除的文件恢复正常。当然前提是物理文件没有被覆盖。类似的工具还有ext3grep、debugfs等，不再赘述。
工具八：Percona Data Recovery Tool for InnoDB
这个工具是Percona公司开发的一款InnoDB数据恢复工具，目前已经停止开发，但是仍然可用。它通过在原始数据文件（ibd） 中直接提取表的行记录，实现我们从损坏的表恢复数据的目的。要完成这类恢复，前提是要知道待恢复的表结构。Percona Data Recovery Tool for InnoDB直接读取InnoDB的物理页，按照我们给出的表定义，把数据恢复成类csv文件。恢复后的数据可能包含正确的行记录，也可能包含不正确的行记录，并且拿到的数据比较乱，需要做进一步的处理才能导入到数据库中。这个办法是没有办法中的办法了，不得已而为之，希望大家都不会用到这个工具。
以上为本人在运维MySQL过程中总结的数据恢复经验，希望能给大家带来帮助，谢谢！
相关专题：
◆ MVP专栏 ◆
◆ 近期活动 ◆
Gdevops全球敏捷运维峰会成都站
峰会官网：www.gdevops.com