Linux and Unix myisamchk command
Quick links
About myisamchk
Syntax
Examples
Related commands
Linux and Unix main page
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).
Notice: 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.
myisamchk [options] tbl_name ...
| --HELP, -H | Display a help message and exit. Options are presented in a single list. |
| --debug=debug_options, -# debug_options | Write a debugging log. A typical debug_options string is 'd:t:o,file_name'. The default is 'd:t:o,/tmp/myisamchk.trace'. |
| --silent, -s | Silent mode. Write output only when errors occur. You can use -s twice (-ss) to make myisamchk very silent. |
| --verbose, -v | Verbose mode. Print more information about what the program does. This can be used with -d and -e. Use -v multiple times (-vv, -vvv) for even more output. |
| --version, -V | Display version information and exit. |
| --wait, -w | Instead of terminating with an error if the table is locked, wait until the table is unlocked before continuing. If you are running mysqld with external locking disabled, the table can be locked only by another myisamchk command. |
| --check, -c | Check the table for errors. This is the default operation if you specify no option that selects an operation type explicitly. |
| --check-only-changed, -C | Check only tables that have changed since the last check. |
| --extend-check, -e | Check the table very thoroughly. This is quite slow if the table has many indexes. This option should only be used in extreme cases. Normally, myisamchk or myisamchk --medium-check should be able to determine whether there are any errors in the table. If you are using --extend-check and have plenty of memory, setting the key_buffer_size variable to a large value helps the repair operation run faster. |
| --fast, -F | Check only tables that haven't been closed properly. |
| --force, -f | Do a repair operation automatically if myisamchk finds any errors in the table. The repair type is the same as that specified with the --recover or -r option. |
| --information, -i | Print informational statistics about the table that is checked. |
| --medium-check, -m | Do a check that is faster than an --extend-check operation. This finds only 99.99% of all errors, which should be good enough in most cases. |
| --read-only, -T | Do not mark the 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. |
| --update-state, -U | Store information in the .MYI file to indicate when the table was checked and whether the table crashed. This should be used to get 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. |
| --backup, -B | Make a backup of the .MYD file as file_name-time.BAK |
| --character-sets-dir=path | The directory where character sets are installed. See Section 9.2, The Character Set Used for Data and Sorting. |
| --correct-checksum | Correct the checksum information for the table. |
| --data-file-length=len, -D len | The maximum length of the data file (when re-creating data file when it is full. |
| --extend-check, -e | Do a repair that tries to recover every possible row from the data file. Normally, this also finds a lot of garbage rows. Do not use this option unless you are desperate. |
| --force, -f | Overwrite old intermediate files (files with names like tbl_name.TMD) instead of aborting. |
| --keys-used=val, -k val | For myisamchk, the option value is a bit-value that indicates which indexes to update. Each binary bit of the option value corresponds to a table index, where the first index is bit 0. An option value of 0 disables updates to all indexes, which can be used to get faster inserts. Deactivated indexes can be reactivated by using myisamchk -r. |
| --no-symlinks, -l | Do not follow symbolic links. Normally myisamchk repairs the table that a symlink points to. This option does not exist as of MySQL 4.0 because versions from 4.0 on do not remove symlinks during repair operations. |
| --max-record-length=len | Skip rows larger than the given length if myisamchk cannot allocate memory to hold them. |
| --parallel-recover, -p | Use the same technique as -r and -n, but create all the keys in parallel, using different threads. This is beta-quality code. Use at your own risk! |
| --quick, -q | Achieve a faster repair by not modifying the data file. You can specify this option twice to force myisamchk to modify the original data file in case of duplicate keys. |
| --recover, -r | Do a repair that can fix almost any problem except unique keys that are not unique (which is an extremely unlikely error with MyISAM tables). If you want to recover a table, this is the option to try first. You should try --safe-recover only if myisamchk reports that the table cannot be recovered using --recover. (In the unlikely case that --recover fails, the data file remains intact.) If you have lots of memory, you should increase the value of sort_buffer_size. |
| --safe-recover, -o | 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. This is an order of magnitude slower than --recover, but can handle a couple of very unlikely cases that --recover cannot. This recovery method also uses much less disk space than --recover. Normally, you should repair first using --recover, and then with --safe-recover only if --recover fails. If you have lots of memory, you should increase the value of key_buffer_size. |
| --set-character-set=name | Change the character set used by the table indexes. This option was replaced by --set-collation in MySQL 5.0.3. |
| --set-collation=name | Specify the collation to use for sorting table indexes. The character set name is implied by the first part of the collation name. This option was added in MySQL 5.0.3. |
| --sort-recover, -n | Force myisamchk to use sorting to resolve the keys even if the temporary files would be very large. |
| --tmpdir=path, -t path | The path of the directory to be used for storing temporary files. If this is not set, myisamchk uses the value of the TMPDIR environment variable. tmpdir can be set to a list of directory paths that are used successively in round-robin fashion for creating temporary files. The separator character between directory names is the colon (:;) |
| --unpack, -u | Unpack a table that was packed with myisampack. |
| --analyze, -a | Analyze the distribution of key values. This improves join performance by enabling the join optimizer to better choose the order in which to join the tables and which indexes it should use. To obtain information about the key distribution, use a myisamchk --description --verbose tbl_name command or the SHOW INDEX FROM tbl_name statement. |
| --block-search=offset, -b offset | Find the record that a block at the given offset belongs to. |
| --description, -d | Print some descriptive information about the table. Specifying the --verbose option once or twice produces additional information. See the section called MYISAMCHK TABLE INFORMATION |
| --set-auto-increment[=value], -A[value] | Force AUTO_INCREMENT numbering for new records to start at the given value (or higher, if there are existing records with AUTO_INCREMENT values this large). If value is not specified, AUTO_INCREMENT numbers for new records begin with the largest value currently in the table, plus one. |
| --sort-records=N, -R N | Sort records according to a particular index. This makes your data much more localized and may speed up range-based SELECT and ORDER BY operations that use this index. (The first time you use this option to sort a table, it may be very slow.) To determine a table's index numbers, use SHOW INDEX, which displays a table's indexes in the same order that myisamchk sees them. Indexes are numbered beginning with 1. If keys are not packed (PACK_KEYS=0), they have the same length, so when myisamchk sorts and moves records, it just overwrites record offsets in the index. If keys are packed (PACK_KEYS=1), myisamchk must unpack key blocks first, then re-create indexes and pack the key blocks again. (In this case, re-creating indexes is faster than updating offsets for each index.) |
Note: As mentioned above myisamchk is a utility to check MyISAM tables, which have .MYD and .MYI extension. These files are stored in the database folder under the /var/lib/mysql directory. For example, if your database was named hope it'd be under the /var/lib/mysql/hope directory. Also keep in mind that the .MYD and .MYI are in all uppercase.
myisamchk -d tbl_name
Running the above command would give a description and statistics about the table similar to the below example.
MyISAM file: posts Record format: Packed Character set: latin1_swedish_ci (8) Data records: 2273 Deleted blocks: 0 Recordlength: 2111 table description: Key Start Len Index Type 1 1 8 unique ulonglong 2 309 600 multip. ? prefix 3 1736 60 multip. varchar prefix 65 60 varchar 17 8 ulonglong 1 8 ulonglong 4 957 8 multip. ulonglong 5 5 254 fulltext ? packed 1 4 float 6 5 254 fulltext ? packed 1 4 float
myisamchk -r posts.MYI
Running the above command would repair the posts.MYI table. If the -r does not repair the table you can force the repair by using the -f option. So instead of the above command you'd type myisamchk -f posts.MYI.