CODEWITHSUNDEEP
mysql
Sophia
Sophia

Reputation: 172

mysql .ibd file deleted. I want to DB data recovery

Because of my computer system error, Some MySql .ibd file deleted in MySql data folder. And there is only the .frm file left. So, Tablespace is missing for table 'reservation'.'question'. error has occurred.

reservation is database. question is table.

I tested the following method to restore data.

First method:

1.Take a backup of .frm file.

2.Create the table using the SQL queries.

3.The following command was executed.

SELECT * FROM reservation.answer2;
ALTER TABLE reservation.answer2 DISCARD TABLESPACE;
ALTER TABLE reservation.answer2 IMPORT TABLESPACE;

The result of this method was not data recovery.

Second Method

1.I have added a property to my.ini

 [mysqld]
 innodb_force_recovery = 1

2.And the following command was executed.

mysqldump -u root -p --lock-all-tables reservation.question > c:\dump20171009.sql;

'--lock-all-tables' for adding this is due to 'when using LOCK TABLES Operation failed with exitcode 2' error.

This command occurred this error.

mysqldump: Couldn't execute 'show create table question': Tablespace is missin g for table reservation.question. (1812)

Aren't there any alternatives?

Is data recovery impossible without .ibd file? I want to data recovery in 'reservation.question'.

Upvotes: 0

Views: 2407

Answers (1)

user149341
user149341

Reputation:

The .frm file only contains data on the structure of the table, not its contents. The .ibd file holds the contents of the table -- if it was deleted, your data is gone.

Upvotes: 2

Related Questions