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.