0

There are numerous question (in this site and elsewhere on the internet) about how to recover innodb database from *.frm and *.ibd file (the point: when you have the undamaged ibdata1).

Situation:

I assigned to a server where the mysql database is just crashed and can't started. To let them start, i saved the whole /var/lib/mysql folder, then deleted the ibdata1, ib_logfile0, and ib_logfile101, in order to let the mysql server start.

That's worked, MyISAM and some InnoDB table got back to online. However there's some databases where some InnoDb table can't back to operation.

It turned out, that there's no backup from the databases (and they operating 3+ years). Everybody excepted that plesk makes dayli database backup, but actually not (Schrödinger's backup).

After this, it's also turned out that the ibdata1 I saved has been previously overwritten or deleted and a new one created by the mysql (I view the file with hexedit and it's filled fully with zeros). I also looked for the previous file in the lost+found but no result.

There are two database which is crutial to restore, one wordpress site and the plesk database.

Then i copied the saved mysql directory to my machine and started trying to restore the broken tables.

Problem:

I have a crashed MySQL database where the ibdata1 file has benn lost and no database backup available. On my machine is the same mariadb version installed, so i moved the save mysql directory from the server and started to restore as much as i can from the two databases requested by the owner.

I already wrote the innodb_force_recovery = 6 line into /etc/mysql/my.cnf to help recover the broken tables.
As described here: Corrupt InnoDB: Start mysqld only innodb_force_recovery=6

Then i tried to reimport the *.ibd files as described here: https://medium.com/magebit/recover-innodb-database-from-frm-and-ibd-files-99fdb0deccad [1]. An that's worked on smaller tables. On other bigger tables like wp_posts when execute ALTER TABLE wp_posts IMPORT TABLESPACE the mysql server crashes:
InnoDB: Trying to access page number 68160 in space 15 space name admin_wordpress/wp_posts, which is outside the tablespace bounds. Byte offset 0, len 16384 i/o type 10.

So the Question: How to restore tables with this kind failure? What i found:

1) https://twindb.com/recover-corrupt-mysql-database/ For this i need ibdata1. So it's a dead end.

2) Percona data recovery tool for innodb Theorically this can connect the existing *.ibd to the new ibdata1 However i can't find binary package, the debian package doesn't contains ibdconnect command. I also found the source: https://github.com/jiramot/percona-data-recovery-tool-for-innodb but this can't be compiled. I tried to resolv the compilation an linking errors but this might tacke more time and maybe this solution doesnot work.

3) And the last chance: Rebuild the site. I can extract string from the ibd files strings admin_wordpress/wp_posts.ibd and that way extract the text of the posts on the wordpress page, nut rebiulding a site this way is a Sisyphean task. (And this mathod can't applied to the other database is should restore)

I there any other working method to extract or restore data from *.ibd files? Even extracting the records to eg.: JSON or BSON or CSV is gonna be good enought to move forward in the recovering process.

[1]: The external site says: drop the database, recreate the empty tables by using mysqlfrm on the broken database *.frm files (this results create table statements). Then execute ALTER TABLE "table" DISCARD TABLESPACE to release the newly created *.ibd file. Move the old *.ibd to the server directory, then execute ALTER TABLE "table" IMPORT TABLESPACE to acquire the old tablespace and therefore recover the data.

Improve this question

1 Answer 1

Reset to default
0

There is a way to fix this issue but it may not work all the time, but still it worth a try (note take a backup first):

  • execute the following command mysqlfrm –server=root:PWD@machine_ip –port=xxxx “/path/to/mytable.frm” > “/destination/path/recovered_mytable.sql”
  • This will generate create table script you can you can execute it to create the table
  • remove new tablespace for mysql alter table mytable drop tablespace;
  • copy the original one to the new table location for example cp /home/mysql/x.ibd /mysql/data/mydb/
  • reactivate your tablespace alter table mytable import tablespace;

for more information about mysqlfrm please refer to https://docs.oracle.com/cd/E17952_01/mysql-utilities-1.4-en/mysqlfrm.html file can be downloaded from https://downloads.mysql.com/archives/utilities/

Improve this answer
2
  • Yes i did this, with the rest of the tables it worked, but some are failed. See in the post at: "when execute ALTER TABLE wp_posts IMPORT TABLESPACE the mysql server crashes: InnoDB: Trying to access page number 68160 in space 15 space name admin_wordpress/wp_posts, which is outside the tablespace bounds. Byte offset 0, len 16384 i/o type 10."
    – Dankó Dávid
    Jun 25, 2019 at 14:32
  • if you are using innodb_force_recovery=6 inside my.cnf and you tried to recover your database from the server side not using 3rd party tool. you can try the following script to fix your database. bazaar.launchpad.net/~percona-dev/…
    – Ahmad Abuhasna
    Jun 27, 2019 at 6:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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