How to Check and Repair a Corrupt MySQL Database

This guide walks you through diagnosing and fixing a corrupt MySQL database, covering both InnoDB and MyISAM storage engines. You’ll learn how to identify corruption, use built-in repair tools, and recover data when possible. Before starting, ensure you have a recent backup and administrative access to the MySQL server.

What You’ll Need

Access to the MySQL command line or a client like phpMyAdmin
MySQL administrative credentials (typically root)
File system access to the MySQL data directory (for some methods)
A current backup of the database (critical)

Step-by-Step

Identify the corruption. Common signs include error messages like “Table ‘xyz’ is marked as crashed”, “Incorrect key file”, or “Corrupt”. Run SHOW TABLE STATUS from the database to see which tables are flagged. For MyISAM tables, the status column may say “crashed”.
Check the MySQL error log. Look in the data directory (often /var/log/mysql/error.log or /var/log/mysqld.log) for detailed messages. Search for “corrupt”, “crash”, or the table name.
Attempt a simple repair. Use the REPAIR TABLE SQL command. Connect to MySQL and run:
```
USE your_database;
REPAIR TABLE your_table;
```
This works for MyISAM and some other engines. For InnoDB, it may not be sufficient.
Use mysqlcheck from the command line. This utility can check and repair tables. To check all tables in a database:
```
mysqlcheck -u root -p your_database
```
To repair:
```
mysqlcheck -u root -p --repair your_database
```
Add --quick for a faster check or --extended for a deeper check.
For InnoDB, use the recovery mode. InnoDB is more complex. Add the following to your MySQL configuration file (my.cnf or my.ini) under [mysqld]:
```
innodb_force_recovery = 1
```
Start MySQL. This mode skips certain checks and allows you to dump data. Start with 1 and increase to 6 if needed, but be aware that higher values may make the server read-only.
Dump the data from the recovery mode. Once MySQL starts, use mysqldump to export the database:
```
mysqldump -u root -p your_database > backup.sql
```
If some tables fail, try dumping individual tables or use --force to continue past errors.
Restore the dump to a fresh database. After a successful dump, stop MySQL, remove the innodb_force_recovery line, restart, and import:
```
mysql -u root -p -e "CREATE DATABASE new_database;"
mysql -u root -p new_database < backup.sql
```
For MyISAM tables, use myisamchk. Stop MySQL, then run:
```
myisamchk --recover /path/to/data/your_database/your_table.MYI
```
Use --safe-recover for more thorough repair if needed. Start MySQL afterward.
If all else fails, restore from backup. If you have a recent backup, restore it. Ensure the backup was taken before the corruption occurred.

Common Mistakes

Running repair without a backup. Some repair operations can worsen corruption. Always back up first.
Using MyISAM repair on InnoDB tables. The commands are different; REPAIR TABLE may not work for InnoDB. Use the recovery mode instead.
Setting innodb_force_recovery too high too quickly. Start at 1 and increment only if necessary. Higher levels can make the database read-only.
Ignoring the error log. The error log often tells you exactly which table and what type of corruption. Skipping it wastes time.
Forgetting to stop MySQL before using myisamchk. Running it while MySQL is active can cause further damage.

Tips

Regularly monitor your MySQL logs and run mysqlcheck as part of maintenance.
Use InnoDB over MyISAM for better crash recovery; it’s more robust.
Keep multiple backups, including off-site copies, and test restores periodically.
If you have a large database, consider using --quick for checks to minimize downtime.
After repair, run ANALYZE TABLE to update index statistics.

Troubleshooting

If the repair does not work, try these steps:

Check disk space. A full disk can cause corruption. Free up space and retry.
Check file permissions. Ensure MySQL has read/write access to the data directory.
If MySQL won’t start with innodb_force_recovery=1, try increasing the value to 2, 3, etc. If it reaches 6 and still won’t start, the data is likely unrecoverable; restore from backup.
If mysqldump fails partway, use --force to skip errors and dump as much as possible.
For MyISAM, if myisamchk fails, try --safe-recover or --quick first, then a full recover.
If you cannot access the database at all, consider using a third-party recovery tool, but be cautious and research before using.

Frequently Asked Questions

Q: How can I tell if my MySQL database is corrupt?

Look for error messages in logs, failed queries, or use CHECK TABLE to test tables. mysqlcheck can also report corruption.

Q: What is the difference between MyISAM and InnoDB repair?

MyISAM tables can be repaired with REPAIR TABLE or myisamchk. InnoDB uses crash recovery and innodb_force_recovery mode, which is more complex.

Q: Will repairing a table delete my data?

No, repair attempts to fix the table structure and data. However, if corruption is severe, some data may be lost. Always back up first.

Q: Can I repair a corrupt MySQL database without stopping the server?

For MyISAM, REPAIR TABLE can be run online. For InnoDB, you may need to restart with recovery mode, which requires downtime.

Q: What should I do if the repair fails?

Restore from a backup. If no backup, try exporting what you can with mysqldump --force and then rebuild the database.

Q: How can I prevent future corruption?

Use InnoDB, keep hardware healthy, ensure proper shutdown, and regularly back up. Also monitor disk space and run integrity checks.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →