4

We have a data loss: the MySQL database is dropped and we have to restore the tables from ibd files. The backup is too old so we have to undelete the deleted database files. Most of the tables are restored but 3 of them are corrupted.

We use InnoDB storage driver.

  • I restored the table structure,
  • then I dropped the tablespace of the table,
  • and then I copied the restored InnoDB files to the database,
  • and then I tried to load the original ibd file by
alter table tbl import tablespace

Most of the ibd files can be restored by this way, but 3 of them are corrupted: how can I fix them? I would like to load all of the recoverable data of the table. How can I fix the ibd file to load it?

I got a following error when I tried to load 34GB ibd datafile:

alter table xmlobjects import tablespace;
ERROR 1034 (HY000): Incorrect key file for table 'xmlobjects'; try to repair it

With innochecksum I get the following error:

innochecksum -v /var/lib/mysql/FET_TDB/xmlobjects.ibd 
InnoDB offline file checksum utility.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
verbose                           TRUE
debug                             FALSE
count                             FALSE
start-page                        0
end-page                          0
page                              0
file /var/lib/mysql/FET_TDB/xmlobjects.ibd = 36423331840 bytes (2223103 pages)...
InnoChecksum; checking pages in range 0 to 2223102
page 959 okay: 0.043% done
page 6783 okay: 0.305% done
page 12735 okay: 0.573% done
page 18559 okay: 0.835% done
page 24575 okay: 1.105% done
page 30527 okay: 1.373% done
page 36287 okay: 1.632% done

...

page 2213695 okay: 99.577% done
page 2221247 okay: 99.917% done
Error; bytes read (12288) doesn't match page size (16384)

mysql error log:

2017-09-15 08:57:55 1 [Warning] IP address '172.17.0.1' could not be resolved: Name or service not known
2017-09-15 08:57:55 1 [Warning] InnoDB: Table '"FET_TDB"."xmlobjects"' tablespace is set as discarded.
2017-09-15 08:57:55 7f80bbeaf700 InnoDB: cannot calculate statistics for table "FET_TDB"."xmlobjects" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
2017-09-15 08:57:55 1 [ERROR] InnoDB: File size 36423331840 is not a multiple of the page size 16384
2017-09-15 08:57:55 1 [Note] InnoDB: Discarding tablespace of table "FET_TDB"."xmlobjects": Data structure corruption

Other tables:

alter table xmlindex__mo import tablespace;
ERROR 2013 (HY000): Lost connection to MySQL server during query

And I see the following messages in the log file:

2017-09-15 08:39:52 1 [Warning] IP address '172.17.0.1' could not be resolved: Name or service not known
2017-09-15 08:39:52 1 [Warning] InnoDB: Table '"FET_TDB"."xmlindex__mo"' tablespace is set as discarded.
2017-09-15 08:39:52 7f33f0570700 InnoDB: cannot calculate statistics for table "FET_TDB"."xmlindex__mo" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html

ls -la /var/lib/mysql/FET_TDB/xmlindex__mo.ibd 
-rw-rw---- 1 mysql mysql 2759852032 Sep 15 08:39 /var/lib/mysql/FET_TDB/xmlindex__mo.ibd

checksum output seems to be correct:

innochecksum -v /var/lib/mysql/FET_TDB/xmlindex__mo.ibd 
InnoDB offline file checksum utility.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
verbose                           TRUE
debug                             FALSE
count                             FALSE
start-page                        0
end-page                          0
page                              0
file /var/lib/mysql/FET_TDB/xmlindex__mo.ibd = 2759852032 bytes (168448 pages)...
InnoChecksum; checking pages in range 0 to 168447
page 9983 okay: 5.927% done
page 26623 okay: 15.805% done
page 32639 okay: 19.377% done
page 38399 okay: 22.796% done
page 44223 okay: 26.254% done
page 49215 okay: 29.217% done
page 60543 okay: 35.942% done
page 66239 okay: 39.324% done
page 72127 okay: 42.819% done
page 86719 okay: 51.482% done
page 92671 okay: 55.015% done
page 98559 okay: 58.511% done
page 105087 okay: 62.386% done
page 139903 okay: 83.055% done
page 159359 okay: 94.605% done
0

You may try this: https://recovery.twindb.com/

Or their tool (that I didn't manage to build against Linux...): https://github.com/chhabhaiya/undrop-for-innodb (Seems it's been dropped: https://twindb.com/recover-corrupt-mysql-database/)

  • The website doesn't recover dropped database yet(I'm the author btw). – akuzminsky Oct 5 '17 at 6:19
0

You can also try Stellar Phoenix database repair for MySQL tool: https://www.stellarinfo.com/mysql-repair.php

I am not the representative of this brand but it comes up with the demo version which shows the preview of repaired data. you can check it before buying its registered version.

  • I'm looking for a live person who successfully recovered database with this tool. – akuzminsky Oct 5 '17 at 6:26
  • 2
    So far their representatives leave spam comments on my website. People who bought this tool come then to us because stellar's tool couldn't do the job. – akuzminsky Oct 5 '17 at 6:28
0

Undeleted files are always corrupted. What you should do is parse MySQL partition with stream_parser (google undrop-for-innodb) and fetch records from index pages.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.