Check and Repair Corruption of MySQL Tables with Mysqlcheck Command

By admin News

Published by Jacob Martin on August 21, 2014

MySQL is a database system that is broadly used by several large and small companies across the globe for storing and managing large quantity of data. It comes with predefined queries which allow you to insert, restore and manage data that is stored in various tables in your MySQL database. So, if you want correct result, the syntax of the queries must be accurate, or else the database engine will display error informing the same.

However, despite of the accurate syntax of format of the queries, you may notice random error messages if there are some issues with the database. For example, a database may be corrupt due to random problems. Luckily, you can fix such errors with the help of inbuilt mysqlcheck command to check and fix corrupt MySQL database.

Checking Single Table

If you notice error message that inform the particular database table is corrupt, you can verify and repair that table with the use of mysqlcheck command. Follow the below to use command:

# mysqlcheck -c -u root -p
Enter password:
databasename.tablename OK

If the password not entered, you will notice the below error message:

# mysqlcheck -c
mysqlcheck: Got error: 1045: Access denied for user ‘root@localhost’ (using password: NO) when trying to connect

Checking All Tables

After fixing the corruption of particular table, if in case error still exist or any other error appears and stating corruption in the database, then run the below command to verify and repair the entire database:

# mysqlcheck -c -u root -p
Enter password:
newdatabasename.table1 OK
newdatabasename.table2 OK
newdatabasename.table3 OK
….

Important Note: Here OK notifies the status of the particular database table.

Checking All Databases as well as All Tables

You may also check all the databases and their respective tables for fixing corruption by following the below command. Just mention all-databases in the query.

#mysqlcheck -c -u root -p –all-databases
Enter password:
databasename.tablename OK
newdatabasename.table1 OK
newdatabasename.table2 OK
newdatabasename.table3 OK
….
….
mysql.help_category
error : Table upgrade required. Please do “REPAIR TABLE ‘help_category’ ” or dump/reload to fix it!
mysql.help_keyword
error : Table upgrade required. Please do “REPAIR TABLE ‘help_keyword’ ” or dump/reload to fix it!
….

Checking All Tables of Particular Databases

The mysqlcheck command let you to verify all tables of some particular databases in the database system. Hence, run the below command in this regard:

# mysqlcheck -c -u root -p –databases databasename newdatabasename
Enter password:
databasename.tablename OK
newdatabasename.table1 OK
newdatabasename.table2 OK
….

Analyzing Table with mysqlcheck Command

You can examine the tables in the database to identify its status with the mysqlcheck command using an option. Run the below command:

# mysqlcheck -a -u root -p
Enter password:
databasename.tablename Table is already up to date

The above mentioned command uses ANALYZE TABLE keyword within. In the meantime the specific table is available in Read-only mode for other processes.

Optimizing Table with mysqlcheck Command

You can also optimize the tables in the database with the same mysqlcheck command with -o option. Run the below command in this regard:

# mysqlcheck -a -u root -p
Enter password:
databasename.tablename OK

The abovementioned command uses OPTIMIZE TABLE keyword within. With this command, you regain the spaces vacated after deleting one or more rows in the table. The same also defragments the specific table.