How to Recover InnoDB Corruption for MySQL

What is InnoDB in MySQL?

InnoDB is a storage engine in MySQL that has replaced MyISAM. It is the default storage engine in MySQL 5.7 and later versions. It has various advantages over MyISAM. It has crash recovery functionality that prevents data loss. Moreover, it helps optimize queries related to the primary keys by arranging data on disk. The InnoDB storage engine enhances performance and consistency.

What Leads to InnoDB Corruption?

InnoDB is an advanced storage engine, but it can get corrupted. Before choosing any reliable method to repair corrupt InnoDB table in MySQL, you must learn the root cause of file corruption. Numerous factors are responsible for the InnoDB corruption. Below we mention some of the most prominent reasons for file corruption.

• It might be possible that the MySQL services get crashed after reboot or it has been restarted automatically.
• When the database storage is full or has a bad sector, you may have difficulty accessing the database items.
• Any hardware failure is one of the major causes of file corruption.
• Sudden power loss or abrupt shutdown also leads to MySQL database file corruption.

These are a few reasons for database file corruption. You can repair corrupt InnoDB tables in MySQL with both manual and professional solutions. We will discuss both methods but first, let us check a few important things that you need to do before fixing the database corruption.

Few Things to Do Before You Recover InnoDB Corruption for MySQL

Before you start the InnoDB database recovery, you need to ensure a few things. It will help you resolve the issue easily and effectively.

#1. Check MySQL Server Log

The best way to identify the root cause of the problem is by checking the server error log. It provides relevant information regarding the InnoDB error.

#2. Check the Integrity of MySQL Table using the CHECK TABLE Command

• If you want to check the integrity of the MySQL table, you can use the CHECK TABLE command.

mysql> CHECK TABLE table_name;

• This command is suitable when your MySQL server is running. However, if the server is offline, the following command will work well.

shell> innochecksum [options] file_name

Now, it is time to check how to repair InnoDB table in MySQL.

How to Repair MySQL InnoDB Table in MySQL

MySQL provides you with numerous techniques to recover corrupted InnoDB data. It has various options to restore crashed data. Moreover, the ‘Dump and Reload’ technique allows you to rebuild corrupted MySQL tables. Apart from that, many other methods help you to recover data from heavily corrupted MySQL database files.

Step 1: Restart the MySQL Server

• Press Win+R to open the Run
• Type msc and press the Enter It will open the services.
• Check for MySQL services. Right-click on it and select either Start, Stop, or Restart as per the conditions.

By performing this step, you can identify whether the InnoDB table is accessible or not. If you can access the server, perform step 3. Otherwise, follow the below step 2.

Step 2: Use Force Recovery of InnoDB

To recover InnoDB corruption for MySQL, you have to enable the innodb_force_recovery functionality in my. cnf file (configuration file) of the MySQL server. Follow the below procedure.

• Open the configuration file of the MySQL server.
• Search for the ‘mysqld’ section and add the below line.

innodb_foce_recovery=1

• Save the updated my.cnf file. Restart the MySQL services.

If you can access the corrupted table, you can use the mysqldump command to dump the table.

Step 3: Dump the Corrupted Table

• Open MySQL Workbench and type the following command with the appropriate details.

mysqldump -u user -p database_name table_name > single_dbtable_dump.sql

• You can dump multiple tables at once by using the same command. Enter the names of all desired tables separated by whitespace.
• Now, execute the below command to drop the corrupted tables.

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

Step 4: Restore MySQL Table

• Restore the data to the new dump file created (single_dbtable_dump) in the previous step.

mysql -u user -p < single_dbtable_dump.sql

Step 5: Disable InnoDB Recovery Mode

After you successfully recover the corrupted MySQL table, you need to disable the InnoDB force recovery. To do so, follow the below procedure.

• Open my.cnf file in a text editor.
• Search for the mysqld section and type the following command.

#innodb_force_recovery=…

• Save the file with updated details. It will disable the InnoDB recovery mode.
• Now, restart the MySQL services by using the following command.

service mysqld start

The above procedure is the complete solution to recover InnoDB corruption for MySQL. After following these steps, anyone can easily repair MySQL InnoDB table corruption. However, as you can see, the above procedure requires technical proficiency. Without prior expertise, no one can restore their valuable data.

So, what should a novice user do to repair MySQL InnoDB table corruption? The answer is the automated MySQL Database Recovery Tool.

Automated Solution to Recover InnoDB Corruption for MySQL

DRS MySQL Database Recovery Tool is an easy-to-use and reliable utility to recover Innodb table corruption in MySQL. It helps you to restore tables, queries, indexes, stored procedures, keys, triggers, views, etc. This application offers multiple file recovery modes to restore data from heavily corrupted files. Moreover, it allows you to export the recovered data to the database and save it as SQL script. Therefore, it is a complete solution to repair corrupted/inaccessible MySQL databases.

Conclusion

A corrupted MySQL InnoDB storage table can be recovered by using different approaches. The manual method is suitable when you are a technical expert because you need to perform complex and tedious manual steps. However, if you are a novice or non-technical user, you can still repair a corrupted MySQL database file using the professional Tool mentioned above.