SQL - MySQL 資料庫的備份與還原

wish

MySQL Server 的日常維護中最重要的一項大概就是資料庫的定時備份，而 MySQL 資料庫的備份方式有很多但一般來說大致上可概分為二種：
Binary Copy (直接複製資料庫檔案)
Dump Database (將資料庫輸出成為文字檔)



一、Binary Copy (直接複製資料庫檔案)

開門見山的說，非常不建議你這樣子做。MySQL 支援許多種不同的 Storage Engine，但並不是每一種 Storage Engine 都是 Binary Portable，意思就是說不是每一種 Storage Engine 都可以讓你把資料庫檔案直接複製到另外一台 MySQL Server，然後還可以正常運作的。MySQL 預設的 MyISAM Storage Engine 是 Binary Portable 的，因此若你的資料庫只有使用 MyISAM Storage Engine 的話，那麼你大可以直接複製資料庫檔案來進行備份（當然還必須考慮到資料的一致性，比方說複製之前先關閉 MySQL Server）。但若您有使用到 InnoDB Storage Engine，那麼你就不能這麼做，因為 InnoDB Storage Engine "不是" Binary Portable，只要 CPU 的浮點運算架構不同，複製過去的資料將無法正常運作。除此之外，你複製起來的 InnoDB 資料庫檔案(share table space)若是還原到不同版本的 MySQL Server 上，也會有很大的機率無法正常運作。除非你很肯定備份(複製)起來的資料只會使用在 "同一台 Server" 並使用 "同一個版本" 的 MySQL Server，不然請勿使用此方法進行備份。

註：
對於 MyISAM Storage Engine，每個資料庫(Database)都是一個獨立的目錄，而資料庫中的資料表則會分別以三個檔案儲存在該目錄中，這三個檔案分別是：
FRM： 儲存這個資料表的結構
MYD： Row Data，也就是你存在資料表(table)裡的資料
MYI： 此資料表的索引
但對於 InnoDB Storage Engine 來說，所有的資料庫(Database)與資料表(Table)都是儲存在同一個(或同一系列)的檔案之中，例如 /var/lib/mysql/ibdata1。


二、Dump Database (將資料庫輸出成為文字檔)

MySQL 在安裝時即有提供一系列的客戶端程式(Client Program)，這些程式其實就是各種功能不同的 Perl Scripts 的集合，其中包括有協助您操控 Server 的 mysqladmin、用來執行 SQL 指令的 mysql、轉換 binary log 用的 mysqlbinlog 等等。其中有一樣工具是專門讓您用來備份資料庫的，那就是 mysqldump。

mysqldump 的使用方式十分的簡易，其語法為：

引用:
mysqldump --lock-all-tables -u root -p 資料庫名稱 > example.sql

--lock-all-tables：進行備份時將正在備份的資料庫裡的資料表，全部鎖定以確保資料的一致性
-u root：使用 root 帳號進行備份
-p：需要輸入密碼，如果你的 root 帳號有密碼保護，而你又不加這個選項，就會直接 ACCESS DENIED
資料庫名稱：你要備份的資料庫名稱
example.sql：這個部份你想取什麼名字都行，總之這裡就是備份出來的檔案名稱

開啟備份出來的檔案看看，你會發現裡面其實是由許多 SQL 指令所組成，而這些 SQL 指令就是用來重建整個資料庫用的，因此當您還原資料庫的時候其實對 MySQL 來說，只是單純的重新執行備份檔裡面所有的 SQL 指令。由於備份出來的檔案是單純的文字檔案，因此它是 Binary Portable，你可以將它複製到任何一台 MySQL Server 上然後進行還原。

還原的方式也很簡單，只要使用以下的指令即可：

引用:
mysql -u root -p 資料庫名稱 < example.sql


大部份論壇程式都會提供資料庫備份機制來協助您備份論壇的資料庫，但除非您無法直接掌控伺服器(例如租用虛擬主機)或是你不具有使用 SHELL 執行指令的權限，否則不建議您使用那些第三方程式所提供的功能來備份資料庫，不然您有可能會遇到備份出來的東西還原不回去的情況。