How to Repair Corrupt MySQL Database

Corruption in MySQL Server database can occur due to several hardware and software issues. Some common issues include, bugs in software, faulty applications, bad sectors on the hard drive, and insufficient storage space. Sometimes, a sudden power failure or abnormal termination of MySQL Server can also lead to corruption in the database. When the database is corrupted, you may encounter various errors and issues, such as:

• Unable to open/access MySQL database.
• Tables failed unexpectedly or crashed.
• Fail to Access tables in the database.
• Can’t read data from the MySQL tables.
• Database page corruption on disk or a failed file read error.

In this article, we will discuss how to repair and restore corrupt MySQL database.

How to Check Corruption in MySQL Database?

Before proceeding to repair the database, you can check the MySQL database or tables for corruption. Here are some ways to do so.

Check the Error Log

In MySQL, the error log contains information about startup and shutdown times of mysqld. It keeps a record of diagnostic messages, like warnings, errors, etc. that occur when the server is running and during startup and shutdown of server. Whenever the server detects corruption in database tables, it writes information to the error log. You can check the MySQL error log to find the relevant information on corrupted tables. The error log is located in the data directory specified in my.ini file. The default location of data directory on Windows system is:

C:\Program Files\MySQL\MySQL Server 8.0\data or C:\ProgramData\MySQL

Use CHECK TABLE Command

The CHECK TABLE command in MySQL is used to check database tables for corruption and errors. If the CHECK TABLE command detects any issue, it shows an error with the table name marked as corrupted. To use the CHECK TABLE command, make sure you have proper permissions to use the CHECK TABLE command. Then, run the command as given below:

Note: To check the partitioned tables in MySQL database, you need to add an ALTER TABLE statement.

Methods to Repair and Restore Corrupt MySQL Database

Once you have identified corruption in MySQL database or tables, you can follow the below methods to repair the corrupt database or tables, depending on the storage engine you’re using.

For MyISAM Storage Engine

Use REPAIR TABLE Command

You can use the REPAIR TABLE command (see the below example) to repair the corrupt or damaged tables in MySQL database.

REPAIR TABLE table name;

Note: You require the SELECT and INSERT privileges to run the REPAIR TABLE command.

Use Myisamchk Command

You can also use the myisamchk command to repair the MySQL database tables. To use this command, you need to first stop the MySQL Server. Then, execute the below command:

myisamchk –recover TABLE

When the tables are repaired, restart the MySQL Server.

For InnoDB Storage Engine

Use ALTER TABLE Command

You can use the ALTER TABLE command to rebuild the corrupted tables. For this, just rename the table with the ALTER TABLE command as given below.

ALTER TABLE tbl_name ENGINE=INNODB

Note: Make sure you have ALTER, CREATE, and INSERT privileges.

When you rename the table, it creates a new table and rebuild the data in the table.

Use Dump and Reload Method

Dumping and reloading tables is also one of the effective methods to rebuild the tables in MySQL database, created using InnoDB storage engine. This method also helps in resolving the errors that occur due to corrupt indexes in the tables. Run the below command to restart the MySQL Server.

service mysql restart

If the MySQL Server fails to start, you can use the Innodb_force_recovery option to force start the storage engine. By default, the innodb_force_recovery option is not enabled. You are required to enable this option from the configuration file (my.cnf). To do this,

• Locate the configuration file (my.cnf) on your system. The location of the configuration file may differ based on the operating system you are using. On Windows system, you can find the configuration file in ‘/etc’ directory. The default path is /etc/mysql/my.cnf.
• Open the my.cnf file, go to the [mysqld] section, and then change the existing statements to the following statements:

[mysqld]

Innodb_force_recovery=1

service mysql restart

The default value for innodb_force_recovery is 0. However, the values from 1 to 6 are permissible. You can set the innodb_force_recovery value to more than 4. However, it may lead to data loss.

Once you enabled the innodb_force_recovery, you can easily access the corrupt or damaged InnoDB table.

Now, use the following mysqldump command to dump the table data:

mysqldump -u user -p database_name table_name > single_dbtable_dump.sql

After this, use the below command to export all the tables to the dump.sql file:

mysqldump --all-databases --add-drop-database --add-drop-table > dump.sql

Now, restart the MySQL Server and drop your database using the DROP DATABASE command.

After this, add a comment by inserting ‘#’ in the innodb_force_recovery=… statement in the [mysqld] to disable the InnoDB recovery mode. Then, save all the applied changes in the my.cnf file and restart your MySQL Server.

Limitations of the Above Methods

The above methods can help you repair corrupt MySQL database or tables but they have some limitations. These are:

• You require privileges and permissions, like SELECT, INSERT, ALTER, etc. on the MySQL database.
• You need to these methods based on the database storage engine you’re using. For example, myisamchk command can repair only tables in the database created using MyISAM storage engine.
• They may not resolve the complex corruption issues in MySQL database.
• Repairing multiple tables can be time-consuming.
• Performing innodb_force_recovery can lead to data loss if you set the value to more than 4.
• If your database contains multiple partitioned tables, you are required to use ALTER TABLE statement individually on each partitioned table.

An Easy and Quick Way to Repair MySQL Database – Use a Professional MySQL Repair Tool

If the above repair methods fail to repair the MySQL database or tables, you can use an advanced MySQL repair tool, like Stellar Repair for MySQL. It is trusted by MVPs and can repair corrupt MySQL database without any data loss. It can restore all the data, including partitioned tables, triggers, and indexes, from the corrupt database and save it in a new MySQL database file. It can repair database, created using InnoDB or MyISAM storage engine. It supports both Linux and Windows operating systems.

Benefits of using Stellar Repair for MySQL

• It has an easy-to-use user interface that does not require any technical skills to perform the repair process.
• It can repair databases created in both MyISAM and InnoDB storage engines with complete precision and integrity.
• It can repair partitioned tables, unique keys, triggers, indexes, etc.
• It provides a preview option to view the repairable data in the corrupt MySQL database.
• It supports specific table recovery.
• It provides option to save the repaired database in various file formats, such as SQL, HTML, XLS, Script, etc.

Conclusion

If your MySQL database is corrupted or damaged, you may encounter random errors or even fail to access the database. In this article, we have discussed the methods to repair corrupt MySQL database. To quickly repair the corrupt database without any data loss, you can use a professional MySQL repair tool, like Stellar Repair for MySQL. The tool can quickly repair the corrupt database and restore all the data, including partitioned tables, indexes, etc., with complete integrity. The tool can repair both InnoDB and MyISAM tables. You can download the trial version of the tool to check its functionality.