400-1100-266

9:00-24:00（工作日）
主页 > 知识库 > mysql5.7.33误删除ibdata文件找回数据的方法

mysql5.7.33误删除ibdata文件找回数据的方法
热门标签：铁路电话系统 Linux服务器 百度竞价排名 AI电销 网站排名优化 服务外包 呼叫中心市场需求 地方门户网站

一、场景说明：

很多时候因为 MySQL 数据库不能启动而造成数据无法访问，但应用的数据通常没有丢失，只是系统表空间等其它文件损坏了，或者遇到 MySQL 的 bug。

这个时候如果没有备份，很多人就以为数据丢失了，但实际上大部分时候数据还是有救的。
对于 MyISAM 引擎的表空间，直接把对应的数据文件拷贝到一个新的数据库就行了，数据就可以恢复了。
对于 InnoDB 引擎的数据库表空间可以采用传输表空间的方式把数据救回来.
前提是MySQL开启了参数 innodb_file_per_table = 1 独立表空间文件

当MySQL的数据表空间文件ibdata文件损坏或者是被无修改和删除，导致MySQL服务重启失败。同时MySQL的数据又没有及时备份，此时如何尽可能多的找回MySQL数据呢？？

二、案例演示：

2.1、确认MySQL故障前，库中的每张表有多少记录

for n in `mysql -e "use db_bbs;show tables;"|sed '1d'`;do echo $n; mysql -e "use db_bbs;select count(*) from $n;";done >test.txt

确认MySQL故障前，库中一共有多少张表：

一共是39张表：

[root@10-10-127-11 ~]# mysql -e "use db_bbs;show tables;"|sed '1d'|wc -l
39

2.2、模拟删除ibdata故障：

删除ibdata文件（生产环境禁止这么干）

innodb_force_recovery =6 利用MySQL的强制启动参数来启动此时的MySQL服务，但是已经是无济于事。由于是数据表空间文件ibdata文件被删除了.所以启动不了此时的MySQL服务。
由于此MySQL采用的innodb引擎。而且开启了独立表空间参数 innodb_file_per_table = 1 。所以此时可以采用采用传输表空间的方式把数据救回来。

2.3、找回数据的办法:

首先来创建已经丢失的表结构：

先要在故障的MySQL服务器上 安装 mysql-utilities。

yum -y install mysql-utilities

使用 mysqlfrm 从 .frm 文件里面找回建表语句:

分析一个 .frm 文件生成建表的语句

mysqlfrm --diagnostic 
[root@test02 db_bbs]# mysqlfrm --diagnostic /data/mysql/data/db_bbs/t_admin.frm |grep -v "^#"
CREATE TABLE `db_bbs`.`t_admin` (
 `f_id` int(4) NOT NULL AUTO_INCREMENT, 
 `f_type` tinyint(1) NOT NULL, 
 `f_username` varchar(80) NOT NULL, 
 `f_password` varchar(80) NOT NULL, 
 `f_nick_name` varchar(80) NOT NULL, 
 `f_real_name` varchar(80) NOT NULL, 
 `f_create_time` bigint(4) NOT NULL, 
 `f_update_time` bigint(4) NOT NULL, 
 `f_last_login_time` bigint(4) DEFAULT NULL, 
 `f_last_login_ip` varchar(80) DEFAULT NULL, 
 `f_status` tinyint(1) NOT NULL, 
PRIMARY KEY `PRIMARY` (`f_id`) USING BTREE
) ENGINE=InnoDB ROW_FORMAT = 2;

把全部的建表语句导入到/tmp/create.sql 文件：

[root@test02 ~]# cd /data/mysql/data/db_bbs/
[root@test02 db_bbs]# for n in `ls -l /data/mysql/data/db_bbs/*.frm|awk -F '/' '{print $NF}'|xargs -n 40`;do mysqlfrm --diagnostic $n|grep -v "^#" >>/tmp/create.sql;done

把生产的建表语句导入到新MySQL实例库中:

[root@10-10-127-11 ~]# mysql db_bbs  create.sql 
ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5' at line 10

