Mysql 資料庫備份

節錄自 http://www.study-area.org/tips/mysql_backup.htm

作者: Duncan Lo <duncan@twn.wox.org>

三.開始備份

 phpBB 討論區的資料檔,主要有兩個部份,就是 php 主程式和 DB 內容,
php 主程式的備份就比較簡單,只要把全部檔案 tar 起來就行了,就像:

   tar cvfz phpbb2_20020601.tgz phpbb

   (上面的 phpbb 是指 phpBB 的 php 網頁程式存放目錄.)
  
 以後有改到 phpBB 網頁程式部份再重新備份一次就行,它的內容資料都寫在
DB 內,所以 php 程式檔部份異動性應該不大.

 再來就是 MySQL DB 部份了,預設 MySQL 的 DB 檔案是存在 /var/lib/mysql 內,
以 DB 名稱為目錄,目錄內就是該 DB 的所有資料,像 phpbb2 這個 DB,
就是存在 /var/lib/mysql/phpbb2 內,在備份前,因為怕資料尚未完全寫入磁碟,
而且 MySQL 會 Lock 在使用的 DB 檔案,所以應該是要先把 MySQL 先 Shutdown 一下,
整個備份的程序可以下像下面的指令去完成:

   /etc/rc.d/init.d/mysqld stop
   tar cvfz phpbb2_db_20020601.tgz phpbb2
   /etc/rc.d/init.d/mysqld start
  
   (上面的 phpbb2 是指存放 phpbb2 這個 DB 的目錄.)
  
 Ok!這樣就完成了! (什麼?就這樣! 3行就結束了?!)

 對!這樣就可以了! 不過要注意的是,怕 DB 內每個 Tables 間的資料有關關聯性,
所以最好是把整個 DB 一次備起來,單獨只備哪個 Tables 的檔案,以後回存時,
怕會有資料關聯不一致的問題!

 以酷!學園的口水討論區為例,有21000筆左右的文章加上1200名註冊會員,資料庫
整個 tar 起來大約30幾 MB 左右,每天備份,以一週為週期來計算,備份大約只需要
(35*7=245) 200 多 MB 左右的空間,一星期的備份燒在一張光碟還夠!


四.如何回存

 phpBB 討論區的回存,只需把檔案解回原來存放網頁的路徑就可以,用以下指令解開:

   tar xvfz phpbb2_20020601.tgz
  
 DB 發生錯誤而要回存時,其實也不難,先找出最近一次完整正常的備份,先把現在錯誤
的網頁或 DB 先更名或 tar 起來,再把好的備份給解開回原來目錄位置就行了,需要
注意的是, MySQL 服務最好也是要先停止,回存完成後再啟動服務,回存 DB 的整個程序
可能像下面:

   /etc/rc.d/init.d/mysqld.stop
   mv phpbb2 phpbb2_error
   tar xvfz phpbb2_db_20020601.tgz
   /etc/rc.d/init.d/mysqld.start
  
 然後再去測試一下網頁及資料庫! 看使用上是否正常就行了...


五.MySQL線上備份

 使用像上面的"檔案"方式備份是個不錯的方法,它最少可以保持該主機某個時間點
的完整檔案備份,但還是有一些問題需要考慮到,有些主機就不只建立一個 DB 而已,
總不能為了備份某個 DB 而把整個 MySQL 服務停止,備份檔案的方式,回存在原主機上
一定適用,但假如 MySQL 版本升級,或是在那天,該網頁空間需遷機移機到別的主機時,
那就沒人敢保證備出來的資料檔可以用,所以我們可以考慮另一種備份的方式,是使用
MySQL 本身提供的功能: "MySQL Data Dump",指令是 "mysqldump".

 使用 MySQL 的 Dump 功能可以把 DB 的"結構","資料"或"結構加資料" Dump 成
文字檔, mysqldump 指令提供的彈性很大,你可以選擇把整個 DB Dump 成一個檔,
或是每個 Table 為一個檔,甚至是把結構檔和資料分開儲存都可以.

 檢測酷!學園的 phpBB 資料庫後發現,以結構加資料 Full Dump 成一個檔案,
檔案的大小大約也是30幾MB左右. 在 Dump 之前最好多下個 Flush-Logs 更新 LOG,
所以整個 Dump 的指令如下:

   mysqladmin -uroot -p flush-logs
   mysqldump phpbb2 -uroot -p --opt > phpbb2_20020601.sql

   (phpbb2 是 DB 名稱, opt 是一個使用完整 Dump 參數)
  
 再使用 time 指令去測試執行時間,這個30幾 MB 的 DB , Dump 出來竟只要15秒左右,
Query 的速度還真是快,假如只是要單獨 Dump 某個 Table 時,只要在上面的指令後,
轉出符號 ">" 前加個 Table 名稱就可以,如只要 phpbb2_users 這個 Table 的 Dump 時,
只要下:

   mysqldump phpbb2 -uroot -p --opt phpbb2_users > phpbb2_users_20020601.sql
  
 Dump 出來的檔案是個純文字檔,你可以用 tar 把它壓起來,以上面30幾 MB 的 DB 為例,
大約可以把檔案大小壓到1/3左右的大小,因為 Dump 出是文字檔,所有的資料都是以明文
顯示,所以必須注意一下備份檔保存的安全性,而且建議備份檔最好再另外儲存於異地以及
其他易於保存的媒體上,像光碟片或磁帶,這樣的備份才有意義.


六.MySQL線上回存

 若要回存整個資料庫,只需將壓縮的備份檔還原成 Dump 的檔案,再用下面的指令回存:

   mysql phpbb2 -uroot -p < phpbb2_20020601.sql
  
 這邊需注意的是,若建立備份時是以"--opt"或"--add-drop-table"為參數時,回存的
動作是先將舊的 Table 先刪除,重建 Table 的結構後再把 Data 匯入,所以回存後,
所有的資料會回到你當時備份那個時間點,因此在回存資料時,可以考慮把現有錯誤或
不完整的 DB 先備份一份下來,以備不時之需,或是拿來比對錯誤的地方在那裡,當然你也
可以把資料回存到另一個測試用的 DB 內,只要把上面指令的 DB 名稱改成你的測試
用 DB 名稱即可.

 使用這種回存方式, MySQL 服務不需停止,也不會動到其他正在使用的 DB ,在一些提供
MySQL 服務的虛擬主機,可用這種方式作你自己的 DB 備份及回存.

 另外,假如你是系統重建或是移機時,切需在新的 MySQL 內,新建一個空白 DB 後,才行
作回存的工作,你可以用下面的指令建立:

   mysql -uroot -p -e "CREATE DATABASE phpbb2"
  
   (上面的phpbb2是你要新建的 DB 名稱)
  
 另一種作法,先用 "mysql" 指令進入 "mysql client console", 然後再用:

   CREATE DATABASE phpbb2;
  
 這樣就行了,記得尾端要加個 ";" 符號該行指令才會執行.