1.myisamchk Method of Invocation
myisamchk [Options] tbl_name ...
Where options specify what you want Myisamchk to do.
It allows you to use the mode "*. Myi "Specifies all the tables in a directory.
Shell> Myisamchk *. Myi
The recommended way to quickly check all MyISAM tables is:
Shell> myisamchk--silent--fast/path/to/datadir/*/*. Myi
When you run Myisamchk, you must make sure that the other programs do not use the table.
Memory allocation is important when you run MYISAMCHK. The memory used by MYISAMCHK cannot exceed the size specified with the-o option. For most cases, using-o sort=16m should be sufficient.
In addition, in the repair of myisamchk need a lot of hard disk space, is basically involved in the table space double the size.
General Options for 2.myisamchk
--debug=debug_options,-# debug_options
Output debug record file. Debug_options strings are often ' d:t:o,filename '.
--silent,-s
Silent mode. Write output only if an error occurs.
--wait, W
If the table is locked, instead of prompting for an error to terminate, wait until the table is unlocked before continuing.
If you do not use--skip-external-locking, you can use MYISAMCHK to check the table at any time. When the table is checked, all clients attempting to update the table will wait until the MYISAMCHK is ready to continue.
Note that if you run mysqld with the--skip-external-locking option, you can lock the table with another MYISAMCHK command.
--var_name=value
You can set the following variable by using the--var_name=value option:
Decode_bits 9
Ft_max_word_len depends on 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 a quick fix, set the key_buffer_size and sort_buffer_size variables to approximately 25% of the available memory.
You can set two variables to a larger value, because only one variable is used at a time.
Myisam_block_size is the memory size used for the index block.
Stats_method affects how null values are processed for index statistics collection when given the--analyze option.
3.MYISAMCHK Check Options
--check,-C
Check the table for errors. If you do not explicitly specify the action type option, this is the default action.
--check-only-changed,-C
Check only the tables that have changed since the last check.
--extend-check, E
Check the table very carefully. If a table has many indexes it will be rather slow.
--fast,-f
Only check for tables that are not properly closed.
--force, F
If Myisamchk finds any errors in the table, it is automatically repaired.
--information, I.
Print statistics for the table you are checking.
--medium-check, M
Check more quickly than--extend-check. Only 99.99% errors are found.
--update-state,-U
Save the information in the. Myi file to indicate when the table was checked and whether the table crashed. This option is used to make the most of the--check-only-changed option,
However, this option should not be used if the MYSQLD server is using a table and is running with the--skip-external-locking option.
--read-only, T
Do not mark the table as checked. If you use MYISAMCHK to check tables that are being used by other applications and are not locked, it is useful
4.myisamchk Repair Options
--backup, b
Back up the. myd file as 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 length of data file
--extend-check,-e
To repair, attempting to recover each row from the data file. In general, a large number of garbage can be found. Do not use this option unless you disregard the consequences.
--force, F
Overwrite old intermediate file (filename is similar to tbl_name.) TMD), and not interrupt
--keys-used=val, K-Val
For Myisamchk, this option value is a bit value indicating the index to update. Each bits of the option value corresponds to an index of the table, where the first index corresponds to bit 0.
Option value 0 disables updates to all indexes, guaranteeing rapid insertion. The disabled index can be reactivated by Myisamchk-r.
--parallel-recover, p
Same as-R and-N, but all keys are created in parallel using different threads.
--quick,-q
Do not modify the data file to quickly fix it.
--recover, R
Can fix almost any problem unless the unique key is not unique (this is very unlikely for MyISAM tables). If you want to restore the table,
This is the first option to try. If the Myisamchk report table cannot be restored with-R, you can only try-O.
In the unlikely event that-R fails, the data file remains intact.
--safe-recover, O
Reads with an old recovery method, reads all rows sequentially, and updates all index trees based on the rows found. This is slower than-R,
But can handle the case that-R cannot handle. This recovery method uses less hard disk space than-R. In general, you should first use-R maintenance, if-R fails with-O.
--sort-recover,-N
Force Myisamchk to resolve key values by sorting, even if the temporary file may be large.
Other options for 5.myisamchk
MYISAMCHK supports options for operations other than the following table check and repair:
--analyze,-a
Analyzes the distribution of key values. This improves the coupling performance by allowing the Join optimizer to better select the order in which the table should be connected and which key should be used.
To obtain distribution-related information, use the Myisamchk--description--verbose tbl_name command or the show KEYS from Tbl_name statement.
--sort-index, S
Sorts the index tree blocks in order from highest to lowest. This optimizes the search and will scan the table for key values faster.
--set-auto-increment[=value],-a[value]
Forces a new record starting at the given value to use the auto_increment number (or a higher value if a record of the auto_increment value is already in use).
If value is not specified, the auto_increment number of the new record should use the maximum value of the current table plus 1.
--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-Char Stripped
6. How to repair the table
Check your watch.
If you have a lot of time, run Myisamchk *. Myi or MYISAMCHK-E *. Myi. Use the-S (silence) option to prohibit unnecessary information.
If the MYSQLD server is down, use the--update-state option to tell Myisamchk to mark the table as ' checked '.
Simple and safe fixes
First, try Myisamchk-r-Q tbl_name (-r-q means "quick recovery mode")
If you get a strange error (for example out of memory error), or if the myisamchk crashes, to Stage 3.
Difficult to repair
Only the first 16K block of the index file is corrupted, 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. Do the following steps:
1. Move the data file to a safe place.
2. Create a new (blank) 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 has no truncate TABLE, use the delete from Tbl_name.
7. Copy old data files to the newly created data file. (Don't just move old files back into new files; you need to keep a copy in case something goes wrong.) ）
Back to Stage 2. Now Myisamchk-r-Q should be working. (This should not be an infinite loop).
You can also use the repair TABLE tbl_name use_frm to automatically execute the entire program.
Very difficult to fix
only. frm The description file is destroyed, you should get to this stage. This should never have happened because the description file does not change after the table is created.
1. Restore the description file from one backup and then back to Phase 3. You can also recover the index file and then return to Stage 2. For the latter, you should start with Myisamchk-r.
2. If you do not make a backup but know exactly how the table was created, create a copy of the table in another database. Delete the new data file and move the profile and index files from other databases to the corrupted database. This provides a new description and index file, but lets. The myd data file was left alone. Go back to Stage 2 and try to rebuild the index file.
Current 1/2 page
12 Next read the full text
