How to Repair InnoDB Tables in MySQL?

Starting from MySQL Server version 5.5 (2010), InnoDB is the default storage engine in MySQL. The InnoDB tables provide advanced features, like B-Tree indexes, row-level locking, clustered indexes, Adaptive Hash indexing, foreign keys, and more.

These InnoDB tables can get corrupted due to various reasons. When the InnoDB tables are corrupted or damaged, you may encounter corruption-related errors, like: 

• [InnoDB] Server was killed when InnoDB redo logging was disabled. Data files could be corrupt. You can try to restart the database with innodb_force_recovery=6

• InnoDB: Checksum mismatch in data file

• 130109 9:46:41 InnoDB: unable to find a record to delete-mark

• ERROR 1030 (HY000): Got error 100 - 'InnoDB error' from storage engine

In this article, we will discuss the reasons for corruption in InnoDB tables and possible methods to repair them.

Causes of Corruption in InnoDB Tables

Corruption in InnoDB tables can occur due to one of the following reasons:

• Power failure stops MySQL Server when writing to the database table

• Bugs in MySQL code or operating system 

• Hardware failure

• The system, hosting the MySQL Server, is shut down unexpectedly

• MySQL service crashes after reboot

Show variables lIKE 'log_error';

Alternatively, you can run the CHECK TABLE command in MySQL to check MySQL database tables for corruption. This command checks the table views and their indexes.

If it finds any issues, it displays an error with the table name. Before running this command, ensure you have all the necessary permissions, such as SELECT, ALTER, etc. Here’s how to run the CHECK TABLE command:

CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option: {
    FOR UPGRADE
  | QUICK
  | FAST
  | MEDIUM
  | EXTENDED
  | CHANGED
}

Methods to repair InnoDB Tables in MySQL

Once you have found the corrupted InnoDB tables, you can rebuild the tables using the ALTER TABLE command. This command recreates the tables and indexes. It helps resolve minor corruption or dictionary issues in InnoDB tables. If this doesn’t work, then follow the below-mentioned methods.

1. Restore from Backups

If you have an updated and readable backup copy, then you can restore the MySQL database using the mysqldump utility. To verify backup in MySQL, you can run the validate command.

Then, restore the database using the mysqldump utility by following the steps given below:

a) First, create an empty database to save the restored database. Here’s the command:

‘mysql > create db_name’

b) Then, restore the database using the following command:

mysql -u root -p db_name < dump.sql

c) 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. Use dump and restore method

You can rebuild InnoDB tables by dumping and reloading them. It helps in repairing the tables, including indexes. 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 severely corrupted InnoDB tables. In such a case, you can use Force InnoDB Recovery with the innodb_force_recovery setting (range 0–6) to control startup behavior and allow the server to start even when corrupt tables exist.

[mysqld]
Innodb_force_recovery=1
service mysql restart

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 the innodb_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 comand below:

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:

DROP TABLE table_name;

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, put a comment in the line to disable the InnoDB recovery mode.

#innodb_force_recovery=...

Now, save the applied changes to the my.cnf file and then restart the MySQL Server.

Use a Professional Repair Tool

To repair multiple corrupt InnoDB tables at once or the entire database, you can use an advanced MySQL database repair tool, like Stellar Repair for MySQL. The tool can repair severely corrupt InnoDB tables with absolute precision. It can recover all the objects, including tables, foreign keys, and

primary keys, with complete integrity. It also supports selective recovery of database objects and allows you to save the repaired files in multiple formats. Also, it is compatible with both Windows and Linux operating systems.

Conclusion

Above, we have learned the methods to repair InnoDB tables in MySQL. You can restore the corrupt tables from backup. However, if the backup is not available, then rebuild the InnoDB tables with the ALTER TABLE command or use the Dump and Reload method.

If the MySQL crashes due to corruption in tables, then you can use Innodb_force_recovery to repair the MySQL database. To repair InnoDB tables with no data loss, you can use a professional MySQL repair tool.

It can quickly repair the corrupt MySQL database and recover all the tables and other objects with complete integrity.