2
6 myisamchk - MyISAM table-maintenance utility
7
9 myisamchk [options] tbl_name ...
10
12 The myisamchk utility gets information about your database tables or
13 checks, repairs, or optimizes them. myisamchk works with MyISAM tables
14 (tables that have .MYD and .MYI files for storing data and indexes).
15 Caution
17 It is best to make a backup of a table before performing a table repair
18 operation; under some circumstances the operation might cause data
19 loss. Possible causes include but are not limited to filesystem errors.
20 Invoke myisamchk like this:
22 shell> myisamchk [options] tbl_name ...
24 The options specify what you want myisamchk to do. They are described
26 in the following sections. You can also get a list of options by
27 invoking myisamchk --help.
28 With no options, myisamchk simply checks your table as the default
30 operation. To get more information or to tell myisamchk to take
31 corrective action, specify options as described in the following
32 discussion.
33 tbl_name is the database table you want to check or repair. If you run
35 myisamchk somewhere other than in the database directory, you must
36 specify the path to the database directory, because myisamchk has no
37 idea where the database is located. In fact, myisamchk doesn't actually
38 care whether the files you are working on are located in a database
39 directory. You can copy the files that correspond to a database table
40 into some other location and perform recovery operations on them there.
41 You can name several tables on the myisamchk command line if you wish.
43 You can also specify a table by naming its index file (the file with
44 the .MYI suffix). This allows you to specify all tables in a directory
45 by using the pattern *.MYI. For example, if you are in a database
46 directory, you can check all the MyISAM tables in that directory like
47 this:
48 shell> myisamchk *.MYI
50 If you are not in the database directory, you can check all the tables
52 there by specifying the path to the directory:
53 shell> myisamchk /path/to/database_dir/*.MYI
55 You can even check all tables in all databases by specifying a wildcard
57 with the path to the MySQL data directory:
58 shell> myisamchk /path/to/datadir/*/*.MYI
60 The recommended way to quickly check all MyISAM tables is:
62 shell> myisamchk --silent --fast /path/to/datadir/*/*.MYI
64 shell> isamchk --silent /path/to/datadir/*/*.ISM
65 If you want to check all MyISAM tables and repair any that are
67 corrupted, you can use the following command:
68 shell> myisamchk --silent --force --fast --update-state \
70 --key_buffer_size=64M --sort_buffer_size=64M \
71 --read_buffer_size=1M --write_buffer_size=1M \
72 /path/to/datadir/*/*.MYI
73 shell> isamchk --silent --force --key_buffer_size=64M \
74 --sort_buffer_size=64M --read_buffer_size=1M --write_buffer_size=1M \
75 /path/to/datadir/*/*.ISM
76 This command assumes that you have more than 64MB free. For more
78 information about memory allocation with myisamchk, see the section
79 called “MYISAMCHK MEMORY USAGE”.
80 MySQL Enterprise. For expert advice on checking and repairing tables,
82 subscribe to the MySQL Network Monitoring and Advisory Service. For
83 more information see
84 http://www.mysql.com/products/enterprise/advisors.html.
85 Important
87 You must ensure that no other program is using the tables while you are
88 running myisamchk. The most effective means of doing so is to shut down
89 the MySQL server while running myisamchk, or to lock all tables that
90 myisamchk is being used on.
91 Otherwise, when you run myisamchk, it may display the following error
93 message:
94 warning: clients are using or haven't closed the table properly
96 This means that you are trying to check a table that has been updated
98 by another program (such as the mysqld server) that hasn't yet closed
99 the file or that has died without closing the file properly, which can
100 sometimes lead to the corruption of one or more MyISAM tables.
101 If mysqld is running, you must force it to flush any table
103 modifications that are still buffered in memory by using FLUSH TABLES.
104 You should then ensure that no one is using the tables while you are
105 running myisamchk
106 However, the easiest way to avoid this problem is to use CHECK TABLE
108 instead of myisamchk to check tables. See Section 5.2.3, “CHECK TABLE
109 Syntax”.
110
112 The options described in this section can be used for any type of table
113 maintenance operation performed by myisamchk. The sections following
114 this one describe options that pertain only to specific operations,
115 such as table checking or repairing.
116 · --help, -?
118 Display a help message and exit.
120 · --debug=debug_options, -# debug_options
122 Write a debugging log. The debug_options string often is
124 ´d:t:o,file_name'.
125 · --silent, -s
127 Silent mode. Write output only when errors occur. You can use -s
129 twice (-ss) to make myisamchk very silent.
130 · --verbose, -v
132 Verbose mode. Print more information about what the program does.
134 This can be used with -d and -e. Use -v multiple times (-vv, -vvv)
135 for even more output.
136 · --version, -V
138 Display version information and exit.
140 · --wait, -w
142 Instead of terminating with an error if the table is locked, wait
144 until the table is unlocked before continuing. If you are running
145 mysqld with external locking disabled, the table can be locked only
146 by another myisamchk command.
147 You can also set the following variables by using --var_name=value
150 syntax:
151 ┌──────────────────┬───────────────────┐
153 │Variable │ Default Value │
154 ├──────────────────┼───────────────────┤
155 │sort_key_blocks │ 16 │
156 ├──────────────────┼───────────────────┤
157 │stats_method │ nulls_unequal │
158 ├──────────────────┼───────────────────┤
159 │write_buffer_size │ 262136 │
160 ├──────────────────┼───────────────────┤
161 │decode_bits │ 9 │
162 ├──────────────────┼───────────────────┤
163 │ft_max_word_len │ version-dependent │
164 ├──────────────────┼───────────────────┤
165 │ft_min_word_len │ 4 │
166 ├──────────────────┼───────────────────┤
167 │ft_stopword_file │ built-in list │
168 ├──────────────────┼───────────────────┤
169 │key_buffer_size │ 523264 │
170 ├──────────────────┼───────────────────┤
171 │myisam_block_size │ 1024 │
172 ├──────────────────┼───────────────────┤
173 │read_buffer_size │ 262136 │
174 ├──────────────────┼───────────────────┤
175 │sort_buffer_size │ 2097144 │
176 └──────────────────┴───────────────────┘
177 It is also possible to set variables by using
179 --set-variable=var_name=value or -O var_name=value syntax. However,
180 this syntax is deprecated as of MySQL 4.0.
181 The possible myisamchk variables and their default values can be
183 examined with myisamchk --help:
184 sort_buffer_size is used when the keys are repaired by sorting keys,
186 which is the normal case when you use --recover.
187 key_buffer_size is used when you are checking the table with
189 --extend-check or when the keys are repaired by inserting keys row by
190 row into the table (like when doing normal inserts). Repairing through
191 the key buffer is used in the following cases:
192 · You use --safe-recover.
194 · The temporary files needed to sort the keys would be more than twice
196 as big as when creating the key file directly. This is often the
197 case when you have large key values for CHAR, VARCHAR, or TEXT
198 columns, because the sort operation needs to store the complete key
199 values as it proceeds. If you have lots of temporary space and you
200 can force myisamchk to repair by sorting, you can use the
201 --sort-recover option.
202 Repairing through the key buffer takes much less disk space than using
205 sorting, but is also much slower.
206 If you want a faster repair, set the key_buffer_size and
208 sort_buffer_size variables to about 25% of your available memory. You
209 can set both variables to large values, because only one of them is
210 used at a time.
211 myisam_block_size is the size used for index blocks.
213 stats_method influences how NULL values are treated for index
215 statistics collection when the --analyze option is given. It acts like
216 the myisam_stats_method system variable. For more information, see the
217 description of myisam_stats_method in the section called “SYSTEM
218 VARIABLES”, and Section 4.7, “MyISAM Index Statistics Collection”. For
219 MySQL 5.0, stats_method was added in MySQL 5.0.14. For older versions,
220 the statistics collection method is equivalent to nulls_equal.
221 The ft_min_word_len and ft_max_word_len variables are available as of
223 MySQL 4.0.0. ft_stopword_file is available as of MySQL 4.0.19.
224 ft_min_word_len and ft_max_word_len indicate the minimum and maximum
226 word length for FULLTEXT indexes. ft_stopword_file names the stopword
227 file. These need to be set under the following circumstances.
228 If you use myisamchk to perform an operation that modifies table
230 indexes (such as repair or analyze), the FULLTEXT indexes are rebuilt
231 using the default full-text parameter values for minimum and maximum
232 word length and the stopword file unless you specify otherwise. This
233 can result in queries failing.
234 The problem occurs because these parameters are known only by the
236 server. They are not stored in MyISAM index files. To avoid the problem
237 if you have modified the minimum or maximum word length or the stopword
238 file in the server, specify the same ft_min_word_len, ft_max_word_len,
239 and ft_stopword_file values to myisamchk that you use for mysqld. For
240 example, if you have set the minimum word length to 3, you can repair a
241 table with myisamchk like this:
242 shell> myisamchk --recover --ft_min_word_len=3 tbl_name.MYI
244 To ensure that myisamchk and the server use the same values for
246 full-text parameters, you can place each one in both the [mysqld] and
247 [myisamchk] sections of an option file:
248 [mysqld]
250 ft_min_word_len=3
251 [myisamchk]
252 ft_min_word_len=3
253 An alternative to using myisamchk is to use the REPAIR TABLE, ANALYZE
255 TABLE, OPTIMIZE TABLE, or ALTER TABLE. These statements are performed
256 by the server, which knows the proper full-text parameter values to
257 use.
258
260 myisamchk supports the following options for table checking operations:
261 · --check, -c
263 Check the table for errors. This is the default operation if you
265 specify no option that selects an operation type explicitly.
266 · --check-only-changed, -C
268 Check only tables that have changed since the last check.
270 · --extend-check, -e
272 Check the table very thoroughly. This is quite slow if the table has
274 many indexes. This option should only be used in extreme cases.
275 Normally, myisamchk or myisamchk --medium-check should be able to
276 determine whether there are any errors in the table.
277 If you are using --extend-check and have plenty of memory, setting
279 the key_buffer_size variable to a large value helps the repair
280 operation run faster.
281 · --fast, -F
283 Check only tables that haven't been closed properly.
285 · --force, -f
287 Do a repair operation automatically if myisamchk finds any errors in
289 the table. The repair type is the same as that specified with the
290 --recover or -r option.
291 · --information, -i
293 Print informational statistics about the table that is checked.
295 · --medium-check, -m
297 Do a check that is faster than an --extend-check operation. This
299 finds only 99.99% of all errors, which should be good enough in most
300 cases.
301 · --read-only, -T
303 Don't mark the table as checked. This is useful if you use myisamchk
305 to check a table that is in use by some other application that
306 doesn't use locking, such as mysqld when run with external locking
307 disabled.
308 · --update-state, -U
310 Store information in the .MYI file to indicate when the table was
312 checked and whether the table crashed. This should be used to get
313 full benefit of the --check-only-changed option, but you shouldn't
314 use this option if the mysqld server is using the table and you are
315 running it with external locking disabled.
316
318 myisamchk supports the following options for table repair operations:
319 · --backup, -B
321 Make a backup of the .MYD file as file_name-time.BAK
323 · --character-sets-dir=path
325 The directory where character sets are installed. See Section 8.1,
327 “The Character Set Used for Data and Sorting”.
328 · --correct-checksum
330 Correct the checksum information for the table.
332 · --data-file-length=len, -D len
334 Maximum length of the data file (when re-creating data file when it
336 is “full”).
337 · --extend-check, -e
339 Do a repair that tries to recover every possible row from the data
341 file. Normally, this also finds a lot of garbage rows. Don't use
342 this option unless you are desperate.
343 · --force, -f
345 Overwrite old intermediate files (files with names like
347 tbl_name.TMD) instead of aborting.
348 · --keys-used=val, -k val
350 For myisamchk, the option value is a bit-value that indicates which
352 indexes to update. Each binary bit of the option value corresponds
353 to a table index, where the first index is bit 0. An option value of
354 0 disables updates to all indexes, which can be used to get faster
355 inserts. Deactivated indexes can be reactivated by using myisamchk
356 -r.
357 · --no-symlinks, -l
359 Do not follow symbolic links. Normally myisamchk repairs the table
361 that a symlink points to. This option does not exist as of MySQL 4.0
362 because versions from 4.0 on do not remove symlinks during repair
363 operations.
364 · --max-record-length=len
366 Skip rows larger than the given length if myisamchk cannot allocate
368 memory to hold them.
369 · --parallel-recover, -p
371 Uses the same technique as -r and -n, but creates all the keys in
373 parallel, using different threads. This is beta-quality code. Use
374 at your own risk!
375 · --quick, -q
377 Achieve a faster repair by not modifying the data file. You can
379 specify this option twice to force myisamchk to modify the original
380 data file in case of duplicate keys.
381 · --recover, -r
383 Do a repair that can fix almost any problem except unique keys that
385 aren't unique (which is an extremely unlikely error with MyISAM
386 tables). If you want to recover a table, this is the option to try
387 first. You should try --safe-recover only if myisamchk reports that
388 the table can't be recovered using --recover. (In the unlikely case
389 that --recover fails, the data file remains intact.)
390 If you have lots of memory, you should increase the value of
392 sort_buffer_size.
393 · --safe-recover, -o
395 Do a repair using an old recovery method that reads through all rows
397 in order and updates all index trees based on the rows found. This
398 is an order of magnitude slower than --recover, but can handle a
399 couple of very unlikely cases that --recover cannot. This recovery
400 method also uses much less disk space than --recover. Normally, you
401 should repair first with --recover, and then with --safe-recover
402 only if --recover fails.
403 If you have lots of memory, you should increase the value of
405 key_buffer_size.
406 · --set-character-set=name
408 Change the character set used by the table indexes. This option was
410 replaced by --set-collation in MySQL 5.0.3.
411 · --set-collation=name
413 Specify the collation to use for sorting table indexes. The
415 character set name is implied by the first part of the collation
416 name. This option was added in MySQL 5.0.3.
417 · --sort-recover, -n
419 Force myisamchk to use sorting to resolve the keys even if the
421 temporary files would be very large.
422 · --tmpdir=path, -t path
424 Path of the directory to be used for storing temporary files. If
426 this is not set, myisamchk uses the value of the TMPDIR environment
427 variable. tmpdir can be set to a list of directory paths that are
428 used successively in round-robin fashion for creating temporary
429 files. The separator character between directory names is the colon
430 (‘:’) on Unix and the semicolon (‘;’) on Windows, NetWare, and OS/2.
431 · --unpack, -u
433 Unpack a table that was packed with myisampack.
435
437 myisamchk supports the following options for actions other than table
438 checks and repairs:
439 · --analyze, -a
441 Analyze the distribution of key values. This improves join
443 performance by enabling the join optimizer to better choose the
444 order in which to join the tables and which indexes it should use.
445 To obtain information about the key distribution, use a myisamchk
446 --description --verbose tbl_name command or the SHOW INDEX FROM
447 tbl_name statement.
448 MySQL Enterprise. For expert advice on optimizing tables, subscribe to
450 the MySQL Network Monitoring and Advisory Service. For more information
451 see http://www.mysql.com/products/enterprise/advisors.html.
452 · --block-search=offset, -b offset
454 Find the record that a block at the given offset belongs to.
456 · --description, -d
458 Print some descriptive information about the table.
460 · --set-auto-increment[=value], -A[value]
462 Force AUTO_INCREMENT numbering for new records to start at the given
464 value (or higher, if there are existing records with AUTO_INCREMENT
465 values this large). If value is not specified, AUTO_INCREMENT
466 numbers for new records begin with the largest value currently in
467 the table, plus one.
468 · --sort-index, -S
470 Sort the index tree blocks in high-low order. This optimizes seeks
472 and makes table scans that use indexes faster.
473 · --sort-records=N, -R N
475 Sort records according to a particular index. This makes your data
477 much more localized and may speed up range-based SELECT and ORDER BY
478 operations that use this index. (The first time you use this option
479 to sort a table, it may be very slow.) To determine a table's index
480 numbers, use SHOW INDEX, which displays a table's indexes in the
481 same order that myisamchk sees them. Indexes are numbered beginning
482 with 1.
483 If keys are not packed (PACK_KEYS=0), they have the same length, so
485 when myisamchk sorts and moves records, it just overwrites record
486 offsets in the index. If keys are packed (PACK_KEYS=1), myisamchk
487 must unpack key blocks first, then re-create indexes and pack the
488 key blocks again. (In this case, re-creating indexes is faster than
489 updating offsets for each index.)
490
492 Memory allocation is important when you run myisamchk. myisamchk uses
493 no more memory than its memory-related variables are set to. If you are
494 going to use myisamchk on very large tables, you should first decide
495 how much memory you want it to use. The default is to use only about
496 3MB to perform repairs. By using larger values, you can get myisamchk
497 to operate faster. For example, if you have more than 32MB RAM, you
498 could use options such as these (in addition to any other options you
499 might specify):
500 shell> myisamchk --sort_buffer_size=16M --key_buffer_size=16M \
502 --read_buffer_size=1M --write_buffer_size=1M ...
503 Using --sort_buffer_size=16M should probably be enough for most cases.
505 Be aware that myisamchk uses temporary files in TMPDIR. If TMPDIR
507 points to a memory filesystem, you may easily get out of memory errors.
508 If this happens, run myisamchk with the --tmpdir=path option to specify
509 some directory located on a filesystem that has more space.
510 When repairing, myisamchk also needs a lot of disk space:
512 · Double the size of the data file (the original file and a copy).
514 This space is not needed if you do a repair with --quick; in this
515 case, only the index file is re-created. This space is needed on the
516 same filesystem as the original data file! (The copy is created in
517 the same directory as the original.)
518 · Space for the new index file that replaces the old one. The old
520 index file is truncated at the start of the repair operation, so you
521 usually ignore this space. This space is needed on the same
522 filesystem as the original index file!
523 · When using --recover or --sort-recover (but not when using
525 --safe-recover), you need space for a sort buffer. The following
526 formula yields the amount of space required:
527 (largest_key + row_pointer_length) × number_of_rows × 2
529 You can check the length of the keys and the row_pointer_length with
530 myisamchk -dv tbl_name. This space is allocated in the temporary
531 directory (specified by TMPDIR or --tmpdir=path).
532 If you have a problem with disk space during repair, you can try
535 --safe-recover instead of --recover.
536
538 Copyright 1997-2007 MySQL AB
539 This documentation is NOT distributed under a GPL license. Use of this
541 documentation is subject to the following terms: You may create a
542 printed copy of this documentation solely for your own personal use.
543 Conversion to other formats is allowed as long as the actual content is
544 not altered or edited in any way. You shall not publish or distribute
545 this documentation in any form or on any media, except if you
546 distribute the documentation in a manner similar to how MySQL
547 disseminates it (that is, electronically for download on a Web site
548 with the software) or on a CD-ROM or similar medium, provided however
549 that the documentation is disseminated together with the software on
550 the same medium. Any other use, such as any dissemination of printed
551 copies or use of this documentation, in whole or in part, in another
552 publication, requires the prior written consent from an authorized
553 representative of MySQL AB. MySQL AB reserves any and all rights to
554 this documentation not expressly granted above.
555 Please email <docs@mysql.com> for more information.
557
559 For more information, please refer to the MySQL Reference Manual, which
560 may already be installed locally and which is also available online at
561 http://dev.mysql.com/doc/.
562
564 MySQL AB (http://www.mysql.com/). This software comes with no
565 warranty.
566MySQL 5.0 07/04/2007 MYISAMCHK(1)