本欄目下相關鏈接


MySQL 備份、優化與故障處理

MySQL 是一種高效快速的中小型數據庫系統,這套系統的讀寫速度,尤其是讀速度可以媲美和超過很多昂貴的商業數據庫系統,同時其功能 也完全可以滿足一般網絡應用軟件的需要,適合為論壇等軟件構建的數據庫支撐環境。Discuz! 的 MySQL 版本充分可以充分發揮該數據庫軟件 的優勢,更加優化的進行數據庫操作,最大限度的保證了系統的穩定。然而,在實際使用中,為了使得數據庫服務器運行在最佳狀態,您可能 仍然需要做一些優化,或在故障時進行必要的維修操作。本文將對 MySQL 系統的日常使用、常規優化方案和故障恢復作以簡要說明。


數據備份與恢復

最快的備份方法:直接文件複製

    MySQL 的數據庫文件支持直接複製(Hot-copy),您在任意類型操作系統的服務器中將數據文件複製下來,拷貝到另外的一台服務器的 MySQL 數據目錄,只要數據庫軟件版本不相差太多(例如不是 3.22 和 3.23/4.0),您可以立即直接使用複製過來的完整數據文件。基於這種特性您可以 使用最短的時間完成數據備份。

    按照常見的安裝習慣,Unix 版本的 MySQL 數據目錄通常可能為(不限於下列):

    • /var/lib/mysql/
    • /usr/local/mysql/data/
    • /usr/local/mysql/var/

    Windows 版本的 MySQL 數據目錄通常可能為(不限於下列):

    • c:\mysql\data\
    • d:\mysql\data\
    • x:\mysql\data\

    在數據目錄下,可能有多個以數據庫名命名的子目錄,將

    建議您在直接複製(Hot-copy)的時候盡量把數據庫服務器停止,這樣備份的數據會較少出錯。如果不方便停止服務器,又恰好在某一文件 的某一部分上,備份和數據庫服務器寫入同時進行,則可能造成拷貝下來的數據文件存在錯誤。但這種錯誤通常不致命,可以經過修復而恢復, 修復的方法將在後面的部分作以說明。

    如果需要恢復某些數據表,只需先把數據庫停止,把備份的文件拷貝到原有的目錄中覆蓋即可。注意在 Unix 系統中,需要檢查覆蓋後的 文件屬性和屬主,以免無法被 MySQL 讀取而報錯。

    本方法只適合擁有獨立服務器的用戶使用,虛擬主機用戶不在此列。

較快而安全的備份方法:mysqldump

    MySQL 軟件本身提供了將數據內容導出為 SQL 文件的工具——mysqldump。通過這一方法備份和恢復,較直接文件複製來說更為安全,但速度 慢一些,數據表大則更是如此。

    您可以在 MySQL 程序文件目錄找到備份使用的工具 mysqldump 及恢復使用的工具 mysql。按照常見的安裝習慣,Unix 版本的 MySQL 程序 文件目錄通常可能為(不限於下列):

    • /usr/bin/
    • /usr/local/mysql/bin/

    Windows 版本的 MySQL 程序文件目錄通常可能為(不限於下列):

    • c:\mysql\bin\
    • d:\mysql\bin\
    • x:\mysql\bin\

    備份和恢復的命令分別為(其中斜體字表示需要替換相關的內容):

    • 備份:mysqldump --force --add-drop-table --extended-insert -h"數據庫主機名" -u"用戶名" -p"密碼" "數據庫名" > 備份數據文件名
    • 恢復:mysql -h"數據庫主機名" -u"用戶名" -p"密碼" "數據庫名" < 備份數據文件名

    Discuz! 在系統設置中已經設計了此種備份模式(Shell 方式),速度與直接在服務器終端操作是完全一致的,但不需要記憶命令與參數, 只需通過 web 界面修改操作即可。此功能需要備份出,和恢復到的服務器上的 PHP 具有 Shell 權限,您可以嘗試一下即知。

