Thursday, March 22, 2012

How to Check, analyze & repair MySQL database corruption by mysqlcheck

MySQL database corruption normally occurs due to hardware failure, out of space problem, login failure, power failure, and many more. A database administrator can check the level of corruption by mysqlcheck program. MySQLcheck is a table maintenance program offered by MySQL to check and repair MySQL table corruption. It performs several other operations apart from check & repair like; to analyze and optimize table. Table maintenance program (mysqlcheck) can be time consuming; especially for the large table because when you run this program on the database then it thoroughly check and analyze all the tables in the database and tries to repair all errors.

By default, MySQL database uses myisam storage engine and it supports all the operation of mysqlcheck. Other storage engine may not support all the operation of mysqlcheck program. In such cases, you may receive below error message:

shell> mysqlcheck employee records

Note: The storage engine for the table doesn't support check

Above error can be fixed by manual mysql database repair method, which is out of scope of this article. I will try to cover this topic in next article.

How to perform table maintenance program? You can perform table maintenance operation by launching mysqlcheck on the database. There are three different ways to invoke mysqlcheck;

  1. shell> mysqlcheck [options] db_name [tbl_name ...]
It will perform all the operation of mysqlcheck on a particular table

  1. shell> mysqlcheck [options] --databases db_name ...
It will perform all the operation of mysqlcheck on a particular database

  1. shell> mysqlcheck [options] --all-databases
It will perform all the operation of mysqlcheck on all databases in MySQL

Note: If you haven’t mentioned the table name then it will try to check all databases. 

Alternative of mysqlcheck: myisamchk is an alternative of mysqlcheck for all those database administrators that are using MyISAM as a storage engine for their databases. Both have similar functionalities but works differently. The main difference is that you can't use mysqlcheck on the mysql database server when mysql server (mysqld) is not running but myisamchk can be use. The benefit of mysqlcheck over myisamchk is that you don’t want to stop mysqld to run mysqlcheck.

No comments:

Post a Comment