Mysql Myisamchk tool user manual page 1/2

1. Call method of myisamchk
Myisamchk [options] tbl_name...
Options specifies what you want myisamchk to do.

It allows you to specify all tables in a directory by using the "*. MYI" mode.
Shell> myisamchk *. MYI

We recommend that you quickly check all MyISAM tables:

Shell> myisamchk -- silent -- fast/path/to/datadir/*. MYI
When running myisamchk, make sure other programs do not use tables.

When you run myisamchk, the memory allocation is important. The memory size used by MYIsamchk cannot exceed the value specified by the-O option. In most cases, it should be enough to use-O sort = 16 m.
In addition, myisamchk requires a lot of hard disk space during restoration, which is basically double the size of the tablespace involved.


2. General options of myisamchk
-- Debug = debug_options,-# debug_options
Output the debugging record file. The debug_options string is usually 'd: t: o, filename '.

-- Silent,-s
Silence mode. Write output only when an error occurs.

-- Wait,-w
If the table is locked, it does not indicate an error termination, but waits until the table is unlocked before it continues.
If you do not use -- skip-external-locking, you can use myisamchk to check the table at any time. When you check the table, all clients that attempt to update the table will wait until myisamchk is ready to continue.
Note that if you use the -- skip-external-locking option to run mysqld, you can only use another myisamchk command to lock the table.

-- Var_name = value
You can use the -- var_name = value option to set the following variables:
Decode_bits 9
Ft_max_word_len depends on the version
Ft_min_word_len 4
Ft_stopword_file built-in list
Key_buffer_size 523264
Myisam_block_size 1024
Read_buffer_size 262136
Sort_buffer_size 2097144
Sort_key_blocks 16
Stats_method nulls_unequal
Write_buffer size 262136
If you want to fix it quickly, set the key_buffer_size and sort_buffer_size variables to about 25% of the available memory.
You can set two variables to a large value, because only one variable is used at a time.
Myisam_block_size is the memory size used for index blocks.
Stats_method affects how to process NULL values for index statistics when the -- analyze option is given.

3. Check options for myisamchk
-- Check,-c
Check table errors. If you do not specify the operation type option explicitly, This is the default operation.

-- Check-only-changed,-C
Only the tables with changes after the last check are checked.

-- Extend-check,-e
Check the table carefully. If the table has many indexes, it will be quite slow.

-- Fast,-F
Only the tables that are not properly closed are checked.

-- Force,-f
If myisamchk finds any errors in the table, it will be automatically repaired.

-- Information,-I
Print the statistical information of the checklist.

-- Medium-check,-m
Faster than -- extend-check. Only 99.99% errors can be found.

-- Update-state,-U
Save the information in the. MYI file to indicate the table check time and whether the table crashes. This option is used to make full use of the -- check-only-changed option,
However, if the mysqld server is using a table and is running with the -- skip-external-locking option, this option is not used.

-- Read-only,-T
Do not mark the table as checked. It is useful if you use myisamchk to check tables that are being used by other applications but are not locked.

4. myisamchk repair options
-- Backup,-B
Back up the. MYD file as a file_name-time.BAK

-- Character-sets-dir = path
Character Set installation directory.

-- Correct-checksum
Correct the checksum information of the table.

-- Data-file-length = len,-D len
Maximum data file length

-- Extend-check,-e
To restore each row from the data file. A large number of spam rows are usually found. Do not use this option unless you ignore the consequences.

-- Force,-f
Overwrite the old intermediate file (the file name is similar to tbl_name.TMD), instead of interrupting

-- Keys-used = val,-k val
For myisamchk, this option value is a bit value, indicating the index to be updated. Each binary digit of the option value corresponds to an index of the table, and the first index corresponds to 0.
If the option value is 0, all index updates are disabled to ensure quick insertion. You can use myisamchk-r to reactivate disabled indexes.

-- Parallel-recover,-p
It is used in the same way as-r and-n, but uses different threads to create all keys in parallel.

-- Quick,-q
Quickly fix the problem without modifying the data file.

-- Recover,-r
It can fix almost all problems, unless the unique key is not unique (this is very impossible for the MyISAM table ). If you want to restore a table,
This is the first option to try. If the myisamchk report table cannot be restored using-r, you can only try-o.
-R fails when it is unlikely, and the data file remains intact ).

-- Safe-recover,-o
Use an old restoration method to read, read all rows in sequence, and update all index Trees Based on the rows found. This is slower than-r,
But it can handle situations where-r cannot handle. This restoration method uses less disk space than-r. In general, you should first use-r for repairs. If-r fails, use-o.

-- Sort-recover,-n
Force myisamchk to parse key values by sorting, even if the temporary file may be large.


5. Other options of myisamchk
Myisamchk supports the following table check and repair options:

-- Analyze,-
Analyze the distribution of key values. This allows the connection optimizer to better select the order in which the table should be joined and the key to be used to improve the connection performance.
To obtain distribution information, run the myisamchk -- description -- verbose tbl_name command or the show keys from tbl_name statement.

-- Sort-index,-S
Sort the index tree blocks in ascending order. This will optimize the search and make the table scan with key values faster.

-- Set-auto-increment [= value],-A [value]
Force the AUTO_INCREMENT number to be used for the new record starting from the given value (or a higher value should be used if there is already a record with the AUTO_INCREMENT value ).
If no value is specified, the AUTO_INCREMENT number of the new record should add 1 to the maximum value of the current table.

-- Description,-d
Print descriptive information about the table.
For example:
[Root @ qa-sandbox-1 mysql] # myisamchk-d user. MYI
MyISAM file: user. MYI
Record format: Packed
Character set: latin1_swedish_ci (8)
Data records: 6 Deleted blocks: 1
Recordlength: 346

Table description:
Key Start Len Index Type
1 1 180 unique char packed stripped
181 48 char stripped


6. how to repair a table

Check your table
If you have a lot of time, run myisamchk *. MYI or myisamchk-e *. MYI. Use the-s (SILENCE) option to disable unnecessary information.
If the mysqld server is down, use the -- update-state option to tell myisamchk to mark the table as 'checked '.

Simple and secure repair
First, try myisamchk-r-q tbl_name (-r-q means "quick recovery mode ")
If you get a strange error (for example, an out of memory error) during the fix, or if myisamchk crashes, to stage 3.

Difficult repairs
Only when the first 16 K block of the index file is damaged, or contains incorrect information, or if the index file is lost, you should be at this stage. In this case, you need to create a new index file. Perform the following operations:

1. Move the data file to a safe place.
2. Create a New (empty) data file and index file using the table description file:
3. shell> mysql db_name
4. mysql> set autocommit = 1;
5. mysql> truncate table tbl_name;
6. mysql> quit
If your MySQL version does not have a truncate table, delete from tbl_name is used.
7. Copy the old data file to the newly created data file. (Do not just move the old file back to the new file; keep a copy to prevent some errors .)

Return to Stage 2. Now myisamchk-r-q should work. (This should not be an infinite loop ).

You can also use repair table tbl_name USE_FRM to automatically execute the entire program.


Very difficult repairs
Only when the. frm description file is damaged should you reach this stage. This should never happen, because after the table is created, the description file will not change.

1. Recover the description file from a backup and return to phase 3. You can also restore the index file and return to phase 2. For the latter, you should start with myisamchk-r.
2. If you do not have a backup, but you know exactly how the table is created, create a copy of the table in another database. Delete the new data file and move the description file and index file from other databases to the damaged database. This provides a new description and index file, but leaves the. MYD data file alone. Go back to Stage 2 and try to recreate the index file.