mysqldata数据如何恢复（mysql5.7.33误删除ibdata文件找回数据的方法）

mysqldata数据如何恢复

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

目录

• 一、场景说明：
• 二、案例演示：
  • 2.1、确认MySQL故障前，库中的每张表有多少记录
  • 2.2、模拟删除ibdata故障：
  • 2.3、找回数据的办法:
  • 2.4、获取导入到新MySQL实例db_bbs库中表记录和和原来的库test.txt表记录文件对比

 

一、场景说明：

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

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

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

 

二、案例演示：

 

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

?

1	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张表：

?

1 2	[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。

?

1	yum -y install mysql-utilities

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

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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 文件：

?

1 2	[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实例库中:

?

1 2	[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这些字符.重新导入建表语句###

批量替换命令如下:

?

1 2 3 4 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库报错字段太长：

?

1 2 3 4 5 6

[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库中

?

1 2 3 4 5

[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文件：

?

1	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

?

1 2 3 4 5

[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 到数据库中**。

?

1 2 3 4 5 6

[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         ........ ........

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

[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

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

?

1 2	[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表导入独立表表空间时报错导致此表恢复数据失败

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

[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表：

?

1 2 3 4 5 6 7 8 9 10

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所有表的建表语句，然后再执行下面的命令：

?

1	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中:

?

1 2	[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库中的表：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

[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 标签：mysql 误删 ibdata 上一篇：云服务器常见的哪几种（新手如何选择合适的云服务器？） 下一篇：jscanvas画折线图（js+canvas实现代码雨效果） 您可能感兴趣 binlog怎么恢复mysql数据库（mysql5.7使用binlog 恢复数据的方法） SQL SERVER与MySQL数据类型的对应关系 mysql字段多有什么问题（MySQL编码不一致可能引起的一些问题） MySQL DATE_FORMAT()用法 对mysql性能优化的看法（聊聊MySQL的COUNT的性能，看看怎么最快?） mysql的三种模式（详解 MySQL的FreeList机制） mysql主键什么情况用uuid（Mysql主键UUID和自增主键的区别及优劣分析） mysql主机双向复制配置（浅析MySQL并行复制） mysql拆分字符串（MySQL 字符串拆分操作含分隔符的字符串截取） mysql的decimal类型（MySQL数据类型DECIMAL用法详解） mysql读写分离同步策略（Mysql主从复制与读写分离图文详解） mysql5.7.20非安装版教程（MySQL5.5 部署的一个问题） xampp数据库表在哪个文件夹（XAMPP集成环境中MySQL数据库的使用） mysql常用存储方案及基本原理（详解分析MySQL8.0的内存消耗） mysql update 多个字段（mysql update case 更新字段值不固定的操作） mysql数据表实例教程（mysql数据库入门第一步之创建表） 冬季钓鱼子线用 长 还是 短（冬季钓鱼子线用） 鱼竿 夏钓短，冬钓长 ，一定是这样 认清优缺点在选竿（鱼竿夏钓短冬钓长） 鲢鳙钓底还是钓浮 流水的水域应怎样做钓（鲢鳙钓底还是钓浮） 入秋后的第二场苹果发布会来了 全新M1系列芯片登场（入秋后的第二场苹果发布会来了） 苹果正式发布自研芯片M1 5nm 32核心 彻底放弃Intel（苹果正式发布自研芯片M1） 苹果自研芯片跑分对比 A16芯片排名靠后，M1系列霸榜（苹果自研芯片跑分对比） 热门推荐 docker 部署springboot项目（Spring Boot Docker打包工具小结） nginx 根据url限流（浅谈Nginx 中的两种限流方式） 数据库中的事务隔离 使用docker对集群进行管理（使用docker搭建kong集群操作） python开发网站github（使用 Python 玩转 GitHub 的贡献板推荐） vmwareesxi7教程（VMware ESXi安装使用记录附下载） 接到面试通知后必须做哪些准备 html5菜单栏（html5写一个BUI折叠菜单插件的实现方法） python气温变化数据分析（基于python历史天气采集的分析） nginx最佳配置（nginx和lvs各自的优劣以及适合的使用环境） 排行榜 1 mysql查看死锁记录（mysql查看死锁与去除死锁示例详解） 2 mysql冷热数据分离方案（MySQL中使用流式查询避免数据OOM） 3 mysql数据库导入错误怎么处理（MySQL数据库导入导出数据之报错解答实例讲解） 4 mysqlsql按日期统计（sqlserver/mysql按天、按小时、按分钟统计连续时间段数据推荐） 5 mysql数据表实例教程（mysql数据库入门第一步之创建表） 6 mysql update 多个字段（mysql update case 更新字段值不固定的操作） 7 mysql常用存储方案及基本原理（详解分析MySQL8.0的内存消耗） 8 xampp数据库表在哪个文件夹（XAMPP集成环境中MySQL数据库的使用） 9 mysql5.7.20非安装版教程（MySQL5.5 部署的一个问题） 首页 编程学习 Web前端 数据库 软件设计 开心学习 ©2013-2021 保留所有权利