myisamchk
The myisamchk utility allows you to check and repair damaged tables, display table information, perform index key value distribution analysis, and disable or enable indexes. Chapter 4, "Query Optimization," provides more information on key analysis and index disabling. Chapter 13, "Database Backups, Maintenance, and Repair," provides more information on table checking and repair.
myisamchk is used for tables that are managed by the MyISAM storage engine. These tables have data and index filenames with .MYD and .MYI suffixes, respectively. If you tell myisamchk to operate on a table of the wrong type, it prints a warning message and ignores the table.
Many of the operations that myisamchk does can also be performed by issuing SQL statements to the server. These statements include ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, and REPAIR TABLE. You can issue these statements directly, or you can use the mysqlcheck program, which provides a command-line interface to most of the SQL table-maintenance statements. In general, it is easier and safer to use these statements or mysqlcheck rather than myisamchk.
One danger of using myisamchk to perform maintenance on a table is that you must prevent the server from accessing the table concurrently. This is necessary because the server and myisamchk both access table files directly. If they are allowed to do so at the same time, you can destroy the table. If you really want to use myisamchk, be sure to consult "Performing Database Maintenance with the Server Running," in Chapter 13, which discusses how to prevent the server from using a table while myisamchk is working on it.
You must also take special care when using myisamchk for tables that contain FULLTEXT indexes if both of these conditions are true:
You are using myisamchk to perform an operation that modifies indexes. These include analysis and repair operations. You are running the server using a non-default value for any of these FULLTEXT-related system variables: ft_max_word_len, ft_min_word_len, or ft_stopword_file.
When both of these conditions hold, you must use appropriate options to tell myisamchk what FULLTEXT parameters to use, because it does not know what values the server is using. If you do not do this, myisamchk will build FULLTEXT indexes using different parameter values than the server expects and FULLTEXT searches will return incorrect results. Suppose that you run your server using the following non-default option settings for the minimum word length and stopword file:
[mysqld]
ft_min_word_len=2
ft_stopword_file=/var/mysql/data/my-stopwords
In this case, you must indicate those same values to myisamchk for any index-changing operation that you perform on tables that contain FULLTEXT indexes. You can do this on the command line with --ft_min_word_len and --ft_stopword_list options, but it's better to record the values in an option file so that you don't forget to use them. Use an option group similar to the one used for the server:
[myisamchk]
ft_min_word_len=2
ft_stopword_file=/var/mysql/data/my-stopwords
You can avoid the problem of FULLTEXT parameter mismatch entirely by using SQL statements such as REPAIR TABLE or ANALYZE TABLE for table maintenance. Then the server does the index modification, and, because it knows what FULLTEXT parameters it is using, applies them for maintenance operations on tables that contain FULLTEXT indexes.
Usage
myisamchk [options] tbl_name[.MYI] ...
With no options, myisamchk checks the named tables for errors. Otherwise, it processes the tables according to the meaning of the specified options. If you perform an operation that might modify a table, it's a good idea to make a copy of it first.
A tbl_name argument can be either the name of a table or the name of the index file for the table. (For MyISAM tables, index files have an extension of .MYI.) Using index filenames is convenient because you can use filename wildcards to operate on all tables for a given storage type in a single command. For example, you can check all the MyISAM tables in the current directory as follows:
% myisamchk *.MYI
myisamchk makes no assumptions about where table files are located. If the files that you want to use are not in the current directory, you must specify the pathname to them. Because table files are not assumed to be located under the server's data directory, you can copy table files into another directory and operate on the copies rather than the originals.
Standard Options Supported by myisamchk
--character-sets-dir --set-variable --version
--debug --silent
--help --verbose
The --silent option means that only error messages are printed. The --verbose option prints more information when given with the --check, --description, or --extend-check options. The --silent and --verbose options can be specified multiple times for increased effect.
The standard --help option prints the help message with options grouped by function. myisamchk also supports --HELP and -H options that display all options in a single alphabetical list.
Options Specific to myisamchk
Some of these options refer to index numbers. Indexes are numbered beginning with 1. You can issue a SHOW INDEX query or use a mysqlshow --keys command to determine the index numbering for a particular table. The Key_name column in the output lists indexes in the same order that myisamchk sees them.
--analyze, -a Perform key distribution analysis. This can help the server perform index-based lookups and joins more quickly. You can obtain information about key distribution after the analysis by running myisamchk again with the --description and --verbose options. --backup, -B For options that modify the data (.MYD) file, make a backup using a filename of the form tbl_name-time.BAK. time is a number representing a timestamp. myisamchk writes the backup file to the directory where the table files are located. This option does not have the same function as the BACKUP TABLE statement. --block-search=n, -b n Print out the start of the table row that contains a block starting at block n. This is for debugging only. --check, -c Check tables for errors. This is the default action if no options are specified. --check-only-changed, -C Check tables only if they have not been changed since the last check. --correct-checksum For tables created with the CHECKSUM = 1 option, ensure that the checksum information in the table is correct. --data-file-length=n, -D n The maximum length to which the data file should be allowed to grow when rebuilding a data file that has become full. (This occurs when a file reaches the size limit imposed by MySQL or by the file-size constraints of your operating system. It also occurs when the number of rows reaches the limit imposed by internal table data structures.) The value is specified in bytes. This option is effective only when used with --recover or --safe-recover. --description, -d Print descriptive information about the table. --extend-check, -e Perform an extended table check. It should rarely be necessary to use this option because myisamchk normally finds any errors with one of the less extensive checking modes. --fast, -F Check tables only if they have not been closed properly. This can occur, for example, if the server host crashes while mysqld has the tables open. In this case, mysqld has no opportunity to close the table. --force, -f Force a table to be checked or repaired even if a temporary file for the table already exists. Normally, myisamchk simply exits after printing an error message if it finds a file named tbl_name.TMD, because that might indicate that another instance of the program is already running. However, the file might also exist because you killed a previous invocation of myisamchk while it was running, in which case the file can be safely removed. If you know that to be the case, use --force to tell myisamchk to run even if the temporary file exists. (Alternatively, you can remove the temporary file manually.) If you use --force when checking tables, the program automatically restarts with --recover for any table found to have problems. In addition, myisamchk will update the table state in the same way that the --update-state option does. --information, -i Print statistical information about table contents. --keys-used=n, -k n Used with --recover. The option value n is a bitmask that indicates which indexes to use. The first index is bit zero. (For example, a value of 6 is binary 110 and indicates that the second and third indexes should be used.) A value of 0 turns off all indexes, which can be used to improve the performance of INSERT, DELETE, and UPDATE operations. Turning the indexes back on restores normal indexing behavior (specify a bitmask that includes a bit for each index). --max-record-length=n Ignore records that are larger than n bytes if memory cannot be allocated for them. This option was introduced in MySQL 4.1.1. --medium-check, -m Check a table using a method that is faster than --extend-check, but slightly less thorough. (The myisamchk help message says that this method finds "only" 99.99% of all errors.) This checking mode should be sufficient for most circumstances. Medium check mode works by calculating CRC values for the keys in the index and comparing them with the CRC values calculated from the indexed columns in the data file. --parallel-recover, -p Perform recovery the same way as for --recover, but rebuild the indexes in parallel using multiple threads. This can be faster than a non-parallel rebuild, but this option should be considered experimental. --quick, -q (boolean) This option is used in conjunction with --recover for faster repair than when --recover is used alone. The data file is not touched when both options are given. To force the program to modify the data file if duplicate key values are found, specify the --quick option twice. --read-only, -T Do not mark the table as having been checked. --recover, -r Perform a normal recovery operation. This can fix most problems except the occurrence of duplicate values in an index that should be unique. --safe-recover, -o Use a recovery method that is slower than the method used for --recover, but that can fix a few problems that --recover cannot. --safe-recover also uses less disk space than --recover. --set-auto-increment[=n], -A[n] Set the AUTO_INCREMENT counter so that subsequent sequence values start at n (or at a higher value if the table already contains records with AUTO_INCREMENT values as large as n). If no value n is specified, this option sets the next AUTO_INCREMENT value to one greater than the current maximum value stored in the table. If n is specified after -A, there must be no intervening space or the value will not be interpreted correctly. You can set the AUTO_INCREMENT value for a MyISAM table without using myisamchk by issuing a statement of the following form:
ALTER TABLE tbl_name AUTO_INCREMENT = n;
--set-character-set=charset When rebuilding indexes, use the collating order of the given character set to determine the order of index entries. This is useful for reordering indexes of MyISAM tables after changing the server's default character set, and normally is used in conjunction with --recover and --quick. This option was more useful before MySQL 4.1, when you could not assign character sets at the table and column levels. Tables created in MySQL 4.1 and up have their own character set information, and changing the server's character set does not affect the tables or their index collating order. --sort-index, -S Sort the index blocks to speed up sequential block reads for subsequent retrievals. --sort-records=n, -R n Sort data records according to the order in which records are listed in index n. Subsequent retrievals based on the given index should be faster. The first time you perform this operation on a table, it may be very slow because your records will be unordered. ALTER TABLE … ORDER BY accomplishes the same thing as --sort-records, and normally will be faster. --sort-recover, -n Force sorted recovery even if the temporary file necessary to perform the operation would become quite large. --start-check-pos=n Begin reading the data file at position n. This option is used only for debugging. --tmpdir=dir_name, -t dir_name The pathname of the directory to use for temporary files. The default is the value of the TMPDIR environment variable, or /tmp if that variable is not set. The option value can be given as a list of directories, to be used in round-robin fashion. Under Unix, separate directory names by colons; under Windows or NetWare, separate them by semicolons. --unpack, -u Unpack a packed table that was packed by myisampack. This option can be used to convert a compressed read-only table to modifiable form. It cannot be used with --quick or with --sort-records. --update-state, -U Update the internal flag that is stored in the table to indicate its state. Tables that are okay are marked as such, and tables for which an error occurs are marked as in need of repair. Using this option makes subsequent invocations of myisamchk with the --check-only-changed option more efficient for tables that are okay. --wait, -w If a table is locked, wait until it is available. Without --wait, the program will wait 10 seconds for a lock and then print an error message if no lock can be obtained.
Variables for myisamchk
The following myisamchk variables can be set using the instructions given in "Setting Program Variables" earlier in this appendix.
For tables that contain FULLTEXT indexes, note the caution described in the introductory myisamchk program description.
decode_bits The number of bits to use when decoding compressed tables. Larger values may result in faster operation but will require more memory. The default value is 9; generally, this is sufficient. ft_max_word_len The maximum length of words that can be included in FULLTEXT indexes. Longer words are ignored. The default value is version dependent. ft_min_word_len The minimum length of words that can be included in FULLTEXT indexes. Shorter words are ignored. The default value is 4. ft_stopword_file The stopword file for FULLTEXT indexes. There is no default, which means the built-in stopword list should be used. key_buffer_size The size of the buffer used for index blocks. The default value is 512KB. (This is used for --safe-recover, but not for --recover or --sort-recover.) key_cache_block_size The size of blocks in the key buffer. The default value is 1MB. This variable was added in MySQL 4.1.1. myisam_block_size The block size used for index blocks in the .MYI file. The default value is 1MB. read_buffer_size The read buffer size. The default value is 256KB. sort_buffer_size The size of the buffer used for key value sorting operations. (This is used for --recover or --sort-recover, but not for --safe-recover.) The default value is 20MB. sort_key_blocks This variable is related to the depth of the B-tree structure used for the index. The default value is 16; you should not need to change it. write_buffer_size The write buffer size. The default value is 256KB.
 |
