Innodb – Mysql innodb recover from frm and ibd

innodbmariadbrecovery

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.

Best Answer

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/

Related Question