0

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!

2 Answers 2

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.

1
  • Thank you for the information! We have asked the backup from the client. Since I am new to MySQL I just wanted to be sure that just ibdata1 file won't help to restore the database easily. Apr 30, 2021 at 11:02
0

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!

1
  • Thank you for the information! We have asked the backup from the client. Your suggestion is helpful. Apr 30, 2021 at 11:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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