一點關于MySQL參數delay_key_write、myisam_recover_options的使_MySQL

一點關于MySQL參數delay_key_write、myisam_recover_options的使用經驗

最近在做數據庫實例遷移的時候遇到了幾個比較詭異的問題：

MyISAM實例正常shutdown后rsync數據文件到另外一臺機器上起實例后，訪問表時提示表自動修復失敗需要repair table。提示信息：Table './test/record_03' is marked as crashed and last (automatic?) repair failed

表損壞后，利用repair table命令將表修復時提示表里存在很多的duplicate key.

warning : Duplicate key for record at 129584986 against record at 62008769

warning : Duplicate key for record at 104678355 against record at 61426294

warning : Duplicate key for record at 297493788 against record at 61697778

warning : Duplicate key for record at 209950328 against record at 61548867

warning : Duplicate key for record at 105894968 against record at 61866949

...

...

補充一下當時的環境信息：

1.MySQL版本為官方版的5.5.12，表引擎是MyISAM

2.遷移的步驟：stop slave->flush tables->正常shutdown實例->rsync表文件->在新機器上起實例。rsync前后表文件MD5值一樣。

3.表的結構類似這樣：

[sql]

create table test (

id int auto_increment primary key,

a varchar(100) not null,

b int not null,

c int,

unique key (a,b)

)

首先說第一個問題，這里分兩點：1.為什么會需要修復？2.為什么自動修復失??？

我是正常關閉實例，不明白表需要修復的原因于是請淘寶丁奇幫忙分析了數據文件，顯示文件頭里記錄顯示表被關閉仍有6個線程在訪問。但我可以確定當時rsync數據文件前是正常shutdown實例的，這個可能是MyISAM本身的問題或者是本身數據文件就已經存在問題都不得而知，總而言之當我遷移過來后訪問表時需要修復這點是合理的，因為表被關閉時還有線程在訪問，這會被MySQL認為表非正常關閉，需要修復。

為什么會自動修復失敗，這是由于我配置了參數myisam_recover_options=default，這個配置表示每次訪問MyISAM表之前都會先檢測表是否需要修復，如果需要則自動進行，這也就是前面看到信息last (automatic?) repair failed。而修復失敗是因為這個參數帶來的修復行為默認是從key cache里面找需要修復的數據，而我當時是shutdown實例，rsync到新環境中起實例，此時已沒有當時的現場(key cache環境)，加上default不會強制進行修復（強制修復表如果索引文件和數據文件數據不一致則自動進行刪除或者增加行），（如果是myisam_recover_options=force，那么即使此時key cache不存在了也會進行強制修復，此時做的就是對比數據文件和索引文件，然后刪除數據文件中多余的行，因此這樣可能會丟數據）。在自動修復時可以再error log中看到如下信息：

130410 9:31:23 [ERROR] /usr/local/mysql55/bin/mysqld: Table './image_0/record_00' is marked as crashed and should be repaired

130410 9:31:23 [Warning] Checking table: './image_0/record_00'

130410 9:31:24 [ERROR] Got an error from unknown thread, /export/home/pb2/build/sb_0-3198286-1302530066.93/mysql-5.5.12/storage/myisam/ha_myisam.cc:868

130410 9:31:24 [Warning] Recovering table: './image_0/record_00'

130410 9:31:28 [Note] Retrying repair of: './image_0/record_00' with keycache

130410 9:31:37 [ERROR] Couldn't repair table: image_0.record_00

接著說第二個問題，在第一個問題中提示表損壞，需要手工repair table，而我執行了這條命令后提示表里存在很多重復鍵數據:

warning : Duplicate key for record at 129584986 against record at 62008769

warning : Duplicate key for record at 104678355 against record at 61426294

warning : Duplicate key for record at 297493788 against record at 61697778

warning : Duplicate key for record at 209950328 against record at 61548867

warning : Duplicate key for record at 105894968 against record at 61866949

這個重復鍵指的是唯一鍵(a,b)，然后進行了一個小測試驗證是數據文件里面有重復的行（在a,b字段上）。邏輯圖大概是這樣

[plain]

id a b c

1 1 2 3

2 1 2 4

3 2 3 55

4 2 3 54

上面這個列表是我自己模擬的，在(a,b)上有唯一鍵約束，但是在數據文件中卻存在(a,b)重復的行。驗證方法較簡單：

/*強制走全表掃描*/select * from test where a=val1 and b=val2;

結果集有2行

/*強制走索引*/select * from test where a=val1 and b=val2;

結果集只有1行

當時遇到問題第一反應是開發人員那邊是不是曾經導過數據且導數據的時候禁止了唯一約束檢查，但是事實上開發說自己沒這么干過，于是開發說這是MySQL的bug，我怎么也覺得MySQL不會存在這樣大的bug，一直覺得是對MySQL使用方法不對。于是對遷移前后的環境做了對比檢查終于發現了疑點：老的環境中配置了delay_key_write=ALL，myisam_recover_options=OFF（默認是OFF）。而我的新環境中則是delay_key_write=ON，myisam_recover_options=default 。

簡單解釋下delay_key_write,每次更新MyISAM表索引后不立即將更新flush到物理磁盤上，這些修改依然保留在內存中，直到這個表被關閉時再flush，ALL表示對所有的表都使用這一行為，ON表示對創建表時使用了delay_key_write的表使用這個功能。OFF表示不進行delay key write。而官方建議在使用delay_key_write時同時用myisam_recover_options這樣才能保證數據一致性，否則，舉個最簡單的例子，索引數據一致都沒flush到磁盤上，突然表被非正常關閉，等下次表被訪問如果不進行相應的檢測修復，那么就會出現數據文件與索引不一致的情況，這樣就會導致數據不一致。

再簡單解釋下myisam_recover_options，default表示每次訪問表時會先判斷是否需要修復，但是不會強制修復（僅僅嘗試從key cache中修復），backup表示修復時會先將老的數據文件先做個備份，force表示強制修復，具體用法見下圖。

此時就大致可以確定是由于MySQL參數使用不合理導致的數據不一致問題，所以在使用MyISAM時如果配置了delay_key_write，就千萬記得同時配置一個myisam_recover_options這樣才可能防止數據不一致性的情況出現。那么我是怎么解決這個問題的呢？目前來說還是通過遷移后的MySQL配置參數環境保持與老環境一致，否則如果我現在強制加上參數myisam_recover_options，那么就會導致所有的表損壞，損壞之后恢復的唯一途徑就是repair table，這會丟失數據（或者配置myisam_recover_options=force,backup，這個效果是跟repair table一樣，且同時對將要修改的MYD文件做一個備份），因為對于重復鍵的行，如果是利用repair table去做的話，我無法干預MySQL留下其中具體的哪行，所以如果強行repair table雖然可以解決重復鍵錯誤，但是最終留下的哪行不一定是用戶想要的那行數據。當然還有另外兩種可行的方法，第一，咨詢業務負責人后手工刪除重復鍵中不需要的行。第二，強制repair table 然后將老的數據留一份做備份，然后用戶端發現異常了再進行數據恢復，不過這個做法不可取，否則都會被用戶投訴致死。

總之，都21世紀了，都5.6了，不要再用MyISAM了吧，我這是歷史系統，沒辦法，后期進行升級改造也是必須進行的。

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com