兼容性最好的備份方法:Discuz! 分卷備份

    早在 Discuz! 的初期版本就率先在論壇軟件中內置了支持大量數據備份與恢復的分卷備份功能,此種方法雖然速度最慢,但具有良好的 兼容性,可以在幾乎所有服務器環境,包括虛擬主機環境成功的備份大量數據。

    分卷備份的使用簡單,您只需在 Discuz! 系統設置中選擇分卷備份,按照提示進行即可。

    在進行數據恢復時,尤其是將備份的數據恢復到另外的服務器、或另外的數據庫中時,您需要事先安裝一個與備份數據版本相同的空論壇, 確保該空論壇使用和備份數據中相同的數據表前綴(即 config.inc.php 中的 $tablepre 設置),同時在設置管理員時,使用與備份數據中相同的 一個管理員用戶名、密碼和安全提問,這樣才能保證分卷備份數據的導入連貫和成功。

    導入前,把備份數據文件完整的上傳到服務器論壇目錄中的 ./forumdata 下,在 系統設置 的 數據庫恢復 功能中選擇第一卷的文件名,點 導入鏈接,之後相關的分卷數據會被自動導入。

    我們同時提供了一個分卷備份的數據恢復工具 restore.php,該工具放在軟件包 ./utilities 目錄中,如果您使用的是完全備份,可以在 不安裝論壇的情況下將數據完整的導入。此工具必須放入服務器上論壇目錄(而非 ./utilities)才能使用,且可以自動導入分卷備份的全部 全部數據,只需將 file 參數指定為分卷數據的第一卷即可(例如 dz_xxx-1.sql),後面的數據會自動隨頁面跳轉而導入(需瀏覽器 JS 支持 ),詳細使用方法見程序運行後的提示。


常規優化方案

建議使用的版本

    通常情況下,Unix 類操作系統用戶可以直接使用 MySQL 官方提供的已經編譯好的軟件包,可以避免可能的兼容性問題。如果對一些特殊參數 有要求,也可以自行編譯源碼,但僅建議有豐富經驗的用戶使用,如果發現有不穩定或不兼容現象,請檢查相關的編譯參數是否存在問題。

    目前我們建議使用的 MySQL 版本為 4.0.x 版,相對 3.23 系列,4.0.x 同樣穩定和安全,不僅提供了更多的功能、更好的兼容性,而且修復 了很多 3.23 系列的 BUG。在 Windows 系統中,請使用 mysqld-nt(NT/2000/2003 系統)作為服務器,mysqld-max-nt 只有在需要用到 bdb 等 功能的時候才需要。

數據庫的常規優化

    MySQL 本身的配置文件 my.cnf(或 my.ini)中的相關參數,對整個數據庫系統來說尤為重要。針對不同的服務器內存容量,MySQL 程序包 中提供了 my-small.cnf、my-medium.cnf、my-large.cnf、my-huge.cnf 四個分別適用於服務器內存不低於 64M、256M、512M、1G 情況下的參數 設置,您可以根據自身機器的實際情況,數據庫應用所佔比重,在上述四個文件中提供的參數基礎上對配置文件進行修改。Unix 類系統用戶, 建議將配置文件命名為 my.cnf 放置於 /etc 中;Windows 系統用戶直接在 Winmysqladmin.exe 中對 my.ini 進行修改即可。

    除了以上默認的配置文件提供的參數以外,通常情況您還需要在 [mysqld] 後修改或增加以下的參數以適應大部分 web 應用程序的需要:

    • 最大連接數為 600,以滿足一般應用對連接數的需要:增加 max_connections = 600。根據我們的經驗,500~1000 是較為合適的數值,沒 必要將其設置為超過 1000,那只會造成對資源的浪費。
    • 不使用 innodb 和 bdb:增加兩行內容,分別是 skip-innodb 和 skip-bdb。Discuz! 和大部分 web 應用程序不需要使用此兩項功能,因此 將其關閉以節約內存和磁盤空間,提高效率。
    • 連接超時時間 5,避免空閒進程過多的內存佔用:增加 wait_timeout = 5。通過減少超時時間,使得使用 pconnect(長期連接)的用戶在 利用其不需反覆驗證用戶名和密碼的同時,避免打開過多的空閒進程,減少內存消耗。
    • 禁止端口連接:增加 skip-networking。如果使用 Unix 類操作系統,數據庫和 httpd 在同一台服務器,且不需要遠程讀取數據庫,可增設 此項參數,關閉默認的 3306 端口,有效提禁止外部網絡未經授權的訪問,避免端口被用以進行 DDoS 攻擊。Windows 系統不需要(不能)增加 這個參數。

    其他參數的配置在此不詳述,如果您對服務器及 MySQL 數據庫有相當的瞭解,可以通過數據庫的日常運行情況,有針對性的進行修改。

