A Quick Guide to Repair Corrupt InnoDB Tables

Leave a reply

InnoDB is the default storage engine in MySQL 5.5 and later versions. It is a crash-safe storage engine that provides crash recovery mechanism. It ensures data consistency within the database even after unexpected system crash. However, the InnoDB tables within the database get corrupted and unreadable due to various reasons. This article will outline the common causes behind corruption in InnoDB tables and mention the solutions to repair them.

What causes Corruption in InnoDB Tables?

InnoDB tables can get corrupt due to one or more of the following reasons:

  • Issues in hard disk where the database is saved. 
  • MySQL server instance restarts suddenly.
  • Bugs in MySQL code.
  • MySQL process gets killed in the middle of writing data to the hard disk.
  • System crashes due to sudden power failure.
  • Insufficient storage space on the hard disk.
  • Malware infection in the system hosting the database.

How to Detect Corruption in InnoDB Tables?

You can use the CHECK TABLE command in MySQL to check InnoDB tables for corruption. It checks the table views and their references, including indexes. It supports InnoDB SPATIAL indexes and Secondary indexes. It also performs R-tree validity check in tables. If it detects any issue, it displays error with the table name. If the InnoDB table and its page (DB_TRX_ID) in a clustered index is corrupted, the server crashes to prevent further use of the table. Here’s how to run the CHECK TABLE command in MySQL Server:

CHECK TABLE tbl_name [, tbl_name] ... [option] ...

option: {
    FOR UPGRADE
  | QUICK
  | FAST
  | MEDIUM
  | EXTENDED
  | CHANGED
}

Here are some things to consider before using the CHECK TABLE command:

  • Make sure you have permissions to use the CHECK TABLE command.
  • If you have a partitioned table, then you are required to add an ALTER TABLE statement. For this, make sure you have all File permissions.
  • The CHECK TABLE statement does not detect all data from the InnoDB file, such as header information. You can use the innochecksum command to check the entire file.
  • If there is a large-sized InnoDB table, the CHECK TABLE command can block threads and process, if InnoDB detects semaphore wait of 240 seconds. You can use the CHECK TABLE QUICK command.

Methods to Repair InnoDB Table in MySQL

Once you have identified the corrupted InnoDB tables, follow the below methods to repair them.

1 – Restore from Backup

Restoring from backup is one of the most convenient methods. If you have an updated backup copy, then you can restore the MySQL database using the mysqldump utility. Here’s how to use this utility to restore the database:

  • First, create an empty database to save the restored database. Here’s the command:
‘mysql > create db_name’
  • Then, restore the database using the following command:
mysql -u root -p db_name < dump.sql
  • It will restore all the objects of the database. You can check the restored InnoDB tables by using the below command:
‘mysql> use db_name;
mysql > show tables;’

2 – Rebuild InnoDB Tables

You can rebuild InnoDB tables by dumping and reloading them. It helps in repairing the indexes within the InnoDB tables. Here are the steps to do so:

Step 1: Restart the MySQL Service

  • In the Run window, type services.msc.
  • In the Services window, find and right-click on the MySQL Service.
  • Click Restart service.

Note: MySQL can crash and fail to start when it tries to access the corrupt InnoDB tables. In such a case, you can use Force InnoDB recovery to rebuild the database. InnoDB has innodb_force_recovery settings to control MySQL behavior during startup. It ranges from 0-6. You can use this option to allow the MySQL server to start even when corrupt tables exist.

Step 2: Use Innodb_force_recovery to Start MySQL Server

First, you need to enable the Innodb_force_recovery option from the configuration file. To do so, follow the below steps:

Search for the configuration file (my.cnf). The my.cnf file’s location varies depending on the operating system. In Windows, the configuration file is located in ‘/etc’ directory. The default path is /etc/mysql/my.cnf.

Once you found the my.cnf file, go to the [mysqld] section and then insert the below statements:

[mysqld]
Innodb_force_recovery=1
service mysql restart

Note: The default value of innodb_force_recovery is 0. However, you can change its value to ‘1’ to start the InnoDB engine and dump the tables. Dumping tables with “innodb_force_recovery value” of 4 or higher can lead to data loss. So, it is recommended that a database backup be created first before proceeding.

  • Enabling theinnodb_force_recovery allows you to access the corrupt table. Next, dump the table data by using the mysqldump command as given below:
mysqldump -u user -p database_name table_name > single_dbtable_dump.sql
  • Next, export all the databases to the dump.sql file by executing the below command:
mysqldump --all-databases --add-drop-database --add-drop-table > dump.sql
  • Now, restart the MySQL Server and then use the DROP DATABASE command to drop the database. To use this command, you require the DROP privileges. So, check and grant the database privileges.
  • If it fails to drop the database, then run the below commands to delete the database manually:
cd /var/lib/mysql
rm -rf db_name
  • Next, disable the InnoDB recovery mode by commenting on the following line in [mysqld]:
#innodb_force_recovery=...
  • Now, save the applied changes to the my.cnf file and then restart the MySQL Server.

However, if the above solutions fail to repair the InnoDB tables, then you can repair them using a third-party MySQL repair tool, like Stellar Repair for MySQL. This tool can repair highly corrupted or damaged InnoDB/MyISAM tables, with complete integrity and precision. The tool supports Windows and Linux operating systems. You can download the demo version of the tool to scan the MySQL database and preview the repairable objects. This will help you to check the tool’s functionality.

Conclusion

InnoDB tables can get corrupted due to several reasons. Above, we have explained the common reasons for corruption in MySQL databases. We have also mentioned the methods to repair and recover the corrupted InnoDB tables in MySQL database. You can use force InnoDB recovery mode to access the damaged tables. However, it may take a lot of time and result in data loss. To repair corrupt InnoDB tables without any data loss, you can opt for a third-party MySQL database recovery software developed by Stellar Data Recovery. It can help you effortlessly repair the damaged MySQL database with complete integrity. The tool helps resolve all types of MySQL corruption errors.

Leave a Reply