MySQL is one of the widely used relational database management systems (RDBMS) for web applications and enterprise software. When the database gets corrupted, you may fail to run queries on the tables or even connect to the storage engine. To recover a MySQL database, the quickest and most reliable solution is to extract the data files from backup.
In this article, we’ll guide you through the exact steps to repair and recover a corrupt MySQL database with native methods. And if they fail, we’ve mentioned an advanced MySQL repair tool that can help you recover corrupt database files without causing data loss.
What happens when Corruption is Detected in MySQL Database?
When MySQL Server detects database corruption, its behavior depends largely on the storage engine used to create tables. The InnoDB engine provides more data consistency. If it detects corruption in any of the pages in table files on your disk while performing a write operation, it uses crash recovery mechanism and crashes your server to prevent the corruption from spreading. On the other hand, when the MyISAM engine detects data file damage, it keeps running but displays the error marking the table as crashed. The complete error message displays as “Table ‘abd’ is marked as crashed and should be repaired.”
What to do Immediately to Recover MySQL Database after Corruption?
To recover a corrupt database, you should first try restoring the backup. For that, you can use mysqldump or the import option in PhpMyAdmin. If you have the .frm and .ibd files handy, you can even try restoring data directly from them, as they contain the table schema, data, and indexes. If the backup file is not available, then first check the storage engine of the MySQL database using the following command:
SHOW TABLE STATUS;
Checking the engine status is important because the repair method varies with the storage engine. Once you confirmed the engine type, then follow the methods mentioned below accordingly:
Use InnoDB Force Recovery for InnoDB Tables
If your MySQL Server fails to start due to corruption, then you can change the Innodb_force_recovery setting in the configuration file. But try to set its value to 3 or lower. If this fails to start your server, add/increase the value to 4 or more. But note that doing so can cause data loss. Once your server starts, then use the dump and reload method to repair the InnoDB tables. Alternatively, you can use the ALTER TABLE and OPTIMIZE TABLE commands that can help you repair minor corruption in InnoDB files.
Repair MyISAM Tables with MySQL Native Methods
If your MyISAM tables become corrupted in MySQL, you can rebuild them using the REPAIR TABLE command:
REPAIR TABLE table_name;
If this fails to work, use the command-line utility to repair or rebuild the MyISAM tables. You can run this command only if your tables are not partitioned. Also, make sure you first stop the MySQL Server.
myisamchk –recover table_name
Next, start the MySQL Server.
You can even try using the open source tool – phpMyAdmin with the XAMPP application for Windows to repair the MyISAM tables. However, this tool gives desired outcome only if the size of the database file is small.
Limitations of Native MyISAM Repair Methods
Below are the some limitations of using REPAIR TABLE, myisamchk, and phpMyAdmin to fix corrupted MyISAM tables.
- These methods may fail if the table’s definition (.frm) file is missing or tables are severely corrupted.
- Using PhpMyAdmin for large-sized tables can result in time-out or crash issues.
- Using myisamchk requires MySQL Server shutdown.
- Running myisamchk on a live server or encountering a mid-repair power failure can cause data loss.
- Repairing large-sized indexes in tables using manual methods requires free disk space.
- The native commands and phpMyAdmin’s interface may fail on partitioned tables.
Overcoming Native Tool Limitations with Stellar Repair for MySQL
To avoid the limitations of the native repair tools, you can use a professional MySQL repair tool -Stellar Repair for MySQL. It is one of the MVPs recommended tools that helps you repair and restore MySQL database tables irrespective of the storage engine. It supports repairing of both InnoDB and MyISAM tables with complete integrity. Also, it supports repairing of partitioned tables unlike native methods. You can even use the tool to repair large-sized tables without any time-out or crash issues. The tool helps you recover all the objects from the damaged .frm, .ibdata, .ibd, .myd, and .myi files.
Conclusion
Your MySQL database can easily get corrupted due to numerous reasons. You can choose the recovery method according to the storage engine and severity of corruption. In case backups are not present, then use an advanced MySQL repair tool, such as Stellar Repair for MySQL. The tool can help you repair severely corrupted databases regardless of the size.