mysql - 如何为仅包含 ibdata 和 *.ibd 文件的 MySQL InnoDB 表重新创建 FRM 文件？

Question

这个问题与我在 stackoverflow 上看到的相关 InnoDB 修复问题略有不同。

假设我已经使用 innodb_file_per_table=1 在我的 MySQL 5.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: 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?

如何重建 FRM 文件？

Answer 1

编辑:我创建了一个简单的脚本来执行下面描述的所有步骤:https://ourstickys.com/recover.sh

---

老问题，但我发现这个更简单的方法:https://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.

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

---

我还找到了一种使用 dbsake 获取 CREATE TABLE 的更快方法:

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

然后:

#only one table
./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 点开始的说明进行操作。