檢查和維護MYSQL數據庫表

2024-07-24 12:55:23

字體：大中小

來源：轉載

供稿：網友

    理想的情況是，mysql從首次安裝以來始終平穩地運行。但有時確實會由于各種原因而出現問題，其范圍可以從電源斷電到硬件故障到不正常地關閉mysql服務器（如用kill -9 終止服務器或機器崩潰）。諸如這樣的情況大部分都超出您的控制范圍，它們會導致數據庫表的毀壞，尤其是在對表進行修改且未完全寫入時所引起的。
    本章的重點是檢測和解決表的問題，而不論問題是如何引起的。對于表的檢查和修復，mysql管理員最好的朋友是myisamchk 和isamchk 實用程序。這兩個程序有好幾個功能，我們丫詰?章討論了怎樣使用它們執行索引鍵的分布分析和索引的釋放與激活。還可以使用它們檢查表和修復有問題的表。這使您能在表變壞之前（使表不能使用之前）修正故障。
    myisamchk 和isamchk 提供的全部選項的清單在附錄e 中。有關其他的背景，請參閱mysql參考指南的“維護mysql安裝”一章。

    表的故障檢測和修正的一般過程如下：
    1) 檢查出錯的表。如果該表檢查通過，則完成任務，否則必須修復它。
    2) 在開始修復之前對表文件進行拷貝，以防萬一。
    3) 試著修復表。
    4) 如果修復操作失敗，從數據庫備份和更新日志中恢復此表。
    上述過程的最后一步假定您已經執行了數據庫備份并允許更新日志有效。如果不是這樣的話，系統將有危險。參考第11章查找一下怎樣使用mysqlaump 和怎樣開啟更新日志。您肯定不想不可挽回地丟失一個表，因此，應努力地做備份。
    在使用myisamchk 或isamchk 檢查或修復表之前，應該滿足一些初步需求：
    建立常規的數據庫備份過程并允許更新日志，以防事情越來越糟使表的毀壞不能修復。筆者好像在以前提醒過這一點？
    在開始試驗之前應先仔細地閱讀本章的內容。尤其是不應該在閱讀“避免與mysql服務器交互作用”之前進行操作，因為它將討論當您試圖在一個表上執行檢查或修復過程時服務器正在使用這個表所引起的問題。它還討論怎樣在服務器運行時防止那些問題發生。
    當運行表檢查或修復時，您應該被注冊在運行mysql的賬號下，因為您需要對表文件讀寫訪問。


myisamchk 和isamchk 的調用語法

    mysql的myisamchk 和isamchk 實用程序很類似，多數時候它們可以用同樣的方式使用。它們之間的主要區別是它們所使用的表的類型。對于myisam 表，使用my i s a m c h k，而對于isam 表，則使用i s a m c h k。您可以通過表的索引文件的擴展名來告訴表使用哪種存儲格式。擴展名“. m y i”表明是一個myisam 表，而“. i s m”表明是isam 表。
    為了使用任一個實用程序，應指明您所要檢查或修復的表，以及指明要執行的操作類型的選項：
    % myisamchk options tbl_name...
    % isamchk options tbl_name...
    tbl_name 參數可以是表名也可以是該表的索引文件名。如果指定多個表，可以很容易地使用文件名模式來拾取目錄中所有相應的文件：
    % myisamchk options *.myi
    % isamchk options *.ism
    不會因為告訴了錯誤的程序來檢查某個表而使該表毀壞，但是除了發布一條警告消息外此程序不做任何事情。例如，下面的第一條語句將檢查當前目錄中的所有myisam 表，而第二條語句只顯示一條警告消息：
    % myisamchk *.myi 正確
    % myisamchk *.ism 不正確─文件類型錯
    不論是myisamchk 還是isamchk 都不對表所在的位置做任何判斷，因此，應該或者在包含表文件的目錄中運行程序，或者指定表的路徑名。這允許您將表文件拷貝到另一個目錄中并用該拷貝進行操作。

