I recently encountered an issue with one of my MySQL databases. A few tables seem to have become corrupted, and I’m getting errors like #126 - Incorrect key file for table and #1030 - Got error -1 from storage engine.

I’ve done some Googling and found a few solutions, but I’m unsure which method is the safest to avoid data loss. Has anyone here dealt with this issue before? What’s the best way to repair corrupted MySQL tables? Any advice would be greatly appreciated!

Reply 1:

Hi! Corrupted MySQL tables can happen for several reasons—abrupt server shutdowns, hardware failures, or bugs in the storage engine. Don’t worry; this is usually fixable!

The first thing you need to know is which storage engine your table is using—MyISAM or InnoDB. The repair process depends on that.

For MyISAM tables:

Backup your database first! Corruption repairs can sometimes cause data loss.

Run the CHECK TABLE table_name; command in MySQL. This will identify whether the table is corrupted.

Use the REPAIR TABLE table_name; command to fix the table.

If the above doesn’t work, you can manually repair it using the myisamchk tool.

Stop your MySQL server.

Run: myisamchk --recover /path/to/table_name.MYI

This process should resolve most MyISAM corruption issues.

Reply 2:

Hey there! For InnoDB tables, the approach is slightly different because InnoDB uses a crash recovery mechanism. Here’s what you can try:

Restart MySQL: Sometimes, a simple restart allows InnoDB to recover on its own.

Force Recovery Mode: If the restart doesn’t work, modify the my.cnf file by adding the following under [mysqld]:

makefile

Copy code

innodbforcerecovery = 1

Increase the value up to 6 if needed. Restart MySQL after each change.

Dump and Restore: Once the table is accessible, dump the data using mysqldump and restore it to a fresh table.

Always proceed with caution while using innodbforcerecovery. At higher levels, it may render your database read-only.

Reply 3:

Adding to what others have said, here are a few general tips for avoiding corruption in the future:

Use a stable power supply or UPS to prevent abrupt shutdowns.

Enable binary logging in MySQL for easy recovery.

Regularly back up your database. Tools like mysqldump, Percona XtraBackup, or automated backups can save you a lot of trouble.

Keep your MySQL server updated to avoid bugs.

If all else fails, check your server logs (/var/log/mysql/error.log on Linux) for detailed error messages. Sometimes the root cause isn’t immediately obvious.

Reply 4:

One last thing! If you’re stuck with serious corruption and can’t repair the table, try these steps:

Rename the corrupted table (RENAME TABLE oldtablename TO corrupted_table;).

Create a new table with the same schema.

Use a tool like SELECT INTO OUTFILE to retrieve as much data as possible from the corrupted table and re-import it into the new one.

Good luck, and don’t forget to test your recovery plan in a staging environment before applying changes to production!
More Visit- https://docs.vultr.com/how-to-repair-a-corrupted-mysql-table