myisamchk

myisamchk

myisamchk is a commandline tool for checking, repairing and optimizing non-partitioned MyISAM tables.

myisamchk is run from the commandline as follows:

myisamchk [OPTIONS] tables[.MYI]

The full list of options are listed below. One or more MyISAM tables can be specified. MyISAM tables have an associated .MYI index file, and the table name can either be specified with or without the .MYI extension. Referencing it with the extension allows you to use wildcards, so it's possible to run myisamchk on all the MyISAM tables in the database with *.MYI.

The path to the files must also be specified if they're not in the current directory.

myisamchk should not be run while anyone is accessing any of the affected tables. It is also best to make a backup before running.

With no options, myisamchk simply checks your table as the default operation.

The following options can be set while passed as commandline options to myisamchk, or set with a [myisamchk] section in your my.cnf file.

General Options

Option	Description
-H, --HELP	Display help and exit. Options are presented in a single list.
-?, --help	Display help and exit. Options are grouped by type of operation.
-debug=options, -
## options	Write a debugging log. A typical debug_options string isd:t:o,file_name´. The default isd:t:o,/tmp/myisamchk.trace´. (Available in debug builds only)
| -t path, --tmpdir=path	Path for temporary files. Multiple paths can be specified, separated by colon (:) on Unix and semicolon (;) on Windows. They will be used in a round-robin fashion. If not set, the TMPDIR environment variable is used.
| -s, --silent	Only print errors. One can use two -s (-ss) to make myisamchk very silent.
| -v, --verbose	Print more information. This can be used with --description and --check. Use many -v for more verbosity.
| -V, --version	Print version and exit.
| -w, --wait	If table is locked, wait instead of returning an error.
| --print-defaults	Print the program argument list and exit.
| --no-defaults	Don't read default options from any option file.
| --defaults-file=filename	Only read default options from the given file filename, which can be the full path, or the path relative to the current directory.
| --defaults-extra-file=filename	Read the file filename, which can be the full path, or the path relative to the current directory, after the global files are read.
| --defaults-group-suffix=str	Also read groups with a suffix of str. For example, --defaults-group-suffix=x would read the groups [myisamchk] and [myisamchk_x]

The following variables can also be set by using --var_name=value, for example --ft_min_word_len=5

Variable	Default Value
decode_bits	9
ft_max_word_len	version-dependent
ft_min_word_len	4
ft_stopword_file	built-in list
key_buffer_size	1044480
key_cache_block_size	1024
myisam_block_size	1024
myisam_sort_buffer_size	134216704
myisam_sort_key_blocks	16
read_buffer_size	262136
sort_buffer_size	134216704
sort_key_blocks	16
stats_method	nulls_unequal
write_buffer_size	262136

Checking Tables

If no option is provided, myisamchk will perform a check table. It is possible to check MyISAM tables without shutting down or restricting access to the server by using CHECK TABLE instead.

The following check options are available:

Option	Description
-c, --check	Check table for errors. This is the default operation if you specify no option that selects an operation type explicitly.
-e, --extend-check	Check the table VERY throughly. Only use this in extreme cases as it may be slow, and myisamchk should normally be able to find out if the table has errors even without this switch. Increasing the key_buffer_size can help speed the process up.
-F, --fast	Check only tables that haven't been closed properly.
-C, --check-only-changed	Check only tables that have changed since last check.
-f, --force	Restart with '-r' (recover) if there are any errors in the table. States will be updated as with '--update-state'.
-i, --information	Print statistics information about the table that is checked.
-m, --medium-check	Faster than extend-check, but only finds 99.99% of all errors. Should be good enough for most cases.
-U --update-state	Mark tables as crashed if you find any errors. This should be used to get the full benefit of the --check-only-changed option, but you shouldn´t use this option if the mariadbd server is using the table and you are running it with external locking disabled.
-T, --read-only	Don't mark table as checked. This is useful if you use myisamchk to check a table that is in use by some other application that does not use locking, such as mariadbd when run with external locking disabled.

