How to recover SQL just from .frm and .ibd files

Mehran Mahouti 1 Reputation point
2021-05-07T14:30:07.447+00:00

Last weak we have a data center migration and during that migration, the server on which my websites are located was damaged, data center managed to recover the folders and files inside, but it was able to recover the database information as .MYD, .MYI, .frm and .ibd, not as .sql.

What did I do:
Mysql version is exactly the same as in the old server
Data copied to /var/lib/mysql/ folder
Files with .MYD and .MYI extensions are automatically detected by MySQL
Note: Authorization should be given to the database user again.

Problem:
I had a problem with .frm and .ibd extensions,. Although the table names of the .frm and .ibd files were detected by MySQL, the data they contained could not be accessed.

Long Solution (really long)

  • WAMP server installed on the computer
  • A new database has been created
  • Tables created manually one by one (really annoying)
  • Run the "Discard Tablespace" command for each created table (this will delete the linked .ibd file)
  • The old .ibd file has been copied to the database folder (in my case: C:\wamp64\bin\mysql\mysql5.7.31\data\dr_erkan_aksoy)
  • Finally, the "import tablespace" command was run for each table.

Quick Solution (I would love to have found this solution from the beginning because I spent hours doing the above-mentioned operations).

  • I downloaded the Demo version of Aryson MySQL Database Repair software (https://www.arysontechnologies.com/mysql-database-repair.html).
  • I clicked the "OPEN" option from the menus and showed the path to the database folder in the window that appeared.
  • In just a few seconds it saw all the tables and also all the data in them !!!
  • I can see the data in the demo version, and cannot recover all, but when I saw that the data could be recovered, I got the license without thinking and was able to recover the data after program activation (Thank God).

The Long solution can help you when you have a few broken table but if there are 50+ tables broken, it can take nearly a day to recover :)

I hope it can help anyone with the same problem.

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
35,882 questions