Manually importing files

For this method You need to have ibd files from MySQL’s datadir and You need to know how was the table created (whole create command).

First step is to create new database, so login to MySQL and create it:

create database corrupted;

Now create table:

use corrupted;
CREATE TABLE `maintenances` (
	`maintenanceid`          bigint unsigned                           NOT NULL,
	`name`                   varchar(128)    DEFAULT ''                NOT NULL,
	`maintenance_type`       integer         DEFAULT '0'               NOT NULL,
	`description`            text                                      NOT NULL,
	`active_since`           integer         DEFAULT '0'               NOT NULL,
	`active_till`            integer         DEFAULT '0'               NOT NULL,
	PRIMARY KEY (maintenanceid)

And here is a tricky part – You need to discard tablespace by invoking this command in MySQL:

use corrupted;

Next step is to copy old file to correct place (using OS shell, not MySQL):

cp /var/lib/mysql-old/zabbix/maintenances.ibd /var/lib/mysql/corrupted/

After that You need to login to MySQL again and import new tablespace:

use corrupted;

In same cases after above steps You will be able to dump this table using mysqldump tool, but it is very often that MySQL will produce this error:

ERROR 1030 (HY000): Got error -1 from storage engine

After that simple go to MySQL log file and see why it is happening. In my case it was:

InnoDB: Error: tablespace id in file './zabbix/maintenances.ibd' is 263, but in the InnoDB data dictionary it is 5.

If the above error occurred You need to start from the beginning but with another method.

Percona InnoDB recovery tools

First You need  those tools – simply visit percona site and download it, unpack it and build those tools (You will find more info how to do this inside this archive). After that You are ready to repair above MySQL error. To do this follow next steps:

Drop table from corrupted database, and create it again (the same way as it was created before).

Stop MySQL daemon! – it is necessary.

Copy table file (overwrite it):

cp /var/lib/mysql-old/zabbix/maintenances.ibd /var/lib/mysql/corrupted/

Use ibdconnect:

./ibdconnect -o /var/lib/mysql/ibdata1 -f /var/lib/mysql/corrupted/maintenances.ibd -d zabbix -t maintenances

Continue reading