MyISAM
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.
InnoDB
Take a look at this Pictorial Representation of InnoDB
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
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
Best Answer
If your client is unable to give you a proper database dump, then whatever task they're asking of you will take far longer and cost much more than anyone should be willing to tolerate.
Is there a way I can restore the database with only ibdata1 file?
⇢ Yes, but not without a remarkable amount of work. People who are not trying to save a multi-billion dollar company from lawsuits should never have to do this.
Shall I need any other files apart from .frm files and ibdata1 file in order to restore the database?
⇢ the
.frmfiles contain the definition of a database. Theibdata1file can contain many databases (depending on how MySQL was configured). If you must restore the database with only theibdata1and.frmfiles, you will need all of the files in their correct directories. From there you may be able to restore so long as your version of MySQL is either exactly the same or just slightly newer than the version the client is running.Note that restoring
ibdataand.frmfiles from a foreign MySQL installation will obliterate any databases you already have configured on the receiving machine.Suppose I can restore the database using just ibdata1 file or I get .frm files in future, Do I need to install a version that matches the client's MySQL version?
⇢ This was answered in the previous bit. While it's technically feasible to take an
ibdata1and accompanying.frmfiles from earlier versions of MySQL and upgrade them, there can be problems when jumping a large number of releases. Going from MySQL 5.3.x to 8.0.24, for example, is almost guaranteed to fail unless the source database is structured more like a CSV than a series of relational tables.If your client is able to access the MySQL server, have them create a proper backup with MySQLDump and provide that file to you. This way you do not have to worry as much about versions or anything else. Everybody will save time and hassle.