登录注册

当前位置：首页 > 文章列表 > 数据库 > MySQL > mysql5.7.33误删除ibdata文件找回数据的方法

mysql5.7.33误删除ibdata文件找回数据的方法

来源：脚本之家 2023-01-07 12:07:01 0浏览 收藏

本篇文章给大家分享《mysql5.7.33误删除ibdata文件找回数据的方法》，覆盖了数据库的常见基础知识，其实一个语言的全部知识点一篇文章是不可能说完的，但希望通过这些问题，让读者对自己的掌握程度有一定的认识(B 数)，从而弥补自己的不足，更好的掌握它。

一、场景说明：

很多时候因为 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 

<p>原因是获取到的建表sql中包含了ROW_FORMAT = 2 这样的参数导致的<br>
###去掉建表语句中的包含ROW_FORMAT = 2, ROW_FORMAT = 5这些字符.重新导入建表语句###</p>
<p>批量替换命令如下:</p>

<pre class="brush:bash;">
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 

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

<pre class="brush:bash;">
[root@10-10-127-11 ~]# mysql db_bbs -f 

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

<pre class="brush:bash;">
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文件找回数据的方法》的内容介绍就到此结束，如果有什么疑问或者建议，可以在golang学习网公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！

mysql MySQL误删 ibdata
版本声明
本文转载于：脚本之家 如有侵犯，请联系study_golang@163.com删除
MySQL 8.0新特性 — 检查性约束的使用简介MySQL 8.0新特性 — 检查性约束的使用简介
上一篇
MySQL 8.0新特性 — 检查性约束的使用简介
MySQL 实例无法启动的问题分析及解决
下一篇
MySQL 实例无法启动的问题分析及解决
查看更多
最新文章
查看更多
课程推荐
  • 前端进阶之JavaScript设计模式
    前端进阶之JavaScript设计模式
    设计模式是开发人员在软件开发过程中面临一般问题时的解决方案，代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景，打造一站式知识长龙服务，适合有JS基础的同学学习。
    543次学习
  • GO语言核心编程课程
    GO语言核心编程课程
    本课程采用真实案例，全面具体可落地，从理论到实践，一步一步将GO核心编程技术、编程思想、底层实现融会贯通，使学习者贴近时代脉搏，做IT互联网时代的弄潮儿。
    516次学习
  • 简单聊聊mysql8与网络通信
    简单聊聊mysql8与网络通信
    如有问题加微信：Le-studyg；在课程中，我们将首先介绍MySQL8的新特性，包括性能优化、安全增强、新数据类型等，帮助学生快速熟悉MySQL8的最新功能。接着，我们将深入解析MySQL的网络通信机制，包括协议、连接管理、数据传输等，让
    500次学习
  • JavaScript正则表达式基础与实战
    JavaScript正则表达式基础与实战
    在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
    487次学习
  • 从零制作响应式网站—Grid布局
    从零制作响应式网站—Grid布局
    本系列教程将展示从零制作一个假想的网络科技公司官网，分为导航，轮播，关于我们，成功案例，服务流程，团队介绍，数据部分，公司动态，底部信息等内容区块。网站整体采用CSSGrid布局，支持响应式，有流畅过渡和展现动画。
    485次学习
查看更多
AI推荐
  • ChatExcel酷表：告别Excel难题，北大团队AI助手助您轻松处理数据
    ChatExcel酷表
    ChatExcel酷表是由北京大学团队打造的Excel聊天机器人，用自然语言操控表格，简化数据处理，告别繁琐操作，提升工作效率！适用于学生、上班族及政府人员。
    3178次使用
  • Any绘本：开源免费AI绘本创作工具深度解析
    Any绘本
    探索Any绘本（anypicturebook.com/zh），一款开源免费的AI绘本创作工具，基于Google Gemini与Flux AI模型，让您轻松创作个性化绘本。适用于家庭、教育、创作等多种场景，零门槛，高自由度，技术透明，本地可控。
    3389次使用
  • 可赞AI：AI驱动办公可视化智能工具，一键高效生成文档图表脑图
    可赞AI
    可赞AI，AI驱动的办公可视化智能工具，助您轻松实现文本与可视化元素高效转化。无论是智能文档生成、多格式文本解析，还是一键生成专业图表、脑图、知识卡片，可赞AI都能让信息处理更清晰高效。覆盖数据汇报、会议纪要、内容营销等全场景，大幅提升办公效率，降低专业门槛，是您提升工作效率的得力助手。
    3418次使用
  • 星月写作：AI网文创作神器，助力爆款小说速成
    星月写作
    星月写作是国内首款聚焦中文网络小说创作的AI辅助工具，解决网文作者从构思到变现的全流程痛点。AI扫榜、专属模板、全链路适配，助力新人快速上手，资深作者效率倍增。
    4523次使用
  • MagicLight.ai：叙事驱动AI动画视频创作平台 | 高效生成专业级故事动画
    MagicLight
    MagicLight.ai是全球首款叙事驱动型AI动画视频创作平台，专注于解决从故事想法到完整动画的全流程痛点。它通过自研AI模型，保障角色、风格、场景高度一致性，让零动画经验者也能高效产出专业级叙事内容。广泛适用于独立创作者、动画工作室、教育机构及企业营销，助您轻松实现创意落地与商业化。
    3797次使用
查看更多
相关文章
关于我们 免责声明 意见反馈 联系我们 内容提交
Golang学习网：公益在线Go学习平台，帮助Go学习者快速成长！
技术交流群
Copyright 2023 http://www.17golang.com/ All Rights Reserved ｜ 苏ICP备2023003363号-1
  • Golang学习网
    关注公众号
    Golang学习网
微信登录更方便
  • 密码登录
  • 注册账号
忘记密码
登录即同意 用户协议 和 隐私政策
返回登录
  • 重置密码
发送验证码