Backup MySQL 資料庫備份 SQL 備份

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

2018-07-17

吳惠麟

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

ON_PERMISSIVE：開啟GTID模式並產生針對每個執行SQL指令的動作產生GTID，且設定從資料庫可接受帶有GTID資訊的記錄以及不帶有GTID資訊的記錄。

OFF_PERMISSIVE：關閉GTID模式，並設定從資料庫可接受帶有GTID資訊的記錄以及不帶有GTID資訊的記錄。

以GTID模式實作複製架構

首先，在主資料庫上新增備份欲採用的使用者帳號，登入資料庫伺服器後，執行下列指令來新建備份用的帳號，以下為新建一個名稱為「rep」的備份帳號，密碼為「reptest」：

mysql>grant REPLICATION SLAVE 
ON *.* to 'rep'@'從資料庫ip' identified
by 'reptest'
mysql>flush privileges;

接著，設定主資料庫伺服器上的my.cnf組態（#之後為註解）：

[mysqld]
server_id = 1 #設定Server的代號，不可
與其他資庫伺服器重複
gtid_mode = on #設定開啟GTID模式
 enforce_gtid_consistency = on
 #設定要驗證是否違反GTID限制
 #設定二進位日誌的所在位置
 log-bin = /usr/local/mysql5/log/
 master-binlog
 log-slave-updates = 1  #設定從資
 料庫可更新主資料庫的二進位檔案
 binlog_do_db=test  #設定要同步的資
 料庫
 binlog_format = row #設定記錄所有
 變動的紀錄
 skip_slave_start=1 #設定從資料庫須
 執行START SLAVE指令，才會進行複製
 master_info_repository = TABLE
 relay_log_info_repository = TABLE

重啟主資料庫伺服器後，可利用如圖8所示的「SHOW GLOBAL VARIABLES LIKE‘%gtid%’;」指令來檢查GTID模式是否已正常運作。

▲圖8 檢查GTID模式是否已正常運作。

接著，同樣在從資料庫伺服器上的「my.cnf」檔案內加上以下設定：

[mysqld]
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on
log-bin = /usr/local/mysql5/log/
slave-binlog
log-slave-updates = 1
binlog_format = row
master_info_repository = TABLE
relay_log_info_repository = TABLE
skip_slave_start=1

之後，再重啟從資料庫伺服器，即可利用下列指令來啟動複製功能：

#先關閉啟動複製功能
mysql>STOP SLAVE for CHANNEL
'channel';  
#設定複製所需要的相關參數，可利用MASTER
_AUTO_POSITION=1的設定來取代傳統的
MASTER_LOG_FILE及MASTER_LOG_POS
參數
mysql>change master to  MASTER_
HOST='主資料庫IP',MASTER_PORT
=3306,MASTER_USER='rep',MASTER_
PASSWORD='reptest',MASTER_AUTO_
POSITION=1 FOR CHANNEL 'channel';
#啟動複製功能
mysql>START SLAVE FOR CHANNEL
'channel';

啟動複製功能之後，可以再執行「show slave status\G」指令，來檢查啟動是否成功，如果成功即會出現如圖9所示的輸出。