Ideas To Fix Corrupted Tables in MySQL

MySQL tables can become corrupted, leading to unreadable data and potential server crashes. This guide walks you through diagnosing and repairing corrupted tables in both MyISAM and InnoDB storage engines, using built-in MySQL tools and recovery methods.

Background

Common causes of table corruption include:

• MySQL server interruption during a write operation
• Concurrent alteration of a table by both an external program and the server
• Unexpected shutdown of the machine
• Hardware failure in the computer
• Software bugs within the MySQL code

Before attempting any troubleshooting or fixes, it's essential to create a backup of your data directory to minimize the risk of data loss.

How to Fix Corrupted Tables

1. Step 1: Stop MySQL and Create a Backup

   Stop the MySQL service:

   sudo systemctl stop mysql

   Note: On certain platforms like Rocky Linux, the MySQL service may be named mysqld instead.

   Duplicate all your data into a fresh backup directory. For Ubuntu systems, the default data directory is typically located at /var/lib/mysql/:

   cp -r /var/lib/mysql /var/lib/mysql_bkp

2. Step 2: Check if the Table is Corrupted (MyISAM)

   If the table is utilizing the MyISAM storage engine, you can determine its status by executing a CHECK TABLE statement from the MySQL prompt:

   mysql> CHECK TABLE table_name;

   The output will contain a message indicating whether the table is corrupted.

3. Step 3: Repair MyISAM Tables

   If the table is confirmed corrupted, attempt a repair:

   mysql> REPAIR TABLE table_name;

   After a successful repair, the output should include a message confirming the completion of the repair process. For example:

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

4. Step 4: Handle InnoDB Tables with Crash Recovery

   If the corrupted table utilizes the InnoDB storage engine (the default in MySQL 8.0 onward), the repair process differs. InnoDB features automated corruption checks and repairs, identifying corrupted pages by performing checksums on each page it reads.

   Attempt restarting the MySQL service to check if it restores access to the server:

   sudo systemctl restart mysql

   InnoDB's built-in crash recovery mechanism typically resolves most issues upon server restart.

5. Step 5: Enable InnoDB Force Recovery (if needed)

   If the server remains crashed or inaccessible, enable InnoDB's force_recovery option. Edit the mysqld.cnf file, typically located in /etc/mysql on Ubuntu and Debian systems, or /etc/my.cnf.d on Red Hat and Rocky systems:

   sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

   In the [mysqld] section, add the following line:

   [mysqld]
   innodb_force_recovery=1

   Save and close the file, then attempt restarting the MySQL service.

6. Step 6: Dump and Reload the InnoDB Table

   If access to the corrupted table is successful, use the mysqldump utility to export your table data to a new file:

   mysqldump database_name table_name > out.sql

   Drop the table from the database:

   mysql -u user -p --execute="DROP TABLE database_name.table_name"

   Restore the table using the dump file created:

   mysql -u user -p < out.sql

Common Issues

• If REPAIR TABLE fails on MyISAM tables, the corruption may be severe. Consider restoring from your backup.
• For InnoDB tables, if innodb_force_recovery=1 doesn't work, you can try incrementing the value up to 6, but higher values may prevent write operations.
• Always remove the innodb_force_recovery line from your configuration file after recovery to restore normal operation.