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

2018-07-17

吳惠麟

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

mysqlpump -u [資料庫使用者名稱] -p
[資料庫使用者密碼] --databases test
>/tmp/test.sql

在還原時，可以利用MySQL程式來進行還原：

mysql  -u [資料庫使用者名稱] -p
[資料庫使用者密碼] < /tmp/test.sql

除了傳統的邏輯備份方式之外，也可以利用在開源碼社群中所提供的XtraBackup軟體來備份資料庫。

以XtraBackup備份資料庫

XtraBackup（在本文所使用的版本為2.4.11）是由Percona公司所開發的熱備份（Hot Backup）資料庫的工具，可讓使用者在資料庫伺服器正常運作的情況下進行備份的作業。

除了一般的備份作業之外，XtraBackup也提供進階的增量備份功能。為了簡化說明起見，在此將只討論正常的備份作業。

首先，先來安裝XtraBackup，執行步驟如下：

yum install http://www.percona.com/
downloads/percona-release/redhat/
0.1-4/percona-release-0.1-4.noarch
.rpm 
yum update yum install percona-
xtrabackup-24 qpres

在安裝完成後，即可使用其所提供的innobackupex程式來備份MyISAM或InnoDB格式的資料庫。如下簡單的說明innobackupex常用的參數：

--apply-log：由於在備份的過程中，資料庫還是有可能會異動，而造成從資料庫與主資料庫的資料不一致，因此可利用此選項將在備份過程中異動的資訊記錄下來，之後再根據相關資訊來完成主資料庫與從資料庫的資料一致性。 --copy-back：還原所備份的資料。

--user：設定備份所使用的資料庫使用者帳號。

--password：設定備份所使用的資料庫使用者密碼。

--databases：設定欲備份的資料庫名稱。

--use-memory：設定在備份時，所要使用的系統記憶體容量。

--stream：設定輸出備份檔案的格式，可利用此選項來壓縮備份資料。

在進行備份作業之前，首先在主資料庫伺服器上新增一個具有備份權限的使用者帳號。可利用下列指令來新增一個名為「mybackup」的使用者（密碼同樣設定成「mybackup」），並設定相關權限：

mysql> CREATE USER 'mybackup'@'
localhost' IDENTIFIED BY
'mybackup';
mysql> GRANT RELOAD, LOCK TABLES,
REPLICATION CLIENT, CREATE TABLESPACE, 
PROCESS, SUPER, CREATE, INSERT,
SELECT ON *.* TO 'mybackup'@'
localhost';
mysql> FLUSH PRIVILEGES;

指令執行之後，結果如圖1所示。接著，在主資料庫的伺服器上新增了備份時所使用的權限後，接著也需要在主機系統上產生一個備份用的帳號，在此帳號也是設定為「mybackup」，執行如下指令：

▲圖1 顯示指令執行結果。

useradd -M -s /sbin/nologin mybackup
usermod -aG mysql mybackup  
#將mybackup用戶加入至mysql群組中

在相關設定完成後，即可進行備份還原的作業。這裡將說明如何備份還原主資料庫伺服器上所有的資料庫至從資料庫伺服器，以及如何備份還原主資料庫伺服器上單一資料庫至從資料庫伺服器的情況。

備份還原所有資料庫

首先，利用Innobackupex程式來備份主資料庫伺服器中所有的資料庫，以如下指令來進行備份：

Innobackupex --user=mybackup 
--password=mybackup --stream=tar
--socket=/tmp/mysql.sock
--port=3306 /mybackup/$(date
+%F)|gzip >/mybackup/$(date
+%F).tar.gz

如果在執行完成後出現如圖2所示的輸出，就表示已成功的執行備份程序，並會在「/mybackup」目錄下產生一個以日期為檔名的壓縮檔，在此檔案名稱為2018-06-08.tar.gz。