檢查表

    myisamchk 和isamchk 提供了表檢查方法，這些方法在徹底檢查表的程度方面有差異。通常用標準方法就足夠了。如果標準檢查報告沒有發現錯誤而您仍然懷疑有毀壞（或許因為查詢沒有正常地工作），可能要執行更徹底的檢查。要想用任意一個實用程序執行標準的表檢查，則不用帶任何選項直接調用即可：
    % myisamchk tbl_name
    % isamchk tbl_name
    為了執行擴充檢查，使用--extend-check 選項。該選項非常慢，但檢查極為徹底。對于該表的數據文件中的每個記錄，索引文件中的每個索引的相關鍵都被檢查以確保它真正指向正確的記錄。myisamchk 還有一個中間選項- - m e d i um - c h e c k，它不如擴展檢查徹底，但速度快。
    如果對于--extend-check 檢查不報告錯誤，則可以肯定表是好的。如果您仍然感覺表有問題，那原因肯定在其他地方。應重新檢查任何好像有問題的查詢以驗證查詢是正確書寫的。如果您認為問題可能是mysql服務器的原因，應考慮整理一份故障報告或升級到新的版本上。
    如果myisamchk 或isamchk 報告表有錯誤，應用下節中的說明修復它們。

修復表

    表的修復是一項可怕的工作，如果具體問題非常獨特則更難進行。然而，有一些常規的指導思想和過程，可以遵循它們來增加修正表的機會。通常，開始時可以用最快的修復方法，看看是否能修正故障。如果發現不行的話，可以逐步升級到更徹底的（但更慢的）修復方法上，直到故障被修復或您不能繼續升級為止（實際上，大多數問題不用更大規模的和更慢的方法就能修正）。如果表不能修復，則從備份中恢復該表。有關使用備份文件和更新日志進行恢復的指導在已第11章中給出。
    1. 執行標準的表修復
    為了修復一個表，執行下列步驟：
    1) 試著用--recover 選項修正表，但也可以用--quick 選項試圖只根據索引文件的內容進行恢復。這樣將不觸及數據文件：
    % myisamchk --recover --quick tbl_name
    % isamchk --recover --quick tbl_name
    2) 如果問題仍存在，再試一下上一步的命令，但忽略--quick 選項，以允許my i s a m c h k或isamchk 前進并修改數據文件：
    % myisamchk --recover tbl_name
    % isamchk --recover tbl_name
    3) 如果還不工作，試一試--safe-recover 修復方法。這種方法比普通的恢復方法要慢，但能夠修正-recover 方法不能修正的幾個問題：
    % myisamchk --safe-recover tbl_name
    % isamchk --safe-recover tbl_name
    如果myisamchk 或isamchk 由于一個“c a n’t create new temp file: file_name” 的錯誤消息在任何一步中停止，應該重復這個命令并增加--force 選項以迫使清除臨時文件。這個臨時文件可能是從上一次失敗的修復中留下的。
    在修復表之前拷貝它們在執行表修復前應該遵循的一個常規的預防措施是做該表的新拷貝。這種情況未必出現，但如果發生，則可以從拷貝文件中做該表的新的拷貝并試試另一種恢復方法。
    2. 標準表修復方法失敗時怎么辦
    如果標準的修復過程未能修復表，則索引文件可能在修復時丟失或毀壞。盡管未必可能，但還是有可能使表的描述文件丟失。不論哪種情況，都需要替換受影響的文件，然后再試試標準修復過程。
    為了重新生成索引文件，可以使用下列過程：
    1) 定位到包含崩潰表的數據庫目錄中。
    2) 將該表的數據文件移到安全的地方。
    3) 調用mysql并通過執行下列語句重新創建新的空表，該語句使用表的描述文件tbl_name.frm 重新開始生成新的數據和索引文件：
    mysql> delete from tbl_name;
    4) 退出mysql，將原始的數據文件移回到數據庫目錄中，替換剛建立的新的空文件。
    5) 再試試標準表修復方法。
    為了恢復該表的描述文件，可先從備份文件中恢復，然后再試著用標準修復方法。如果由于某些原因沒有備份，但知道建立表的create table 語句，則仍可以恢復該文件：
    1) 定位到包含崩潰表的數據庫目錄中。
    2) 將該表的數據文件移動到安全的地方。如果想要使用索引的話，還需將索引文件移走。
    3) 調用mysql并發布create table 語句建立該表。
    4) 退出mysql，將原始數據文件移回數據庫目錄中，替換剛才新建的數據文件。如果在步驟2移動了索引文件，則也要將其移回數據庫目錄中。
    5) 再試試標準表修復方法。

