Posted in:

Quick Solution to Repair Corrupt InnoDB Table in MySQL

InnoDB is a default storage engine in MySQL 5.5 and later advanced versions. It supports crash recovery and can only get corrupted sometimes. However, sometimes it might get corrupted due to unforeseen reasons like hardware failure, bugs in the MySQL code or OS, MySQL Server crash, etc.

When the InnoDB table gets damaged, the data stored within it turns inaccessible. Hence, it should be repaired ASAP to make the InnoDB table accessible again.

Consequently, today we have equipped this user-friendly guide to help those users who want to repair corrupt InnoDB Table in MySQL & perform MySQL recovery in no time.

Thus, if you are one such user, don’t skip a single stanza of this post to get rid of InnoDB table corruption.

Major Factors Behind the Corruption of InnoDB Table in MySQL

There could be several reasons for the InnoDB tables to become corrupt. 

But below, we have shortlisted the common causes behind the InnoDB table corruption in MySQL. Let’s take a look:

  • When PC abruptly shuts down or stops working 
  • Due to automatically restarting the MySQL Server 
  • Crashing MySQL service after reboot
  • Hardware failure
  • There is a bug in the MySQL code or operating system
  • MySQL server stopped in the middle of database table writing process.

So, as you are aware of the causes, now it’s time to know how to repair corrupted InnoDB table in MySQL with possible solutions.

Measures Need to Take Before Repairing the Corrupt InnoDB Table

  • Create a Proper Backup

It’s highly suggested to create a valid backup of your vital database before you repair the InnoDB table. This is because resolving this issue by following the below manual ways can lead to data loss. 

  • Check the Error Log Files

Checking the MySQL error log assists to identify the corruption in an affected table. There are two ways to check the InnoDB file for errors. However, it completely depends upon the situation to use the below methods:

Case 1– If the MySQL Server is running then you can use the command “CHECK TABLE”. This command will eventually help you to find out errors in the InnoDB table. 

Here, is the command to run:

mysql> CHECK TABLE table_name;

Case 2- If the MySQL Server has crashed or is offline, you can use the Innochecksum. This will assist you to check the error for the page on the InnoDB table.

Here, is the command to run:

shell> innochecksum [options] file_name

How to Repair Corrupt InnoDB Table in MySQL Manually?

In order to repair corrupted innodb table manually, follow the below step-by-step methods:

Step 1- Just restart MySQL service and then attempt to access MySQL server. Here’s how you can perform this:

  • Press the ‘Win’ + ‘R’ keys simultaneously to open Run dialog box.
  • There, type in services.msc >> hit Enter.
  • Under the Services box, you have to search for the MySQL Service & right-click on it.
  • Now, click on restart or start service.

In case, if the server is not accessible, you can force InnoDB recovery to do so. For this, it is necessary to enable the option ‘innodb_force_recovery’ in MySQL my.cnf configuration file via executing the following:

  • Just open a configuration file & add the below command line in [mysqld] section:

[mysqld]

innodb_force_recovery=1

  • In this instance, you have to save & close my.cnf file.
  • Now, attempt to restart MySQL service.

But if you can access a corrupt table, simply dump your InnoDB table data via running the below mysqldump command directly:

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

Although, you can easily dump numerous tables that are separated by the whitespace in an above-given command line.

  • After that, drop a table from db by running the below command:

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

  • Attempt restoring a table with the help of the newly generated dump file in MySQL:

mysql -u user -p < single_dbtable_dump.sql

Step 2- Now, stop MySQL service >> disable the InnoDB recovery option by entering the below command in the section of [mysqld]:

#innodb_force_recovery=…

Step 3- Here, save all the that has been made to my.cnf file >> restart MySQL service via running the below command line:

service mysqld start

Finally, you can perform MySQL database recovery from dump (backup file) which you’ve created now.

Alternative Solution for Repairing Corrupt InnoDB Tables

If you want to automatically repair a corrupted InnoDB table in MySQL, consider using the most powerful MySQL database repair software like Stellar Repair for MySQL.

Not only InnoDB, but this utility can also repair corrupt MyISAM table. 

Even it repairs severely damaged MySQL databases and restores their stuff with ease.

Apart from that, it previews the repaired database stuff after scanning the corrupted MySQL database.

All you need to do is to download and install Stellar Repair for MySQL tool on your PC & follow the below steps to know how it works:

  1. Run the Stellar Repair for MySQL on your computer.
  2. Here, you have to select the version of MySQL from the dialog box “Select Data Folder” >> click OK option.
  3. After this, select the corrupt MySQL database from the dialog box “Select Data Folder” >> click on Repair to initiate the repairing process.
  4. At this instance, the tool will show fixed table & other recoverable items on your screen.
  5. Here, simply choose table(s) that you wish to restore >> then go to File menu >> click Save option.
  6. From a Save Database dialog box, you have to choose MySQL in the Save As >> enter the required credentials to immediately connect to the Server. 
  7. Finally, click on Save.

Now, you can easily find your repaired file in the same location with a different name where the original database file is stored.

Final Verdict

InnoDB table corruption in MySQL is not a common issue. However, it can sometimes corrupt due to some unexpected reasons & make the data saved within it inaccessible. 

Regardless of the reason behind the InnoDB table corruption, it can be easily fixed by applying the solutions mentioned above in this post. 

However, the best solution among them is – Stellar Repair for MySQL. It can repair corrupt InnoDB tables as well as MyISAM tables in a hassle-free manner.

Moreover, to avoid further InnoDB file corruption, it is essential to follow the below preventive measures:

  • Don’t forget to specify a primary key for each table while using the often-queried columns. 
  • It is recommended that sets associated with the DML tasks be grouped into transactions.
  • Running the MySQL Server from the command prompt will be more efficient when troubleshooting the Innodb issues rather than from mysqld_safe or Windows service.
  • Enable the option “innodb_file_per_table” to place indexes & data for individual tables in separate files.
  • Also, enable the InnoDB Monitors to acquire appropriate information regarding the current issue.