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

OptionDescription
-H, --HELPDisplay help and exit. Options are presented in a single list.
-?, --helpDisplay help and exit. Options are grouped by type of operation.
-debug=options, -# optionsWrite a debugging log. A typical debug_options string is ´d:t:o,file_name´. The default is ´d:t:o,/tmp/myisamchk.trace´. (Available in debug builds only)
-t path, --tmpdir=pathPath 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, --silentOnly print errors. One can use two -s (-ss) to make myisamchk very silent.
-v, --verbosePrint more information. This can be used with --description and --check. Use many -v for more verbosity.
-V, --versionPrint version and exit.
-w, --waitIf table is locked, wait instead of returning an error.
--print-defaultsPrint the program argument list and exit.
--no-defaultsDon't read default options from any option file.
--defaults-file=filenameOnly 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=filenameRead 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=strAlso 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

VariableDefault Value
decode_bits9
ft_max_word_lenversion-dependent
ft_min_word_len4
ft_stopword_filebuilt-in list
key_buffer_size1044480
key_cache_block_size1024
myisam_block_size1024
myisam_sort_buffer_size134216704
myisam_sort_key_blocks16
read_buffer_size262136
sort_buffer_size134216704
sort_key_blocks16
stats_methodnulls_unequal
write_buffer_size262136

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:

OptionDescription
-c, --checkCheck table for errors. This is the default operation if you specify no option that selects an operation type explicitly.
-e, --extend-checkCheck 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, --fastCheck only tables that haven't been closed properly.
-C, --check-only-changedCheck only tables that have changed since last check.
-f, --forceRestart with '-r' (recover) if there are any errors in the table. States will be updated as with '--update-state'.
-i, --informationPrint statistics information about the table that is checked.
-m, --medium-checkFaster than extend-check, but only finds 99.99% of all errors. Should be good enough for most cases.
-U --update-stateMark 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 mysqld server is using the table and you are running it with external locking disabled.
-T, --read-onlyDon'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 mysqld 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':

OptionDescription
-B, --backupMake a backup of the .MYD file as 'filename-time.BAK'.
--correct-checksumCorrect 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-checkTry 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, --forceOverwrite 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-keysCreate 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, --recoverCan 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-recoverForces recovering with sorting even if the temporary file would be very large.
-p, --parallel-recoverUses the same technique as '-r' and '-n', but creates all the keys in parallel, in different threads.
-o, --safe-recoverUses 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_nameDirectory where the character sets are installed.
--set-collation=nameChange the collation (and by implication, the character set) used by the index.
-q, --quickFaster 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, --unpackUnpack file packed with myisampack.

Other Actions

OptionDescription
-a, --analyzeAnalyze 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=nameSpecifies 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, --descriptionPrint 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-indexSort 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=offsetFind 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

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.