My WAMP directory accidentally get deleted by another user. Only data folder in MySQLis available. And, in that only database-folders (folders in "\bin\mysql\mysql5.6.12\data\" with name of databases) are available. All files including "ibdata1" in root of "\bin\mysql\mysql5.6.12\data\" are also deleted.

The database folders contains files with below extensions only.

*.frm, *.ibd

and "db.opt" file.

How the databases can be recovered?

I already have tried to recover bdata1. But, unable to get it back. And, some database contains MYISAM also.



For a MyISAM table mydb.mytable, you should have three files

  • \bin\mysql\mysql5.6.12\data\mydb\mytable.frm
  • \bin\mysql\mysql5.6.12\data\mydb\mytable.MYD
  • \bin\mysql\mysql5.6.12\data\mydb\mytable.MYI

They should already be accessible as a table since each file contains needed data, metadata, and index info. Collectively, they form the table. There are no external storage engine mecahnisms to access.


Take a look at this Pictorial Representation of InnoDB

InnoDB Architecture

The only thing that attaches ibdata1 to the .ibd files is the data dictionary.

Your mission, should you decide to accept it, is to create each table and swap in the .ibd

Before you do anything, make a full copy of "\bin\mysql\mysql5.6.12\data" to another

Here is a sample

Suppose you have a database mydb with the table mytable. This means

  • You have the folder \bin\mysql\mysql5.6.12\data\mydb
  • Inside that folder, you have
    • mytable.frm
    • mytable.ibd

You need the .frm. If you look at my post How can extract the table schema from just the .frm file?, you can download a MySQL utility that can generate the SQL needed to create the table.

You should now do the following

  • Move mytable.ibd to \bin\mysql\mysql5.6.12\data
  • Run the SQL to create the InnoDB table
  • Login to mysql and run ALTER TABLE mydb.mytable DISCARD TABLESPACE; (This will delete \bin\mysql\mysql5.6.12\data\mydb\mytable.ibd)
  • Copy \bin\mysql\mysql5.6.12\data\mytable.ibd into \bin\mysql\mysql5.6.12\data\mydb
  • Login to mysql and run ALTER TABLE mydb.mytable IMPORT TABLESPACE; (This will register \bin\mysql\mysql5.6.12\data\mydb\mytable.ibd into the data dictionary)

After this, the table mydb.mytable should be fully accessible. You can test that accessibility by simply running:

SELECT * FROM mydb.mytable LIMIT 10;

Give it a Try !!!

DRINK (Data Recovery Incorporates Necessary Knowledge) Responsibly

| improve this answer | |
  • To complete this great answer, for innodb you will need to remove any FK constraint that references the table before discarding its tablespace. After the import, you should recreate the FK. – SebaGra Sep 12 '17 at 19:06
  • after the table space import the information_schema.key_column_usage (probably other tables as well) will return no data on the first select so you have to execute at least one query and wait a few seconds before the next query can work. (mysql Ver 14.14 Distrib 5.7.19, for linux-glibc2.12 (x86_64) using EditLine wrapper) – user3338098 Sep 10 '19 at 19:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.