MySQL Repair Database: How to Fix Corrupted Tables

MySQL repair database is the process of fixing corrupted or damaged tables so they can be read and written to again. You repair a table using the REPAIR TABLE SQL command, the mysqlcheck command-line tool, or the myisamchk utility, depending on the storage engine and how severe the damage is. This article explains when and how to use each method, what causes corruption, and how to prevent it from happening again.

What Causes MySQL Table Corruption

Corruption means the internal structure of a table file no longer matches what MySQL expects. Common causes include:

• Server crashes or power loss while writing to a table (especially with MyISAM).
• Hardware issues such as failing disks, bad RAM, or network storage glitches.
• Killing a MySQL process with kill -9 or forcing a shutdown.
• Disk full errors that interrupt writes.
• Bugs in MySQL versions or storage engine software.
• Copying table files (like .frm, .MYD, .MYI) while the server is running.

InnoDB tables are more resilient than MyISAM because they use transactional logs and doublewrite buffers, but they can still become corrupted, especially if the disk fails or the server crashes mid-transaction.

How to Check if a Table Is Corrupted

Before repairing, confirm the table actually has a problem. Common symptoms include:

• Error messages like Table 'db.table' is marked as crashed.
• Queries return incorrect results or fail with ERROR 145 or ERROR 126.
• The table cannot be opened, or SELECT returns only part of the data.

You can check tables using CHECK TABLE:

CHECK TABLE tbl_name;

This returns a status: OK means no problem, ERROR means corruption, and WARNING means an issue that may need attention. For MyISAM tables, you can also use myisamchk with the --check option.

How to Repair a MySQL Database (Step by Step)

Here is the general process, from the least invasive to the most thorough:

1. Back up first. If you can still read the table, dump it with mysqldump or copy the entire data directory. A repair can sometimes worsen damage if the file is severely corrupted.
2. Stop the MySQL server if you plan to use myisamchk on MyISAM tables. Running it while the server is active can cause further corruption.
3. Try REPAIR TABLE for MyISAM, Archive, and CSV tables (and for InnoDB it may help with secondary index issues):

   REPAIR TABLE tbl_name;

   You can add QUICK or EXTENDED options: REPAIR TABLE tbl_name QUICK; repairs only the index tree, while EXTENDED rebuilds row by row.

4. Use mysqlcheck from the command line to repair all tables in a database or all databases at once:

   mysqlcheck -u root -p --auto-repair --check db_name;
   mysqlcheck -u root -p --auto-repair --check --all-databases;

   The --auto-repair flag automatically runs REPAIR TABLE on any table that fails the check.

5. Use myisamchk for MyISAM tables when the server is stopped:

   myisamchk --recover /var/lib/mysql/db_name/tbl_name.MYI;

   For severe cases, use --safe-recover which is slower but more thorough.

6. For InnoDB, the typical repair is to restore from a backup, or use ALTER TABLE tbl_name ENGINE=InnoDB; to rebuild the table. In some cases, setting innodb_force_recovery to a value from 1 to 6 in the config file can start the server to dump data, but this should be a last resort and requires careful handling.

Choosing the Right Repair Method

The best method depends on the storage engine and how the table is used:

• MyISAM tables: Use REPAIR TABLE first. If that fails, stop the server and run myisamchk --recover. For very large tables, myisamchk is often faster.
• InnoDB tables: REPAIR TABLE does not fix row data corruption. Instead, try ALTER TABLE tbl_name ENGINE=InnoDB; which rebuilds the table. If the server won’t start, use innodb_force_recovery to get it running and dump the data, then restore.
• CSV or Archive tables: REPAIR TABLE can fix index issues but not the data file itself.

Common Problems and What to Do If Repair Fails

Sometimes a repair does not succeed. Here are common situations and next steps:

• Repair hangs or takes too long: For large MyISAM tables, myisamchk --safe-recover can take hours. Let it run, or restore from backup.
• Repair says “Table is full” or “Disk full”: The repair needs temporary disk space. Free up space or move the table to a larger disk.
• InnoDB won’t start after crash: Set innodb_force_recovery=1 in my.cnf and restart. Increase the number if it still fails. Once started, dump the data with mysqldump and reload into a fresh database.
• Data is still missing after repair: A repair can only recover what is physically present. If rows were never written, they are gone. Restore from a backup.

Preventing MySQL Corruption

Corruption is often avoidable. Key practices include:

• Use InnoDB instead of MyISAM for critical data, since InnoDB is crash-safe.
• Keep the server on a reliable power source and use a filesystem that supports proper flushing (like ext4 or XFS).
• Never copy table files while the server is running.
• Monitor disk space and hardware health (SMART checks).
• Perform regular backups and test restores.
• Set innodb_flush_log_at_trx_commit=1 for full durability (at the cost of some performance).

Frequently Asked Questions

Can I repair a MySQL table without stopping the server?

Yes, for MyISAM tables you can use REPAIR TABLE or mysqlcheck while the server is running. However, myisamchk requires the server to be stopped because it accesses the files directly. For InnoDB, you can run ALTER TABLE online, but if the server won’t start, you may need to use innodb_force_recovery which requires a restart.

What is the difference between REPAIR TABLE and mysqlcheck?

REPAIR TABLE is a SQL command that works on a single table. mysqlcheck is a command-line tool that can check and repair multiple tables or all databases at once. Internally, mysqlcheck uses REPAIR TABLE for MyISAM tables, but it also has options like --auto-repair to automate the process.

Does REPAIR TABLE work on InnoDB tables?

It can repair secondary index issues but not row data corruption. For InnoDB, the recommended approach is to rebuild the table with ALTER TABLE tbl_name ENGINE=InnoDB; or restore from a backup. If the server won’t start, use innodb_force_recovery to dump data.

How long does a MySQL repair take?

It depends on the table size, engine, and damage. A quick MyISAM index repair can take seconds, while a full myisamchk --safe-recover on a large table can take hours. InnoDB rebuilds are usually faster but still proportional to table size.

Can I repair a corrupted table without a backup?

Sometimes, but not always. If the table is only slightly damaged, repair tools may recover most data. If the damage is severe, you may lose data. Always try to back up before repairing, and if you have no backup, consider using a tool like mysqlfrm to recover table definitions, or contact a professional data recovery service.

Is it safe to run REPAIR TABLE on a production database?

It can lock the table and may cause downtime. For MyISAM, REPAIR TABLE locks the table during repair. For InnoDB, ALTER TABLE can be done online with ALGORITHM=INPLACE, but it still takes resources. If possible, do repairs during low traffic or on a replica.

Related

• Party Games: Why Everyone Should Try Them
• 5 Retro Gadgets That Still Just Work Today
• Git Tool: Who Works on What?