新闻资讯

MySQL数据恢复的九个方法

发布时间:2020-11-18 浏览次数:10

在 DBA中,所有做 DBA的朋友都会遇到 MySQL数据损坏或丢失的问题,例如,忘记添加 where条件的更新、删除语句,或者由于 MySQL服务器异常宕机而损坏数据文件等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上开源。


原理主要是因为修改前后Update和Delete语句的所有状态都记录在binlog中(如下图所示)。通过解析和处理binlog,可以得到原始的SQL、回滚SQL、INSERT语句等。,并恢复“更新和删除”的错误操作。

1


工具四: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 


2

3、使用vim打开要恢复的ibd文件,16进制查看


3


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和截断操作无能为力。主要原理是,在Linux文件系统中,删除文件只删除文件系统的inode信息,物理文件还在磁盘上,所以误删除的文件可以通过这个工具恢复正常。当然前提是物理文件不被覆盖。类似的工具有ext3grep、debugfs等。所以就不再描述了。


工具八:Percona Data Recovery Tool for InnoDB

该工具是Percona公司开发的InnoDB数据恢复工具,目前已经停止开发,但仍然可用。通过直接从原始数据文件(ibd)中提取表的行记录,我们可以从损坏的表中恢复数据。要完成这种恢复,前提是要知道要恢复的表结构。针对innodb的Percona数据恢复工具直接读取InnoDB的物理页面,根据我们给出的表定义将数据恢复成类似csv的文件。恢复的数据可能包含正确或不正确的行记录,获得的数据杂乱无章,需要进一步处理后才能导入数据库。这种方式是没有出路的,只能这样做。希望大家不要用这个工具。


以上为本人在运维MySQL过程中总结的数据恢复经验,希望能给大家带来帮助,谢谢!


上一篇: 没有了

下一篇: 运维数据库的作用和特点有哪些?

产品试用 产品试用
400-820-6580 免费电话