MySQL BackUp 跟 Recovery

備份很重要，還原也很重要

前言

現實的世界並不完美。當災難發生時，事情可能以多種方式出錯使情況更糟，有時是因為故意而變糟的，但往往並不是故意變糟的。一旦我們知道出錯的原因後，總是會過得很不愉快。

我們經常可以看到開發人員做了這樣的事情。他們在沒有任何備份的情況下，不小心 Drop Table；或是他們做備份，但不知道如何 restore 它；有時突然發生災難時造成資料中心毀損，手中並沒有資料或資料庫備份的情況下做還原。

事實上，災難和人為錯誤總會存在，因此在本文章中，我們要探討如何通過將資料庫恢復到其原始狀態來進行有效備份(backup)和恢復(restore)資料庫。

mysqldump

mysqldump 是一個執行邏輯備份的客戶端實用程式。邏輯備份是一種包含create 和 insert 等語句的備份方式，使它可以重新創建資料到表中。

mysqldump 透過邏輯備份，產生可以運行以再現原始 schema 和和資料的SQL 語句並儲存在 .sql 檔中。

create 語句備份 table schema

insert 語句備份資料

除了SQL語句， mysqldump 命令還可以生成 .csv，分隔文本或 XML 格式。事實上，透過採用任何上述的格式存檔後，後面可以在其他任何資料庫（如SQL Server， Oracle 或任何其他接受這些格式輸入的資料庫）中導入同樣的資料。

請記住，對於大規模備份和還原，我的經驗是，建議使用資料檔(data file)的物理備份副本。取得原始資料後，將其保存在安全的存儲位置。爾後當想要恢復它時，就可以將這些檔案移回 MySQL 的資料檔位置。

mysqldump 使用的幾個情境

本段落中，我們以 saklia database 備份為例，並且以 saklia 來模擬商業情境，我們要維護兩個地點的資料庫，一個在台北總部的資料庫，另一個在國外，地點隨你挑選。考慮商業法律問題，兩家公司分別獨立運作，不過 IT 一樣會協作。

我們挑選 Kevin 跟 Ken 擔任 DBA，其中 Kevin 是台北總部資料庫的 DBA 而 Ken 負責另外一家外國公司的 DBA。

因為 Kevin 負責總部的資料庫，他還有額外的認識確保台北跟外國公司的資料庫的安全性。

某天， Kevin 要將兩家所有的銷售資料整合並計算出其利潤，所以他要求 Ken 也把資料交給他以利整合，由於兩家公司的資料庫是分開的，所以 Ken 可以怎麼做？這裡提到我們第一個使用情境：備份單一個資料庫，並將資料檔交給 Kevin 。

我們輸入以下指令(Windows 用戶可以直接到安裝 mysql 的資料夾透過 cmd 去執行 mysqldump.exe )

> mysqldump -h 127.0.0.1 -u root -p saklia > c:\backup\backup.sql

其中 -h 表示主機名(我們在自己的資料庫主機上，所以用 127.0.0.1 代表 localhost)，-u 表示資料庫用戶名，-p 表示其密碼。

通過這樣的指令，我們可以在 C 槽的 backup 資料夾中看到一個 backup.sql 檔，打開檔案就會看到整個 sakila 資料庫的備份內容。

如果我們打算備份多個資料庫

> mysqldump -h 127.0.0.1 -u root -p --database database1 database2 > c:\backup\backup.sql

甚至整個資料庫

> mysqldump -h 127.0.0.1 -u root -p --all-database > c:\backup\backup.sql

都可以透過以上的指令去作處理。

另外一天， Kevin 被要求要整理出兩家公司使用者的活動紀錄，所以他要掉調閱在資料庫中的 activity_log 這張 table，所以 Ken 可以怎麼做？這裡提到我們第二個使用情境：備份單一個表，並將資料檔交給 Kevin 。

> mysqldump -h 127.0.0.1 -u root -p saklia --tabels activity_log > c:\backup\backup.sql

-- table 參數會讓 mysqldump 將此關鍵字後面的所有名稱認作是 table 名。如果要備份特定多個表，一樣在 activity_log 後面加入要備份的 table 名稱即可。

還有最後一種備份的情況，例如要計算稅金，而兩家公司的稅金制度不同，我們的稅金計算方式是寫在 store procedure 中。那要如何達到備份 store procedure、event、trigger 等內容？我們再將條件設得更複雜，我只要這些 routines，我不需要創建 shema、資料等內容。

> mysqldump -h 127.0.0.1 -u root -p saklia --routines --no-create --no-data --no-create-db --skip-opt

--no-create 不會產生任何 table 的定義 --no-data不會產生任何表格內容。--no-create-db 不會產生資料庫語句，而最後一個選項 --skip-opt 將關閉任何其他選項設置的選項。

利用 mysql 進行 restore

成功的還原資料檔與成功備份資料檔同樣有價值。其次，如果沒有進行資料庫還原的測試，災難恢復策略永遠不會完成。我們不斷學習適當的備份策略，但是，當我們嘗試恢復之前所製作的備份檔時，真正的麻煩就開始了。

我的經驗是在恢復過程在關鍵時刻失敗，資料 dump 出得來，還原不回去。因為當時沒有任何一個人測試過備份和恢復過程的整個完整過程。在那次的經驗過後，我們大家都要備份並且進行還原確保備份資料檔不會失效。

請記住，在測試環境的 Server 上進行資料備份並測試資料備份檔是否可完整還原。

進行restore 的幾個情境

回到前面提過的情境，我們讓 Kevin 作一點事情好了，他不能不作任何事情只會一直要別人做備份，然後只寫這篇文章。所以 Kevin 要把 Sakila 資料庫還原。

當 Kevin 拿到 Ken 寄過來的 .sql 檔，他需要把這些備份資料檔匯入在總部的資料庫，所以第一個情境來了，他該如何還原 Sakila 資料庫？

首先，他要再額外建立一個新的 Schema(因為不能直接把舊的資料庫覆蓋掉)，所以我先建立好一個 sakliaanother schema，接著把資料倒入該 schema 中。

> mysql -h 127.0.0.1 -u root -p sakliaanother < c:\backup\backup.sql

再後來，台北總部因為不知道甚麼原因發大財了，所以要直接換一台新的 Server，舊有的 Server 上有好幾個資料庫，所以我們要把所有的資料庫做一次性還原，這是我們的第二個情境。我們新的主機上面資料庫沒有任何舊有的 Schema，所以我不需要再額外建立新的 Schema。

> mysql -h 127.0.0.1 -u root -p < c:\backup\backup.sql

backup 跟 restore 的差異

下面的灰框分別寫了 backup 跟 restore 的 script

-- 建立 backup
> mysqldump options > backup.sql
-- 還原備份
> mysql options < backup.sql

我們可以發現執行的程式不同外，以及箭頭的方向不同，其實箭頭的方向很明顯， > 表示把資料倒進 backup.sql 中，而 < 表示讓 mysql 從 backup.sql 把資料讀進去。

總結

資料庫裡的資料都是經過日積月纍所形成的，它是營運上珍貴的資產，萬一資料因誤刪、意外並且而無法挽回，那勢必是相當慘烈的損失。之前有聽過一家新創公司因為資料庫損毀直接倒閉的情況都有。
在 DevOps 的時代，資料備份不應該只是維運人員會作的事，我認為人人都應該對資料庫有所認識，建立良好且正確的資料庫備份觀念。