EDIT:我创建了一个简单的脚本,它执行下面描述的所有步骤:https://ourstickys.com/recover.sh https://ourstickys.com/recover.sh
老问题,但我发现这个更简单的方法:https://dba.stackexchange.com/questions/16875/restore-table-from-frm-and-ibd-file 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/ https://dev.mysql.com/downloads/utilities/
我还找到了一种更快的方法来获取CREATE TABLE
通过使用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
其次是:
mysql -uUSER -p recover_db < recover.sql
如果需要,您也可以在单行程序中执行它:
./dbsake frmdump /path/to/*.frm | mysql -uUSER -p recover_db
此时您可以按照上述第 3 点开始的说明进行操作。