Restore database with only ibdata1 file

All we need is an easy explanation of the problem, so here it is.

I need to analyze the client’s data and for that requirement client has provided us only ibdata1 file having size around 150MB.

I am not aware of table structure and number of tables as of now. After some research I found that I will need .frm files in order to recreate the tables.

I have below questions:

  1. Is there a way I can restore the database with only ibdata1 file?

  2. Shall I need any other files apart from .frm files and ibdata1 file in order to restore the database?

  3. 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?

Thank you!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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 .frm files contain the definition of a database. The ibdata1 file can contain many databases (depending on how MySQL was configured). If you must restore the database with only the ibdata1 and .frm files, 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 ibdata and .frm files 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 ibdata1 and accompanying .frm files 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.

Method 2

I need to analyze the client’s data and for that requirement client has provided us only ibdata1 file …

Completely useless.
You asked them for a book so they’ve taken a copy of the book, cut out all of the words and sent them to you in a great, big, cardboard box. If you’re very, very lucky, you might be able to put it all back together into something like the original book, but it’s far, far easier for them to just send you a proper copy of the complete book!

Go back to the client and ask them for a proper, consistent "dump" of the database that you can "restore" onto your system. (Hint, they should be using mysqldump to create this).

If they cannot offer such a sensible option, then tell them how much it’s going to cost you (i.e. them) for you to "reverse engineer" the data out of this [useless] file they’ve sent you – that should focus their minds!

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply