快捷導(dǎo)航

MySQL利用frm文件和ibd文件恢復(fù)表結(jié)構(gòu)和表數(shù)據(jù)

更新時(shí)間：2024年05月20日 10:56:21 作者：月巴左耳東

當(dāng)MySQL數(shù)據(jù)庫遭遇崩潰或數(shù)據(jù)丟失時(shí),利用備份的 .frm 和 .ibd 文件恢復(fù)數(shù)據(jù)是一種有效的解決方案,.frm 文件包含表的結(jié)構(gòu)信息,而 .ibd 文件則存儲(chǔ)表的實(shí)際數(shù)據(jù),本文將提供一個(gè)詳細(xì)的步驟指南,演示如何利用這些文件恢復(fù)MySQL表數(shù)據(jù)和表結(jié)構(gòu),需要的朋友可以參考下

frm文件和ibd文件簡(jiǎn)介

在MySQL中，使用默認(rèn)的存儲(chǔ)引擎innodb創(chuàng)建一張表，那么在庫名文件夾下面就會(huì)出現(xiàn)表名.frm和表名.ibd兩個(gè)文件
- ibd文件是innodb的表數(shù)據(jù)文件
- frm文件是innodb的表結(jié)構(gòu)文件
  - 需要注意的是，frm文件和ibd文件都是不能直接打開的
  - 恢復(fù)數(shù)據(jù)之前，需要先恢復(fù)表結(jié)構(gòu)
- 在有建表語句的前提下，可以直接跳到ibd文件恢復(fù)表數(shù)據(jù)，不需要使用frm文件恢復(fù)表結(jié)構(gòu)

frm文件恢復(fù)表結(jié)構(gòu)

前提是已經(jīng)備份了對(duì)應(yīng)的frm文件
建議重新啟動(dòng)一個(gè)MySQL實(shí)例，待數(shù)據(jù)恢復(fù)后，通過mysqldump備份數(shù)據(jù)，再重新恢復(fù)到需要使用的數(shù)據(jù)庫里
在新啟動(dòng)的實(shí)例上創(chuàng)建一個(gè)同名的表，例如study.frm，表示表名稱為study
- 在不知道表結(jié)構(gòu)的情況下，可以先定義一個(gè)字段，稍后可以通過mysql.err日志內(nèi)查看表字段的數(shù)量

create table study (id int);

創(chuàng)建完表后，在對(duì)應(yīng)的數(shù)據(jù)目錄下就會(huì)生成study.frm和study.ibd文件，然后使用之前備份的study.frm來替換現(xiàn)有的study.frm，切記，不要著急替換study.ibd文件，這個(gè)文件在恢復(fù)表結(jié)構(gòu)后再使用
- 注意替換文件后的study.frm文件的權(quán)限，確保和其他文件的屬主和屬組是一樣的
- 重啟mysql數(shù)據(jù)庫

查看日志

grep study mysql.err | grep columns

容器啟動(dòng)的MySQL，直接使用docker restart <容器id>來重啟MySQL服務(wù)

如果是容器啟動(dòng)的MySQL，可以使用下面的命令在容器外查看日志

docker logs <容器id> | grep study | grep columns

通過日志，我們可以看到，study這個(gè)表，之前有5個(gè)字段，但是我們現(xiàn)在只有1個(gè)字段

[Warning] InnoDB: Table hello@002dworld/study contains 1 user defined columns in InnoDB, but 5 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.

這個(gè)時(shí)候，我們可以把原來的表刪掉

drop table study;

然后重新創(chuàng)建一個(gè)和原來的表相同字段的表，切記，表名稱要一樣，字段內(nèi)容不重要，只需要字段數(shù)量一致

create table study (id1 int,id2 int,id3 int,id4 int,id5 int);

現(xiàn)在可以看到我們的建表語句了，當(dāng)然，這個(gè)是上面使用的建表語句，咱們繼續(xù)往下

show create table study\G
*************************** 1. row ***************************
       Table: study
