cannot recover mysql idb file I recently have an hdd failure and I try to restore Griffith (https://github.com/micjahn/Griffith) database from a /var/lib/mysql/griffith file copy. My new computer uses a more recent version of MySQL:


original :  5.7.19-0ubuntu
new :       5.7.20-0ubuntu


I read many a thread about innoDB database recovery and I succeeded to restore many tables but the main table (movies) fails to get restored.

Here is what I do:


create a new database
find table structure with https://recovery.twindb.com/
recreate tables
use for all tables:

ALTER TABLE mytable DISCARD TABLESPACE;

copy backuped files
use for all tables:

ALTER TABLE mytable IMPORT TABLESPACE;



On the last step on the movies table, it fails and mysql server terminates.

After some analysis, I found out that the original database was created with utf8 encoding (twindb script was using latin1). But it does not fix the issue.

I also changed mysql config with:

innodb_force_recovery=6


It helps, doing a select count(*) gives some info, but I cannot dump the table to make a real backup.

In mysql logs I have this error when running the IMPORT TABLESPACE command: 


[...many errors and warnings...]
[ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found.
[ERROR] InnoDB: Fetch of persistent statistics requested for table `griffith`.`movies` 
but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats 
are not present or have unexpected structure. Using transient stats instead.
[ERROR] InnoDB: Trying to access page number 1647523377 in space 238, space name 
griffith/movies, which is outside the tablespace bounds. 
Byte offset 0, len 16384, i/o type read. 
If you get this error at mysqld startup, please check that your my.cnf
matches the ibdata files that you have in the MySQL server.
[ERROR] InnoDB: Server exits.



Can someone help me?
Looks like one of the pages in movies.ibd is corrupt, so it can't read it.

I see two options here.

1) There is a script fetch_data.sh that can copy as many records as possible from a corrupt InnoDB table into a MyISAM table. Start MySQL with innodb_force_recovery=6, create a MyISAM table with same structure and use the script to copy records.

2) From the same toolkit use c_parser/stream_parser to fetch records from movies.ibd. You can't import it anyway, because it's corrupt.

Ah, and as a TwinDB author I can suggest one more option :-) - use https://recovery.twindb.com/ to recover movies.ibd. But it's going to cost $$$.