避免與mysql服務器交互作用

    當您正在運行表的檢查/修復實用程序時，您或許不想讓mysql服務器和實用程序同時訪問一個表。如果兩個程序都向表中寫數據顯然是一件壞事，但是，當一個程序在寫入時另一個程序在讀取也不是件好事。如果表正由一個程序寫入，同時進行讀取的另一個程序會被
搞亂。
    如果您關閉服務器，就可以保證在服務器和myisamchk 或isamchk 之間沒有交互作用。但是管理員極不愿意使服務器完全地脫機，因為這使得沒有故障的數據庫和表也不可用。本節中討論的過程將幫助您避免服務器和myisamchk 或isamchk 之間的交互作用。
    服務器有兩種類型的鎖定方法。它使用內部鎖定避免客戶機的請求相互干擾──例如，避免客戶機的select 查詢被另一個客戶機的update查詢所干擾。服務器還使用外部鎖定（文件級鎖）來防止其他程序在服務器使用表時修改該表的文件。通常，在表的檢查操作中服務器將外部鎖定與myisamchk 或isamchk 組合使用。但是，外部鎖定在某些系統中是禁用的，因為它不能可靠地進行工作。對運行myisamchk 和isamchk 所選擇的過程取決于服務器是否能使用外部鎖定。如果不使用，則必須使用內部鎖定協議。
    如果服務器用--skip-locking 選項運行，則外部鎖定禁用。該選項在某些系統中是缺省的，如l i n ux。可以通過運行mysqladmin variables 命令確定服務器是否能夠使用外部鎖定。檢查skip_locking 變量的值并按以下方法進行：
    如果skip_locking 為o ff，則外部鎖定有效。您可以繼續并運行任一個實用程序來檢查表。服務器和實用程序將合作對表進行訪問。但是，在運行任何一個實用程序之前，應該用mysqladmin flush-tables 刷新表的高速緩存。為了修復表，應該使用表的修復鎖定協議。
    如果skip_locking 為o n，則禁用外部鎖定，但在myisamchk 或isamchk 檢查或修復一個表時服務器并不知道，最好關閉服務器。如果堅持使服務器保持開啟狀態，需要確保在您使用此表時沒有客戶機來訪問它。必須使用恰當的鎖定協議告訴服務器使該表獨處，并阻塞客戶機對其訪問。
    這里所描述的鎖定協議使用服務器的內部鎖定機制，以防止服務器在您利用my i s a m c h k或isamchk 工作時訪問表。通常的辦法是調用mysql并對要檢查或修復的表發布l o c k table 語句。然后，在mysql空閑時（即運行，但除了保持該表鎖定外不用它做任何事情），運行myisamchk 或i s a m c h k。在myisamchk 或isamchk 結束后，可以切換到mysql會話中并釋放該鎖以告訴服務器程序執行完畢此表可以再次使用了。
    檢查和修復的鎖定協議有點區別。對于檢查，您只需要獲得讀鎖。在這種情況下，只能讀取表，但不能修改它，因此它也允許其他客戶機讀取它。讀鎖足以防止其他客戶機修改表。對于修復，您必須獲得寫鎖以防止任何客戶機在您對表進行操作時修改它。
    鎖定協議使用lock table 和unlock table 語句獲得并釋放鎖。協議還使用f l u s h tables 告訴服務器刷新磁盤中任何未決的改變，并在通過表修復實用程序修改表后重新打開該表。您必須從單個mysql會話中執行所有l o c k、flush 和unlock 語句。如果鎖定一個表然后退出mysql，則該鎖將釋放，且運行myisamchk 或isamchk 將不再是安全的！
    如果保持打開兩個窗口的狀態，且一個運行mysql，而另一個運行myisamchk 或i s a m c h k，則運行鎖定過程將會變得很容易。這樣允許您很容易地在程序之間進行切換。如果不是運行在視窗環境中，當運行myisamchk 或isamchk 時，將需要使用外殼程序的作業控制工具暫停和恢復mysql。下面的指導顯示出對myisamchk 或isamchk 的命令，可用與您正在使用的表相對應的那個命令。
    1. 對檢查操作鎖定表
    此過程只針對表的檢查，不針對表的修復。在窗口1中，調用mysql并發布下列語句：
    % mysqldb_name
    mysql>lock table tbl_name read;
    mysql>flush tables;
    該鎖防止其他客戶機在檢查時寫入該表和修改該表。flush 語句導致服務器關閉表的文件，它將刷新仍然在高速緩存中的任何未寫入的改變。
    當mysql空閑時，切換到窗口2 并檢查該表：
    % myisamchk tbl_name
    % isamchk tbl_name
    當myisamchk 或isamchk 結束時，切換回到窗口1的mysql會話并釋放該表鎖：
    mysql>unlock table;
    如果myisamchk 或isamchk 指出發現該表的問題，將需要執行表的修復。
    2. 對修復操作鎖定表
    修復表的鎖定過程類似于檢查表的過程，但有兩個區別。第一，您必須得到寫鎖而非讀鎖。由于您將要修改表，因此根本不允許客戶機對其進行訪問。第二，必須在執行修復之后發布flush table 語句，因為myisamchk 和isamchk 建立了新的索引文件，除非再次刷新
