开发者社区> 稀奇古怪> 正文

MySQL数据恢复的九把瑞士军刀

简介:
+关注继续查看

摘要: 作者介绍 李辉,新浪爱彩票运维负责人,常用网名:门牙没了。主导新浪爱彩票的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语句等,可以恢复UpdateDelete误操作。

 

20170426110757987.jpg

 

工具四:innodb_force_recovery

 

MySQL非正常重启或者磁盘故障等原因可能导致MySQL数据文件损坏,损坏后会导致MySQL server无法启动。如果也没有备份文件,可以使用这个选项强制InnoDB启动,阻止一些后台操作的运行,从而dump出数据库中的数据。

 

innodb_force_recovery可选的值为0-6,默认情况下的值为0,大的数字包含前面所有数字的影响。当设置参数值大于0后,可以对表进行select,create,drop操作,但insert,update或者delete这类操作是不允许的。

 

  1. SRV_FORCE_IGNORE_CORRUPT:忽略检查到的corrupt页

  2. SRV_FORCE_NO_BACKGROUND:阻止主线程的运行,如主线程需要执行full purge操作,会导致crash

  3. SRV_FORCE_NO_TRX_UNDO:不执行事务回滚操作

  4. SRV_FORCE_NO_IBUF_MERGE:不执行插入缓冲的合并操作

  5. SRV_FORCE_NO_UNDO_LOG_SCAN:不查看重做日志,InnoDB存储引擎会将未提交的事务视为已提交

  6. 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。

 

操作过程:

  1. 应用物理备份的log:innobackupex --apply-log

  2. 备份数据库中的ibd:cp test.ibd test.bak

  3. 丢弃数据库中的ibd:alter table test discard tablespace;

  4. 复制物理备份中的ibd到数据库目录:cp /bak/test.ibd /data/test/; chown mysql:mysql /data/test/test.ibd

  5. 导入ibd:alter table test import tablespace;

 

  • 有物理备份,但是数据库中表结构已经被drop。

 

这种情况有点复杂,因为表被drop后元数据中的space id和index id已经被删除。但space id和index id会留空,不会被新创建的table占用,给我们留下了恢复的机会。只需要重建表结构,然后在ibdata中还原该表的space id即可,还原过程需要percona recovery tool的协助。

 

操作过程:

  1. 应用物理备份的log:innobackupex --apply-log

  2. 数据库中重建表:create table test(id int);

  3. 关闭数据库

  4. 用物理备份中的ibd覆盖数据库中的ibd

  5. 使用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

  6. 使用percona recovery tool对ibdata做checksum:~/percona-data-recovery-tool-for-innodb-0.5/innochecksum -f /data/ibdata1

  7. 重复执行执行步骤6,直到没有任何输出为止

  8. 启动MySQL

 

工具六:手工修改ibd

 

这种方法适用于只有ibd文件和表结构了,frm和ibdata全部损坏的情况。其原理是在新数据库上创建表,然后修改待恢复的ibd的文件头,使之适应新表的space id和index id,从而读取出ibd中的数据。

 

操作过程:

1、新建数据库,创建需要恢复的数据库的表结构。

2、使用vim打开此表的ibd文件,16进制查看。

 [root@localhost test]# vim -b tmp.ibd

                          :%!xxd 

20170426110807117.jpg

 

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

20170426110815344.jpg

 

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文件。恢复后的数据可能包含正确的行记录,也可能包含不正确的行记录,并且拿到的数据比较乱,需要做进一步的处理才能导入到数据库中。这个办法是没有办法中的办法了,不得已而为之,希望大家都不会用到这个工具。

 

原文发布时间为:2017-04-26

本文来自云栖社区合作伙伴DBAplus

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
27987 0
穿越数据的变迁 - 如何使用阿里云Redis数据闪回进行按时间点的数据恢复
如何使用阿里云Redis数据闪回进行按时间点的数据恢复
2795 0
装多系统删除某个系统后,如何恢复ubuntu引导
在重装系统或者再装多个系统后可能会出现ubuntu的引导文件不存在的情况,windows系列的引导文件可以用winpe修复,但是ubuntu就不可以,虽然网上很多种修复ubuntu的引导文件 方式,但是bcd等方式比较麻烦,本人觉得还是输入代码的方式较为方便(纯属个人意见), 如果你 的  开机启动界面的ubuntu引导不见了,直接进入新安装的window系统中。
643 0
MySQL误操作数据恢复的简单实践(r11笔记第67天)
    前几天有个同事碰到了一个MySQL数据恢复的问题,他运行了一条update语句,结果忘记了加where条件,结果等反应过来已经晚了。我简单确认了下,是否存在备份,没有,是否开启了日志,没有。
1117 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
20106 0
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
23538 0
+关注
119
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载