How to repair MySQL databases and tables

This article explian about how to repair MySQL database and table. As the mysql is updated time by time. Mysql deals with some tools that we can use it for repairing the databases and tables.

It is common that our database get corrupted due to many reason like it does not get restored properly or server get rebooted while updating database etc. We can repair the table or the corresponding databases through various steps by mysqlcheck command.

First of all before doing mysqlcheck we need to take the current mysql backup so that if any issues occurs we can able to revert it to the stable condition. The specific thing we should consider is that the mysqlcheck commands works 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 have to follow the below steps

For checking all the database,

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

To check a specific DATABASE

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

Checks 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 the “OK” for the table.

If the database table has displayed any error means we can 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’s 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 have to 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 working 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.

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 regarding the fix

The above mentioned commands will help you to troubleshoot the mysql database and table repair