Cantech Knowledge Base

Your Go-To Hosting Resource

  1. Linux
  2. How to Repair a Corrupted MySQL Table?

How to Repair a Corrupted MySQL Table?

MySQL tables sometimes experience corruption which results in unreadable data which is because of encountered errors. The server typically crashes when attempts are made to read corrupted tables. Maintaining data integrity in MySQL requires repairing table structures because MySQL functions as a Relational Database Management System which organizes related data into tables. Tables in databases are structured with horizontal rows termed records and vertical columns called attributes, where each row is uniquely identified by a specific key.

MySQL along with other RDBMSes incorporates mechanisms to maintain data integrity consistency and accuracy. RDBMS platforms perform SQL-based CRUD operations under the ACID framework which ensures Atomicity, Consistency, Isolation, and Durability.

There are number of causes that lead to table corruption, they are:

  • MySQL server suddenly halts during a write operation. 
  • A concurrently running server process attempts to modify a table while an external program alters the same table. 
  • Machine shuts down unexpectedly. 
  • Computer hardware fails. 
  • A software bug exists within the MySQL code base. 
  • Before starting troubleshooting or table repair, create a backup of your data directory if you suspect table corruption. The potential for data loss will be reduced through these measures.

Pre-requisites

  • Systems that run on Linux like Ubuntu etc.
  • Knowledge of SQL.
  • Successfully installed MySQL.

Repair Process of Corrupted MySQL Table

1. Stop the MySQL service

sudo systemctl stop mysql

2. Copy all the data into a new backup directory

Copy all of your data into a new backup directory. On Ubuntu systems, the default data directory is /var/lib/mysql/:

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

3. Restart MySQL

Make the backup and begin investigating whether the table is in fact corrupted. If the table uses the MyISAM storage engine, check if it’s corrupted by restarting MySQL and run a CHECK TABLE statement from MySQL prompt:

sudo systemctl start mysql
mysql> CHECK TABLE table_name;

If the MyISAM table is corrupted, it can be repaired by running a REPAIR TABLE statement:

mysql> REPAIR TABLE table_name;

If the repair is successful, message like this can be seen in the output:

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

If the corrupted table uses the InnoDB storage engine, then the process for repairing it will be different. InnoDB is the default storage engine in MySQL as of version 8.0, and it has features like automated corruption checking and repair operations. InnoDB identifies

corrupted pages by performing checksums. It automatically stops the MySQL server, If there are any kind of discrepancies.

In rare cases, if there is a need to rebuild a corrupted InnoDB table, MySQL documentation suggests using the “Dump and Reload” method. It included regaining access to the corrupted table, using the mysqldump utility to create a logical backup of the table. This will help retain the table structure and the data within it, and then reload the table back into the database.

After this, restart MySQL service to check if it can allow the access to the server:

sudo systemctl restart mysql

If the server is crashed or inaccessible, enable InnoDB’s force_recovery option. It can be edited using mysqld.cnf. file. On Ubuntu and Debian systems it will be in etc/mysql.

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

Add this command in [mysqld] section:

/etc/mysql/mysql.conf.d/mysqld.cnf
. . .
[mysqld]
. . .
innodb_force_recovery=1

Save and close the file, and restart the MySQL service again. After successfully accessing the corrupted table, use mysqldump utility to put your table data to a new file. Rename the file for example: out.sql:

mysqldump database_name table_name > out.sql

Next, drop the table from the database. Use the following syntax, To avoid the process of reopening the MySQL prompt:

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

4. Restore the table with the dump file.

mysql -u user -p < out.sql

Tables that use InnoDB could be corrupted, but risk of table corruption and crashes are minimal, due to its auto-recovery features.

FAQ’s

How to reindex MySQL tables?

Add OPTIMIZE TABLE command to reindex a table. Then, MySQL will reread all the records in the table and reconstruct all of its indexes. If the table is large, reindexing the table can be time consuming if the table is large.

How to replace data in MySQL table?

To replace data in a MySQL table, you can update existing records using the UPDATE statement based on a condition. If you need to insert new data while replacing existing records, use REPLACE INTO, which removes the old row and inserts a new one. Alternatively, INSERT … ON DUPLICATE KEY UPDATE allows updating specific fields if a duplicate key exists.

How to optimize a table in MySQL?

To optimize a MySQL table, use the OPTIMIZE TABLE command, which reorganizes the table and reclaims unused space. You can also analyze and update index statistics using ANALYZE TABLE for better query performance. Regularly defragmenting tables and ensuring proper indexing can further enhance optimization.

How to refresh MySQL table?

To refresh a MySQL table, you can use FLUSH TABLES to close and reopen the table, ensuring updated data is loaded. If you need to reload data, you can use TRUNCATE TABLE to clear all rows or DELETE FROM with a condition. Additionally, restarting the MySQL server can refresh all tables and caches.

March 28, 2025