Restoring corrupted InnoDB MySQL databases

Recently my Zabbix MySQL database was corrupted. Unfortunately I’ve needed historical data (database backup was too old), so there was only one way: restore everything I can from corrupted database. On the other hand I had every table in the separate file (innodb_file_per_table=1 in my.cnf), which was very helpful.

There are three ways to restore corrupted InnoDB databases (you should decide which one to choose, sometimes You will need to use not only one):

• manually importing files to newly created database
• using Percona InnoDB recovery tools
• using innodb_force_recovery

For above methods You will need to have files from Your datadir (for example: /var/lib/mysql), so copy it somwhere.

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)
) ENGINE=InnoDB;

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

use corrupted;
ALTER TABLE maintenances DISCARD TABLESPACE;

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;
ALTER TABLE maintenances IMPORT TABLESPACE;

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

There will be some output and on the end there should be:

SYS_INDEXES is updated successfully

Now we can repair ibdata1 file:

./innochecksum -f /var/lib/mysql/ibdata1

Repeat this step until there will be no output.

Now You can start MySQL daemon again and You should be able to dump this table, if not follow instructions to see the last method.

Use innodb_force_recovery

In this method we will just copy table file and power up MySQL with innodb_force_recovery parameter. Here are the steps:

Change MySQL configuration. In [mysqld] section set datadir to Your copy of MySQL files, and set innodb_force_recovery parameter to 6:

datadir=/var/lib/mysql

innodb_force_recovery=6

Restart MySQL and You should be able to dump all corrupted tables by mysqldump.

Hope this post will help You. If You have some questions please leave comment below.