There are a number of reasons, such as sudden system crash, power failure, hardware issues, or bugs in the MySQL that can cause MySQL database (MyISAM/InnoDB table files) corruption. When it happens, you may fail to start tables or sometimes accessing their data using queries causes errors. In this article, we will explain some easy to follow methods to repair InnoDB/MyISAM tables in MySQL. Additionally, we will also cover how to export a corrupt MySQL database to SQL scripts after repair.
Common Causes of MySQL Database Corruption
Corruption in InnoDB (.ibd files, ibdata1) /MyISAM (.MYD, .MYI, .frm) files occurs due to the following reasons:
- Hardware failures
- Incomplete write operations during heavy transactions
- Corrupt or truncated InnoDB redo logs
- File system errors or storage device failure
- Accidentally or intentionally used KILL statements during active transactions to force the MySQL server process to terminate
- Power failure stops MySQL Server when writing to the database table
- Bugs in MySQL code or operating system
- The system, hosting the MySQL Server, is shut down unexpectedly
- MySQL service crashes after reboot
Common Signs of MySQL Database Corruption
Here are some issues and errors that indicate your database is corrupted:
- Tables marked as crashed — Table './dbname/tablename' is marked as crashed
- mysqldump failing with errors mid-export
- CHECK TABLE or REPAIR TABLE returning errors
- InnoDB: "page corruption" errors in MySQL error logs
- Queries returning incomplete or no results from specific tables
- MySQL service failing to start due to corrupt ibdata1 or redo logs
Methods to Repair MySQL Database
To export a corrupted MySQL database, it is important to repair it first so that the data can be accessed effortlessly. Repairing ensures that standard export tools work properly without errors, since they often fail to export corrupted or damaged MySQL tables. Here are the MySQL database repair and restore methods.
Method 1 - Restore Backup using mysqldump
If you have an updated, readable backup, then you can restore the MySQL database using the mysqldump utility. You can run the validate command to verify backup in MySQL. Then, restore the database using the mysqldump utility. Here’s how:
- First, create an empty database to save the restored database. Here’s the command:
‘mysql > CREATE DATABASE db_name’ - Then, use the following command to restore the database:
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;’
Method 2 – Use Dump and Reload Method (If tables are accessible)
- You can repair corrupt database in MySQL by dumping and reloading. Here's how:
- The initial step is run the following command to dump all the content in DB table:
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 command 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 DATABASE db_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.
This method supports InnoDB and MyISAM tables. If the database (InnoDB) cannot be accessed normally or it is severely corrupted, then you may need to start MySQL with the innodb_force_recovery option (values 1–6). It allows the server to start even when corrupt tables exist.
Note: Setting the values higher than 4, can cause data loss.
This helps you to dump and reload the severely corrupted InnoDB tables. Once the repairing process is complete, remove the option and reload the dump into a clean database.
Method 3 - Use myisamchk Command
You can use the myisamchk command to repair the MyISAM tables in MySQL. It can help you recover all the data from the corrupt MyISAM tables, except unique keys. It also provides the following options that you can add according to the level of corruption:
- --recover: Standard repair helps you to rebuild indexes.
- --safe-recover: Used to repair severely corrupted table by checking it thoroughly.
- --force: To repair while ignoring the warnings.
Here’s how to run this command:
myisamchk --recover table_name.MYI
Method 4 - Use a Professional MySQL Repair Tool
If you want to repair MyISAM and InnoDB tables quickly and without data loss, then you can use a professional MySQL repair tool like Stellar Repair for MySQL. It can repair large-sized corrupt tables quickly and help you recover all the objects with complete precision and integrity. It can easily resolve errors and issues that occurred due to MySQL/MariaDB database corruption.
How to Export a Corrupt MySQL Database to SQL Scripts?
Once the database has been successfully repaired, the next step is to export it to SQL scripts as a backup. Most admins preferred this step to use this portable backup with schema definitions and data (in the form of statements), for future use. This backup can be restored on the same server or migrated to another environment. Here are the common ways to do so:
1 - Use mysqldump utility to export MySQL database
To export MySQL database using this utility, use this command:
mysqldump -u [username] -p [database_name] > [output_file.sql]
Limitations of mysqldump:
• Slow for very large databases as it works row-wise.
• Requires manual reload into MySQL.
2 - Export MySQL Database with Stellar Repair for MySQL
The professional MySQL repair tool - Stellar Repair for MySQL - also provides you a direct option to export the repaired databases to SQL scripts. You can restore a corrupt database and export it to a live MySQL. Also, it ensures complete recovery of tables, foreign keys, triggers, stored procedures, and unique keys. It is the most reliable method to directly repair and export the MySQL database to SQL scripts. It does not rely on row-wise export or manual reload.
Mysqldump vs Stellar Repair for MySQL (Export after Repair)
| Mysqldump Utility | Stellar Repair for MySQL |
|---|---|
| Requires manual export after repair and manual reload into MySQL | Repairs corrupt InnoDB and MyISAM databases and directly exports to SQL scripts or restores to the MySQL server |
| Slow for large databases | Faster export, optimized for large and corrupt databases |
| May fail during export if corruption remains or some tables are inaccessible | Handles severe corruption and helps ensure complete data export |
| Command-line utility that requires technical knowledge | GUI-based tool with a user-friendly interface and minimal manual steps |
Best Practices to Avoid MySQL Database Repair & Export Issues
- Check the MySQL data directory to ensure that the correct database file (.ibd or ibdata1) is selected.
- Confirm the MySQL version compatibility and foreign key order before exporting database.
- Create backup file (dump) of the database.
- Configure and enable innodb_log_file_size properly and monitor error logs for corruption warnings.
- Maintain hardware health by regularly monitoring your system disks and deploying UPS systems to prevent unclean shutdowns.
- Run Integrity Checks by schedule, CHECK TABLE jobs during low traffic, and keep monitoring slow query/error logs.
- Keep MySQL recovery tools ready to recover MySQL database on non production copies proactively.
Conclusion
Repairing the corrupted MySQL database quickly is essential to maintain business continuity. Above, we saw methods to repair the MySQL database and then export it to SQL scripts. You can use the repaired tools discussed in the article to repair and restore the database (InnoDB/MyISAM). The advanced settings - innodb_force_recovery - can help when the server doesn’t start due to severe corruption in tables. The best option is to use a reliable MySQL repair tool, like Stellar Repair for MySQL. It can effectively repair the MySQL database and also directly export it to SQL scripts.
Top comments (0)