Dealing with Data Corruption in InnoDB Storage Engine of MySQL

MySQL is an open-source relational database management system RDBMS that supports two diverse types of storage engines - MyISAM and InnoDB. The implementation of the InnoDB storage engine is certainly more complicated than MyISAM as it was developed to conform to the ACID model, which is in fact a de-facto standard in the world of databases. These features, including the ability to recover data after a system crash, make the InnoDB storage engine very robust. However, like other storage engines, it is prone to data corruption. The data may get corrupted due to various factors and reasons.

Causes of InnoDB Table Corruption

The common causes that can lead to table and data corruption are:

• The server hosting MySQL engine suddenly shuts down due to a power failure or hardware failure.
• A hardware error on storage or memory error may lead to corruption when the storage engine is writing data to the table.
• Bugs in the MySQL software or in the operating system can lead to data corruption.
• InnoDB table corruption issue occurs more frequently on Windows if the database runs in a virtualized environment.

How to Deal and Fix Data Corruption in InnoDB Storage Engine in MySQL?

When dealing with  InnoDB table corruption, it is suggested to perform the following checks, before attempting any solution. Also, take a backup of your data.

The InnoDB storage engine offers an error log, called MySQL Server Log. You can open the log and read its content to look for information about the error.

Also, you can execute the following commands in the MySQL Shell to find the list of affected tables.

If the MySQL is currently running, use the Check Table command.

Check Table

If the MySQL Server crashes and cannot start, perform the check using the command Innochecksum. The syntax is:

innochecksum [options]

After these checks, you can start fixing corrupted data. Follow these steps:

• Restart the MySQL service
• In the search dialog box, type Services.msc and press Enter.

• In the Services window, search for MySQL Service (MySQL80) and right-click on it.
• Click Stop and then Start or click on Restart.

• Enable the innodb_force_recovery option

If you cannot access the MySQL Server, enable the innodb_force_recovery option by following these steps:

• Locate the my.cnf option file and search for the [mysqld] section in this file.
• Add the key: Innodb_force_recovery=1.
• Save the option file.

Now, try again to restart the MySQL Server.

• Dump data (export data)

If MySQL Server service starts, you can access the corrupted table. In this case, you can dump the data.

To dump data, you can use the dedicated command - mysqldump.

mysqldump -u /user -p dbname tablename [ … tablename ] > dumped_data.sql /user

• Drop the corrupted table

Once the data is dumped, drop the source table. From the Shell, execute the DROP TABLE command:

mysql -u /user -p –execute = "DROP TABLE db_name.tablename
/user

• Recreate table and import the data back

Restore the table from the dumped data using the below command:

mysql -u /user -p < dumped_data.sql
/user

• Disable the innodb_force_recovery option

Stop the MySQL service and then disable InnoDB recovery mode. For this, follow the below steps:

• Locate the my.cnf option file and search inside this file for the [mysqld] section.
• Remove the key: Innodb_force_recovery or set the value to 0.
• Save the option file.

Now, start the MySQL service.

Alternative Solution to Repair Corrupt InnoDB Table in MySQL

The above procedure has two major disadvantages. First, to perform the procedure, you must have a good understanding of the working logic of MySQL. Second, there is a risk of losing data.

To overcome these limitations, you can use a dedicated MySQL recovery software, such as Stellar Repair for MySQL

Key features of this powerful, yet easy-to-use software are:

• It is able to recover not only the data stored in table but also recovers all the MySQL objects, including keys, data types, triggers, and views.
• It supports both the storage engines used in MySQL.
• It can repair InnoDB tables and MySQL tables.
• It is capable of handling virtually all kinds of data corruption errors.
• It supports MySQL databases created on Windows operating system and Linux operating system.
• It can process multiple corrupted MySQL database files in a single program execution.

To Conclude

MySQL, when used with the InnoDB storage engine, is a very robust RDBMS and provides a good level of reliability against data corruption. For example, it is able to recover data after a system crash. However, data corruption is still possible under several circumstances. Above, we have discussed the causes that can lead to data corruption in MySQL. We have also mentioned a stepwise procedure to manually recover data in case a table stored in InnoDB format is corrupt. However, the manual procedure has some possible disadvantages and may result in data loss. To avoid these, you can use Stellar Repair for MySQL - a software specifically developed to repair MySQL database and recover all the data.