MySQL之myisamchk

標籤：des   blog   io   os   使用   ar   for   檔案   資料   

Description, check and repair of MyISAM tables.
Used without options all tables on the command will be checked for errors
Usage: myisamchk [OPTIONS] tables[.MYI]

 

Global options:
-H, --HELP Display this help and exit.
-?, --help Display this help and exit.
-O, --set-variable var=option.
Change the value of a variable. Please note that
this option is deprecated; you can set variables
directly with ‘--variable-name=value‘.
-t, --tmpdir=path Path for temporary files. Multiple paths can be
specified, separated by colon (:), they will be used
in a round-robin fashion.
-s, --silent Only print errors. One can use two -s to make
myisamchk very silent.
-v, --verbose Print more information. This can be used with
--description and --check. Use many -v for more verbosity.
-V, --version Print version and exit.
-w, --wait Wait if table is locked.

Check options (check is the default action for myisamchk):
-c, --check Check table for errors.
-e, --extend-check Check the table VERY throughly. Only use this in
extreme cases as myisamchk should normally be able to
find out if the table is ok even without this switch.
-F, --fast Check only tables that haven‘t been closed properly.
-C, --check-only-changed
Check only tables that have changed since last check.
-f, --force Restart with ‘-r‘ if there are any errors in the table.
States will be updated as with ‘--update-state‘.
-i, --information Print statistics information about table that is checked.
-m, --medium-check Faster than extend-check, but only finds 99.99% of
all errors. Should be good enough for most cases.
-U --update-state Mark tables as crashed if you find any errors.
-T, --read-only Don‘t mark table as checked.

Repair options (When using ‘-r‘ or ‘-o‘):
-B, --backup Make a backup of the .MYD file as ‘filename-time.BAK‘.
--correct-checksum Correct checksum information for table.
-D, --data-file-length=# Max length of data file (when recreating data
file when it‘s full).
-e, --extend-check Try to recover every possible row from the data file
Normally this will also find a lot of garbage rows;
Don‘t use this option if you are not totally desperate.
-f, --force Overwrite old temporary files.
-k, --keys-used=# Tell MyISAM to update only some specific keys. # is a
bit mask of which keys to use. This can be used to
get faster inserts.
--max-record-length=#
Skip rows bigger than this if myisamchk can‘t allocate
memory to hold it.
-r, --recover Can fix almost anything except unique keys that aren‘t
unique.
-n, --sort-recover Forces recovering with sorting even if the temporary
file would be very big.
-p, --parallel-recover
Uses the same technique as ‘-r‘ and ‘-n‘, but creates
all the keys in parallel, in different threads.
-o, --safe-recover Uses old recovery method; Slower than ‘-r‘ but can
handle a couple of cases where ‘-r‘ reports that it
can‘t fix the data file.
--character-sets-dir=...
Directory where character sets are.
--set-collation=name
Change the collation used by the index.
-q, --quick Faster repair by not modifying the data file.
One can give a second ‘-q‘ to force myisamchk to
modify the original datafile in case of duplicate keys.
NOTE: Tables where the data file is currupted can‘t be
fixed with this option.
-u, --unpack Unpack file packed with myisampack.

Other actions:
-a, --analyze Analyze distribution of keys. Will make some joins in
MySQL faster. You can check the calculated distribution
by using ‘--description --verbose table_name‘.
--stats_method=name Specifies how index statistics collection code should
treat NULLs. Possible values of name are "nulls_unequal"
(default for 4.1/5.0), "nulls_equal" (emulate 4.0), and
"nulls_ignored".
-d, --description Prints some information about table.
-A, --set-auto-increment[=value]
Force auto_increment to start at this or higher value
If no value is given, then sets the next auto_increment
value to the highest used value for the auto key + 1.
-S, --sort-index Sort index blocks. This speeds up ‘read-next‘ in
applications.
-R, --sort-records=#
Sort records according to an index. This makes your
data much more localized and may speed up things
(It may be VERY slow to do a sort the first time!).
-b, --block-search=#
Find a record, a block at given offset belongs to.

 

由於臨時斷電，使用kill -9中止MySQL服務進程，所有的這些都可能會毀壞MySQL的資料檔案。如果在被幹擾時，服務正在改變檔案，檔案可能會留下錯誤的或不一致的狀態。因為這樣的毀壞有時是不容易被發現的，當你發現這個錯誤時可能是很久以後的事了。於是，當你發現這個問題時，也許所有的備份都有同樣的錯誤。  myisamchk通過一行一行地建立一個“.MYD”(資料 )檔案的副本來工作，它通過由刪除老的“.MYD 檔案並且重新命名新檔案到原來的檔案名稱結束修複階段。如果你使用--quick，myisamchk不建立一個臨時“.MYD”檔案，只是假定“.MYD”檔案是正確的並且僅建立一個新的索引檔案，不接觸“.MYD”檔案，這是安全的，因為myisamchk自動檢測“.MYD”檔案是否損壞並且在這種情況下，放棄修複。你也可以給myisamchk兩個--quick選項。在這種情況下，myisamchk不會在一些錯誤上(象重複鍵)放棄，相反試圖通過修改“.MYD”檔案解決它們。通常，只有在你在太少的空閑磁碟空間上實施一個正常修複，使用兩個--quick選項才有用。在這種情況下，你應該至少在運行myisamchk前做一個備份。

 

1、myisamchk
使用myisamchk必須暫時停止MySQL伺服器。例如，我們要檢修test資料庫。執行以下操作：

# service mysqld stop ;# myisamchk -r /var/lib/mysql/test/*MYI# service mysqld start;

myisamchk會自動檢查並修複資料表中的索引錯誤。

 

2、mysqlcheck
使用mysqlcheck無需停止MySQL，可以進行熱修複。

#mysqlcheck –r DBname Tablename –uuser –ppassword

 

注意，無論是myisamchk還是mysqlcheck，一般情況下不要使用-f強制修複，-f參數會在遇到一般修複無法成功的時候刪除

MySQL之myisamchk