The corruption in InnoDB tables can cause various errors and issues in MySQL. Some errors like “InnoDB: cannot open table db/site from the internal data dictionary of InnoDB through the .frm file for the table exists”, error 1146: Table ‘xxxx” doesn’t exist, if the InnoDB tables is marked as corrupt in the error message, and more indicates the server fails to read the table in database. It can occur due to corruption in the MySQL tables. There are so many factors like sudden power failure, bugs in the operating system, hard-ware or software failure, which may result in corruption in database. In this article, we’ll learn step-wise instructions to repair InnoDB Tables to help you resolve the maximum errors that take place due to corruption.
How to repair InnoDB Tables in MySQL?
Following are the step-wise methods you can select to rebuild MySQL InnoDB tables:
1- Use Alter Table command:
You can first try renaming the InnoDB table with ALTER TABLE command to rebuild it. When you give a new name to the table, it forces the InnoDB table to recreate the table and rebuild the data in a table. Also, if you run ALTER TABLE command, it defragment an InnoDB table. This method sometimes works to resolve corruption issues in InnoDB tables in MySQL. Here’s the command that you can use to rebuild the InnoDB table using the ALTER TABLE statement in the MySQL server:
Note: Make sure you have ALTER, CREATE, and INSERT privileges for the table.
ALTER TABLE tbl_name ENGINE=INNODB
If the above method fails then follow next step.
2: Use Dump and reload method:
One of the methods to rebuild MySQL InnoDB tables is by dumping and reloading them. Using this method, you can fix the errors in MySQL that appears due to the corrupt indexes in the InnoDB tables. Here are the steps you are required to follow to do this:
First, Restart the MySQL Server.
service mysql restart
Note: Sometimes, you may fail to start the MySQL or Server crashes abundantly when it detects corrupt InnoDB tables. Here, you can use Force InnoDB recovery to rebuild your MySQL database. MySQL search engine has Innodb_force_recovery settings to customize the MySQL behavior during the startup process. You can use these settings (whose range is from 0-6.) to allow the server to start even if it detect corrupt tables.
Step 2: Use Innodb_force_recovery settings to Start MySQL Server
By default the Inndb_force_recovery option is not enabled. You have to enable this option from the configuration file. To do this, here are the steps:
First locate to the configuration file(my.cnf) on your computer where the MySQL server is saved. The location of the configuration file may differ with the operating system you are using. In Windows, you can find the configuration file in is saved in ‘/etc’ directory by following the path i.e. /etc/mysql/my.cnf.
In etc directory, open the my.cnf file, locate to the [mysqld] section and then change the existing statements to the following statements:
[mysqld]
Innodb_force_recovery=1
service mysql restart
The above step helps you to start the InnoDB engine and then dump the tables. You can set the innodb_force_recovery value more than 4. But if you add the value more than 4 then it may lead to data loss. This is the reason why it has been recommended to create the backup first and then proceed.
Once you enabled the innodb_force_recovery, you can easily access the corrupt or damaged InnoDB table.
Next, use the below command to dump the table data with the help of mysqldump command:
mysqldump -u user -p database_name table_name > single_dbtable_dump.sql
After this, use the following command to export all the databases to the dump.sql file:
mysqldump –all-databases –add-drop-database –add-drop-table > dump.sql
Next, try restarting the MySQL server and then drop your database using the DROP DATABASE command.
After this, disable the InnoDB recovery mode in the [mysqld] by commenting on the given- below for example:
#innodb_force_recovery=…
After this, save all the changes you did in the my.cnf file and then restart your MySQL Server.
Method 3-Use a professional MySQL repair tool
If the above rebuild methods fail to rebuild the InnoDB tables then you can use a professional MySQL database recovery software like Stellar Repair for MySQL. The tool is one of the best MySQL repair tool, trusted by MVPs, to repair MySQL tables. It can repair and restore all data from the corrupt InnoDB tables and save them to a new MySQL file. The tool is capable to repair InnoDB and MyISAM tables created in both Linux and Windows system. You can even download the tool in your system with the Linux operating system. It can repair large-sized inaccessible or crashed table due to corruption in just a few simple steps.
Conclusion
You may fail to access the corrupt or damaged InnoDB tables, which can lead to interruption in accessing the database and impacting the workflow. You can follow the methods discussed above to rebuild the InnoDB tables in MySQL. To save you time, the best method is to use a professional MySQL repair tool like Stellar Repair for MySQL. This tool is specially built to repair InnoDB and MyISAM tables in MySQL with complete consistency, accuracy, and precision.