The Ultimate Guide To Repair MySQL Database

One of the day-to-day tasks of database administrators is to keep the MySQL Server up and running. They have to also ensure that the database remains healthy. 

However, even after taking all the precautions, situations arise that can lead to corruption in the database. 

Corruption in MySQL databases can occur due to multiple reasons, such as server failure, hardware or software issues, virus attacks, etc. 

When the database gets corrupted, the biggest challenge for DBAs is to avoid downtime, as it can lead to loss of productivity and business. 

So, it is important to repair the corrupt database as quickly as possible to minimize downtime and prevent loss of business. 

In this guide, we will cover the reasons behind corruption in MySQL databases and mention some effective solutions to repair the database quickly. 

We have also shared some best practices to prevent corruption in MySQL databases.

Causes Behind Corruption in MySQL Database

MySQL databases can get corrupted due to the following factors:

• MySQL Server Failure

If the MySQL Server fails due to sudden power failure or any other reason, it can disturb data integrity, resulting in corruption. 

• Hardware Failure

Hardware failure or issues can also cause database corruption.

• Mysql Application Is Killed in the Middle of a Write

If MySQL (a single multi-threaded program) is abruptly terminated or killed in the middle of a write operation due to a faulty disk or any application-level issue, it can lead to corruption in the database. 

• Incompatible Data Type/Transactions

The database can get corrupted if you use external utilities incorrectly to modify the tables. For example, incorrect transactions/data types. 

• Incorrect MySQL Storage Engine Configuration

Incorrect code or configuration in MySQL storage engine (MyISAM/InnoDB) can lead to database corruption. 

• Faulty Applications/Updates

Corruption in MySQL databases can also occur due to faulty applications or operating system updates. 

• Insufficient Disk Space

When the disk space is low, MySQL Server may fail to perform read and write operations, resulting in database corruption.  

• Human Errors

Human errors, such as running incorrect SQL statements and deleting critical files, can corrupt the database.

How To Repair Corrupt MySQLtabase?

Follow the below troubleshooting methods to repair and recover a corrupt MySQL database.

Method 1 - Restore MySQL Database From Backup

In case of database corruption, the easiest and simplest way is to restore the database copy from the last known backup. 

If you have created a logical backup using the mysqldump utility, follow the below steps to restore the database:

• First, create an empty database by using the following command:

‘mysql > create db_name’

• Then, restore the database by using the below command: 

mysql -u root -p db_name < dump.sql

Here, u = username

p = password

db_name = database name

dump.sql = path to the dump file

• Now, check the restored objects in the database by using the below command:

‘mysql> use db_name; 

mysql > show tables;’

Method 2 - Check and Repair MySQL Database Using Mysqlcheck Command

If you've not created any backup or the backup is obsolete, then use the mysqlcheck command to check and repair tables in the MySQL database. 

This command uses a list of SQL statements to check and repair the database tables. Here are the steps:

• Open the command-line terminal on the system hosting MySQL server. 
• First, check the MySQL database. To check specific database, run this command:

mysqlcheck database_name 

• If you want to check a specific table in the database, then run the below command:   

mysqlcheck database_name table_name

• If a specific table in the database is corrupted, then repair the table by using the below command:

mysqlcheck --r database_name table_name

• To repair all the databases, use the below command:

mysqlcheck --repair --all-databases

Method 3 - Repair MySQL Database

MySQL storage engines manage how data is stored, managed, and manipulated in the database. MySQL supports two types of storage engines - InnoDB and MyISAM. 

You can follow the below steps to repair the MySQL database, according to your database storage engine:

A - If You’re Using the InnoDB Storage Engine

Step 1- Restart the Mysql Service

• In the Run dialog box, type services. msc.
• In the Services window, find and right-click on the MySQL Service.
• Click Restart service.

Step 2 - Use innodb_force_recovery To Start the Mysql Server

You need to enable the 'innodb_force_recovery’ option from the configuration file. Here are the steps:

• Find the configuration file (my. conf). The my. cnf file’s location varies, based on your operating system. In Windows, the configuration file is located in the ‘/etc’ directory. The default path is /etc/mysql/my. cf.
• Once you found the my.conf file, search for the [mysqld] section, and then insert the below statements:

[mysqld] 

Innodb_force_recovery=1 

service mysql restart

• The default value of innodb_force_recovery is 0. However, you may be required to modify its value to '1' to start the InnoDB db engine and dump the tables. 

Note: Make sure to create a database backup before proceeding. Dumping tables with an innodb_force_recovery value of 4 or higher can lead to data loss. 

• If you’re able to access the corrupt table, dump the table data by using the mysqldump command as given below:

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

• To export all the databases to the dump.sql file, use the below command:

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

• Next, restart the MySQL Server and then use the DROP DATABASE command to drop the database. 
• In case it fails to drop the database, then use the below commands to delete the database manually:

cd /var/lib/mysql 

rm -rf db_name

• After dropping the database, disable the InnoDB recovery mode by commenting on the following line in [mysqld]:

#innodb_force_recovery=...

• Once you applied all the changes to the my. cnf file, save it, and restart the MySQL Server.

B - If Your Database Source Engine Is Myisam

You can run the myisamchk command to repair the MyISAM tables. 

Following are the steps to do so:

• First, stop the MySQL Server.
• Check the corrupted MyISAM table using the below command:

myisamchk TABLE

• If it detects corruption in the table, you can recover the table by executing the below command:

myisamchk –recover TABLE

• Next, start the MySQL Server.

Useful Tips to Prevent Corruption in MySQL Database

You can follow the given tips to prevent corruption in MySQL database:

• Testing MySQL kernel helps in identifying any software issues that can lead to corruption. So, test the MySQL kernel regularly with the MySQL command. 
• Take regular backups of your MySQL database using a consistent backup method according to your application needs. 
• A power outage can interrupt MySQL applications' ongoing operations and result in corruption in the database. So, ensure you have an uninterrupted power supply/UPS to avoid sudden power failure.
• Keep an updated version of the antivirus software on your machine to prevent virus/malware attacks.

Conclusion

The above-discussed troubleshooting methods can help you repair and recover the corrupted MySQL database. However, the manual methods may take time and result in data loss. If you can’t risk losing data, then you can opt for Stellar Repair for MySQL. It can help you quickly repair the corrupt MySQL database and restore all its objects with complete integrity. The tool can also help you repair multiple MySQL databases in one go.