Create Table: CREATE TABLE `study` (
  `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
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

確認(rèn)是否開啟了innodb_force_recovery參數(shù)，正常情況下，如果不是為了恢復(fù)數(shù)據(jù)是不會(huì)開啟這個(gè)參數(shù)的
- innodb_force_recovery 參數(shù)需要配置到 my.cnf 中的 [mysqld] 模塊下，取值范圍是0-6， 默認(rèn)是0
- 1: (SRV_FORCE_IGNORE_CORRUPT): 忽略檢查到的corrupt頁
- 2: (SRV_FORCE_NO_BACKGROUND): 阻止主線程的運(yùn)行，如主線程需要執(zhí)行full purge操作，會(huì)導(dǎo)致crash
- 3: (SRV_FORCE_NO_TRX_UNDO): 不執(zhí)行事務(wù)回滾操作
- 4: (SRV_FORCE_NO_IBUF_MERGE): 不執(zhí)行插入緩沖的合并操作
- 5: (SRV_FORCE_NO_UNDO_LOG_SCAN): 不查看重做日志，InnoDB存儲(chǔ)引擎會(huì)將未提交的事務(wù)視為已提交
- 6: (SRV_FORCE_NO_LOG_REDO): 不執(zhí)行前滾的操作
- 當(dāng)設(shè)置參數(shù)值大于0后，可以對(duì)表進(jìn)行select、create、drop操作，但insert、update或者delete這類操作是不允許的

grep 'innodb_force' my.cnf

插入配置到my.cnf配置文件中，然后再次替換study.frm文件，并重啟MySQL服務(wù)
- 注意替換文件后的study.frm文件的權(quán)限，確保和其他文件的屬主和屬組是一樣的

sed -i '/\[mysqld\]/a\innodb_force_recovery=6' my.cnf

重啟完成后，再次查看建表語句

show create table study\G
*************************** 1. row ***************************
       Table: study
Create Table: CREATE TABLE `study` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `time` int(11) DEFAULT NULL,
  `lang` varchar(20) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

到這里，我們已經(jīng)成功找回之前的建表語句了，通過這個(gè)語句，就可以恢復(fù)之前的表了
- 復(fù)制獲取的建表語句，注釋掉之前的innodb_force_recovery參數(shù)，并且重啟MySQL服務(wù)

sed -i '/innodb_force_recovery/s/^\(.*\)$/#\1/g' my.cnf

再次刪掉study這個(gè)表

drop table study;

然后使用上面獲取到的建表語句重新建表，注意最后加上一個(gè)分號(hào)，這是SQL的語法格式

CREATE TABLE `study` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `time` int(11) DEFAULT NULL,
  `lang` varchar(20) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ibd文件恢復(fù)表數(shù)據(jù)

在有建表語句的情況下，使用idb文件恢復(fù)數(shù)據(jù)，相比使用frm文件恢復(fù)表數(shù)據(jù)要簡(jiǎn)單方便很多
刪除當(dāng)前的ibd文件

alter table study discard tablespace;

將之前備份的study.ibd文件復(fù)制到對(duì)應(yīng)的數(shù)據(jù)目錄下，使用下面的命令將數(shù)據(jù)加載到MySQL數(shù)據(jù)庫里
- 注意替換文件后的study.ibd文件的權(quán)限，確保和其他文件的屬主和屬組是一樣的

alter table study import tablespace;

再次查看數(shù)據(jù)表，發(fā)現(xiàn)之前的數(shù)據(jù)也回來了

select * from study;

+------+------+------+----------+---------+
| id   | name | age  | time     | lang    |
+------+------+------+----------+---------+
|    1 | tom  |   26 | 20211024 | chinese |
+------+------+------+----------+---------+

記得備份數(shù)據(jù)，數(shù)據(jù)是無價(jià)的

通過腳本利用ibd文件恢復(fù)數(shù)據(jù)

前提是表結(jié)構(gòu)是存在的
注意自己的數(shù)據(jù)庫是否區(qū)分大小寫，以及表名稱是否有大小寫，如果表名稱有大小寫，新啟動(dòng)的mysql一定要開啟大小寫[開啟大小寫參數(shù)：lower_case_table_names = 0]
mysql_user變量的值為mysql數(shù)據(jù)目錄的屬主和屬組
根據(jù)實(shí)際場(chǎng)景修改mysql_cmd變量的值，修改成自己用戶名，用戶密碼，主機(jī)ip
mysql_data_dir變量的值為mysql數(shù)據(jù)存儲(chǔ)路徑
back_data_dir變量的值為備份下來的ibd文件存儲(chǔ)路徑

#!/bin/bash
base_dir=$(cd `dirname $0`; pwd)
mysql_user='mysql'
mysql_cmd="mysql -N -uroot -proot -h192.168.70.49"
databases_list=($(${mysql_cmd} -e 'SHOW DATABASES;' | egrep -v 'information_schema|mysql|performance_schema|sys'))
mysql_data_dir='/var/lib/mysql'
back_data_dir='/tmp/back-data'

for (( i=0; i<${#databases_list[@]}; i++ ))
do
  tables_list=($(${mysql_cmd} -e "SELECT table_name FROM information_schema.tables WHERE table_schema=\"${databases_list[i]}\";"))
  database_name=${databases_list[i]/-/@002d}

  for (( table=0; table<${#tables_list[@]}; table++ ))
  do
    ${mysql_cmd} -e "alter table \`${databases_list[i]}\`.${tables_list[table]} discard tablespace;"
    rm -f ${mysql_data_dir}/${database_name}/${tables_list[table]}.ibd
    cp ${back_data_dir}/${database_name}/${tables_list[table]}.ibd ${mysql_data_dir}/${database_name}/
    chown -R ${mysql_user}.${mysql_user} ${mysql_data_dir}/${database_name}/
    ${mysql_cmd} -e "alter table \`${databases_list[i]}\`.${tables_list[table]} import tablespace;"
    sleep 5
  done
done

通過shell腳本導(dǎo)出mysql所有庫的所有表的表結(jié)構(gòu)

mysql_cmd和dump_cmd的變量值根據(jù)實(shí)際環(huán)境修改，修改成自己用戶名，用戶密碼，主機(jī)ip
databases_list只排除了mysql的系統(tǒng)庫，如果需要排除其他庫，可以修改egrep -v后面的值
導(dǎo)出的表結(jié)構(gòu)以庫名來命名，并且加入了CREATE DATABASE IF NOT EXISTS語句

#!/bin/bash
base_dir=$(cd `dirname $0`; pwd)
mysql_cmd="mysql -N -uroot -proot -h192.168.70.49"
dump_cmd="mysqldump -uroot -proot -h192.168.70.49"
databases_list=($(${mysql_cmd} -e 'SHOW DATABASES;' | egrep -v 'information_schema|mysql|performance_schema|sys'))

for (( i=0; i<${#databases_list[@]}; i++ ))
do
  tables_list=($(${mysql_cmd} -e "SELECT table_name FROM information_schema.tables WHERE table_schema=\"${databases_list[i]}\";"))

  [[ ! -f "${base_dir}/${databases_list[i]}.sql" ]] || rm -f ${base_dir}/${databases_list[i]}.sql
  echo "CREATE DATABASE IF NOT EXISTS \`${databases_list[i]}\`;" >> ${base_dir}/${databases_list[i]}.sql
  echo "USE \`${databases_list[i]}\`;" >> ${base_dir}/${databases_list[i]}.sql

  for (( table=0; table<${#tables_list[@]}; table++ ))
  do
    ${dump_cmd} -d ${databases_list[i]} ${tables_list[table]} >> ${base_dir}/${databases_list[i]}.sql
  done
done

以上就是MySQL利用frm文件和ibd文件恢復(fù)表結(jié)構(gòu)和表數(shù)據(jù)的詳細(xì)內(nèi)容，更多關(guān)于MySQL frm ibd恢復(fù)表結(jié)構(gòu)和數(shù)據(jù)的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: