The myisamchk utility gets information about your database tables or checks, repairs, or optimizes them. myisamchk works with
MyISAMtables (tables that have
.MYIfiles for storing data and indexes).
The use of myisamchk with partitioned tables is not supported.
It is best to make a backup of a table before performing a table repair operation; under some circumstances the operation might cause data loss. Possible causes include but are not limited to file system errors.
Invoke myisamchk like this:
With no options, myisamchk simply checks your table as the default operation. To get more information or to tell myisamchk to take corrective action, specify options as described in the following discussion.
tbl_nameis the database table you want to check or repair. If you run myisamchk somewhere other than in 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 wish. You can also specify a table by naming its index file (the file with the
.MYIsuffix). This allows you to specify all tables in a directory by using the pattern
*.MYI. For example, if you are in a database directory, you can check all the
MyISAMtables in that directory like this:
If you are not in the database directory, you can check all the tables there by specifying the path to the directory:
You can even check all tables in all databases by specifying a wildcard with the path to the MySQL data directory:
The recommended way to quickly check all
myisamchk --silent --fast
If you want to check all
MyISAMtables and repair any that are corrupted, you can use the following command:
myisamchk --silent --force --fast --update-state \
--key_buffer_size=64M --sort_buffer_size=64M \
--read_buffer_size=1M --write_buffer_size=1M \
MySQL Enterprise. For expert advice on checking and repairing tables, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
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 to lock all tables that myisamchk is being used on.
Otherwise, when you run myisamchk, it may display the following error message:
warning: clients are using or haven't closed the table properly
This means that you are trying to check a table that has been updated by another program (such as the mysqld server) that hasn't yet closed the file or that has died without closing the file properly, which can sometimes lead to the corruption of one or more
If mysqld is running, you must force it to flush any table modifications that are still buffered in memory by using
FLUSH TABLES. You should then ensure that no one is using the tables while you are running myisamchk
myisamchk supports the following options, which can be specified on the command line or in the
[myisamchk]option file group. myisamchk also supports the options for processing option files described at Section 126.96.36.199.1, “Command-Line Options that Affect Option-File Handling”.
Format Config File Description Introduction Deprecated Removed --analyze analyze Analyze the distribution of key values --backup backup Make a backup of the .MYD file as file_name-time.BAK --block-search=offset block-search Find the record that a block at the given offset belongs to --check check Check the table for errors --check-only-changed check-only-changed Check only tables that have changed since the last check --correct-checksum correct-checksum Correct the checksum information for the table --data-file-length=len data-file-length Maximum length of the data file (when re-creating data file when it is full) --debug[=debug_options] debug Write a debugging log decode_bits=# decode_bits Decode_bits --description description Print some descriptive information about the table --extend-check extend-check Do a repair that tries to recover every possible row from the data file --extended-check extended-check Check the table very thoroughly --fast fast Check only tables that haven't been closed properly --force force Do a repair operation automatically if myisamchk finds any errors in the table --force force-recover Overwrite old temporary files. For use with the -r or -o option ft_max_word_len=# ft_max_word_len Maximum word length for FULLTEXT indexes ft_min_word_len=# ft_min_word_len Minimum word length for FULLTEXT indexes ft_stopword_file=value ft_stopword_file Use stopwords from this file instead of built-in list --HELP Display help message and exit --help Display help message and exit --information information Print informational statistics about the table that is checked key_buffer_size=# key_buffer_size The size of the buffer used for index blocks for MyISAM tables --keys-used=val keys-used A bit-value that indicates which indexes to update --max-record-length=len max-record-length Skip rows larger than the given length if myisamchk cannot allocate memory to hold them --medium-check medium-check Do a check that is faster than an --extend-check operation myisam_block_size=# myisam_block_size Block size to be used for MyISAM index pages --parallel-recover parallel-recover Uses the same technique as -r and -n, but creates all the keys in parallel, using different threads (beta) --quick quick Achieve a faster repair by not modifying the data file. read_buffer_size=# read_buffer_size Each thread that does a sequential scan allocates a buffer of this size for each table it scans --read-only read-only Don't mark the table as checked --recover recover Do a repair that can fix almost any problem except unique keys that aren't unique --safe-recover safe-recover Do a repair using an old recovery method that reads through all rows in order and updates all index trees based on the rows found --set-auto-increment[=value] set-auto-increment Force AUTO_INCREMENT numbering for new records to start at the given value --set-collation=name set-collation Specify the collation to use for sorting table indexes --silent silent Silent mode sort_buffer_size=# sort_buffer_size The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE --sort-index sort-index Sort the index tree blocks in high-low order sort_key_blocks=# sort_key_blocks sort_key_blocks --sort-records=# sort-records Sort records according to a particular index --sort-recover sort-recover Force myisamchk to use sorting to resolve the keys even if the temporary files would be very large stats_method=value stats_method Specifies how MyISAM index statistics collection code should treat NULLs --tmpdir=path tmpdir Path of the directory to be used for storing temporary files --unpack unpack Unpack a table that was packed with myisampack --update-state update-state Store information in the .MYI file to indicate when the table was checked and whether the table crashed --verbose Verbose mode --version Display version information and exit write_buffer_size=# write_buffer_size Write buffer size