CODEWITHSUNDEEP
mysqldatabase-schemadatabase-administration
confusedMind
confusedMind

Reputation: 2653

Issue recovering table from .ibd file MySql

MySQL server suddenly stopped working and the service would go on and off and I could not take backup as the service would not run so best I could do is Copy the whole DATA folder .

Also I had a earlier backup so using that along with the ibd files i restored all the table except one for one last table its saying :

ERROR 1808 (HY000): Schema mismatch (Clustered index 
validation failed. Because the .cfg file is missing, table 
definition of the IBD file could be different. Or the data file 
itself is already corrupted.)

And I have no clue what to do.

Upvotes: 1

Views: 2462

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 563021

I think the only thing you can do is rely on the version of the table from your backup. You cannot restore the .ibd file you copied.

The error message is saying one of two things happened:

  • The table structure changed since the backup was made, so the .ibd file no longer matches the metadata stored in the data dictionary. When the content of the tablespace file doesn't match the metadata, InnoDB is in the same boat as you are: "I have no clue what to do."

  • Even if the table structure did not change, the .ibd file was physically corrupted enough that it cannot be read by InnoDB.

Either way, that tablespace file cannot be read by InnoDB.

Obviously this creates a problem if you restored all the other tables successfully. Now you have more current data in most of your tables, except for the last one, which is outdated. If there are any rows in these tables that reference each other, they may have orphaned records (for example, a record showing a certain user bought a product, but the user doesn't exist in the users table).

That's unfortunate, and difficult to correct.

In general, copying .ibd files is not a reliable way of backing up an InnoDB database. You need to use proper backup tools like mysqldump or Percona XtraBackup.

Another solution to restore data after your last backup is to use point in time recovery using binary log files. But for that to work, you would need all the binary log files since your most recent backup, and the backup would need information about the binary log position when the backup was taken. See https://dev.mysql.com/doc/refman/en/point-in-time-recovery.html

Upvotes: 1

Related Questions