該表的高速緩存否則服務器將不會注意到它：
    % mysqldb_name
    mysql>lock table tbl_name write;
    mysql>flush tables;
    利用mysql的空閑切換到窗口2，做該表的數據庫文件的拷貝，然后運行myisamchk 或i s a m c h k：
    % cp tbl_name.* |some|other|directory
    % myisamchk --recover tbl_name
    % isamchk --recover tbl_name
    --recover 選項只是針對安裝而設置的。這些特殊選項的選擇將取決于您執行修復的類型。myisamchk 或isamchk 運行完成后，切換回到窗口1的mysql會話，再次刷新該表的高速緩存并釋放表鎖：
    mysql>flush tables;
    mysql>unlock table;

快速運行myisamchk 和i s a m c h k

    myisamchk 和isamchk 的運行可能會花很長時間，尤其是您正在處理一個大表或使用一個更廣泛的檢查或修復方法時。通過告訴這些程序在運行時使用更多的內存，能夠提高它們的速度。這兩個實用程序都有幾個可設置的操作參數。其中最重要的是控制程序使用的緩沖
區大小的變量：

變量	含義
key _ buffer _ s i z e	用于存放索引塊的緩沖區大小
r e a d _ buffer _ s i z e	讀操作用的緩沖區大小
sort _ buffer _ s i z e	排序用的緩沖區大小
w r i t e _ buffer _ s i z e	寫操作用的緩沖區大小

    要想查看任一個程序使用的這些變量的缺省值，可用--help 選項運行該程序。要想指定其他的值，可在該命令上使用--set-variable variable=value 或-o variable=value。您可以將變量的名字簡化成key、r e a d、sort 和w r i t e。例如，可告訴myisamchk 使用16mb 的排序緩沖區和1mb 的讀寫緩沖區，其調用如下：
    % myisamchk -0 sort=16m -0 read=1m write=1m ...
    sort _ buffer_size 只能利用--recover 選項來使用（而不是利用- - s a f e _ r e c o ver）,在這種情況下，key _ buffer 不能使用。
    減少服務器的停機時間
    防止服務器訪問（您正在處理的）表的另一種方法是在數據目錄的外面使用該表文件的拷貝。這樣并不能消除交互作用的問題，因為仍然必須防止服務器訪問（并可能修改）正在進行拷貝的表。但是，如果您不愿意使服務器脫機的話，該路線可能是使服務器停機時間最小化的一種方法，這對您是有吸引力的。在將該表的文件拷貝到另一個目錄時關閉服務器，然后恢復服務器。
    myisamchk 的未來打算
    myisamchk 的表檢查和修復功能打算在mysql3.23 版本系列的某個時候被合并到服務器中。如果這種打算實現，對表的檢查和修復將更容易，因為服務器與my i s a m c h k的交互問題將不再會出現。
    同樣，您能夠告訴服務器在啟動時檢查表，因此在啟動服務器前將不需要設置任何特殊的命令在引導期間執行。該程序不對isam 表進行操作，因此在服務器獲得表的檢修復能力時，應考慮將isam 表轉換成myisam 表。請查看新發行版的mysql參考指南，了解在此范圍內有什么新進展。可以用alter table 語句轉換表的類型：
    alter table tbl_name type=myisam

上一篇：安排預防性的維護MYSQL數據庫服務器

下一篇：安全網絡訪問mysql數據庫服務器