Backup MySQL 資料庫備份 SQL 備份

實作MySQL備份還原　詳實說明三種機制模式

2018-07-17

吳惠麟

如何安全地保存資料一直是IT管理的重要項目，而勤做資料備份絕對是最重要的例行工作。本文將以MySQL資料庫為例，實際示範幾種資料備份的機制，包括以mysqlpump進行備份、透過XtraBackup備份資料庫，以及使用MySQL內建的GTID機制來同步主資料庫與從資料庫內的資料。

處於一個資訊爆炸的時代，相信資料對企業而言是最彌足珍貴的資產，而對於一個資料庫管理者而言，定時的備份作業也是不可或缺的作業。因此本文將以MySQL資料庫伺服器（採用MySQL 5.7.16版本，作業系統為CentOS 7）為例來說明並實作幾種資料備份的機制。

在說明之前，先簡單地定義主資料庫（Master）和從資料庫（Slave）的角色。主資料庫表示系統實際在使用的資料庫，從資料庫則是複製主資料庫資料的備份資料庫，而資料庫伺服器指的即是MySQL。

以mysqlpump進行備份

一般想到備份資料庫資料時，通常都會想到利用mysqldump程式來傾印（Dumper）資料庫的內容，並以SQL指令的形式來保存，此種備份方式又稱為邏輯備份。之後，再利用執行此檔案內的SQL指令來回復資料庫的資料。

在MySQL 5.7之後的版本，除了mysqldump程式外，還提供了一個名稱為mysqlpump的備份程式。

事實上，此程式為mysqldump的衍生，同樣也是利用SQL指令的形式來保存資料庫的資料，但增加了如下所述的特色：

1. 可利用平行處理的方式來備份資料庫，相對mysqldump而言，可加快備份的效率。

2. 具有更好的備份功能。包括可備份儲存函式（Storage Function）、觸發函式（Trigger Function）等資訊。

3. 可利用SQL指令來儲存相關系統資料庫的資訊，例如以CREATE USER、GRANT等SQL指令來儲存，而不是過去利用INSERT指令來儲存系統資料庫的資訊，如此可讓使用者更容易得知系統資料庫的資訊。

4. 可直接產生壓縮後的備份文件。

接著，說明mysqlpump常用的選項，內容如下所述：

-- add-drop-database：在執行建立資料庫的SQL指令之前，加上刪除資料庫的指令。

--add-drop-table：在執行建立資料庫表格的SQL指令之前，加上刪除資料庫表格的指令。

--add-drop-user：在新增資料庫用戶的SQL指令前，加上刪除資料庫用戶的指令，此參數需要與--users參數同時使用，否則將不會生效。

--add-locks：在執行備份的作業時，使用鎖資料庫表格（LOCK TABLES）機制並在備份完成後再解鎖（UNLOCK TABLES），以避免在備份的過程中，資料庫有異動，而造成資料不一致的問題。要特別注意的是，使用此選項時不能夠開啟平行備份的的功能（須設定--default-parallelism=0來關閉平行備份功能）。

--all-databases：設定備份所有的資料庫資訊。

--bind-address：由於資料庫伺服器上可能會設定多個IP（例如伺服器有多個網路介面），因此可利用此選項來設定連接MySQL伺服器的網路介面。

--complete-insert：設定備份的SQL指令為完整的insert指令。

--compress：設定使用壓縮方式來壓縮客戶端和服務器進行傳輸的所有數據。

--compress-output：設定壓縮所使用的演算法，目前支援的壓縮演算法有LZ4和ZLIB。

--databases：設定要進行備份的資料庫，可利用空格來設定多個資料庫以進行備份。

--default-parallelism：設定平行處理時所使用的線程（Thread）數，預設為2，如果設成0，表示不使用平行處理備份。

--events：備份資訊庫的事件，預設為開啟，如果要關閉時，須使用--skip-events參數。

--exclude-databases：在備份時，指定排除該參數指定的資料庫，可利用逗號分隔多個資料庫，就能夠設定排除多個資料庫。

--skip-dump-rows：設定只備份資料庫表格的結構，並不備份資料庫的數據。

-- max-allowed-packet：設定傳輸時所使用的最大緩衝區（Buffer）的空間。

--user：設定在執行備份作業時，所使用的使用者名稱。