The myisamchk utility gets information about
your database tables or checks, repairs, or optimizes them.
myisamchk works with
MyISAM tables (tables that have
.MYD and .MYI files
for storing data and indexes).
You can also use the CHECK TABLE
and REPAIR TABLE statements to
check and repair MyISAM tables. See
Section 12.4.2.2, “CHECK TABLE Syntax”, and
Section 12.4.2.5, “REPAIR TABLE Syntax”.
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:
shell> myisamchk [options] tbl_name ...
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
invoking myisamchk --help.
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_name is 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 .MYI
suffix). This enables 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
MyISAM tables in that directory like this:
shell> myisamchk *.MYI
If you are not in the database directory, you can check all the tables there by specifying the path to the directory:
shell> 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:
shell> myisamchk /path/to/datadir/*/*.MYI
The recommended way to quickly check all
MyISAM tables is:
shell> myisamchk --silent --fast /path/to/datadir/*/*.MYI
If you want to check all MyISAM tables and
repair any that are corrupted, you can use the following
command:
shell>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
This command assumes that you have more than 64MB free. For more information about memory allocation with myisamchk, see Section 4.6.3.6, “myisamchk Memory Usage”.
For additional information about using
myisamchk, see
Section 6.6, “MyISAM Table Maintenance and Crash Recovery”.
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
MyISAM tables.
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
However, the easiest way to avoid this problem is to use
CHECK TABLE instead of
myisamchk to check tables. See
Section 12.4.2.2, “CHECK TABLE Syntax”.
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 4.2.3.3.1, “Command-Line Options that Affect Option-File Handling”.
Table 4.9. myisamchk Options
| Format | Option 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 |