Home arrow MySQL arrow Page 4 - Disaster Prevention and Recovery with the MySQL Database

4.6.2.7 Using myisamchk for Crash Recovery - MySQL

If you need to administer MySQL, this article gets you off to a good start. In this section, we discuss the issues of disaster prevention and recovery. The fifth of a multi-part series, it is excerpted from chapter four of the book MySQL Administrator's Guide, written by Paul Dubois (Sams; ISBN: 0672326345).

TABLE OF CONTENTS:
  1. Disaster Prevention and Recovery with the MySQL Database
  2. 4.6.2.2 General Options for myisamchk
  3. 4.6.2.4 Repair Options for myisamchk
  4. 4.6.2.7 Using myisamchk for Crash Recovery
  5. 4.6.2.9 How to Repair Tables
  6. 4.6.3 Setting Up a Table Maintenance Schedule
  7. 4.6.4 Getting Information About a Table
By: Sams Publishing
Rating: starstarstarstarstar / 7
June 22, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

If you run mysqld with --skip-external-locking (which is the default on some systems, such as Linux), you can't reliably use myisamchk to check a table when mysqld is using the same table. If you can be sure that no one is accessing the tables through mysqld while you run myisamchk, you only have to do mysqladmin flush-tables before you start checking the tables. If you can't guarantee this, then you must stop mysqld while you check the tables. If you run myisamchk while mysqld is updating the tables, you may get a warning that a table is corrupt even when it isn't.

If you are not using --skip-external-locking, you can use myisamchk to check tables at any time. While you do this, all clients that try to update the table will wait until myisamchk is ready before continuing.

If you use myisamchk to repair or optimize tables, you must always ensure that the mysqld server is not using the table (this also applies if you are using --skip-external-locking). If you don't take down mysqld, you should at least do a mysqladmin flush-tables before you run myisamchk. Your tables may become corrupted if the server and myisamchk access the tables simultaneously.

This section describes how to check for and deal with data corruption in MySQL databases. If your tables get corrupted frequently you should try to find the reason why. See Section A.4.2, "What to Do If MySQL Keeps Crashing."

The MyISAM table section contains reasons for why a table could be corrupted. See Section 8.1.4, "MyISAM Table Problems."

When performing crash recovery, it is important to understand that each MyISAM table tbl_name in a database corresponds to three files in the database directory:

File

Purpose

tbl_name.frm

Definition (format) file

tbl_name.MYD

Data file

tbl_name.MYI

Index file


Each of these three file types is subject to corruption in various ways, but problems occur most often in data files and index files.

myisamchk works by creating a copy of the .MYD data file row by row. It ends the repair stage by removing the old .MYD file and renaming the new file to the original file name. If you use --quick, myisamchk does not create a temporary .MYD file, but instead assumes that the .MYD file is correct and only generates a new index file without touching the .MYD file. This is safe, because myisamchk automatically detects whether the .MYD file is corrupt and aborts the repair if it is. You can also specify the --quick option twice to myisamchk. In this case, myisamchk does not abort on some errors (such as duplicate-key errors) but instead tries to resolve them by modifying the .MYD file. Normally the use of two --quick options is useful only if you have too little free disk space to perform a normal repair. In this case, you should at least make a backup before running myisamchk.

4.6.2.8 How to Check MyISAM Tables for Errors

To check a MyISAM table, use the following commands:

  • myisamchk tbl_name

    This finds 99.99% of all errors. What it can't find is corruption that involves only the data file (which is very unusual). If you want to check a table, you should normally run myisamchk without options or with either the -s or --silent option.

  • myisamchk -m tbl_name

    This finds 99.999% of all errors. It first checks all index entries for errors and then reads through all rows. It calculates a checksum for all keys in the rows and verifies that the checksum matches the checksum for the keys in the index tree.

  • myisamchk -e tbl_name

    This does a complete and thorough check of all data (-e means "extended check"). It does a check-read of every key for each row to verify that they indeed point to the correct row. This may take a long time for a large table that has many indexes. Normally, myisamchk stops after the first error it finds. If you want to obtain more information, you can add the --verbose (-v) option. This causes myisamchk to keep going, up through a maximum of 20 errors.

  • myisamchk -e -i tbl_name

    Like the previous command, but the -i option tells myisamchk to print some informational statistics, too.

In most cases, a simple myisamchk with no arguments other than the table name is sufficient to check a table.



 
 
>>> More MySQL Articles          >>> More By Sams Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: