mysql5.7.33誤刪除ibdata文件找回?cái)?shù)據(jù)的方法
一、場(chǎng)景說(shuō)明：
很多時(shí)候因?yàn)?MySQL 數(shù)據(jù)庫(kù)不能啟動(dòng)而造成數(shù)據(jù)無(wú)法訪(fǎng)問(wèn)，但應(yīng)用的數(shù)據(jù)通常沒(méi)有丟失，只是系統(tǒng)表空間等其它文件損壞了，或者遇到 MySQL 的 bug。
這個(gè)時(shí)候如果沒(méi)有備份，很多人就以為數(shù)據(jù)丟失了，但實(shí)際上大部分時(shí)候數(shù)據(jù)還是有救的。
對(duì)于 MyISAM 引擎的表空間，直接把對(duì)應(yīng)的數(shù)據(jù)文件拷貝到一個(gè)新的數(shù)據(jù)庫(kù)就行了，數(shù)據(jù)就可以恢復(fù)了。
對(duì)于 InnoDB 引擎的數(shù)據(jù)庫(kù)表空間可以采用傳輸表空間的方式把數(shù)據(jù)救回來(lái).
前提是MySQL開(kāi)啟了參數(shù) innodb_file_per_table = 1 獨(dú)立表空間文件
當(dāng)MySQL的數(shù)據(jù)表空間文件ibdata文件損壞或者是被無(wú)修改和刪除，導(dǎo)致MySQL服務(wù)重啟失敗。同時(shí)MySQL的數(shù)據(jù)又沒(méi)有及時(shí)備份，此時(shí)如何盡可能多的找回MySQL數(shù)據(jù)呢？？
二、案例演示：
2.1、確認(rèn)MySQL故障前，庫(kù)中的每張表有多少記錄
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
確認(rèn)MySQL故障前，庫(kù)中一共有多少?gòu)埍恚?/p>
一共是39張表：
[root@10-10-127-11 ~]# mysql -e "use db_bbs;show tables;"|sed '1d'|wc -l 39
2.2、模擬刪除ibdata故障：
刪除ibdata文件（生產(chǎn)環(huán)境禁止這么干）
innodb_force_recovery =6 利用MySQL的強(qiáng)制啟動(dòng)參數(shù)來(lái)啟動(dòng)此時(shí)的MySQL服務(wù)，但是已經(jīng)是無(wú)濟(jì)于事。由于是數(shù)據(jù)表空間文件ibdata文件被刪除了.所以啟動(dòng)不了此時(shí)的MySQL服務(wù)。
由于此MySQL采用的innodb引擎。而且開(kāi)啟了獨(dú)立表空間參數(shù) innodb_file_per_table = 1 。所以此時(shí)可以采用采用傳輸表空間的方式把數(shù)據(jù)救回來(lái)。
2.3、找回?cái)?shù)據(jù)的辦法:
首先來(lái)創(chuàng)建已經(jīng)丟失的表結(jié)構(gòu)：
先要在故障的MySQL服務(wù)器上 安裝 mysql-utilities。
yum -y install mysql-utilities
使用 mysqlfrm 從 .frm 文件里面找回建表語(yǔ)句:
分析一個(gè) .frm 文件生成建表的語(yǔ)句
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;
把全部的建表語(yǔ)句導(dǎo)入到/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
把生產(chǎn)的建表語(yǔ)句導(dǎo)入到新MySQL實(shí)例庫(kù)中:
[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 這樣的參數(shù)導(dǎo)致的
###去掉建表語(yǔ)句中的包含ROW_FORMAT = 2, ROW_FORMAT = 5這些字符.重新導(dǎo)入建表語(yǔ)句###
批量替換命令如下:
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
導(dǎo)出建表語(yǔ)句到新MySQL實(shí)例db_bbs庫(kù)報(bào)錯(cuò)字段太長(zhǎng)：
[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
修改完字段長(zhǎng)度類(lèi)型，重新導(dǎo)入建表sql到全新的MySQL庫(kù)中
[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實(shí)例的 沒(méi)有包括數(shù)據(jù)的 .ibd 文件拋棄掉，然后再導(dǎo)入故障數(shù)據(jù)庫(kù)的.idb文件###
拋棄掉新建庫(kù)的數(shù)據(jù).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 文件都已經(jīng)被拋棄了。然后把舊的有數(shù)據(jù)的 .ibd 文件拷貝到這個(gè)新MySQL實(shí)例的 ./data/db_bbs/ 目錄下面，別忘了把屬主改過(guò)來(lái)：chown mysql. ，再把這些數(shù)據(jù)文件 import 到數(shù)據(jù)庫(kù)中**。
[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
導(dǎo)入每個(gè)表的表空間時(shí)，出現(xiàn)個(gè)別表報(bào)錯(cuò)：
[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.)
校驗(yàn)表文件，發(fā)現(xiàn)只有browse_record表導(dǎo)入獨(dú)立表表空間時(shí)報(bào)錯(cuò)導(dǎo)致此表恢復(fù)數(shù)據(jù)失敗
[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 表恢復(fù)失敗 報(bào)錯(cuò)解決辦法如下：
參考：https://blog.csdn.net/weixin_30607659/article/details/94987901
刪除導(dǎo)入到新MySQL實(shí)例的表browse_record，然后執(zhí)行下面的建表語(yǔ)句，新建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實(shí)例單獨(dú)刪除表browse_record，刪除失敗的話(huà)，那就直接drop掉新MySQL實(shí)例上的db_bbs庫(kù)，重新導(dǎo)入db_bbs所有表的建表語(yǔ)句，然后再執(zhí)行下面的命令：
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
重新導(dǎo)入表空間到新實(shí)例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]#
到此處數(shù)據(jù)修復(fù)完成
校驗(yàn)MySQL db_bbs庫(kù)中的表：
[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、獲取導(dǎo)入到新MySQL實(shí)例db_bbs庫(kù)中表記錄和和原來(lái)的庫(kù)test.txt表記錄文件對(duì)比
[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
和原來(lái)的庫(kù)test.txt表記錄文件對(duì)比。
[root@test02 ~]# vimdiff test.txt11 test.txt
表記錄完全一致
到此處MySQL的數(shù)據(jù)修復(fù)完畢
參考資料：
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
到此這篇關(guān)于mysql5.7.33誤刪除ibdata文件找回?cái)?shù)據(jù)的方法的文章就介紹到這了,更多相關(guān)mysql誤刪ibdata內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
- MyBatis批量插入/修改/刪除MySql數(shù)據(jù)
- mysql利用mysqlbinlog命令恢復(fù)誤刪除數(shù)據(jù)的實(shí)現(xiàn)
- mysql數(shù)據(jù)庫(kù)刪除重復(fù)數(shù)據(jù)只保留一條方法實(shí)例
- mysql 大表批量刪除大量數(shù)據(jù)的實(shí)現(xiàn)方法
- 淺談為什么MySQL不建議delete刪除數(shù)據(jù)
- Python批量刪除mysql中千萬(wàn)級(jí)大量數(shù)據(jù)的腳本分享
- Mysql刪除數(shù)據(jù)以及數(shù)據(jù)表的方法實(shí)例
- MySQL刪除數(shù)據(jù)，表文件大小依然沒(méi)變的原因
- MySQL 快速刪除大量數(shù)據(jù)（千萬(wàn)級(jí)別）的幾種實(shí)踐方案詳解
- MySQL Delete 刪數(shù)據(jù)后磁盤(pán)空間未釋放的原因
相關(guān)文章
CentOS 6.6 源碼編譯安裝MySQL 5.7.18教程詳解
這篇文章主要介紹了CentOS 6.6 源碼編譯安裝MySQL 5.7.18教程詳解,需要的朋友可以參考下2017-07-07
mysql-canal-rabbitmq 安裝部署超詳細(xì)教程
這篇文章主要介紹了mysql-canal-rabbitmq 安裝部署超詳細(xì)教程,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-03-03
lnmp下如何關(guān)閉Mysql日志保護(hù)磁盤(pán)空間
這篇文章主要介紹了lnmp下如何關(guān)閉Mysql日志保護(hù)磁盤(pán)空間的相關(guān)資料,需要的朋友可以參考下2015-09-09
SELECT語(yǔ)句是SQL中最常用的語(yǔ)句。它的用途是從一個(gè)或多個(gè)表中檢索信息。為了使用SELECT檢索表數(shù)據(jù)，必須至少給出兩條信息：想選擇什么、從什么地方選擇2022-10-10
這篇文章主要介紹了MySQL使用初步之MySQL數(shù)據(jù)庫(kù)的基本命令,需要的朋友可以參考下2017-05-05
MySQL學(xué)習(xí)之MySQL基本架構(gòu)與鎖
這篇文章主要介紹了MySQL的基本架構(gòu)和鎖，鎖的分類(lèi)有兩種有按粒度分，按功能，也有不同的類(lèi)型，感興趣的小伙伴可以參考閱讀2023-03-03
這篇文章主要介紹了MySQL系列之十三 MySQL的復(fù)制,詳細(xì)的講述了多種復(fù)制架構(gòu)的案例和MySQL復(fù)制相關(guān)概念等,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下2021-07-07
MySQL存儲(chǔ)引擎MyISAM與InnoDB的9點(diǎn)區(qū)別
這篇文章主要介紹了MySQL存儲(chǔ)引擎MyISAM與InnoDB的9點(diǎn)區(qū)別,寫(xiě)給有選擇困難癥的同學(xué),需要的朋友可以參考下2014-08-08
這篇文章主要介紹了jQuery 綁定事件的方式總結(jié)的相關(guān)資料,這里整理了幾種方法，需要的朋友可以參考下2016-11-11
這篇文章主要為大家詳細(xì)介紹了mysql 5.7.23 安裝配置方法圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-09-09