Linux myisamchk command

On Unix-like operating systems running MySQL, The myisamchk utility gets information about your MySQL database tables, and checks, repairs, or optimizes them.

The myisamchk command works with MyISAM tables (tables that have .MYD and .MYI files for storing data and indexes).

Description

The options specify what you want myisamchk to do. They are described in the following sections. You can also get a list of options by running myisamchk --help.

With no options, myisamchk checks your table as the default operation. To get more information or tell myisamchk to take corrective action, specify the appropriate options.

The tbl_name is the database table you want to check or repair. If you run myisamchk somewhere other than the database directory, you must specify the path to the database directory, because myisamchk has no idea where the database is located. In fact, myisamchk does not actually care whether the files you are working on are located in a database directory. You can copy the files that correspond to a database table into some other location and perform recovery operations on them there.

You can name several tables on the myisamchk command line if you want. You can also specify a table by naming its index file (the file with the .MYI suffix). This enables you to specify all tables in a directory using the pattern *.MYI. For example, if you are in a database directory, you can check all the MyISAM tables in that directory like this:

myisamchk *.MYI

If you are not in the database directory, you can check all the tables there by specifying the path to the directory:

myisamchk /path/to/database_dir/*.MYI

You can even check all tables in all databases by specifying a wildcard with the path to the MySQL data directory:

myisamchk /path/to/datadir/*/*.MYI

The recommended way to quickly check all MyISAM tables is:

myisamchk --silent --fast /path/to/datadir/*/*.MYI

To check all MyISAM tables and repair any that are corrupted, you can use the following command:

myisamchk --silent --force --fast --update-state 
          --key_buffer_size=64M --myisam_sort_buffer_size=64M 
          --read_buffer_size=1M --write_buffer_size=1M 
          /path/to/datadir/*/*.MYI

This command assumes the system has more than 64 MB free. For more information about memory allocation with myisamchk, see the section called "MYISAMCHK MEMORY USAGE".

For additional information about using myisamchk, see Section 7.6, "MyISAM Table Maintenance and Crash Recovery".

Important: You must ensure that no other program is using the tables while you are running myisamchk. The most effective means of doing so is to shut down the MySQL server while running myisamchk, or lock all tables that myisamchk is using.

Otherwise, when you run myisamchk, it may display the following error message:

warning: clients are using or haven't closed the table properly

This error means another program, such as the mysqld server, updated the table you're trying to check, and the program hasn't closed the file or died without closing the file properly, which can sometimes lead to the corruption of one or more MyISAM tables.

If mysqld is running, you must force it to flush any table modifications that are still buffered in memory using FLUSH TABLES. Then ensure that no one is using the tables while you are running myisamchk. However, the easiest way to avoid this problem is to use CHECK TABLE instead of myisamchk to check tables.

Syntax

myisamchk [options] tbl_name ...

Options

The options described in this section can be used for any table maintenance operation performed by myisamchk. The sections following this one describe options that pertain only to specific operations, such as table checking or repairing.

You can also set the following variables using --var_name=value syntax:

The possible myisamchk variables and their default values can be examined with myisamchk --help:

sort_buffer_size is used when the keys are repaired by sorting keys, which is the normal case when you use --recover. As of MySQL 5.5.29, myisam_sort_buffer_size is available as an alternative name to sort_buffer_size. myisam_sort_buffer_size is preferable to sort_buffer_size because its name corresponds to the myisam_sort_buffer_size server system variable with a similar meaning. sort_buffer_size should be considered deprecated.

key_buffer_size is used when you are checking the table with --extend-check, or when the keys are repaired by inserting keys row by row into the table (like when doing normal inserts). Repairing through the key buffer is used in the following cases:

• You use --safe-recover.
• The temporary files needed to sort the keys would be more than twice as big as when creating the key file directly. This is often the case when you have large key values for CHAR, VARCHAR, or TEXT columns, because the sort operation needs to store the complete key values as it proceeds. If you have lots of temporary space and you can force myisamchk to repair by sorting, you can use the --sort-recover option.

Repairing through the key buffer takes much less disk space than using sorting, but is also much slower.

If you want a faster repair, set the key_buffer_size and myisam_sort_buffer_size variables to about 25% of your available memory. You can set both variables to large values, because only one of them is used at a time.

myisam_block_size is the size used for index blocks.