原因是获取到的建表sql中包含了ROW_FORMAT = 2 这样的参数导致的
###去掉建表语句中的包含ROW_FORMAT = 2, ROW_FORMAT = 5这些字符.重新导入建表语句###

批量替换命令如下:

cat /tmp/create.sql|sed -e 's/ENGINE=InnoDB ROW_FORMAT = 2;/ENGINE=InnoDB ;/g'|grep ROW_FORMAT |uniq -c
cat /tmp/create.sql|sed -e 's/ENGINE=InnoDB ROW_FORMAT = 5;/ENGINE=InnoDB ;/g'|grep ROW_FORMAT |uniq -c
sed -i 's/ENGINE=InnoDB ROW_FORMAT = 2;/ENGINE=InnoDB ;/g' /tmp/create.sql
sed -i 's/ENGINE=InnoDB ROW_FORMAT = 5;/ENGINE=InnoDB ;/g' /tmp/create.sql
cat /tmp/create.sql|grep ROW_FORMAT |uniq -c

导出建表语句到新MySQL实例db_bbs库报错字段太长：

[root@10-10-127-11 ~]# mysql db_bbs -f  create.sql 
ERROR 1074 (42000) at line 232: Column length too big for column 'f_content' (max = 16383); use BLOB or TEXT instead
ERROR 1074 (42000) at line 299: Column length too big for column 'f_desc' (max = 16383); use BLOB or TEXT instead
ERROR 1074 (42000) at line 365: Column length too big for column 'f_body_image' (max = 16383); use BLOB or TEXT instead
ERROR 1074 (42000) at line 406: Column length too big for column 'f_content' (max = 16383); use BLOB or TEXT instead
ERROR 1074 (42000) at line 433: Column length too big for column 'f_summary' (max = 16383); use BLOB or TEXT instead

修改完字段长度类型，重新导入建表sql到全新的MySQL库中

[root@10-10-127-11 ~]# mysql db_bbs -f  create.sql 
[root@10-10-127-11 ~]# 
[root@10-10-127-11 ~]# 
[root@10-10-127-11 ~]# mysql -e "use db_bbs;show tables;"|sed '1d'|wc -l
39

###将新建的MySQL实例的 没有包括数据的 .ibd 文件抛弃掉，然后再导入故障数据库的.idb文件###

抛弃掉新建库的数据.ibd文件：

mysql -e "show tables from db_bbs" | grep -v Tables_in_db_bbs| while read a; do mysql -e "ALTER TABLE db_bbs.$a DISCARD TABLESPACE" ;done

[root@10-10-127-11 db_bbs]# ll *.ibd|wc -l
39
[root@10-10-127-11 db_bbs]# mysql -e "show tables from db_bbs" | grep -v Tables_in_db_bbs| while read a; do mysql -e "ALTER TABLE db_bbs.$a DISCARD TABLESPACE" ;done
[root@10-10-127-11 db_bbs]# ll *.ibd|wc -l
ls: cannot access *.ibd: No such file or directory

*可以看到所有的 .idb 文件都已经被抛弃了。然后把旧的有数据的 .ibd 文件拷贝到这个新MySQL实例的 ./data/db_bbs/ 目录下面，别忘了把属主改过来：chown mysql. ，再把这些数据文件 import 到数据库中**。

[root@test02 db_bbs]# scp *.ibd root@10.10.127.11:/data/mysql/data/db_bbs/
root@10.10.127.11's password: 
browse_record.ibd                                                            100% 100MB 50.0MB/s  00:02  
t_admin.ibd         
........
........
[root@10-10-127-11 db_bbs]# ll *.ibd|wc -l
39
[root@10-10-127-11 db_bbs]# ll *.ibd
-rw-r----- 1 root root 104857600 Mar 14 21:56 browse_record.ibd
-rw-r----- 1 root root   98304 Mar 14 21:56 t_admin.ibd
-rw-r----- 1 root root   98304 Mar 14 21:56 t_anonymous_code.ibd
-rw-r----- 1 root root   98304 Mar 14 21:56 t_apply.ibd
-rw-r----- 1 root root  9437184 Mar 14 21:56 t_attach.ibd
-rw-r----- 1 root root  147456 Mar 14 21:56 t_banner.ibd
-rw-r----- 1 root root  163840 Mar 14 21:56 t_banner_log.ibd
-rw-r----- 1 root root  114688 Mar 14 21:56 t_black_ip.ibd
-rw-r----- 1 root root   98304 Mar 14 21:56 t_black_user.ibd
-rw-r----- 1 root root   98304 Mar 14 21:56 t_block_userbaseinfo.ibd
-rw-r----- 1 root root   98304 Mar 14 21:56 t_collect.ibd
-rw-r----- 1 root root   98304 Mar 14 21:56 t_country_code.ibd
-rw-r----- 1 root root  163840 Mar 14 21:56 t_ct_goods.ibd
-rw-r----- 1 root root  131072 Mar 14 21:56 t_ct_goods_record.ibd
-rw-r----- 1 root root  9437184 Mar 14 21:56 t_ct_integral.ibd
-rw-r----- 1 root root 46137344 Mar 14 21:56 t_ct_integral_record.ibd
-rw-r----- 1 root root 27262976 Mar 14 21:56 t_ct_news.ibd
-rw-r----- 1 root root  9437184 Mar 14 21:56 t_ct_order.ibd
-rw-r----- 1 root root   98304 Mar 14 21:56 t_feedback.ibd
-rw-r----- 1 root root   98304 Mar 14 21:56 t_lexicon.ibd
-rw-r----- 1 root root  327680 Mar 14 21:56 t_logs.ibd
-rw-r----- 1 root root   98304 Mar 14 21:56 t_manage.ibd
-rw-r----- 1 root root   98304 Mar 14 21:56 t_module.ibd
-rw-r----- 1 root root  9437184 Mar 14 21:56 t_post_extend.ibd
-rw-r----- 1 root root 12582912 Mar 14 21:56 t_post.ibd
-rw-r----- 1 root root   98304 Mar 14 21:56 t_post_video.ibd
-rw-r----- 1 root root   98304 Mar 14 21:56 t_realtime_message.ibd
-rw-r----- 1 root root   98304 Mar 14 21:56 t_recommend.ibd
-rw-r----- 1 root root 46137344 Mar 14 21:56 t_reply.ibd
-rw-r----- 1 root root   98304 Mar 14 21:56 t_reward.ibd
-rw-r----- 1 root root  196608 Mar 14 21:56 t_sensitive_word.ibd
-rw-r----- 1 root root   98304 Mar 14 21:56 t_system_message.ibd
-rw-r----- 1 root root  9437184 Mar 14 21:56 t_userbaseinfo.ibd
-rw-r----- 1 root root  344064 Mar 14 21:56 t_userextendinfo.ibd
-rw-r----- 1 root root 12582912 Mar 14 21:56 t_user_health.ibd
-rw-r----- 1 root root   98304 Mar 14 21:56 t_user_message.ibd
-rw-r----- 1 root root  442368 Mar 14 21:56 t_user_read_module_log.ibd
-rw-r----- 1 root root 17825792 Mar 14 21:56 t_viewpoint.ibd
-rw-r----- 1 root root  114688 Mar 14 21:56 t_white_ip.ibd

[root@10-10-127-11 db_bbs]# chown mysql.mysql *.ibd

mysql -e "show tables from db_bbs" | grep -v Tables_in_db_bbs| while read a; do mysql -e "ALTER TABLE db_bbs.$a import TABLESPACE" ;done

导入每个表的表空间时，出现个别表报错：

[root@10-10-127-11 db_bbs]# mysql -e "show tables from db_bbs" | grep -v Tables_in_db_bbs| while read a; do mysql -e "ALTER TABLE db_bbs.$a import TABLESPACE" ;done 
ERROR 1808 (HY000) at line 1: Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.)

校验表文件，发现只有browse_record表导入独立表表空间时报错导致此表恢复数据失败

