myisamchk for table maintenance and crash recovery
To check/repair MyISAM tables (.MYI and .MYD) you should
use the myisamchk utility. To check/repair ISAM tables
(.ISM and .ISD) you should use the isamchk
utility. See section 9.4 MySQL table types.
In the following text we will talk about myisamchk but everything
also applies to the old isamchk.
You can use the myisamchk utility to get information about your database
tables, check and repair them or optimize them. The following sections
describe how to invoke myisamchk (including a description of its
options), how to set up a table maintenance schedule, and how to use
myisamchk to perform its various functions.
You can in most cases also use the command OPTIMIZE TABLES to
optimize and repair tables, but this is not as fast or reliable (in case
of real fatal errors) as myisamchk. On the other hand,
OPTIMIZE TABLE is easier to use and you don't have to worry about
flushing tables.
See section 7.9 OPTIMIZE TABLE syntax.
myisamchk invocation syntax
myisamchk is invoked like this:
shell> myisamchk [options] tbl_name
The options specify what you want myisamchk to do. They are
described below. (You can also get a list of options by invoking
myisamchk --help.) With no options, myisamchk simply checks your
table. To get more information or to tell myisamchk to take corrective
action, specify options as described below and in the following sections.
tbl_name is the database table you want to check. If you run
myisamchk somewhere other than in the database directory, you must
specify the path to the file, since myisamchk has no idea where your
database is located. Actually, myisamchk doesn't care whether or not
the files you are working on are located in a database directory; you can
copy the files that correspond to a database table into another 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 name as an index file
name (with the `.MYI' suffix), which 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
tables in the 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
myisamchk supports the following options:
-a, --analyze
-#, --debug=debug_options
debug_options string often is
'd:t:o,filename'.
-d, --description
-e, --extend-check
myisamchk should find all errors even without this option.
-f, --force
-f when checking tables (running myisamchk
without -r), myisamchk will automatically restart with -r
on any table for which an error occurs during checking.
--help
-i, --information
-k #, --keys-used=#
-r. Tell the ISAM table handler to update only the first
# indexes. Higher-numbered indexes are deactivated. This can be used
to get faster inserts! Deactivated indexes can be reactivated by using
myisamchk -r.
-l, --no-symlinks
myisamchk repairs the table a symlink points at.
-q, --quick
-r to get a faster repair. Normally, the original data file
isn't touched; you can specify a second -q to force
the original data file to be used.
-r, --recover
-o, --safe-recover
-r, but can
handle a couple of cases that -r cannot handle.
-O var=option, --set-variable var=option
-s, --silent
-s twice (-ss) to make myisamchk
very silent.
-S, --sort-index
-R index_num, --sort-records=index_num
SELECT and ORDER BY operations on
this index. (It may be VERY slow to do a sort the first time!)
To find out a table's index numbers, use SHOW INDEX, which shows a
table's indexes in the same order that myisamchk sees them. Indexes are
numbered beginning with 1.
-u, --unpack
myisampack.
-v, --verbose
-d and
-e. Use -v multiple times (-vv, -vvv) for more
verbosity!
-V, --version
myisamchk version and exit.
-w, --wait
Possible variables for the --set-variable (-O) option are:
key_buffer_size current value: 16776192 read_buffer_size current value: 262136 write_buffer_size current value: 262136 sort_buffer_size current value: 2097144 sort_key_blocks current value: 16 decode_bits current value: 9
myisamchk memory usage
Memory allocation is important when you run myisamchk. myisamchk
uses no more memory than you specify with the -O options. If you are
going to use myisamchk on very large files, you should first decide how
much memory you want it to use. The default is to use only about 3M to fix
things. By using larger values, you can get myisamchk to operate
faster. For example, if you have more than 32M RAM, you could use options
such as these (in addition to any other options you might specify):
shell> myisamchk -O sort=16M -O key=16M -O read=1M -O write=1M ...
Using -O sort=16M should
probably be enough for most cases.
Be aware that myisamchk uses temporary files in TMPDIR. If
TMPDIR points to a memory file system, you may easily get out of
memory errors. If this happens, set TMPDIR to point at some directory
with more space and restart myisamchk
It is a good idea to perform table checks on a regular basis rather than
waiting for problems to occur. For maintenance purposes, you can use
myisamchk -s to check tables. The -s option causes
myisamchk to run in silent mode, printing messages only when errors
occur.
It's a good idea to check tables when the server starts up.
For example, whenever the machine has done a reboot in the middle of an
update, you usually need to check all the tables that could have been
affected. (This is an ``expected crashed table''.) You could add a test to
safe_mysqld that runs myisamchk to check all tables that have
been modified during the last 24 hours if there is an old `.pid'
(process ID) file left after a reboot. (The `.pid' file is created by
mysqld when it starts up and removed when it terminates normally. The
presence of a `.pid' file at system startup time indicates that
mysqld terminated abnormally.)
An even better test would be to check any table whose last-modified time is more recent than that of the `.pid' file.
You should also check your tables regularly during normal system operation.
At TcX, we run a cron job to check all our important tables once a week,
using a line like this in a `crontab' file:
35 0 * * 0 /path/to/myisamchk -s /path/to/datadir/*/*.MYI
This prints out information about crashed tables so we can examine and repair them when needed.
As we haven't had any unexpectedly crashed tables (tables that become corrupted for reasons other than hardware trouble) for a couple of years now (this is really true), once a week is more than enough for us.
We recommend that to start with, you execute myisamchk -s each
night on all tables that have been updated during the last 24 hours,
until you come to trust MySQL as much as we do.
To get a description of a table or statistics about it, use the commands shown below. We explain some of the information in more detail later.
myisamchk -d tbl_name
myisamchk in ``describe mode'' to produce a description of your
table. If you start the MySQL server using the --skip-locking
option, myisamchk may report an error for a table that is updated while
it runs. However, since myisamchk doesn't change the table in describe
mode, there isn't any risk of destroying data.
myisamchk -d -v tbl_name
myisamchk is doing, add -v
to tell it to run in verbose mode.
myisamchk -eis tbl_name
myisamchk -eiv tbl_name
-eis, but tells you what is being done.
Example of myisamchk -d output:
MyISAM file: company.MYI
Record format: Fixed length
Data records: 1403698 Deleted blocks: 0
Recordlength: 226
table description:
Key Start Len Index Type
1 2 8 unique double
2 15 10 multip. text packed stripped
3 219 8 multip. double
4 63 10 multip. text packed stripped
5 167 2 multip. unsigned short
6 177 4 multip. unsigned long
7 155 4 multip. text
8 138 4 multip. unsigned long
9 177 4 multip. unsigned long
193 1 text
Example of myisamchk -d -v output:
MyISAM file: company
Record format: Fixed length
File-version: 1
Creation time: 1999-10-30 12:12:51
Recover time: 1999-10-31 19:13:01
Status: checked
Data records: 1403698 Deleted blocks: 0
Datafile parts: 1403698 Deleted data: 0
Datafilepointer (bytes): 3 Keyfile pointer (bytes): 3
Max datafile length: 3791650815 Max keyfile length: 4294967294
Recordlength: 226
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 8 unique double 1 15845376 1024
2 15 10 multip. text packed stripped 2 25062400 1024
3 219 8 multip. double 73 40907776 1024
4 63 10 multip. text packed stripped 5 48097280 1024
5 167 2 multip. unsigned short 4840 55200768 1024
6 177 4 multip. unsigned long 1346 65145856 1024
7 155 4 multip. text 4995 75090944 1024
8 138 4 multip. unsigned long 87 85036032 1024
9 177 4 multip. unsigned long 178 96481280 1024
193 1 text
Example of myisamchk -eis output:
Checking MyISAM file: company Key: 1: Keyblocks used: 97% Packed: 0% Max levels: 4 Key: 2: Keyblocks used: 98% Packed: 50% Max levels: 4 Key: 3: Keyblocks used: 97% Packed: 0% Max levels: 4 Key: 4: Keyblocks used: 99% Packed: 60% Max levels: 3 Key: 5: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 6: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 7: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 8: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 9: Keyblocks used: 98% Packed: 0% Max levels: 4 Total: Keyblocks used: 98% Packed: 17% Records: 1403698 M.recordlength: 226 Packed: 0% Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 Record blocks: 1403698 Delete blocks: 0 Recorddata: 317235748 Deleted data: 0 Lost space: 0 Linkdata: 0 User time 1626.51, System time 232.36 Maximum resident set size 0, Integral resident set size 0 Non physical pagefaults 0, Physical pagefaults 627, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 639, Involuntary context switches 28966
Example of myisamchk -eiv output:
Checking MyISAM file: company Data records: 1403698 Deleted blocks: 0 - check file-size - check delete-chain block_size 1024: index 1: index 2: index 3: index 4: index 5: index 6: index 7: index 8: index 9: No recordlinks - check index reference - check data record references index: 1 Key: 1: Keyblocks used: 97% Packed: 0% Max levels: 4 - check data record references index: 2 Key: 2: Keyblocks used: 98% Packed: 50% Max levels: 4 - check data record references index: 3 Key: 3: Keyblocks used: 97% Packed: 0% Max levels: 4 - check data record references index: 4 Key: 4: Keyblocks used: 99% Packed: 60% Max levels: 3 - check data record references index: 5 Key: 5: Keyblocks used: 99% Packed: 0% Max levels: 3 - check data record references index: 6 Key: 6: Keyblocks used: 99% Packed: 0% Max levels: 3 - check data record references index: 7 Key: 7: Keyblocks used: 99% Packed: 0% Max levels: 3 - check data record references index: 8 Key: 8: Keyblocks used: 99% Packed: 0% Max levels: 3 - check data record references index: 9 Key: 9: Keyblocks used: 98% Packed: 0% Max levels: 4 Total: Keyblocks used: 9% Packed: 17% - check records and index references [LOTS OF ROW NUMBERS DELETED] Records: 1403698 M.recordlength: 226 Packed: 0% Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 Record blocks: 1403698 Delete blocks: 0 Recorddata: 317235748 Deleted data: 0 Lost space: 0 Linkdata: 0 User time 1639.63, System time 251.61 Maximum resident set size 0, Integral resident set size 0 Non physical pagefaults 0, Physical pagefaults 10580, Swaps 0 Blocks in 4 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 10604, Involuntary context switches 122798
Here are the sizes of the data and index files for the table used in the preceding examples:
-rw-rw-r-- 1 monty tcx 317235748 Jan 12 17:30 company.MYD -rw-rw-r-- 1 davida tcx 96482304 Jan 12 18:35 company.MYM
Explanations for the types of information myisamchk produces are
given below. The ``keyfile'' is the index file. ``Record'' and ``row''
are synonymous.
ISAM file
Isam-version
Creation time
Recover time
Data records
Deleted blocks
Datafile: Parts
Data
records.
Deleted data
Datafile pointer
Keyfile pointer
Max datafile length
.MYD file) can become, in bytes.
Max keyfile length
.MYI file) can become, in bytes.
Recordlength
Record format
Fixed length.
Other possible values are Compressed and Packed.
table description
Key
Start
Len
Index
unique or multip. (multiple). Indicates whether or not one value
can exist multiple times in this index.
Type
packed, stripped or empty.
Root
Blocksize
Rec/key
myisamchk -a. If this is not updated at all, a default
value of 30 is given.
Keyblocks used
myisamchk, the values are very
high (very near the theoretical maximum).
Packed
CHAR/VARCHAR/DECIMAL keys. For long strings like
names, this can significantly reduce the space used. In the third example
above, the 4th key is 10 characters long and a 60% reduction in space is
achieved.
Max levels
Records
M.recordlength
Packed
Packed
value indicates the percentage savings achieved by doing this.
Recordspace used
Empty space
Blocks/Record
myisamchk.
See section 13.4.3 Table optimization.
Recordblocks
Deleteblocks
Recorddata
Deleted data
Lost space
Linkdata
Linkdata is the sum of the amount of
storage used by all such pointers.
If a table has been compressed with myisampack, myisamchk
-d prints additional information about each table column. See
section 12.7 The MySQL compressed read-only table generator, for an example of this
information and a description of what it means.
myisamchk for crash recovery
If you run mysqld with --skip-locking (which is the default on
some systems, like Linux), you can't reliably use myisamchk to
check a table when mysqld is using the same table. If you
can be sure that no one is accessing the tables through mysqld
while you run myisamchk, you only have to do mysqladmin
flush-tables before you start checking the tables. If you can't
guarantee the above, then you must take down mysqld while you
check the tables. If you run myisamchk while mysqld is updating
the tables, you may get a warning that a table is corrupt even if it
isn't.
If you are not using --skip-locking, you can use myisamchk
to check tables at any time. While you do this, all clients that try
to update the table will wait until myisamchk is ready before
continuing.
If you use myisamchk to repair or optimize tables, you
MUST always ensure that the mysqld server is not using
the table (this also applies if you are using --skip-locking).
If you don't take down mysqld you should at least do a
mysqladmin flush-tables before you run myisamchk.
The file format that MySQL uses to store data has been extensively tested, but there are always external circumstances that may cause database tables to become corrupted:
mysqld process being killed in the middle of a write
This chapter describes how to check for and deal with data corruption in MySQL databases. If your tables get corrupted a lot you should try to find the reason for this! See section G.1 Debugging a MySQL server.
When performing crash recovery, it is important to understand that each table
tbl_name in a database corresponds to three files in the database
directory:
| File | Purpose |
| `tbl_name.frm' | Table definition (form) file |
| `tbl_name.MYD' | Data file |
| `tbl_name.MYI' | Index file |
Each of these three file types is subject to corruption in various ways, but problems occur most often in data files and index files.
myisamchk works by creating a copy of the `.MYD' (data) file
row by row. It ends the repair stage by removing the old `.MYD'
file and renaming the new file to the original file name. If you use
--quick, myisamchk does not create a temporary `.MYD'
file, but instead assumes that the `.MYD' file is correct and only
generates a new index file without touching the `.MYD' file. This
is safe, because myisamchk automatically detects if the
`.MYD' file is corrupt and aborts the repair in this case. You can
also give two --quick options to myisamchk. In this case,
myisamchk does not abort on some errors (like duplicate key) but
instead tries to resolve them by modifying the `.MYD'
file. Normally the use of two --quick options is useful only if
you have too little free disk space to perform a normal repair. In this
case you should at least make a backup before running myisamchk.
To check a table, use the following commands:
myisamchk tbl_name
myisamchk without options or
with either the -s or --silent option.
myisamchk -e tbl_name
-e means
``extended check''). It does a check-read of every key for each row to verify
that they indeed point to the correct row. This may take a LONG time on a
big table with many keys. myisamchk will normally stop after the first
error it finds. If you want to obtain more information, you can add the
--verbose (-v) option. This causes myisamchk to keep
going, up through a maximum of 20 errors. In normal usage, a simple
myisamchk (with no arguments other than the table name) is sufficient.
myisamchk -e -i tbl_name
-i option tells myisamchk to
print some informational statistics, too.
In the following we only talk about using myisamchk on MyISAM
tables (extensions .MYI and .MYD). If you are using
ISAM tables (extensions .ISM and .ISD), you should use
isamchk instead.
The symptoms of a corrupted table are usually that queries abort unexpectedly and that you observe errors such as these:
In these cases, you must repair your tables. myisamchk
can usually detect and fix most things that go wrong.
The repair process involves up to four stages, described below. Before you
begin, you should cd to the database directory and check the
permissions of the table files. Make sure they are readable by the Unix user
that mysqld runs as (and to you, since you need to access the files
you are checking). If it turns out you need to modify files, they must also
be writable by you.
If you are going to do a repair a table, you must first take down the
mysqld server. Note that when you do mysqladmin shutdown,
the mysqld server will still be alive for a while after
mysqladmin returns until all queries are stopped and all keys have
been flushed to disk.
Stage 1: Checking your tables
Run myisamchk *.MYI or (myisamchk -e *.MYI if you have more time).
Use the -s (silent) option to suppress unnecessary information.
You have to repair only those tables for which myisamchk announces an
error. For such tables, proceed to Stage 2.
If you get weird errors when checking (such as out of
memory errors), or if myisamchk crashes, go to Stage 3.
Stage 2: Easy safe repair
First, try myisamchk -r -q tbl_name (-r -q means ``quick recovery
mode''). This will attempt to repair the index file without touching the data
file. If the data file contains everything that it should and the delete
links point at the correct locations within the data file, this should work
and the table is fixed. Start repairing the next table. Otherwise, use the
following procedure:
myisamchk -r tbl_name (-r means ``recovery mode''). This will
remove incorrect records and deleted records from the data file and
reconstruct the index file.
myisamchk --safe-recover tbl_name.
Safe recovery mode uses an old recovery method that handles a few cases that
regular recovery mode doesn't (but is slower).
If you get weird errors when repairing (such as out of
memory errors), or if myisamchk crashes, go to Stage 3.
Stage 3: Difficult repair
You should only reach this stage if the first 16K block in the index file is destroyed or contains incorrect information, or if the index file is missing. In this case, it's necessary to create a new index file. Do so as follows:
shell> mysql db_name mysql> DELETE FROM tbl_name; mysql> quit
Go back to Stage 2. myisamchk -r -q should work now. (This shouldn't
be an endless loop).
Stage 4: Very difficult repair
You should reach this stage only if the description file has also crashed. That should never happen, because the description file isn't changed after the table is created.
myisamchk -r.
To coalesce fragmented records and eliminate wasted space resulting from
deleting or updating records, run myisamchk in recovery mode:
shell> myisamchk -r tbl_name
You can optimize a table in the same way using the SQL OPTIMIZE TABLE
statement. OPTIMIZE TABLE is easier, but myisamchk is faster.
There is also no possibility of unwanted interaction between a utility
and the server, because the server does all the work when you use
OPTIMIZE TABLE.
myisamchk also has a number of other options you can use to improve
the performance of a table:
-S, --sort-index
-R index_num, --sort-records=index_num
-a, --analyze
For a full description of the option see See section 13.1.1 myisamchk invocation syntax.
When using MySQL with log files, you will from time to time want to remove/backup old log files and tell MySQL to start logging on new files. See section 9.2 The update log.
One a Linux (Redhat) installation, you can use the
mysql-log-rotate script for this. If you installed MySQL
from an RPM distribution, the script should have been installed
automatically.
On other systems you must install a short script yourself that you
start from cron to handle log files.
You can force MySQL to start using new log files by using
mysqladmin flush-logs or by using the SQL command FLUSH LOGS.
If you are using MySQL 3.21 you must use mysqladmin refresh.
The above command does the following:
--log) is used, closes and reopens the log file.
(`mysql.log' as default).
--log-update) is used, closes the update log and
opens a new log file with a higher sequence number.
If you are using only an update log, you only have to flush the logs and then move away the old update log files to a backup. If you are using the normal logging, you can do something like:
shell> cd mysql-data-directory shell> mv mysql.log mysql.old shell> mysqladmin flush-tables
and then take a backup and remove `mysql.old'.
Go to the first, previous, next, last section, table of contents.