MySQL in a Nutshell, 2nd Edition

Name

myisamchk

Synopsis

myisamchk options table[.MYI][...]

This utility checks, repairs, and optimizes MyISAM tables. It works with the table files directly and does not require interaction with the MySQL server. Therefore, it may be necessary to specify the path along with the table or table names in the second argument. Also, tables that are being checked should be locked or the MySQL server daemon should be stopped. This utility works with the index files for the tables, so the suffix .MYI may be given for table names to prevent it from attempting to analyze other files. Omitting the suffix (e.g., work_req instead of work_req.*) will have the same effect as giving a specific suffix (work_req.MYI). To check all of the tables in a database, use the wildcard (i.e., *.MYI). Here is a basic example of how you can use myisamchk to check one table:

myisamchk /data/mysql/workrequests/requests
Checking MyISAM file: /data/mysql/workrequests/requests
Data records:     531   Deleted blocks:       0
myisamchk: warning: 3 clients is using or hasn't closed the table properly
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check record links
MyISAM-table '/data/mysql/workrequests/requests' is usable but should be 
   fixed

No options are specified here, so the default of --check is used. Notice that myisamchk detected a problem with the table. To fix this problem, you can run the utility again, but with the --recover option like so:

myisamchk --recover /data/mysql/workrequests/requests
- recovering (with sort) MyISAM-table
 '/data/mysql/workrequests/requests'
Data records: 531
- Fixing index 1

The following sections list the options available with myisamchk.

myisamchk check options

--check, -c: This option checks tables for errors.
--check-only-changed, -C: This option checks only tables that have changed since the last check.
--extend-check, -e: This option checks tables thoroughly. Use it only in extreme cases.
--fast, -F: Use this option to have the utility check only tables that haven’t been closed properly.
--force, -f: This option repairs tables that report errors during check mode. It restarts the utility with the --recover option if any errors occur.
--information, -i: This option displays statistical information about tables being checked.
--medium-check, -m: This option checks tables more thoroughly than --check, but not as thoroughly as --extend-check.
--read-only, -T: This option tells the utility not to mark tables with status information so that tables may be used by the utility during its check. Tables are not marked as checked when using this option.
--update-state, -U: This option has the utility update tables to indicate when they were checked and mark them as crashed if any errors are found.

myisamchk repair options

--backup, -B: This option makes copies of datafiles (table.MYD), naming them table-datetime.BAK.
--character-sets-dir=path: This option sets the directory where character sets are located.
--correct-checksum: This option corrects a table’s checksum information.
--data-file-length=number, -D number: This option sets the maximum length of a datafile for rebuilding a full datafile.
--extend-check, -e: This option instructs the utility to attempt to recover all rows, including intentionally deleted ones.
--force, -f: This option instructs the utility to ignore error messages and to overwrite temporary files.
--keys-used=bitfield, -k bitfield: This option instructs the utility to have MyISAM updates use only specific keys for faster data inserts.
--max-record-length=number: This option tells the utility to skip rows larger than the length specified if there is not enough memory.
--no-symlinks, -l: This option instructs the utility not to follow symbolic links at the filesystem level.
--parallel-recover, -p: This option is the same as the --recover option, but it creates all keys in parallel using different threads.
--quick, -q: This option repairs only indexes, not datafiles, of uncorrupted tables.
-qq: This option repairs only indexes and updates datafiles only when duplicates are found.
--recover, -r: Use this option to recover a table that has been corrupted. You might also try increasing the variable sort_buffer_size with this option. If this option does not work, try --safe-recover.
--safe-recover, -o: Use this option if --recover fails. It also repairs rows that the --sort-recover option cannot handle (e.g., duplicate values for unique keys).
--set-character-set=set: This option specifies the character set to use.
--set-collation=set: This option specifies the collation to use with the utility when sorting table indexes. Execute SHOW COLLATION; on the server to retrieve a list of collations that may be used with this option.
--sort-recover, -n: This option instructs the utility to use the sort buffer regardless of whether the temporary file would be too large based on default limits.
--tmpdir=path, -t path: This option specifies the directory used by the utility for temporary files. Multiple directories may be given in a colon-separated list on Unix systems and a semicolon-separated list on Windows systems. By default, this utility uses the value for the environmental variable TMPDIR.
--unpack, -u: This option unpacks tables that were packed with the myisampack utility.

Other myisamchk options

--analyze, -a: This option optimizes the use of keys in tables. It can help with some joins. Use the --description and the --verbose options to show the calculated distribution.
--block-search=offset, -b offset: This option searches for a row based on a given offset.
--description, -d: This option displays information about the table.
--set-auto-increment[=value], -A [value]: This option sets the value of an auto-increment column for the next row created. If no value is given, the next value above the highest value found for the column is used.
--sort-index, -S: This option sorts indexes.
--sort-records=index, -R index: This option sorts rows based on the index given.

Global myisamchk options

--debug[=options], -# [options]: This option logs debugging information. The set of options used by default is 'd:t:o,logname'. See Table 16-1 at the end of the list of options under the mysqldump utility for an explanation of these flags and others that may be used.
--character-sets-dir=path: This option specifies the directory containing character sets.
--help, -?: This option displays basic help information.
--silent, -s: This option displays only print error messages. With -ss even less information will be displayed.
--sort-index, -S: This option sorts indexes.
--sort-records=value, -R value: This option sorts records based on the index given.
--tmpdir=path, -t path: This option sets the path for temporary files. Additional paths may be given in a colon-separated list.
--verbose, -v: This option displays more information. Additional vs (e.g., -vv) will provide more information.
--version, -V: This option displays the version of the utility.
--wait, -w: This option instructs the utility to wait before proceeding if the table is locked.