A Step-by-Step Guide to Repair MySQL Database

Errors in MySQL database may occur due to software upgrades, table inconsistencies, changes in an index, etc. These errors can turn the database or specific tables corrupt. Also, factors like server crash, power outage, or hardware issues can damage a MySQL database and render it inaccessible.

Before troubleshooting the database and tables for issues, you must first check for corrupted data and tables. If the MySQL service is not running, you can check a table for corruption using the “CHECK TABLE tbl_name” command. If the service is running, you can run the “mysqlcheck db_name tbl_name” command to check a specific database table for corruption. After identifying the corrupted database and table(s), there are different methods you can use to repair the MySQL database.

This article discusses the step-by-step approach to repair MySQL database.

Steps to Repair MySQL Database

The steps to repair MySQL database depends on the database engine used. For repairing MyISAM database tables, you can use Step 1, 2, 3, or 5.  Skip to Step 4 or 5 to fix corrupted InnoDB database tables.

Step 1 – Use the REPAIR TABLE Statement

The REPAIR TABLE command is the first option you can use to fix a corrupted MyISAM database table. It also helps get back all the data from the corrupted table.

mysql > REPAIR TABLE tbl_name;

Note: At times, the server might shut down during the repair process. In that case, you will need to run the REPAIR TABLE command again when the server restarts.

If this method doesn’t fix the corrupted table, skip to the next step.

Step 2 – Use the mysqlcheck Command

If you do not want to stop the server when running the repair operation, use the mysqlcheck command with the –repair (-r) option to fix the corrupted database table(s). The steps are as follows:

• Browse the MySQL database folder, which is usually located at:

cd /var/lib/mysql

• Now, run the following mysqlcheck command with the -r option for repairing the database table:

mysqlcheck -r <db_name> <tbl_name>

If this step doesn’t work for you, proceed to the next one.

Step 3 – Use the myisamchk Command

If the server is not running and you need to check and repair any corrupted MyISAM table, you can use the myisamchk command. To use the command, perform these steps:

• Open the terminal and execute the below query to shut down your MySQL server:

systemctl mysql stop

• Go to the location where the database is stored:

cd /var/lib/mysql/<db_name>

• Run the below myisamchk command with the –recover option to do the repair:

myisamchk –recover <tbl_name>

• After repairing the table, restart the server.

systemctl restart mysql

Now check if you can open the recovered table. To do so, run the following queries:

USE <db_name>;

SELECT * FROM <tbl_name>;

If this doesn’t fix the MyISAM table(s), skip to step 5.

Step 4 – Run InnoDB Force Recovery

For repairing InnoDB databases, you will need to force repair the database. The detailed steps are as follows:

• Find and open the MySQL configuration file (my.cnf).
• Add the following to the configuration file:

[mysqld]

innodb_force_recovery=1

Start InnoDB force recovery with value “1” and increase the value up to “6”. However, forcing InnoDB recovery with a value greater than “3” can result in data loss.

• Save the my.cnf file and close it.
• Restart your server to apply the changes made to the configuration file.
• Export the recovered databases to a backup file by running the following mysqldump command:

mysqldump db_name > dump_file.sql

• Start the MySQL server and drop the damaged database tables:

DROP TABLE IF EXISTS <tbl_name>;

Note: If the table does not drop, try removing the database table manually. Log in as the root user and navigate to the directory where the databases are. If you’re unable to drop the table, try deleting it manually by using this command:

rm -r <tbl_name>

• Stop the server and open the my.cnf file. In the file, disable the recovery mode by commenting out the innodb_force_recovery statement added above. Now, save and close the my.cnf file. Start your server again.
• Create a new database and restore the database from the dump file:

CREATE DATABASE new_db;

mysql new_db < dump_file.sql

Now see if you can access the database and all the tables. If not, proceed to Step 5.

Step 5: Use a MySQL Database Repair Tool

You can use a professional MySQL database repair tool to fix the database tables of both MyISAM and InnoDB storage engines. You can use Stellar Repair for MySQL software for the repair operation. It is purpose-built to fix corrupted MySQL databases and helps recover all the database records while maintaining data integrity.

Conclusion

MySQL provides several options to repair a table based on the database engine used (i.e. MyISAM or InnoDB). You can run the REPAIR TABLE query, mysqlcheck, or myisamchk command to fix the corrupted database table. For InnoDB database tables, you will need to perform InnoDB force recovery. If none of these options helps repair the database, using a specialized MySQL database repair tool can help. The tool can repair a corrupted MySQL database and the tables for MyISAM and InnoDB engines.