如何为只有ibdata和* .ibd文件的MySQL InnoDB表重新创建FRM文件？ [英] How do I recreate an FRM file for an MySQL InnoDB table with only the ibdata and *.ibd files?

问题描述

假设我已经在我的MySQL 5.1数据库中恢复了以下内容：innodb_file_per_table = 1：

  db / tablename.ibd 
 innodb / ibdata1 
 innodb / ib_logfile0 
 innodb / ib_logfile1 
  

我失去了 db / tablename.frm 文件。我可以启动数据库服务器，但InnoDB抱怨：

  110723 13:26:33 InnoDB：Error：table'db / tablename '
 InnoDB：在InnoDB数据字典中有表空间ID为5943，
 InnoDB：但是具有该id或名称的表空间不存在。有
 InnoDB：你删除或移动了.ibd文件？ 
  

如何重建FRM文件？

解决方案

EDIT ：我创建了一个简单的脚本，执行以下所有步骤： https://ourstickys.com/recover.sh

---

old问题，但我发现这个更简单的方法： http：// dba.stackexchange.com/questions/16875/restore-table-from-frm-and-ibd-file

 我已经使用MySQL Utilites和MariaDB 10恢复了我的MySQL 5.5 * .ibd和* .frm文件。
 
 1）生成创建SQL。 
您可以从frm文件获取创建sql的。您必须使用：https：//dev.mysql.com/doc/mysql-utilities/1.5/en/mysqlfrm.html 
 
 shell> mysqlfrm --server = root：pass @ localhost：3306 c：\MY\t1.frm --port = 3310 
 
其他方式你可以创建sql。 
 
 2）创建表
在数据库上创建表。 
 
 3）alter table xxx丢弃表空间
丢弃要替换* .ibd文件的表。 
 
 4）将您的* .ibd文件（MySQL或MariaDB）复制到MariaDB的数据路径
首先我尝试使用MySQL 5.5和5.6来重新启动，但数据库崩溃并立即停止表空间id损坏的错误。 （错误1030（HY000）：从存储引擎得到错误-1）
我使用MariaDB 10.1.8后，我已成功恢复我的数据。 
 
 5）alter table xxx import tablespace 
当您运行此语句时，MariaDB警告文件，但它不重要，因为恢复您的数据:)数据库仍然继续，您可以看到您的数据。 
 
我希望这些信息对您有所帮助。 
  

让我补充一点，你可以在这里下载mysqlfrm： https://dev.mysql.com/downloads/utilities/

---

我也找到了一种更快的方法来使用 dbsake 来获得 CREATE TABLE >：

  curl -s http://get.dbsake.net> dbsake 
 chmod u + x dbsake 
  

那么：

  #only一个表
 ./dbsake frmdump /path/to/table.frm> recover.sql 
 
 #multiple tables 
 ./dbsake frmdump /path/to/*.frm> recover.sql 
  

后跟：

  mysql -uUSER -p recover_db< recover.sql 
  

您也可以在一个班轮中执行：

  ./ dbsake frmdump /path/to/*.frm | mysql -uUSER -p recover_db 
  

此时，您可以按照上述指示从第3点开始。 / p>

This is a slightly different question than the related InnoDB repair questions I have seen on stackoverflow.

Assume that I have restored the following in my MySQL 5.1 database with innodb_file_per_table=1:

db/tablename.ibd
innodb/ibdata1
innodb/ib_logfile0
innodb/ib_logfile1

I have lost the db/tablename.frm file. I can start the database server, but InnoDB complains:

110723 13:26:33  InnoDB: Error: table 'db/tablename'
InnoDB: in InnoDB data dictionary has tablespace id 5943,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?

How can I reconstruct the FRM file?

解决方案

EDIT: I created a simple script that does all the steps described below: https://ourstickys.com/recover.sh

---

old question, but I found this easier way to do it: http://dba.stackexchange.com/questions/16875/restore-table-from-frm-and-ibd-file

I have recovered my MySQL 5.5 *.ibd and *.frm files with using MySQL Utilites and MariaDB 10.

1) Generating Create SQLs.
You can get your create sql's from frm file. You must use : https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqlfrm.html

shell> mysqlfrm --server=root:pass@localhost:3306 c:\MY\t1.frm --port=3310

Other way you may have your create sql's.

2) Create Your Tables
Create your tables on the database.

3) alter table xxx discard tablespace
Discard your tables which do you want to replace your *.ibd files.

4) Copy your *.ibd files (MySQL Or MariaDB) to MariaDB's data path
First i try to use MySQL 5.5 and 5.6 to restrore, but database crashes and immediately stops about tablespace id broken error. (ERROR 1030 (HY000): Got error -1 from storage engine) 
After i have used MariaDB 10.1.8, and i have succesfully recovered my data.

5) alter table xxx import tablespace
When you run this statement, MariaDB warns about file but its not important than to recover your data :) Database still continues and you can see your data.

I hope this information will helpful for you.

Let me add that you can download the mysqlfrm here: https://dev.mysql.com/downloads/utilities/

---

I also found a faster way to get the CREATE TABLE by using dbsake:

curl -s http://get.dbsake.net > dbsake
chmod u+x dbsake

then:

#only one table
./dbsake frmdump /path/to/table.frm > recover.sql

#multiple tables
./dbsake frmdump /path/to/*.frm > recover.sql

followed by:

mysql -uUSER -p recover_db < recover.sql

you can also execute it in a one liner if you want:

./dbsake frmdump /path/to/*.frm | mysql -uUSER -p recover_db

at this point you can follow the above instructions from point 3 on.

这篇关于如何为只有ibdata和* .ibd文件的MySQL InnoDB表重新创建FRM文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！