Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What if I delete ibdata1 in mysql (LINUX)

Tags:

mysql

I am using LINUX and I have mistakenly deleted ibdata1 file in mysql folder.

I restarted the 'mysqld' service and Now when I am trying to read data from the databases it is not able to find out older tables.

Am I in a big trouble? Please throw some light on this if any idea.

Thanks for your time.

Thanks and Regards, SachinJadhav.

like image 258
SachinJadhav Avatar asked Feb 08 '10 15:02

SachinJadhav


People also ask

Is it safe to delete ibdata1?

Save this answer. Show activity on this post. ibdata1 contains InnoDB dictionary which is vitally important for InnoDB. If you delete it any access to a table will fail with Table doesn't exist error.

How do I recover my ibdata1 file?

Forcing InnoDB recovery may help you dump and export the affected databases. You can then drop the databases and restart the server, which will recreate the ibdata1 file. Next, import the databases from the dump file back into your MySQL server and restore the database.

What is ibdata1 in MySQL?

The file ibdata1 is the system tablespace for the InnoDB infrastructure. It contains several classes for information vital for InnoDB. Table Data Pages. Table Index Pages. Data Dictionary.

Can I delete .IBD file?

You must not delete an . ibd file if you want to avoid data loss or downtime. These . ibd files contain your data and indexes.


2 Answers

You are in big trouble...

By default ibdata1 contains all the table's data (and therefore is lost). Even if you configure it to use a separate file per table, all internal MySQL's data is stored in that file, and as far as I know, there is no way to restore it.

Really hope you have a backup....

like image 175
Johnco Avatar answered Sep 23 '22 01:09

Johnco


Unless you defined innodb_file_per_table, this is a single tablespace that along with the transaction logs (stored in ib_logfile*) keep your table data.

Deleting it will effictively delete all data stored in the InnoDB tables.

like image 41
Quassnoi Avatar answered Sep 24 '22 01:09

Quassnoi