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?
Best Answer
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 $$$.