一、场景说明：
很多时候因为 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 表恢复失败 报错解决办法如下：
参考：
删除导入到新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://blog.csdn.net/sonny_alice/article/details/80198200
到此这篇关于mysql5.7.33误删除ibdata文件找回数据的方法的文章就介绍到这了,更多相关mysql误删ibdata内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！