MySQL 利用frm文件和ibd文件恢復(fù)表數(shù)據(jù)
frm文件和ibd文件簡介
在MySQL中，如果我們使用了默認(rèn)的存儲引擎innodb創(chuàng)建一張表，那么在文件夾下面就會出現(xiàn)表名.frm和表名.ibd兩個(gè)文件，如果我們使用的是Myisam存儲引擎，那么就會出現(xiàn)三個(gè)文件，這里我們給出例子：
[root@ /data/yeyz]#ll total 580 -rw-rw---- 1 mysql mysql 8586 Apr 3 17:44 a.frm -rw-rw---- 1 mysql mysql 0 Apr 3 17:44 a.MYD -rw-rw---- 1 mysql mysql 1024 Apr 3 17:44 a.MYI -rw-rw---- 1 mysql mysql 8586 Apr 3 17:44 b.frm -rw-rw---- 1 mysql mysql 98304 Apr 3 17:45 b.ibd -rw-rw---- 1 mysql mysql 61 Nov 23 09:54 db.opt -rw-rw---- 1 mysql mysql 8556 Apr 29 21:37 tbl_test_2.frm -rw-rw---- 1 mysql mysql 98304 Apr 29 21:37 tbl_test_2.ibd -rw-rw---- 1 mysql mysql 8556 Apr 29 21:33 tbl_test.frm -rw-rw---- 1 mysql mysql 98304 Apr 29 21:33 tbl_test.ibd -rw-rw---- 1 mysql mysql 8614 Apr 29 21:40 test.frm -rw-rw---- 1 mysql mysql 98304 Apr 29 21:43 test.ibd -rw-rw---- 1 mysql mysql 8666 Apr 2 15:13 unstandard_ins.frm -rw-rw---- 1 mysql mysql 98304 Apr 3 11:46 unstandard_ins.ibd -rw-rw---- 1 mysql mysql 8586 Apr 3 17:44 yeyz.frm -rw-rw---- 1 mysql mysql 28 Apr 3 17:44 yeyz.MYD -rw-rw---- 1 mysql mysql 2048 Apr 3 17:44 yeyz.MYI
其中ibd文件是innodb的表數(shù)據(jù)文件，而frm文件是innodb的表結(jié)構(gòu)文件，mysiam存儲引擎的表中，frm是表結(jié)構(gòu)，MYI文件是索引文件，而MYD文件是數(shù)據(jù)文件，從這里也可以看出，innodb存儲引擎的索引和數(shù)據(jù)是在一起的，而Myisam存儲引擎索引和數(shù)據(jù)是分開的。
需要注意的是，這個(gè)frm文件和ibd文件都是不能直接打開的。
考慮這樣一種需求，數(shù)據(jù)庫需要快速恢復(fù)一個(gè)表中的數(shù)據(jù)，而這個(gè)表所在的庫的數(shù)據(jù)量非常大，恢復(fù)起來可能耗費(fèi)的時(shí)間也比較長，那么全庫恢復(fù)肯定不是最佳的選擇。那這種情況下怎么辦呢？我們可以使用frm文件盒ibd文件來對數(shù)據(jù)進(jìn)行恢復(fù)。下面我們分析分析這個(gè)過程。
frm文件恢復(fù)表結(jié)構(gòu)
當(dāng)然，表結(jié)構(gòu)需要使用frm文件來恢復(fù)。我們第一反應(yīng)想到的是，可以把這兩個(gè)文件直接拷貝到一個(gè)新的數(shù)據(jù)庫實(shí)例中，然后直接啟動實(shí)例，這樣可以么？當(dāng)然是不行的。侄兒要是能行，估計(jì)DBA都可以下崗了。哈哈，廢話不多說，來看操作過程。
首先，我們創(chuàng)建一個(gè)新的實(shí)例專門用來恢復(fù)數(shù)據(jù)，如果你使用線上的某一臺機(jī)器來執(zhí)行恢復(fù)，那你必須承擔(dān)數(shù)據(jù)庫重啟的風(fēng)險(xiǎn)以及DML阻塞的風(fēng)險(xiǎn)，所以最好的方法還是使用一臺專門的實(shí)例來進(jìn)行恢復(fù)。那么我們?nèi)绾螐膄rm文件中拿到我們想要的表結(jié)構(gòu)呢？
我拿線上的一個(gè)記錄慢日志的表舉個(gè)例子，為了寫著方便，表名稱我寫成了"aaa",這個(gè)表的結(jié)構(gòu)是這樣的：
mysql--root@localhost:test_recover 12:08:43>>show create table aaa\G *************************** 1. row *************************** Table: aaa Create Table: CREATE TABLE `aaa` ( `maintain_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增列', `slowquery_filename` varchar(50) DEFAULT NULL COMMENT '慢日志文件名', `slowquery_path` varchar(150) DEFAULT NULL COMMENT '慢日志全路徑', `slowquery_process` tinyint(20) unsigned NOT NULL DEFAULT '0' COMMENT '慢日志是否被解析', `slowquery_uploadtime` datetime DEFAULT CURRENT_TIMESTAMP, `slowquery_analyzetime` date DEFAULT NULL COMMENT '慢日志解析時(shí)間', `slowquery_starttime` date DEFAULT NULL, `slowquery_endtime` date DEFAULT NULL, `instance_ip` varchar(15) DEFAULT NULL COMMENT '慢日志IP地址', `instance_port` int(11) DEFAULT NULL COMMENT '慢日志端口號地址', PRIMARY KEY (`maintain_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set, 1 warning (0.01 sec)
要從frm文件中得到這樣的一個(gè)表，我們要做的步驟如下：
1、在實(shí)例上創(chuàng)建一個(gè)同名的表aaa，由于我們不知道這個(gè)表的結(jié)構(gòu)，我們可以給它設(shè)定只有一個(gè)字段id，也就是
create table aaa (id int);
我們知道，這個(gè)時(shí)候會在對應(yīng)的data目錄下生成新的aaa.frm和aaa.ibd文件，然后我們使用我們備份的aaa.frm來替代之前的aaa.frm,然后重啟數(shù)據(jù)庫。
是的，你沒有看錯(cuò)，我們使用備份的表結(jié)構(gòu)文件來替代它生成的表結(jié)構(gòu)文件。
2.看看重啟之后錯(cuò)誤日志輸出的結(jié)果吧，如下：
2019-03-22T03:17:28.652390Z 16 [Warning] InnoDB: Table test_recover/store_goods_price contains 1 user defined columns in InnoDB, but 12 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2019-04-02T07:56:31.558461Z 41 [Warning] InnoDB: Table test_recover/dv_control contains 1 user defined columns in InnoDB, but 14 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2019-05-23T03:14:10.161122Z 92 [Warning] InnoDB: Table test_recover/aaa contains 1 user defined columns in InnoDB, but 10 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
可以看到，10-12行的錯(cuò)誤日志里面提示我們這個(gè)表aaa只包含1個(gè)字段，但是frm中包含10個(gè)字段，字段的數(shù)量不符。
這和我們預(yù)料的結(jié)果符合，因?yàn)槲覀冊趧?chuàng)建表aaa的時(shí)候，只給了他1個(gè)字段id，而我們要恢復(fù)的aaa表有10個(gè)字段，肯定是無法從frm中讀取的。此時(shí)你可能很容易就能想到，如果我們把這個(gè)aaa表的字段調(diào)成10個(gè)，那么最終的結(jié)果是什么呢？
3.將aaa表的字段數(shù)量升級成10個(gè)，然后重新拷貝frm文件，修改配置文件中的參數(shù)innodb_force_recovery=6，我們看看最終的結(jié)果：
mysql--root:(none) 12:04:20>>use test_recover; Database changed mysql--root:test_recover 12:04:25>>create table aaa (id1 int,id2 int,id3 int,id4 int,id5 int,id6 int,id7 int,id8 int,id9 int,id10 int); Query OK, 0 rows affected (0.03 sec) mysql--root@localhost:test_recover 12:05:08>>show create table aaa\G *************************** 1. row *************************** Table: aaa Create Table: CREATE TABLE `aaa` ( `id1` int(11) DEFAULT NULL, `id2` int(11) DEFAULT NULL, `id3` int(11) DEFAULT NULL, `id4` int(11) DEFAULT NULL, `id5` int(11) DEFAULT NULL, `id6` int(11) DEFAULT NULL, `id7` int(11) DEFAULT NULL, `id8` int(11) DEFAULT NULL, `id9` int(11) DEFAULT NULL, `id10` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
然后我們重啟實(shí)例，再次查看表aaa，可以看到結(jié)果如下：
mysql--root:test_recover 12:08:43>>show create table aaa\G *************************** 1. row *************************** Table: aaa Create Table: CREATE TABLE `aaa` ( `maintain_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增列', `slowquery_filename` varchar(50) DEFAULT NULL COMMENT '慢日志文件名', `slowquery_path` varchar(150) DEFAULT NULL COMMENT '慢日志全路徑', `slowquery_process` tinyint(20) unsigned NOT NULL DEFAULT '0' COMMENT '慢日志是否被解析', `slowquery_uploadtime` datetime DEFAULT CURRENT_TIMESTAMP, `slowquery_analyzetime` date DEFAULT NULL COMMENT '慢日志解析時(shí)間', `slowquery_starttime` date DEFAULT NULL, `slowquery_endtime` date DEFAULT NULL, `instance_ip` varchar(15) DEFAULT NULL COMMENT '慢日志IP地址', `instance_port` int(11) DEFAULT NULL COMMENT '慢日志端口號地址', PRIMARY KEY (`maintain_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set, 1 warning (0.01 sec)
可以看到，我們想要的表結(jié)構(gòu)已經(jīng)從frm文件中恢復(fù)出來了，需要注意的是，這個(gè)過程中我們并沒有使用ibd文件。
總結(jié)一下利用frm文件恢復(fù)表結(jié)構(gòu)的步驟：
1、首先創(chuàng)建一個(gè)同名的表，然后啟動實(shí)例
2、使用備份的frm文件替代生成的frm文件，重啟實(shí)例
3、查看錯(cuò)誤日志，從錯(cuò)誤日志中獲取到備份的frm文件中的字段數(shù)量m
4、重新創(chuàng)建同名表，保證字段數(shù)量為m，與備份表保持一致，然后重新拷貝備份的frm文件到對應(yīng)目錄
5、修改實(shí)例的配置文件中的參數(shù)innodb_force_recovery=6，然后重啟數(shù)據(jù)庫，就可以看到對應(yīng)的表結(jié)構(gòu)創(chuàng)建語句，我們把它保存下來，下一步恢復(fù)數(shù)據(jù)的時(shí)候要用。這一步相當(dāng)重要
6、將參數(shù)innodb_force_recovery=6注釋掉，重新使用默認(rèn)的值，然后重啟數(shù)據(jù)庫，準(zhǔn)備恢復(fù)表數(shù)據(jù)。
至此，表結(jié)構(gòu)恢復(fù)完畢。
解釋一下innodb_force_recovery參數(shù)，這個(gè)參數(shù)的最大值是6，在該等級下，僅支持一部分查詢功能，DML都不支持，從名稱就可以看出來，這是在一些強(qiáng)行恢復(fù)的場景下才會使用的參數(shù)，一般情況下這個(gè)參數(shù)可以不要，使用默認(rèn)值就行。有興趣更深了解的同學(xué)可以參考官方文檔。
ibd文件恢復(fù)表數(shù)據(jù)
上一步執(zhí)行完成之后，我們已經(jīng)獲取了對應(yīng)的表結(jié)構(gòu)，現(xiàn)在我們看看如何恢復(fù)表數(shù)據(jù)。
恢復(fù)表數(shù)據(jù)的方法比較簡單，大體步驟如下：
1、利用我們上一步中獲取的建表語句，重新創(chuàng)建一張表，然后執(zhí)行：
flush table aaa for export;
這個(gè)語法是將表里面的數(shù)據(jù)落盤，并獲取該表的鎖，為后面恢復(fù)做好準(zhǔn)備。
2、然后我們使用如下語句：
alter table aaa discard tablespace;
這個(gè)語句會刪除當(dāng)前的ibd文件。
3、然后我們使用我們之前備份的ibd文件，將其拷貝到對應(yīng)的實(shí)例目錄下面
4、最后在將ibd文件重新加載進(jìn)來，使用如下語句：
alter table aaa import tablespace;
重啟數(shù)據(jù)庫，這樣，我們的數(shù)據(jù)就恢復(fù)成功了。
簡單總結(jié)一下
整個(gè)恢復(fù)的流程算是介紹完了，其中比較巧妙的地方就是從frm文件中獲取表結(jié)構(gòu)信息，我們使用了兩次拼湊表創(chuàng)建語句的方法，最終得到了待恢復(fù)的表的表結(jié)構(gòu)，然后使用alter table discard tablespace和alter table import tablespace的方法來恢復(fù)表中的數(shù)據(jù)。整個(gè)過程看著比較復(fù)雜，其實(shí)完全可以按照步驟抽象出來一個(gè)腳本，這樣在下次恢復(fù)的時(shí)候，只需要輸入要恢復(fù)的表的名稱，就可以快速的恢復(fù)表結(jié)構(gòu)和數(shù)據(jù)，不失為一種應(yīng)急的數(shù)據(jù)恢復(fù)預(yù)案。
以上就是MySQL 利用frm文件和ibd文件恢復(fù)表數(shù)據(jù)的詳細(xì)內(nèi)容，更多關(guān)于MySQL 恢復(fù)表數(shù)據(jù)的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
mysql 數(shù)據(jù)同步 出現(xiàn)Slave_IO_Running:No問題的解決方法小結(jié)
mysql replication 中slave機(jī)器上有兩個(gè)關(guān)鍵的進(jìn)程，死一個(gè)都不行，一個(gè)是slave_sql_running，一個(gè)是Slave_IO_Running，一個(gè)負(fù)責(zé)與主機(jī)的io通信，一個(gè)負(fù)責(zé)自己的slave mysql進(jìn)程。2011-05-05
MySQL性能優(yōu)化的一些技巧幫助你的數(shù)據(jù)庫
你完成了你的品牌新的應(yīng)用程序,一切工作就像一個(gè)魅力;突然間,一個(gè)大爆發(fā)的用戶你的MySQL服務(wù)器,您的網(wǎng)站已關(guān)閉,是什么問題導(dǎo)致的呢？以下是MySQL性能優(yōu)化的一些技巧,將幫助你,幫助你的數(shù)據(jù)庫2013-01-01
本文主要介紹了MySQL優(yōu)化之Index Merge的使用，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-03-03
sql如何使用group by分組,同時(shí)查詢其它字段
文章介紹了使用SQL的GROUP BY進(jìn)行分組查詢時(shí)的一些規(guī)則和技巧,主要強(qiáng)調(diào)了在SELECT后面的字段要么是聚合函數(shù)的一部分,要么必須包含在GROUP BY子句中,此外,文章還討論了如何在GROUP BY時(shí)查詢其他字段,通過使用MAX或MIN函數(shù)來實(shí)現(xiàn)2024-12-12
MySQL explain根據(jù)查詢計(jì)劃去優(yōu)化SQL語句
MySQL是一種常見的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，常被用于各種應(yīng)用程序中存儲數(shù)據(jù)，當(dāng)涉及到大量的數(shù)據(jù)時(shí)，就需要MySQL的explain功能來幫助優(yōu)化，本文將詳細(xì)介紹MySQL的explain功能，感興趣的朋友可以參考閱讀2023-04-04
Druid數(shù)據(jù)庫連接池的jar包的使用方式
這篇文章主要介紹了Druid數(shù)據(jù)庫連接池的jar包的使用方式,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-04-04
phplib中常用的方法有set_file,set_block,set_var,parse,ppasre,p,get等。2010-04-04
如何實(shí)現(xiàn)mysql的遠(yuǎn)程連接
這篇文章詳細(xì)介紹了mysql如何實(shí)現(xiàn)遠(yuǎn)程連接，文中有詳細(xì)的代碼實(shí)例講解，有一定的參考價(jià)值，需要的朋友可以參考閱讀2023-04-04
MySQL中LAST_INSERT_ID()函數(shù)的實(shí)現(xiàn)
本文主要介紹了MySQL中LAST_INSERT_ID()函數(shù)的作用和使用方法,LAST_INSERT_ID()函數(shù)用于返回上一次INSERT操作生成的自增ID,對于需要獲取新插入記錄的主鍵的場景非常重要,感興趣的可以了解一下2024-10-10
今天小編就為大家分享一篇關(guān)于MySQL不同表之前的字段復(fù)制，小編覺得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來看看吧2019-03-03