How to Use myisamchk
myisamchk has many modes in which it can be used. The following is its basic syntax:
myisamchk [options] table_name [table_name2 ...]
Or you can use wildcard characters on the names of the directories and files:
myisamchk [options] /path/to/mysql/data/*/*.MYI
Using the first syntax, you can run myisamchk on a single table, whereas the second syntax pattern enables you to run it on several tables at once, including across several databases.
You can run it with or without a number of options, whose meanings you will see in a moment.
To use the first syntax given previously—where you refer to a table by table_name—you must be in the data directory of the MySQL database you're working on. If you're not, myisamchk won't know where your database resides or even which table or database you're referring to. For example:
# cd /usr/local/mysql/var/store # myisamchk products
runs myisamchk on the table products in the database store.
But using the second syntax, with wildcard characters, you can invoke myisamchk like this:
myisamchk [options] /path/to/data/directory/*.ISM
Here is a sample usage:
# myisamchk -r -q /usr/local/mysql/var/*/*.MYI
This runs myisamchk with options -r and -q on all .MYI (index) files in all databases on the server.
For now, you should hold back from trying out myisamchk: it's better for you to first understand the scope and meaning of the various options.
Table 16.1 lists the various options you may pass to myisamchk. You will see from the table that the options fall into several categories: checking, repair, optimization, and general modifiers.
Tip
Some options to myisamchk require you to refer to indexes by number. To find out what indexes a table has, run SHOW INDEX or SHOW KEYS. This produces a list of indexes on a table, and the order of the indexes determines this index_num, as required by some myisamchk options.
The first index listed will have an index_num of 1.
Note
The -c, -F, -m, and -U options to myisamchk are not available in the older isamchk utility for ISAM tables. The -C option has a different meaning in isamchk (it sets the default character set).
Now that you have an overview of what options are available, it's time to look at some scenarios to illustrate which options you would use.
Checking a Table
Caution
myisamchk is a powerful tool and will recover your data from the vast majority of corruptions. However, before running it to repair or optimize a table, and preferably before checking a table, you should do the following:
Shut down mysqld if you can. If mysqld is accessing the table (and worst of all, writing) while myisamchk is trying to do a recovery or optimization, it may cause data corruption. If myisamchk is checking, it may think there's a problem when there isn't.
If you can't shut down mysqld, you should perform a mysqladmin flush-tables to save tables to disk and ensure that no other client is accessing the table when you run myisamchk.
If you can't be sure nobody else is accessing the table, make copies of the .frm, .MYD, and .MYI files for that table. Use myisamchk to work on the copies; then stop mysqld briefly and replace the original (suspect) ones with the repaired ones. However, if users write to the live copies of the tables while you're doing the repair, you'll have data concurrency issues, and their updates will be lost.
If you are working on the original files rather than copies, back up the files to a safe place before doing the recovery.
Before you repair a table, it's a good idea to know whether it needs repairing! There are several ways of checking a table, each with its own merits:
Use myisamchk with no options. For example, to check the table people:
# myisamchk people Checking MyISAM file: people Data records: 10 Deleted blocks: 0 - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check record linksmyisamchk used with no options checks only the index file. It won't pick up problems in the data file or table definition file.
The preceding output indicates that there are no errors with the table people, but if there are problems, you may get output like this:
# myisamchk people Checking MyISAM file: people Data records: 0 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 myisamchk: error: Found 10 keys of 0 - check record links myisamchk: error: Record-count is not ok; is 10 Should be: 0 myisamchk: warning: Found 10 parts Should be: 20 parts MyISAM-table 'people' is corrupted Fix it using switch "-r" or "-o"Use myisamchk with -i for information. Here's an example with a corrupted table:
# myisamchk -i people Checking MyISAM file: people Data records: 0 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 myisamchk: error: Found 10 keys of 0 - check record links myisamchk: error: Record-count is not ok; is 10 Should be: 0 myisamchk: warning: Found 10 parts Should be: 20 parts Record blocks: 10 Delete blocks: 0 Record data: 245 Deleted data: 0 Lost space: 14 Linkdata: 37 MyISAM-table 'people' is corrupted Fix it using switch "-r" or "-o" User time 0.00, System time 0.00 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 24, Physical pagefaults 206, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 0, Involuntary context switches 0The preceding output shows that the table has been marked as crashed; in fact, SELECT * FROM people returns the following error:
ERROR 1016: Can't open file: 'people.MYD'. (errno: 145)
MySQL knows that the table is “crashed” and cannot run queries on it. The output suggests that you repair the table, which you'll do in a moment.
Use myisamchk with -m for medium checking. -m checks each data record against its index entry, finding just about all errors.
Here's an example:
# myisamchk -m people Checking MyISAM file: people Data records: 0 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 myisamchk: error: Found 10 keys of 0 - check record links myisamchk: error: Record-count is not ok; is 10 Should be: 0 myisamchk: warning: Found 10 parts Should be: 20 parts MyISAM-table 'people' is corrupted Fix it using switch "-r" or "-o"As before, the output tells you that the table is marked as crashed. You could also run it with both -m and -i for more information:
# myisamchk -m -i people Checking MyISAM file: people Data records: 0 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 myisamchk: error: Found 10 keys of 0 - check record links myisamchk: error: Record-count is not ok; is 10 Should be: 0 myisamchk: warning: Found 10 parts Should be: 20 parts Record blocks: 10 Delete blocks: 0 Record data: 245 Deleted data: 0 Lost space: 14 Linkdata: 37 MyISAM-table 'people' is corrupted Fix it using switch "-r" or "-o" User time 0.00, System time 0.00 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 59, Physical pagefaults 206, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 0, Involuntary context switches 0In rare cases, if the error is such that -m has not found it, but you still suspect a corruption and want to test further, you can perform an extended check with -e. This takes considerably longer if the table is large.
Here's an example again:
# myisamchk -e people Checking MyISAM file: people Data records: 0 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 myisamchk: error: Found 10 keys of 0 - check records and index references myisamchk: error: Record at: 0 Can't find key for index: 1 MyISAM-table 'people' is corrupted Fix it using switch "-r" or "-o"You can get more information with myisamchk -e -i:
# myisamchk -e -i people Checking MyISAM file: people Data records: 0 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 myisamchk: error: Found 10 keys of 0 - check records and index references myisamchk: error: Record at: 0 Can't find key for index: 1 MyISAM-table 'people' is corrupted Fix it using switch "-r" or "-o" User time 0.00, System time 0.00 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 59, Physical pagefaults 207, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 0, Involuntary context switches 0You can get even more detailed information by adding the verbose option, -v. Here's a sample output:
# myisamchk -e -v people Checking MyISAM file: people Data records: 0 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed - check file-size - check key delete-chain block_size 1024: - check record delete-chain No recordlinks - check index reference - check data record references index: 1 myisamchk: error: Found 10 keys of 0 - check records and index references myisamchk: error: Record at: 0 Can't find key for index: 1 myisamchk: error: Record at: 20 Can't find key for index: 1 myisamchk: error: Record at: 40 Can't find key for index: 1 myisamchk: error: Record at: 60 Can't find key for index: 1 myisamchk: error: Record at: 80 Can't find key for index: 1 myisamchk: error: Record at: 104 Can't find key for index: 1 myisamchk: error: Record at: 128 Can't find key for index: 1 myisamchk: error: Record at: 152 Can't find key for index: 1 myisamchk: error: Record at: 256 Can't find key for index: 1 myisamchk: error: Record at: 276 Can't find key for index: 1 myisamchk: error: Record-count is not ok; is 10 Should be: 0 myisamchk: warning: Found 10 parts Should be: 20 parts MyISAM-table 'people' is corrupted Fix it using switch "-r" or "-o"
The preceding output is reassuring: MySQL knows that there's a corruption but clearly knows a good deal about the problem.
Having conducted a check and found a problem, you'll need to repair the table.
Repairing a Table
To perform any kind of repair, you can use myisamchk with either -r or -o. -r is the fast, modern recovery method to be used in most cases. (There are a few cases where the older -o will work but -r won't.)
You should attempt repairs according to the following strategy.
Quick Repair
In most cases, corruption exists purely within the index file. You should first attempt to do a “quick repair” using myisamchk -r -q.
A quick repair fixes only the index file without touching the data file. Here's an example, using the preferred method of shutting down mysqld before starting the repair:
# mysqladmin -p shutdown Enter password: # myisamchk -r -q people - check key delete-chain - check record delete-chain - recovering (with sort) MyISAM-table 'people' Data records: 10 - Fixing index 1
After doing this, you can try one of the previous checks to see whether it fixed the problem.
Caution
If it didn't fix it, now is the time to back up your data! You should not proceed with further repairs without doing this.
Normal Repairs
The next type of repair to attempt is myisamchk -r. As well as rebuilding indexes, it deletes any corrupted records from the data file and recovers wasted space (more on this later), thus closing up space occupied by deleted records.
Here's an example where the table people has a corruption:
# myisamchk -r people
- recovering (with keycache) MyISAM-table 'people'
Data records: 0
Data records: 10
There's a confusion about how many data records it has. Alternatively, you may see something like this:
# myisamchk -r people
- recovering (with sort) MyISAM-table 'people'
Data records: 10
- Fixing index 1
In the second example, the table is fixed.
However, if this doesn't work, it may be worth trying the older repair method myisamchk -o, like this:
Difficult Index Repairs
There may be cases where the index file is so badly damaged that it cannot be repaired. If the preceding attempts fail, follow this procedure:
1. | Copy your data file to a safe place outside the data directory. |
2. | |
3. | Do a mysqladmin flush-tables or a SQL
FLUSH TABLES to ensure that the changes are written to disk. |
4. | |
5. |
Table Definition Corruption
It's rare for the table definition file (.frm) to get corrupted because table definitions are not often changed. However, it can happen.
If the table definition has become corrupted, follow this procedure:
1. | Copy your data file to a safe place outside the data directory. |
2. | Restore the table definition file from a backup (if you have one), or failing that, issue a CREATE TABLE command if you know the definition of the table. |
3. | Do a mysqladmin flush-tables or an SQL
FLUSH TABLES to ensure that the changes are written to disk. |
4. | Copy the data file back into the data directory. (You may need to change ownership of the file back to mysql using chown.) |
5. |
Optimizing a Table
When a table has numerous insert and delete operations performed on it, gaps can appear between the data records. This can arise where a shorter record is occupying the space previously used by a longer record.
You can recover wasted space within a data file by using myisamchk -r.
Here's an example of working on a table people, which has the columns first_name, last_name, and title, with a non-unique index on last_name (which makes it index number 1). First you'll want to flush tables to disk:
# mysqladmin -p flush-tables
Enter password:
At this point, you should ideally shut down mysqld, or at least lock tables from other users—though for clarity this is not shown here.
Then run myisamchk to get the descriptive information on the table people:
# myisamchk -d people
MyISAM file: people
Record format: Packed
Character set: latin1 (8)
Data records: 10 Deleted blocks: 1
Recordlength: 114
table description:
Key Start Len Index Type
1 5 100 multip. char packed stripped NULL
As you can see from the preceding output, Deleted blocks is 1, so this space (admittedly small) is wasted. To reclaim it, run myisamchk -r (remembering to shut down mysqld first if at all possible):
# mysqladmin -p shutdown Enter password: # myisamchk -r people - recovering (with sort) MyISAM-table 'people' Data records: 10 - Fixing index 1
Now you can get the descriptive information again:
# myisamchk -d people
MyISAM file: people
Record format: Packed
Character set: latin1 (8)
Data records: 10 Deleted blocks: 0
Recordlength: 114
table description:
Key Start Len Index Type
1 5 100 multip. char packed stripped NULL
This time there are no deleted blocks, so the table is better packed than before.
It's a good idea to run myisamchk -r regularly on a growing database to minimize any space that is being left among the tables.
Optimizing Performance with Better Indexing
You may want to ensure that the indexes are kept in sort order on the disk. This is a good idea for tables on which you're running queries that use indexes and require a fast seek time.
You can do this by running myisamchk -S.
Here's an example:
# myisamchk -S people
- Sorting index for MyISAM-table 'people'
This action sorts the .MYI index file but doesn't affect the data file. You can also sort the data records themselves so that they're arranged in order on the disk. This helps speed up SELECT queries (or others) that look for data in a certain range but don't use an index.
You can sort the data file using myisamchk -R=index_num, where index_num is the number of the index that will govern data order.
Here's an example of using the first index (number 1):
# myisamchk -R1 people