In this blog, we would like to give you some useful insights on how to repair MySQL database and table. This is probably one of the most common, yet cumbersome aspects you may have to deal with on a regular basis.  As you are aware the MySQL database is updated from time by time. MySQL deals with some tools that we can use for repairing the databases and tables.

It is very common problem that most of us face, as the database gets corrupted due to many possible reasons such as the fact that it may not get restored properly, the server is getting rebooted while updating database, among others. Here are some of the steps you can use to repair the table or the corresponding databases by using the mysqlcheck command.

First of all, before doing mysqlcheck we need to take the current MySQL backup so that if any issues occur, we are able to revert it back to a more stable point. The specific thing we should consider is that the mysqlcheck commands should work on the database engine InnoDB.

Change the directory to MySQL as follows,

cd /var/lib/mysql

InnoDB Engines

To use the mysqlcheck for InnoDB engines we must follow the below steps:

• For checking all the database

 mysqlcheck -c -u root -p --all-databases

• For checking a specific DATABASE

 mysqlcheck -c "DATABASE name" -u root -p

• For checking all the tables under the mentioned database name

#mysqlcheck -c "DATABASE name" "TABLE name" -u root -p

Checks the mentioned tables under the DATABASE name

However, if a MySQL table passes the check it will display an “OK” message for the table.

If the database table has displayed any error means we need to repair the table by the following command:

 mysqlcheck -r "DATABASE name" "Table name" -u root -p

Repair and optimization of tables for all databases

There is a simple command to automatically check, repair and optimize all tables in all databases when running a MySQL server on Linux/Unix/BSD.

 mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

MyISAM Engines

To use the mysqlcheck for MyISAM engines we must follow the below steps

If we are using MyISAM storage engine for MySQL we can use the myisamchk commands to repair the table.

The myisamchk command only works under the database or the tables using MyISAM engines. It will not be work under InnoDB engine.

The mysqlcheck program enables us to check and repair databases while MySQL is running this is useful when we want to work on MySQL without stopping.

Command Steps for checking all the MySQL tables

myisamchk table name

To check all the tables under the database

myisamchk * .MYI

How to repair a table from myisamchk command,

myisamchk --recover "table name"

After the repair, please check for the MySQL repair and confirm whether the issue is fixed.

Conclusion 

The above-mentioned commands will help you troubleshoot the MySQL database and table repair. 

If you need any further support regarding the issue, our server helpdesk support experts are ready to help you, available at your disposal. 

Nixtree Team specialises in Linux server management services such as, cPanel Server Management, Plesk Server Management, Free Panel Server Management, Directadmin Server management and more. 

Need help in managing your server? Get Started Now!