MySQL 資料庫的備份與還原

資料庫的備份與還原（包含遷移）是維護網站的時候很必備而且常用的技能。
MySQL Server 的日常維護中最重要的一項大概就是資料庫的定時備份，而 MySQL 資料庫的備份方式有很多但一般來說大致上可概分為二種：

   1. Binary Copy (直接複製資料庫檔案)
   2. 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)都是一個獨立的目錄，而資料庫中的資料表則會分別以三個檔案儲存在該目錄中，

這三個檔案分別是：

   1. FRM： 儲存這個資料表的結構
   2. MYD： Row Data，也就是你存在資料表(table)裡的資料
   3. 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

------------------------------------------------------
MySQL 的備份及復原
要備份 MySQL 資料庫主要分為兩個方法，一是將資料庫目錄完整備份：二是使用 MySQL 內建的 mysqldump 程式。

備份資料庫目錄
MySQL 預設的儲存目錄在 /var/lib/mysql 內容，底下會有以資料庫名稱的目錄，例如 mydb 目錄便應該是 mydb 資料庫的資料。

如果 MySQL 正在運行，請先停止 MySQL，原因是可能會有資料未完全寫入，而 MySQL 會 lock 在使用中的 DB 檔案。

01 /etc/rc.d/init.d/mysqld stop
02 cd /var/lib/mysql/
03 tar zxcf mydb_backup.tgz mydb
04 /etc/rc.d/init.d/mysqld start


以上指令會先停止 MySQL，然後把 mydb 資料庫製作一個 taz 檔的備份，並儲存到 mydb_backup.tgz。
在使用以上指令時，請根據個別系統的設定作出修改。

好了，以上就麼 3 句指令就完成備份了，如果不幸的事情發生了，資料庫發生錯誤而要復原資料，可使用以下指令：

01 /etc/rc.d/init.d/mysqld stop
02 cd /var/lib/mysql/
03 mv mydb mydb_error
04 tar zxvf mydb_backup.tgz
05 /etc/rc.d/init.d/mysqld start


以上指令是先把 /var/lib/mysql/mydb 移到 /var/lib/mysql/mydb_error，然後將原先製作的備份檔解壓到 /var/lib/mysql/mydb。

mysqldump
雖然以上方法十分簡單，但有一個問題存在，那就是在備份及復原時均需停止 MySQL 的運作，這樣對於實際應用十分不便。再者，這樣備份出來的檔案，如果在相同版本的 MySQL Server 應該沒有問題，但移到版本不同的 Server 則不一定可以成功復原。

因為有以上的問題，MySQL 已經內建了備份工具，它就是 mysqldump。
mysqldump 的備份方法是將資料庫內的每個資料表結構及每筆資料產生 SQL 語句，然後存到文字檔。而且它可以自訂每一個資料表一個檔案，以及將資料表結構及資料分開儲存，以下是使用例子：

01 mysqldump --user=root -p mydb > /backup/mydb.sql


以上指令會使用 mysqldump 將 mydb 備份到 /backup/mydb.sql，在輸入指令後，需要輸入 MySQL 的 root 密碼。

至於復原資料同樣簡單，只要一句指令便完成：

01 mysql --user=root -p mydb < /backup/mydb.sql
(原文使用"mysqldump"來復原，應為筆誤，改成上述指令才對)

以上指令會將 /backup/mydb.sql 備份檔復原到 mydb 裡面。如果你的 MySQL Server 不止一個資料庫，希望可以一次過將所有資料庫備份起來，可以寫一個簡單的 shell script 完成，又或者使用以下指令：

01 mysqldump --user=root -p --all-databases > /backup/mysql.sql


這個 --all-databases 代表所有資料庫，這樣 mysqldump 便會將所有資料庫備份到 /backup/mysql.sql。