[root@10-10-127-11 db_bbs]# mysqlcheck -c db_bbs
db_bbs.browse_record
Warning : InnoDB: Tablespace has been discarded for table 'browse_record'
Error  : Tablespace has been discarded for table 'browse_record'
error  : Corrupt
db_bbs.t_admin                   OK
db_bbs.t_anonymous_code              OK
db_bbs.t_apply                   OK
db_bbs.t_attach                  OK
db_bbs.t_banner                  OK
db_bbs.t_banner_log                OK
db_bbs.t_black_ip                 OK
db_bbs.t_black_user                OK
db_bbs.t_block_userbaseinfo            OK
db_bbs.t_collect                  OK
db_bbs.t_country_code               OK
db_bbs.t_ct_goods                 OK
db_bbs.t_ct_goods_record              OK
db_bbs.t_ct_integral                OK
db_bbs.t_ct_integral_record            OK
db_bbs.t_ct_news                  OK
db_bbs.t_ct_order                 OK
db_bbs.t_feedback                 OK
db_bbs.t_lexicon                  OK
db_bbs.t_logs                   OK
db_bbs.t_manage                  OK
db_bbs.t_module                  OK
db_bbs.t_post                   OK
db_bbs.t_post_extend                OK
db_bbs.t_post_video                OK
db_bbs.t_realtime_message             OK
db_bbs.t_recommend                 OK
db_bbs.t_reply                   OK
db_bbs.t_reward                  OK
db_bbs.t_sensitive_word              OK
db_bbs.t_system_message              OK
db_bbs.t_user_health                OK
db_bbs.t_user_message               OK
db_bbs.t_user_read_module_log           OK
db_bbs.t_userbaseinfo               OK
db_bbs.t_userextendinfo              OK
db_bbs.t_viewpoint                 OK
db_bbs.t_white_ip                 OK

上面的browse_record 表恢复失败 报错解决办法如下：

参考：https://blog.csdn.net/weixin_30607659/article/details/94987901

删除导入到新MySQL实例的表browse_record，然后执行下面的建表语句，新建browse_record表：