Repairing Tables

It is also possible to repair MyISAM tables by using REPAIR TABLE.

The following repair options are available, and are applicable when using '-r' or '-o':

Option	Description
-B, --backup	Make a backup of the .MYD file as 'filename-time.BAK'.
--correct-checksum	Correct the checksum information for table.
-D len, --data-file-length=#	Max length of data file (when recreating data file when it's full).
-e, --extend-check	Try to recover every possible row from the data file. Normally this will also find a lot of garbage rows; Don't use this option if you are not totally desperate.
-f, --force	Overwrite old temporary files. Add another --force to avoid 'myisam_sort_buffer_size is too small' errors. In this case we will attempt to do the repair with the given myisam_sort_buffer_size and dynamically allocate as many management buffers as needed.
-k val, --keys-used=#	Specify which keys to update. The value is a bit mask of which keys to use. Each binary bit corresponds to a table index, with the first index being bit 0. 0 disables all index updates, useful for faster inserts. Deactivated indexes can be reactivated by using myisamchk -r.
--create-missing-keys	Create missing keys. This assumes that the data file is correct and that the number of rows stored in the index file is correct. Enables --quick
--max-record-length=#	Skip rows larger than this if myisamchk can't allocate memory to hold them.
-r, --recover	Can fix almost anything except unique keys that aren't unique (a rare occurrence). Usually this is the best option to try first. Increase myisam_sort_buffer_size for better performance.
-n, --sort-recover	Forces recovering with sorting even if the temporary file would be very large.
-p, --parallel-recover	Uses the same technique as '-r' and '-n', but creates all the keys in parallel, in different threads.
-o, --safe-recover	Uses old recovery method; Slower than '-r' but uses less disk space and can handle a couple of cases where '-r' reports that it can't fix the data file. Increase key_buffer_size for better performance.
--character-sets-dir=directory_name	Directory where the character sets are installed.
--set-collation=name	Change the collation (and by implication, the character set) used by the index.
-q, --quick	Faster repair by not modifying the data file. One can give a second '-q' to force myisamchk to modify the original datafile in case of duplicate keys. NOTE: Tables where the data file is corrupted can't be fixed with this option.
-u, --unpack	Unpack file packed with myisampack.

Other Actions

Option	Description
-a, --analyze	Analyze distribution of keys. Will make some joins faster as the join optimizer can better choose the order in which to join the tables and which indexes to use. You can check the calculated distribution by using '--description --verbose table_name' or SHOW INDEX FROM table_name.
--stats_method=name	Specifies how index statistics collection code should treat NULLs. Possible values of name are "nulls_unequal" (default), "nulls_equal" (emulate MySQL 4.0 behavior), and "nulls_ignored".
-d, --description	Print some descriptive information about the table. Specifying the --verbose option once or twice produces additional information.
-A [value], --set-auto-increment[=value]	Force auto_increment to start at this or higher value. If no value is given, then sets the next auto_increment value to the highest used value for the auto key + 1.
-S, --sort-index	Sort the index tree blocks in high-low order. This optimizes seeks and makes table scans that use indexes faster.
-R index_num, --sort-records=#	Sort records according to the given index (as specified by the index number). This makes your data much more localized and may speed up range-based SELECTs and ORDER BYs using this index. It may be VERY slow to do a sort the first time! To see the index numbers, SHOW INDEX displays table indexes in the same order that myisamchk sees them. The first index is 1.
-b offset, --block-search=offset	Find the record to which a block at the given offset belongs.

For more, see Memory and Disk Use With myisamchk.

Examples

Check all the MyISAM tables in the current directory:

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

Check all tables in all databases by specifying a wildcard with the path to the MariaDB data directory:

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

The recommended way to quickly check all MyISAM tables:

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

Check all MyISAM tables and repair any that are corrupted:

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

See Also

• Memory and Disk Use With myisamchk

_{This page is licensed: CC BY-SA / Gnu FDL}