MySQL myisamchk修複正在使用，無法訪問資料表

Posted on December 31, 2009 by axl MyISAM是MySQL的預設storage engine. MyISAM table很容易爛掉(corrupted)。
此文章將教你如何檢查/修複這些爛掉的MyISAM tables.

每次你在MySQL DB 製造一個 table，將會在檔案系統上同時製造出*.frm、*.MYD，跟*.MYI等三種格式的檔案。

*.frm = 用來儲存資料表格式(file to store table format)
*.MYD(MyData) = 用來儲存資料(file to store data)
*.MYI(MyIndex) =用來儲存索引(file to store index)

如何檢查DB?哪個table 需要修複：

用root 執行以下指令，假設要檢查DB1下的各個table

代碼如下	複製代碼
#myisamchk /var/lib/mysql/DB1/*.MYI >> /tmp/myisamchk.log

螢幕輸出中，如果發現以下字樣，該資料表就應修複

myisamchk: error: Keypointers and record positions doesn’t match
MyISAM-table ‘/var/lib/mysql/DB1/news_post_comment.MYI’ is corrupted
Fix it using switch “-r” or “-o”
myisamchk: warning: 1 client is using or hasn’t closed the table properly
MyISAM-table ‘/var/lib/mysql/DB1/news_post.MYI’ is usable but should be fixed
myisamchk: warning: 1 client is using or hasn’t closed the table properly
MyISAM-table ‘/var/lib/mysql/DB1/news_post_push_log.MYI’ is usable but should be fixed

Redirect出來的檔案中會顯示更多資訊。如下

代碼如下	複製代碼
Checking MyISAM file: /var/lib/mysql/DB1/yam_bbauth.MYI Data records: 14 Deleted blocks: 0 - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check record links

如何利用myisamchk修複爛掉的tables：

找出爛掉的table之後，用以下指令修複，

代碼如下	複製代碼
#myisamchk –r /var/lib/mysql/DB1/news_post_comment.MYI - recovering (with sort) MyISAM-table ‘/var/lib/mysql/DB1/news_post_comment.MYI Data records: 1 - Fixing index 1

如果table正被某個application使用，你可能會看到： clients are using or haven’t closed the table properly。
所以建議修複前將mysqld關掉或用FLUSH TABLES鎖住TABLES，防止修複時有application對DB TABLE做更動。

如何讓檢查跟修複同時進行：

代碼如下	複製代碼
#myisamchk --silent --force --fast --update-state /var/lib/mysql/DB1/*.MYI myisamchk: MyISAM file /var/lib/mysql/DB1/groups.MYI myisamchk: warning: 1 client is using or hasn’t closed the table properly myisamchk: MyISAM file /var/lib/mysql/DB1/profiles.MYI myisamchk: warning: 1 client is using or hasn’t closed the table properly

options 的意義如下：
• s, –silent option: Prints only errors. You can use two -s to make myisamchk very silent.
• -f, –force option: Restart myisamchk automatically with repair option -r, if there are any errors in the table.
• -F, –fast option: Check only tables that haven’t been closed properly.
• -U –update-state option: Marks tables as crashed, when it finds any error.

修複時手動分配記憶體給龐大的DB

龐大的table，修複需要很長的時間。修複時可以手動增加記憶體參數。

代碼如下	複製代碼
# myisamchk --silent --force --fast --update-state --key_buffer_size=512M --sort_buffer_size=512M --read_buffer_size=4M --write_buffer_size=4M /var/lib/mysql/DB1/*.MYI

用myisamchk 擷取table資訊

1

代碼如下

複製代碼

#myisamchk -dvv profiles.MYI MyISAM file: profiles.MYI Record format: Packed Character set: latin1_swedish_ci (8) File-version: 1 Creation time: 2007-08-16 18:46:59 Status: open,changed,analyzed,optimized keys,sorted index pages Auto increment key: 1 Last value: 88 Data records: 88 Deleted blocks: 0 Datafile parts: 118 Deleted data: 0 Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4 Datafile length: 6292 Keyfile length: 6144 Max datafile length: 4294967294 Max keyfile length: 4398046510079 Recordlength: 2124 table description: Key Start Len Index Type Rec/key Root Blocksize 1 2 3 unique int24 1 1024 1024 2 5 765 unique char packed stripped 1 2048 4096 Field Start Length Nullpos Nullbit Type 1 1 1 2 2 3 no zeros 3 5 765 no endspace

最後一招

 #myisamchk --help

MySQL 使用 myisamchk 修複 *.MYI 時, 出現下述訊息:

myisamchk: Disk is full writing '/tmp/ST3lJjsW' (Errcode: 28). Waiting for someone to free space... (Expect up to 60 secs delay for server to c ontinue after freeing disk space)

myisamchk: Retry in 60 secs. Message reprinted in 600 secs

解法1:

■由訊息可以知道, myisamchk 需要使用到 /tmp, /tmp 容量不夠就會出現此訊息, 所以如果 /tmp 可以清出空間, 清出 /tmp 的空間就可以解決了.
但是如果 /tmp 本身切的就不夠大, 可以使用下述解法.

解法2:

此解法取自: free disk space error using myisamchk

■myisamchk -rq --sort_buffer_size=256M --key_buffer_size=256M --read_buffer_size=32M --write_buffer_size=32M --sort-index --analyze --tmpdir=/more-disk-space-directory/ /var/lib/mysql/database/T1.MYI
■?: 修改 --tmpdir=/more-disk-space-directory/ 指定到空間比較大的地方去即可.