CREATE TABLE `browse_record` (
 `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
 `post_id` int(4) unsigned NOT NULL,
 `user_id` int(4) unsigned NOT NULL,
 `status` tinyint(1) unsigned NOT NULL,
 `update_time` bigint(4) unsigned NOT NULL,
 `create_time` bigint(4) unsigned NOT NULL,
 PRIMARY KEY (`id`) USING BTREE,
 KEY `browse` (`post_id`,`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 row_format=compact;

提示：如果在新MySQL实例单独删除表browse_record，删除失败的话，那就直接drop掉新MySQL实例上的db_bbs库，重新导入db_bbs所有表的建表语句，然后再执行下面的命令：

mysql -e "show tables from db_bbs" | grep -v Tables_in_db_bbs| while read a; do mysql -e "ALTER TABLE db_bbs.$a DISCARD TABLESPACE" ;done

重新导入表空间到新实例MySQL中:

[root@10-10-127-11 db_bbs]# mysql -e "show tables from db_bbs" | grep -v Tables_in_db_bbs| while read a; do mysql -e "ALTER TABLE db_bbs.$a import TABLESPACE" ;done
[root@10-10-127-11 db_bbs]#

到此处数据修复完成

校验MySQL db_bbs库中的表：

[root@10-10-127-11 db_bbs]# mysqlcheck -c db_bbs
db_bbs.browse_record                OK
db_bbs.t_admin                   OK
db_bbs.t_anonymous_code              OK
db_bbs.t_apply                   OK
db_bbs.t_attach                  OK
db_bbs.t_banner                  OK
db_bbs.t_banner_log                OK
db_bbs.t_black_ip                 OK
db_bbs.t_black_user                OK
db_bbs.t_block_userbaseinfo            OK
db_bbs.t_collect                  OK
db_bbs.t_country_code               OK
db_bbs.t_ct_goods                 OK
db_bbs.t_ct_goods_record              OK
db_bbs.t_ct_integral                OK
db_bbs.t_ct_integral_record            OK
db_bbs.t_ct_news                  OK
db_bbs.t_ct_order                 OK
db_bbs.t_feedback                 OK
db_bbs.t_lexicon                  OK
db_bbs.t_logs                   OK
db_bbs.t_manage                  OK
db_bbs.t_module                  OK
db_bbs.t_post                   OK
db_bbs.t_post_extend                OK
db_bbs.t_post_video                OK
db_bbs.t_realtime_message             OK
db_bbs.t_recommend                 OK
db_bbs.t_reply                   OK
db_bbs.t_reward                  OK
db_bbs.t_sensitive_word              OK
db_bbs.t_system_message              OK
db_bbs.t_user_health                OK
db_bbs.t_user_message               OK
db_bbs.t_user_read_module_log           OK
db_bbs.t_userbaseinfo               OK
db_bbs.t_userextendinfo              OK
db_bbs.t_viewpoint                 OK
db_bbs.t_white_ip                 OK

2.4、获取导入到新MySQL实例db_bbs库中表记录和和原来的库test.txt表记录文件对比

[root@10-10-127-11 ~]# for n in `mysql -e "use db_bbs;show tables;"|sed '1d'`;do echo $n; mysql -e "use db_bbs;select count(*) from $n;";done >test.txt11

和原来的库test.txt表记录文件对比。

[root@test02 ~]# vimdiff test.txt11 test.txt

表记录完全一致
到此处MySQL的数据修复完毕

参考资料：
https://mp.weixin.qq.com/s/r3KTPsFay292JnO0lgTLUg
https://www.cnblogs.com/jiangxu67/p/4744283.html
https://blog.csdn.net/Sonny_alice/article/details/80198200
https://www.cnblogs.com/jiangxu67/p/4744283.html

到此这篇关于mysql5.7.33误删除ibdata文件找回数据的方法的文章就介绍到这了,更多相关mysql误删ibdata内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

您可能感兴趣的文章:
  • MyBatis批量插入/修改/删除MySql数据
  • mysql利用mysqlbinlog命令恢复误删除数据的实现
  • mysql数据库删除重复数据只保留一条方法实例
  • mysql 大表批量删除大量数据的实现方法
  • 浅谈为什么MySQL不建议delete删除数据
  • Python批量删除mysql中千万级大量数据的脚本分享
  • Mysql删除数据以及数据表的方法实例
  • MySQL删除数据，表文件大小依然没变的原因
  • MySQL 快速删除大量数据（千万级别）的几种实践方案详解
  • MySQL Delete 删数据后磁盘空间未释放的原因

标签：衡水 铜川 仙桃 湖南 湘潭 崇左 黄山 兰州

巨人网络通讯声明：本文标题《mysql5.7.33误删除ibdata文件找回数据的方法》，本文关键词  ；如发现本文内容存在版权问题，烦请提供相关信息告之我们，我们将及时沟通与处理。本站内容系统采集于网络，涉及言论、版权与本站无关。
  • 相关文章
  • 推荐文章

    上一篇：MySQL 实例无法启动的问题分析及解决

    下一篇：MySQL 8.0新特性 — 检查性约束的使用简介

     
    微信咨询办理
    在线QQ客服
    ( 客服小王 )
    ( 客服小李 )
    ( 客服小刘 )
    ( 客服小张 )
    关于我们 | 荣誉资质 | 呼叫中心 | 代理加盟 | 客户案例 | 服务支持 | 产品介绍 | 行业资讯

    Copyright © 1999-2012 诚信 合法 规范的巨人网络 www.1330.cn 始建于2005年

    全国网站建设网站开发咨询热线：400-1100-266   0514-86177077

    江苏扬州巨人网络科技有限公司   总部地址：江苏省扬州信息产业基地11号楼4层

    《增值电信业务经营许可证》苏B2-20120278    苏ICP备15040257号 网站地图
    全国咨询热线
    400-1100-266
    在线客服( QQ：340506921 )
    版权所有：巨人网络(扬州)科技有限公司——拥有网站建设十年经验，具备独立网站开发能力，能够提供完善的网站设计及相关的网站制作服务！     全国统一服务热线：400-1100-266
    收缩
    • 微信客服
    • 微信二维码

    • 电话咨询

    • 400-1100-266