數據表優化(Optimize Table)

    當您的庫中刪除了大量的數據後,您可能會發現數據文件尺寸並沒有減小。這是因為刪除操作後在數據文件中留下碎片所致。Discuz! 在 系統數設置界面提供了數據表優化的功能,可以去除刪除操作後留下的數據文件碎片,減小文件尺寸,加快未來的讀寫操作。您只要在做完 批量刪除,或定期(如每一兩個月)進行一次數據表優化操作即可。


故障解決辦法

由於 MySQL 本身的讀寫及鎖定機制等方面的原因,與一些其他數據庫軟件一樣,在特殊情況下的極為頻繁讀寫時,或在服務器掉電、死機等 情況下,相關的數據文件可能會發生被損壞的情況,通常可以採用以下的方式加以解決。

repair.php 修復工具

    Discuz! 自帶了一個使用 PHP 編寫的數據表修復工具 repair.php,虛擬主機用戶也可使用。該工具位於軟件包的 ./utilities 目錄中, 使用時需要上傳到服務器上論壇目錄(注意不要連 ./utilities 目錄一起上傳,否則無法運行),在瀏覽器運行後點擊下面的鏈接即可。

    這個工具能修復大多數常見的數據庫錯誤,尤其是錯誤號為 126、127 的錯誤,對 145 錯誤也可修復,同時能對數據表在修復之後進行優化。 如果一次修復不成功,可以嘗試多次,或將數據庫重啟後再試。

myisamchk 修復工具

    MySQL 自帶了專門用戶數據表檢查和修復的工具——myisamchk,當 repair.php 多次修復均無法成功時,可以在服務器終端使用 myisamchk 進行修復。在 MySQL 的程序文件目錄(見《數據備份與恢復》中的說明)可以找到這個工具。

    常用的修復命令為 myisamchk -r 數據文件目錄/數據表名.MYI,如果 -r 參數不能奏效,可以先把數據文件備份(備份可使用直接文件複製 的方式,詳見《數據備份與恢復》中的說明)後使用 -o 參數。

    另外 MySQL 官方文檔中還提供了針對上面操作均無法奏效時的特殊辦法,如先清空重建數據表,然後再用備份的數據文件覆蓋等,這種特別 複雜的情況用戶通常不會碰到,因此這裡不再做過於深入的研究。

數據表經常性損壞的解決方法

    首先請確認在服務器硬件不存在問題(如內存工作不穩定、散熱條件不好等),且使用中的操作系統版本也沒有相關的 BUG 報告或升級補丁。 這種情況下,如果數據庫仍出現經常性的損壞,請檢查是否 MySQL 的編譯方式或參數存在問題。通常情況下使用官方提供的編譯好的版本是比較 穩定的,可以長期使用。如果您鍾愛自行編譯相關程序,請確認您的語言編譯器(如 gcc)和配置的相關參數沒有導致不穩定的因素。同時,磁盤 分區滿也可能是導致數據表經常性損壞的原因。網上提供了一些問題的處理方法(英文),需要時可多參考,並針對您的具體服務器環境制定解決 方案。