Best Ways to Repair Corrupt InnoDB Table in MySQL

Summary: In this post, we will discuss some of the best ways to repair a corrupt InnoDB table in MySQL, which may get corrupted due to hardware failure, MySQL Server crash, and other reasons. These ways comprise a step-wise procedure to repair corrupted InnoDB table manually or by using MySQL repair software.

Starting from MySQL 5.5 version, InnoDB is used as the default storage engine for all the new tables in a MySQL database. The great thing about InnoDB is that it supports crash recovery. Thus, it cannot get corrupted easily, but when it does, it may happen due to MySQL Server crash after a reboot, hardware failure or bugs in the underlying platform, etc.

When an InnoDB table gets corrupted, the data within it becomes unreadable and inaccessible. And, an attempt to read from a corrupted table can cause the MySQL Server to crash.

How to Repair Corrupt InnoDB Table in MySQL?

Note: Before attempting to repair the corrupted InnoDB table, make sure to back up your MySQL database files first.

Following are the ways using which you can repair corrupt InnoDB database table

1. Fix Corrupted InnoDB Table Manually

You will rarely need to repair InnoDB tables, as the storage engine helps perform crash recovery that can fix most issues once the server gets restarted. However, there may arise a situation where you will need to manually rebuild or repair a corrupt table. Here’s how you can do it:

Step 1: Restart the MySQL Service

Restart the MySQL Service, to check if you can access the server, by performing the following:

1. Open the Run window by clicking ‘Windows+R’ keys together.
2. Type msc and hit Enter.
3. From the Services window, look for MySQL Service and right-click on it, and then click restart.

If the server is accessible, skip to Step 3. But if it won’t start or continues to crash, use the innodb_force_recovery option explained in the next step.

Step 2: Force InnoDB Recovery

Enable the innodb_force_recovery option in MySQL my.cnf configuration file to regain access to the MySQL database and its corrupt table. To do so, follow these steps:

• Open my.cnf file and add the following line of code in the [mysqld] section:

[mysqld]
Innodb_force_recovery=1

• Save and close the configuration file.
• Attempt to restart the MySQL service again.

Step 3: Drop the Corrupt Table

Once you can access the corrupt table, dump the table data to a new file using the following mysqldump command:

mysqldump –u user –p db_name table_name > single_dbtable_dump.sql

Note: Add the table name separated by whitespace in the above command to dump multiple tables.

Now, drop the corrupt table from the database:

mysql –u user –p –execute=”DROP TABLE db_name.table_name”

Step 4: Restore the Table

Use the newly created dump file to restore the table from the dump (i.e., single_dbtable_dump) file:

mysql –u user –p < single_dbtable_dump.sql

Step 5: Restart MySQL in Normal Mode

When the corrupted table is repaired, stop the MySQL Service and disable InnoDB recovery mode by entering the following line in the [mysqld] section:

#innodb_force_recovery=...

Now restart the MySQL Service in normal mode.

Note: If you haven’t used the innodb_force_recovery option, just stop and restart the server in normal mode.

2. Fix Corrupted InnoDB Table by Using MySQL Repair Software

Using ‘innodb_force_recovery’ value greater than 4 to regain access to the database can lead to data corruption. Also, in cases of severe corruption, you may fail to restart your MySQL server. You can overcome these limitations of manually repairing the corrupt table of MySQL database by using specialized MySQL repair software such as Stellar Repair for MySQL.

The software can repair and restore InnoDB tables of MySQL database while keeping its data intact. Also, it helps recover table properties, keys, relationship constraints, etc.

This MySQL database repair utility helps repair both InnoDB and MyISAM tables created on Windows and Linux OS-based systems.

Steps to Repair Corrupt InnoDB Table using the software are as follows:

1. Free download, install and run Stellar Repair for MySQL
2. From the Select Data Folder dialog box, select MySQL version you are using, and then click OK.
3. From the Select Database box, choose the database that you want to repair, and then click Repair.
4. A preview of the repaired database, table, and other recoverable components is displayed in the software preview window.
5. Select the table(s) you want to restore, and then click Save on File menu.
6. From the Save Database box, choose MySQL under Save As, and enter the credentials required to Connect to Server. Click Save.

You can find the repaired database file in a new folder at the same location where your original database is stored.

You can find the repaired database file in a new folder at the same location where your original database is stored.

Conclusion

Corruption in the InnoDB table can make the data within it unreadable, and if not dealt with time, it may even result in a server crash and unplanned downtime. You can fix the corrupt InnoDB table by following the manual step-wise procedure. However, the manual process to repair InnoDB tables can result in data loss and may not deliver the expected outcome.

However, by using a MySQL database repair tool such as Stellar Repair for MySQL, you can repair tables on the InnoDB storage engine and restore it to its original functional state. This file repair utility helps repair MySQL database created on both Windows and Linux OS. It can not only repair InnoDB tables but can also fix corruption in MyISAM tables of MySQL database.