Recovering MySql tables from .ibd files: an ongoing challenge

Question

The MySQL server suddenly stopped working, the service would be intermittent, I couldn't do a backup because the service wasn't running, so the best I could do was copy the entire DATA folder.

Also, I had an older backup, so using that along with the ibd file restored all the tables except the last one, which says:

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.)

I do not know what to do.

Answer 1

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

The error message indicates that one of two things has occurred:

• The table structure has changed since the backup, so the .ibd file no longer matches the metadata stored in the data dictionary. InnoDB is in the same boat as you when the contents of a tablespace file don't match the metadata: "I don't know what to do."

• Even if the table structure has not changed, physical damage to the .ibd file is enough to make InnoDB unreadable.

Either way, InnoDB cannot read the tablespace file.

Obviously this will create problems if you successfully restore all other tables. There is now more recent data in most of the tables, except for the last table, which is out of date. If there are rows in these tables that reference each other, they may have orphan records (for example, a record showing that a user purchased a product, but that user does not exist in the Users table).

This is unfortunate and difficult to correct.

Generally speaking, copying .ibd files is not a reliable way to back up an InnoDB database. You need to use an appropriate backup tool such as mysqldump or Percona XtraBackup.

Another solution for recovering data after the last backup is to use binary log files for point-in-time recovery. But to achieve this, you need all the binary log files since the most recent backup, and the backup needs information about the location of the binary logs at the time the backup was taken. See

https://dev.mysql.com/doc /refman/en/point-in-time-recovery.html