How To Fix Corrupted Tables in MySQL

Updated on November 1, 2022

Manager, Developer Education

How To Fix Corrupted Tables in MySQL


Occasionally, MySQL tables can become corrupted, meaning that an error has occurred and the data held within them is unreadable. Attempts to read from a corrupted table will usually lead to the server crashing.

Some common causes of corrupted tables are:

  • The MySQL server stops in middle of a write.
  • An external program modifies a table that’s simultaneously being modified by the server.
  • The machine is shut down unexpectedly.
  • The computer hardware fails.
  • There’s a software bug somewhere in the MySQL code.

If you suspect that one of your tables has been corrupted, you should make a backup of your data directory before troubleshooting or attempting to fix the table. This will help to minimize the risk of data loss.

First, stop the MySQL service:

  1. sudo systemctl stop mysql

Note: On some platforms such as Rocky Linux, the MySQL service may be called mysqld instead.

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

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

After making the backup, you’re ready to begin investigating whether the table is in fact corrupted. If the table uses the MyISAM storage engine, you can check whether it’s corrupted by restarting MySQL and running a CHECK TABLE statement from the MySQL prompt:

  1. sudo systemctl start mysql
  1. CHECK TABLE table_name;

A message will appear in this statement’s output letting you know whether or not it’s corrupted. If the MyISAM table is indeed corrupted, it can usually be repaired by issuing a REPAIR TABLE statement:

  1. REPAIR TABLE table_name;

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 | +--------------------------+--------+----------+----------+

If the table is still corrupted, though, the MySQL documentation suggests a few alternative methods for repairing corrupted tables.

On the other hand, 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 features automated corruption checking and repair operations. InnoDB checks for corrupted pages by performing checksums on every page it reads, and if it finds a checksum discrepancy it will automatically stop the MySQL server.

There is rarely a need to repair InnoDB tables, as InnoDB features a crash recovery mechanism that can resolve most issues when the server is restarted. However, if you do encounter a situation where you need to rebuild a corrupted InnoDB table, the MySQL documentation recommends using the “Dump and Reload” method. This involves regaining access to the corrupted table, using the mysqldump utility to create a logical backup of the table, which will retain the table structure and the data within it, and then reloading the table back into the database.

With that in mind, try restarting the MySQL service to see if doing so will allow you access to the server:

  1. sudo systemctl restart mysql

If the server remains crashed or otherwise inaccessible, then it may be helpful to enable InnoDB’s force_recovery option. You can do this by editing the mysqld.cnf file. On Ubuntu and Debian systems, this file is usually in etc/mysql. On Red Hat and Rocky systems, this file is usually in /etc/my.cnf.d.

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

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

. . .
. . .

Save and close the file, and then try restarting the MySQL service again. If you can successfully access the corrupted table, use the mysqldump utility to dump your table data to a new file. You can name this file whatever you like, but here we’ll name it out.sql:

  1. mysqldump database_name table_name > out.sql

Then drop the table from the database. To avoid having to reopen the MySQL prompt, you can use the following syntax:

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

Following this, restore the table with the dump file you just created:

  1. mysql -u user -p < out.sql

Note that the InnoDB storage engine is generally more fault-tolerant than the older MyISAM engine. Tables using InnoDB can still be corrupted, but because of its auto-recovery features the risk of table corruption and crashes is markedly lower.

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about our products

Tutorial Series: How To Troubleshoot Issues in MySQL

This guide is intended to serve as a troubleshooting resource and starting point as you diagnose your MySQL setup. We’ll go over some of the issues that many MySQL users encounter and provide guidance for troubleshooting specific problems. We will also include links to DigitalOcean tutorials and the official MySQL documentation that may be useful in certain cases.

About the authors
Default avatar

Manager, Developer Education

Technical Writer @ DigitalOcean

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Nice to use command for Ubuntu servers is mysqlcheck, that can automatize the check and repair. You can check and repair all tables for specific database with the command: sudo mysqlcheck --auto-repair --optimize --databases <database name>

or in case you need to do check and repair for all tables in all databases, run: sudo mysqlcheck --auto-repair --optimize --all-databases

more switches are available with: mysqlcheck -?

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Featured on Community

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel