How to Fix corrupted Tables in MySQL

Introduction

MySQL is one of the most popular open-source relational database management systems. It powers many of the web applications and websites we use every day. However, like any database system, MySQL can sometimes encounter errors that lead to data corruption.

Corrupted MySQL tables occur when the data in a table becomes unreadable or invalid. This usually happens due to unexpected shutdowns, hardware failures, software bugs, or unauthorized modifications to table data. Corrupted tables lead to crashes when MySQL attempts to read from or write to the invalid data.

Fortunately, MySQL provides mechanisms to detect corruption and rebuild damaged tables. The exact methods depend on whether the corrupted tables use the MyISAM or InnoDB storage engine.

In this guide, we will cover:

• Common causes of corrupted MySQL tables
• How to check for and repair MyISAM table corruption
• Recovering InnoDB tables using dump and restore
• Enabling InnoDB force recovery mode

Understanding these recovery techniques can help you minimize downtime and data loss when facing corrupted MySQL tables.

Causes of Corrupted MySQL Tables

Some common events that can lead to table corruption include:

• Unexpected shutdowns – If the database server crashes or loses power during a write operation, incomplete writes can corrupt data.
• Hardware failures – Faulty RAM, hard drives, and other hardware can introduce data errors.
• Software bugs – Bugs in MySQL code or other software interacting with the database can result in corruption.
• Unauthorized data modifications – Users or applications directly modifying MySQL data files instead of using the database API risk corruption.
• Operating system issues – Bugs, crashes, and permissions issues with the underlying OS can also corrupt tables.
• Database server overload – Heavy load and resource exhaustion can cause MySQL to fail to write data properly.

Proper database administration, reliable hardware, and robust software reduce the chances of corruption. But data integrity issues can still occur occasionally despite best practices.

Checking for and Repairing MyISAM Table Corruption

The MyISAM storage engine was the default in older versions of MySQL. MyISAM uses table-level locking and stores data and indexes in separate files.

MyISAM tables support directly checking for and repairing corruption. When you suspect corruption, confirm by running CHECK TABLE:

mysql> CHECK TABLE tablename;

This will scan the table and reveal any corruption issues. If corruption exists, use REPAIR TABLE to rebuild MyISAM tables:

mysql> REPAIR TABLE tablename;

MySQL will rebuild the data and indexes for the table to restore consistency.

Assuming the repair was successful, you will see a message like this in your output:

+--------------------------+--------+----------+----------+ 
| Table                    | Op     | Msg_type | Msg_text |
+--------------------------+--------+----------+----------+
| database_name.table_name | repair | status   | OK       |
+--------------------------+--------+----------+----------+

However, sometimes the corruption is too severe for MySQL to repair the table. In these cases, the MySQL documentation suggests:

• Trying the REPAIR TABLE command a second time. Subsequent repair attempts may work.
• Use myisamchk from the command line to rebuild tables. This tool can fix more extensive corruption.
• Extract data from the table with SELECT queries into another table, then recreate the original table from the extracted data.
• Use mysqldump to export the schema and data to a SQL dump file. Then drop the table, recreate it, and import the dump file. The dump and restore process can rebuild corrupt tables inaccessible to direct repair.
• Restore the entire MySQL data directory from backup files taken before the corruption occurred. This allows restarting with known good data.

Recovering Corrupted InnoDB Tables

InnoDB is the modern default MySQL storage engine. Unlike MyISAM, InnoDB stores data and indexes together in shared tablespace files.

InnoDB also has advanced crash recovery capabilities. It performs checksums to detect corruption and can automatically roll back incomplete transactions on restart after a crash.

Usually restarting MySQL is enough for InnoDB to restore data consistency after a failure. But occasionally tables still become corrupted and require rebuild.

An InnoDB feature called innodb_force_recovery allows read-only access to corrupted tables to extract data. To enable:

• Stop the MySQL service.
• Edit my.cnf and add:

innodb_force_recovery=1 

• Restart MySQL.

With force recovery enabled, you can access corrupted tables to extract the data. The recommended technique is:

• Use SELECT INTO OUTFILE to export the full table data to a text file.
• Drop the original corrupted table.
• Recreate the table from the schema.
• Use LOAD DATA INFILE to import the extracted data file to populate the rebuilt table.

This recreation process revives InnoDB tables inaccessible to normal repair.

Higher innodb_force_recovery values attempt crash recovery on more extensively corrupted data. But this can worsen data loss if corruption is severe. Use the lowest force recovery level that allows access for extraction.

Best Practices to Avoid Corruption

While MySQL includes repair options for recovery, avoiding corruption in the first place is ideal. Steps to help prevent corrupted tables include:

• Use an uninterruptible power supply to avoid crashes from power loss.
• Ensure MySQL writes are flushed from memory to disk at transaction commit to prevent data loss.
• Isolate MySQL on reliable, high-performance storage without competing I/O workloads.
• Validate and test all application and management tool code that interacts with the database.
• Limit direct access to data files from outside MySQL and avoid manual manipulation.
• Take regular logical backups via mysqldump or SELECT INTO OUTFILE for disaster recovery.
• Use replication to maintain resilient redundant copies of data.
• Monitor for fragmentation and reorganize tables before space issues occur.
• Keep all MySQL software up-to-date to benefit from the latest fixes.
• Follow security best practices to restrict disruption from unauthorized access.
• Configure monitoring to identify issues like unflushed writes or long semaphore waits that can precede corruption.

Conclusion

Despite precautions, database corruption still occurs. But MySQL provides checkpointing, checksums, crash-safe data structures and other features to maximize detection and recovery from many failure scenarios.

Knowing the manual repair and rebuild processes for both MyISAM and InnoDB tables enables restoring access quickly when automated recovery is not enough. Paying attention to MySQL best practices helps avoid most corruption issues in the first place.

With proper configuration, monitoring, and backup procedures, MySQL database admins can act swiftly to return to normal operations with minimal data loss should corruption ever strike.