Friday, January 13, 2012

Check & Repair Corrupt MySQL Database using myisamchk

MYISAMCHK is a table maintenance utility developed by MySQL. This utility works with MyISAM table that uses .MDI for index and .MYD for data. Database administrator can also use REPAIR Table & CHECK Table statements to repair and check corrupt MySQL database. It offers several options to check, repair and optimize corrupt MySQL database. The options are given below: 

  • myisamchk General Options
  • myisamchk Check Options
  • myisamchk Repair Options
  • myisamchk Memory Usages
Caution: Always make a backup of corrupt MySQL database before using any Repair Option on the database, under some scenarios Repair Options might cause of data loss.

Start myisamchk by this:

shell> myisamchk [options] tbl_name ...

Place your option, that you want to do at the Options and table name at tbl_name. Table name is the name of table that you want to check or repair. If you don't know what are the available options then invoke this:

myisamchk –help

This will give a list of all available options.

Note: If you have not specify the option then MySQL set Check options by default.

All the data or records related to the database are placed at the database directory. If you want to run myisamchk at the different location then you have to specify the location database directory because MySQL has no any idea about location of the database.

How to repair MySQL database at other Location? To do this operation, you have to copy the files that correspond to the database table and paste it to the other location. Now perform repair operation on there.

Recommended way for quickly check and repair MySQL database:

To check corrupt MySQL database run this command:

shell> myisamchk --silent --fast /path/to/datadir/*/*.MYI
shell> isamchk --silent /path/to/datadir/*/*.ISM

To reapir corrupt MySQL database, run this command:

shell> myisamchk --silent --force --fast --update-state \
--key_buffer_size=64M --sort_buffer_size=64M \
--read_buffer_size=1M --write_buffer_size=1M \
shell> isamchk --silent --force --key_buffer_size=64M \
--sort_buffer_size=64M --read_buffer_size=1M --write_buffer_size=1M \

Important: It is recommended to you; first of all close all the programs before running myisamchk command.

No comments:

Post a Comment