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:設定在執行備份作業時,所使用的使用者名稱。

--password:設定在執行備份作業時所使用的使用者密碼。

--watch-progress:設定在備份時須即時顯示備份的進度資訊。

接下來,簡單說明幾個常用的備份範例。

若要備份test資料庫(包括資料庫結構與資料),並且將相關資訊儲存在test.sql檔案的時候:


追蹤我們Featrue us

本站使用cookie及相關技術分析來改善使用者體驗。瞭解更多

我知道了!