stats_method influences how NULL values are treated for index statistics collection when the --analyze option is given. It acts like the myisam_stats_method system variable.

ft_min_word_len and ft_max_word_len indicate the minimum and maximum word length for FULLTEXT indexes. ft_stopword_file names the stopword file. These need to be set under the following circumstances:

If you use myisamchk to perform an operation that modifies table indexes (such as repair or analyze), the FULLTEXT indexes are rebuilt using the default full-text parameter values for minimum and maximum word length and the stopword file unless you specify otherwise. This can result in queries failing.

The problem occurs because these parameters are known only by the server. They are not stored in MyISAM index files. To avoid the problem if you have modified the minimum or maximum word length or the stopword file in the server, specify the same ft_min_word_len, ft_max_word_len, and ft_stopword_file values to myisamchk you use for mysqld. For example, if you have set the minimum word length to 3, you can repair a table with myisamchk like this:

myisamchk --recover --ft_min_word_len=3 tbl_name.MYI

To ensure that myisamchk and the server use the same values for full-text parameters, you can place each one in both the [mysqld] and [myisamchk] sections of an option file:

[mysqld]
ft_min_word_len=3
[myisamchk]
ft_min_word_len=3

An alternative to using myisamchk is to use the REPAIR TABLE, ANALYZE TABLE, OPTIMIZE TABLE, or ALTER TABLE. These statements are performed by the server, which knows the proper full-text parameter values to use.

"Check" Options

myisamchk supports the following options for table checking operations:

"Repair" Options

myisamchk supports the following options for table repair operations (operations performed when an option such as --recover or --safe-recover is given):

Other Options

myisamchk supports the following options for actions other than table checks and repairs:

Obtaining table information

To obtain a description of a MyISAM table or statistics about it, use the commands shown here. The output from these commands is explained later in this section.

The tbl_name argument can be the name of a MyISAM table or the name of its index file. Multiple tbl_name arguments can be given.

Suppose a table named person has the following structure. The MAX_ROWS table option is included so that in the example output from myisamchk shown later, some values are smaller and fit the output format more easily.

CREATE TABLE person
(
  id         INT NOT NULL AUTO_INCREMENT,
  last_name  VARCHAR(20) NOT NULL,
  first_name VARCHAR(20) NOT NULL,
  birth      DATE,
  death      DATE,
  PRIMARY KEY (id),
  INDEX (last_name, first_name),
  INDEX (birth)
) MAX_ROWS = 1000000;

Suppose also the table has these data and index file sizes:

-rw-rw----  1 mysql  mysql  9347072 Aug 19 11:47 person.MYD
-rw-rw----  1 mysql  mysql  6066176 Aug 19 11:47 person.MYI

Example of myisamchk -dvv output:

MyISAM file:         person
Record format:       Packed
Character set:       latin1_swedish_ci (8)
File-version:        1
Creation time:       2009-08-19 16:47:41
Recover time:        2009-08-19 16:47:56
Status:              checked,analyzed,optimized keys
Auto increment key:              1  Last value:                306688
Data records:               306688  Deleted blocks:                 0
Datafile parts:             306688  Deleted data:                   0
Datafile pointer (bytes):        4  Keyfile pointer (bytes):        3
Datafile length:           9347072  Keyfile length:           6066176
Max datafile length:    4294967294  Max keyfile length:   17179868159
Recordlength:                   54
table description:
Key Start Len Index   Type                 Rec/key         Root  Blocksize
1   2     4   unique  long                       1        99328       1024
2   6     20  multip. varchar prefix           512      3563520       1024
27        20          varchar                  512
3   48    3   multip. uint24 NULL           306688      6065152       1024
Field Start Length Nullpos Nullbit Type
1     1     1
2     2     4                      no zeros
3     6     21                     varchar
4     27    21                     varchar
5     48    3      1       1       no zeros
6     51    3      1       2       no zeros

Explanations for the types of information myisamchk produces are given here. "Keyfile" refers to the index file. "Record" and "row" are synonymous, as are "field" and "column."

The initial part of the table description contains these values:

The table description part of the output lists of all keys in the table. For each key, myisamchk displays some low-level information:

The last part of the output provides information about each column:

The Huff tree and Bits fields are displayed if the table was compressed with myisampack. See myisampack, for an example of this information. Example of myisamchk -eiv output:

Checking MyISAM file: person
Data records:  306688   Deleted blocks:       0
- check file-size
- check record delete-chain
No recordlinks
- check key delete-chain
block_size 1024:
- check index reference
- check data record references index: 1
Key:  1:  Keyblocks used:  98%  Packed:    0%  Max levels:  3
- check data record references index: 2
Key:  2:  Keyblocks used:  99%  Packed:   97%  Max levels:  3
- check data record references index: 3
Key:  3:  Keyblocks used:  98%  Packed:  -14%  Max levels:  3
Total:    Keyblocks used:  98%  Packed:   89%
- check records and index references*** LOTS OF ROW NUMBERS DELETED ***
Records:            306688  M.recordlength:       25  Packed:            83%
Recordspace used:       97% Empty space:           2% Blocks/Record:   1.00
Record blocks:      306688  Delete blocks:         0
Record data:       7934464  Deleted data:          0
Lost space:         256512  Linkdata:        1156096
User time 43.08, System time 1.68
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 0, Physical pagefaults 0, Swaps 0
Blocks in 0 out 7, Messages in 0 out 0, Signals 0
Voluntary context switches 0, Involuntary context switches 0
Maximum memory usage: 1046926 bytes (1023k)

myisamchk -eiv output includes the following information:

Memory usage

The manner you allocate memory is important when you run myisamchk.

myisamchk uses no more memory than its memory-related variables are set to use. If you are going to use myisamchk on large tables, decide first how much memory you want it to use. The default is to use only about 3 MB to perform repairs, but using larger values, you can get myisamchk to operate faster. For example, if you have more than 512 MB RAM available, you could use options such as these (in addition to any other options you might specify):

shell> myisamchk --myisam_sort_buffer_size=256M \
           --key_buffer_size=512M \
           --read_buffer_size=64M \
           --write_buffer_size=64M ...

Using --myisam_sort_buffer_size=16M is probably enough for most cases.

Be aware that myisamchk uses temporary files in the directory specified by the environment variable TMPDIR. If TMPDIR points to a memory file system, out of memory errors can easily occur. If this happens, run myisamchk with the --tmpdir=path option to specify a directory on a file system with more space. When performing repair operations, myisamchk also needs a lot of disk space:

• Twice the size of the data file (the original file and a copy). This space is not needed if you do a repair with --quick; in this instance, only the index file is re-created. This space must be available on the same file system as the original data file, as the copy is created in the same directory as the original.
• Space for the new index file that replaces the old one. The old index file is truncated at the start of the repair operation, so you usually ignore this space. This space must be available on the same file system as the original data file.
• When using --recover or --sort-recover (but not when using --safe-recover), you need space on disk for sorting. This space is allocated in the temporary directory (specified by the TMPDIR environment variable, or with the --tmpdir=path option). The following formula yields the amount of space required:

(largest_key + row_pointer_length) * number_of_rows * 2

• You can check the length of the keys and the row_pointer_length with myisamchk -dv tbl_name (see the section called "OBTAINING TABLE INFORMATION WITH MYISAMCHK"). The row_pointer_length and number_of_rows values are the Datafile pointer and Data records values in the table description. To determine the largest_key value, check the Key lines in the table description. The Len column indicates the number of bytes for each key part. For a multiple-column index, the key size is the sum of the Len values for all key parts.

If you have a problem with disk space during repair, you can try --safe-recover instead of --recover.

Examples

Also, realize that the .MYD and .MYI are in all uppercase.

myisamchk -d tbl_name

Running the command above would give a description and statistics about the table similar to the example below.

MyISAM file: posts Record format: Packed Character set: latin1_swedish_ci (8)
Data records: 2273 Deleted blocks: 0 Recordlength: 2111 table description: Key Start Len Index type 1 1 8
unique ulonglong 2 309 600 multip. ? prefix 3 1736 60 multip. varchar prefix 65 60 varchar 17 8 ulonglong 1 8
ulonglong 4 957 8 multip. ulonglong 5 5 254 fulltext ? packed 1 4 float 6 5 254 fulltext ? packed 1 4 float

myisamchk -r posts.MYI

Running the command above would repair the posts.MYI table. If the -r does not repair the table, you can force the repair using the -f option. So instead of the command above, you'd type myisamchk -f posts.MYI.

Related commands

mysql — An open-source relational database management system.