MySQL数据恢复的九把瑞士军刀
副标题[/!--empirenews.page--]
《MySQL数据恢复的九把瑞士军刀》要点: 作者介绍 李辉,新浪爱彩票运维负责人,常用网名:门牙没了.主导新浪爱彩票的MySQL运维工作.培训合伙人、资深讲师,中国科学院大学在读研究生(大数据方向),擅长大型项目的关系型数据库运维和管理,现在在数据库运维自动化方向研究. 做DBA的朋友可能都遇到过MySQL数据损坏或丢失的问题,比如忘加where条件的update、delete语句,或者MySQL服务器异常宕机导致数据文件损坏等.本文针对在日常运维中由于误操作、数据文件损坏、硬盘损坏、备份失效等情况导致的各种数据丢失或损坏的场景,提供了九种恢复方案,供大家参考. 注:高危操作请勿在没有测试的情况下,直接在生产环境使用. 工具一:完全备份+binlog恢复数据最常见的做法,只要有这两样东西,无论是误操作还是数据库损坏等,都能恢复数据到指定的时间节点,能覆盖大多数的恢复场景,也是DBA手中最重要的资产.恢复方法比较简单这里就不过多赘述了. 工具二:业务逻辑反推恢复update误操作这种方法适合做了误操作但停机会造成更大影响的场景,通过逻辑反推可以迅速恢复数据到正常状态.下面我们以用户充值表为例,来看看如何恢复误操作. 充值状态说明:0未充值,1已充值,2充值失败,3充值异常. 示例1: 某开发在处理用户充值故障时漏掉了用户id,导致大面积的用户充值状态被篡改.由于此表中有last_update_time字段,所以我们可以根据最后修改时间恢复这次的误操作.
示例2: 某开发在处理用户充值状态时,漏掉了where条件,导致全表被更新.
执行时丢失了where条件,此时就要根据其它表中记录的用户最后的充值status来进行恢复了,比如用户充值历史表,先从用户充值历史表中取得用户最后一次充值的记录,分析此次充值的status,恢复到用户充值表即可.这种恢复方法和业务逻辑密切相关. 从这里我们也可以看出此方法并不是很严谨,比较适合小规模的恢复. 工具三:MySQL flashback最早的相关资料是在彭立勋的博客上,随后他提交给了MariaDB,网易等大厂在自己的分支中也实现了该功能.对于仍然在使用官方主流版本的同学来说,业内开源的mysqlbinlog_flashback和binlog2sql这两个闪回工具是个不错的选择,作者已经在Github上开源. 其原理主要是由于binlog中会记录Update和Delete语句在更改前后的所有状态(如下图),对binlog进行解析和处理即可得到原始SQL、回滚SQL、INSERT语句等,可以恢复Update和Delete误操作. 工具四:innodb_force_recoveryMySQL非正常重启或者磁盘故障等原因可能导致MySQL数据文件损坏,损坏后会导致MySQL server无法启动.如果也没有备份文件,可以使用这个选项强制InnoDB启动,阻止一些后台操作的运行,从而dump出数据库中的数据. innodb_force_recovery可选的值为0-6,默认情况下的值为0,大的数字包含前面所有数字的影响.当设置参数值大于0后,可以对表进行select,create,drop操作,但insert,update或者delete这类操作是不允许的.
[mysqld]中加入此参数,尝试启动MySQL,如果启动失败就逐步增加参数的值,直到启动为止,当然其数据一致性也会越来越差.数据库启动后,InnoDB类型的表只能读不能写,此时把表中的数据dump出来,或导入MyISAM表里面,即可恢复损坏的数据. 工具五:DISCARD、IMPORT TABLESPACE这种方法适用于修复frm文件损坏,或者误操作、ibd损坏但是有物理备份的情况.修复数据要分两种情况讨论:
这种情况下恢复是比较简单的,物理备份中的ibd、数据库中ibd的space id和index id,都是和ibdata文件中的space id和index id一致的,所以可以直接拿物理备份中的ibd覆盖数据库中的ibd. 操作过程:
这种情况有点复杂,因为表被drop后元数据中的space id和index id已经被删除.但space id和index id会留空,不会被新创建的table占用,给我们留下了恢复的机会.只需要重建表结构,然后在ibdata中还原该表的space id即可,还原过程需要percona recovery tool的协助. 操作过程:
工具六:手工修改ibd这种方法适用于只有ibd文件和表结构了,frm和ibdata全部损坏的情况.其原理是在新数据库上创建表,然后修改待恢复的ibd的文件头,使之适应新表的space id和index id,从而读取出ibd中的数据. 操作过程: 1、新建数据库,创建需要恢复的数据库的表结构. 2、使用vim打开此表的ibd文件,16进制查看. 3、使用vim打开要恢复的ibd文件,16进制查看 4、修改要恢复的ibd文件,将红方框中的值修改的和刚刚创建的新表的ibd文件一致.看到后面大段的0000没,我们只需要修改文件头就可以了.00000c0偏移量以后的不用修改. 5、把待恢复的ibd文件覆盖刚刚创建的新表的ibd文件.修改文件权限为MySQL用户. 6、重启MySQL,重启时加上参数innodb_force_recovery. 7、将数据dump出来,找回数据成功. 工具七:extundelete(编辑:源码门户网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |