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

吾爱主题阅读：134 2024-04-02 08:00:48 评论：0

一、场景说明：

很多时候因为 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 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表记录文件对比

1	`[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表记录文件对比。