Mysql資料備份之邏輯備份

什麼樣的備份是資料庫邏輯備份呢？

大家都知道，資料庫在返回資料給我們使用的時候都是按照我們最初所設計期望的具有一定邏輯關聯格式的形式一條一條資料來展現的，具有一定的商業邏輯屬性，而在實體儲存的層面上資料庫軟體卻是依照資料庫軟體所設計的某種特定格式經過一定的處理後存放。

資料庫邏輯備份就是備份軟體按照我們最初所設計的邏輯關係，以資料庫的邏輯結構物件為單位，將資料庫中的資料按照預先定義的邏輯關聯格式一條一條產生相關的文字文件， 以達到備份的目的。

常用的邏輯備份

邏輯備份可以說是最簡單，也是目前中小型系統最常使用的備份方式。在MySQL中我們常用的邏輯備份主要就是兩種，一種是將資料產生可以完全重現目前資料庫中資料的INSERT 語句，另一個是將資料通過邏輯備份軟體，將我們資料庫表資料以特定分隔符號進行分隔後記錄在文字檔中。

1、產生 INSERT 語句備份

兩種邏輯備份各有優劣，所針對的使用場景也會稍有差別，我們先來看看產生 INSERT語句的邏輯備份。

在 MySQL 資料庫中，我們一般都是透過 MySQL 資料庫軟體自帶工具程式中的 mysqldump來實作聲稱 INSERT 語句的邏輯備份檔。其使用方法基本上如下：

Dumping definition and data mysql database or table

Usage: mysqldump [OPTIONS] database [tables]

OR mysqldump [OPTIONS] database [tables]

OR mysqldump [OPTIONS] --databasesDB1]

OR mysqldump [OPTIONS] --databasesDB1] 3.

OR mysqldump [OPTIONS] --all-databases [OPTIONS]

由於mysqldump 的使用方法比較簡單，大部分需要的資訊都可以透過執行「mysqldump --help」而獲得。這裡我只想結合 MySQL 資料庫的一些概念原理和大家探討一下當我們使用mysqldump 來做資料庫邏輯備份的時候有些什麼技巧以及需要注意一些什麼內容。

我們都知道，對於大多數使用資料庫的軟體或網站來說，都希望自己資料庫能夠提供盡可能高的可用性，而不是時不時的就需要停機停止提供服務。因為一旦資料庫無法提供服務，系統就無法再透過存取資料來提供一些動態功能。所以對於大多數系統來說如果要讓每次備份都停機來做可能都是不可接受的，可是mysqldump 程序的實現原理是透過我們給的參數資訊加上資料庫中的系統表資訊來一個表一個表取得資料然後產生INSERT 語句再寫入備份檔案中的。這樣就出現了一個問題，在系統正常運作過程中，很可能會不斷有資料變更的請求正在執行， 這樣就可能造成在 mysqldump 備份出來的資料不一致。也就是說備份資料很可能不是同一個時間點的數據，甚至可能都沒辦法滿足完整性限制。這樣的備份集對於有些系統來說可能並沒有太大問題，但是對於有些對資料的一致性和完整性要求比較嚴格系統來說問題就大了，就是一個完全無效的備份集。

對於如此場景，我們該如何做？我們知道，想資料庫中的資料一致，那麼只有兩種情況下可以做到。

第一、同一時刻取出所有資料；

第二、資料庫中的資料處於靜止狀態。 🎜🎜對於第一種情況，大家一定會想，這可能嗎？不管如何，只要有兩個以上的表，就算我們如何寫程序，都不可能昨晚完全一致的取數時間點啊。是的，我們確實無法透過常規方法讓取數的時間點完全一致，但是大家不要忘記，在同一個事務中，資料庫是可以做到所讀取的資料是處於同一個時間點的。所以，對於交易支援的儲存引擎，如Innodb 或BDB 等，我們就可以透過控制將整個備份過程控制在同一個交易中，來達到備份資料的一致性和完整性，而且mysqldump 程式也給我們提供了相關的參數選項來支援該功能，就是透過「--single-transaction」選項，可以不影響資料庫的任何正常服務。 🎜

對於第二種情況我想大家首先想到的肯定是將需要備份的表鎖定，只允許讀取而不允許寫入。是的，我們確實只能這麼做。我們只能透過一個折衷的處理方式，讓資料庫在備份過程中僅提供資料的查詢服務，鎖定寫入的服務，來使資料暫時處於一個一致的不會被修改的狀態，等mysqldump 完成備份後再取消寫入鎖定，重新開始提供完整的服務。 mysqldump 程式本身也提供了相關選項如“--lock-tables”和“--lock-all-tables”，在執行之前會鎖定表，執行結束後自動釋放鎖定。這裡有一點要注意的就是， 「--lock-tables」並不是一次性將需要dump 的所有表鎖定，而是每次僅鎖定一個資料庫的表，如果你需要dump 的表分別在多個不同的在資料庫中，一定要使用「--lock-all-tables」才能確保資料的一致完整性。

當透過 mysqldump 產生 INSERT 語句的邏輯備份檔案的時候，有一個非常有用的選項可以供我們使用，那就是 “--master-data[=value]”。當新增了「--master-data=1」的時候，mysqldump 會將目前MySQL 使用到binlog 日誌的名稱和位置記錄到dump 檔案中，並且是被以CHANGE_MASTER 語句的形式記錄，如果只是使用「- -master-data”或“--masterdata＝2”，則CHANGE_MASTER 語句會以註解的形式存在。這個選項在實施 slave 的線上搭建的時候是非常有用的，即使不是進行線上搭建 slave，也可以在某些情況下做恢復的過程中透過備份的 binlog 做進一步恢復操作。

在某些場景下，我們可能只是為了將某些特殊的資料匯出到其他資料庫中，而又不希望透過先建立臨時表的方式來實現，我們還可以在透過mysqldump 程式的「—where= 'wherecondition'”來實現，但只能在僅dump 一個表的情況下使用。其實除了以上一些使用訣竅之外， mysqldump 還提供了其他很多有用的選項供大家在不同的場景下只用，如通過“--no-data”僅僅dump 數據庫結構創建腳本，通過“--no- createinfo」去掉dump 檔案中建立表格結構的指令等等，有興趣的讀者朋友可以詳細閱讀mysqldump程式的使用介紹再自行測試。

2、產生特定格式的純文字備份資料檔案備份

除了透過產生INSERT 指令來做邏輯備份之外，我們還可以透過另一種方式將資料庫中的資料以特定分隔字元將資料分隔記錄在在文字檔案中，以達到邏輯備份的效果。這樣的備份資料與 INSERT 指令檔相比，所需使用的儲存空間較小，資料格式較為清晰明確，編輯方便。但是缺點是在同一個備份檔案中不能存在多個表的備份數據，沒有資料庫結構的重建命令。對於備份集需要多個文件，對我們產生的影響無非就是文件多了維護和恢復成本增加，但這些基本上都可以透過編寫一些簡單的腳本來實現

那我們一般可以使用什麼方法來產生這樣的備份集檔案呢，其實MySQL 也已經給我們實作的對應的功能。

在 MySQL 中一般都會使用以下兩種方法來取得可以自訂分隔符號的純文字備份檔案。

1、透過執行SELECT ... TO OUTFILE FROM ...指令來實作

在MySQL 中提供了一種SELECT 語法，專供使用者透過SQL 語句將某些特定資料以指定格式輸出到文字檔案中，同時也提供了實用工具和相關的命令可以方便的將匯出檔案原樣再匯入到資料庫中。正不正是我們做備份所需要的麼？

這個指令有幾個需要注意的參數如下：

實作字元轉義功能的「FIELDS ESCAPED BY ['name']」 將SQL 語句中需要轉義的字元進行轉義；

可以將欄位的內容“包裝”起來的“FIELDS [OPTIONALLY] ENCLOSED BY 'name'”，如果不使用“OPTIONALLY”則包括數字類型的所有類型資料都會被“包裝”，使用“OPTIONALLY”之後，則數字類型的資料不會被指定字元“包裝”。

透過"FIELDS TERMINATED BY"可以設定每兩個欄位之間的分隔符號；

而透過「LINES TERMINATED BY」則會告訴 MySQL 輸出檔案在每筆記錄結束的時候需要
新增什麼字元。

如以下例子：

root@localhost : test 10:02:02> SELECT * INTO OUTFILE '/tmp/dump.text'

-> FIELDS TERMINATED BY ',' OPALL > LINES TERMINATED BY 'n'

-> FROM test_outfile limit 100;

Query OK, 100 rows affected (0.00 sec)

:/tmp# cat dump.text

350021,21,"A","abcd"

350022,22,"B","abcd"

350023,23,"B","abcd"

350023,23,"C","abcd"

350023,23,"C","abcd"

350023,23,"C"

350024,24,"D","abcd"

350025,25,"A","abcd"

... ...

2、透過mysqldump 導出

... ...

2、透過mysqldump 導出

... ...我們都知道mydump中的資料以INSERT 語句的形式產生相關備份文件，其實除了產生INSERT 語句之外，mysqldump 也同樣能實現上面「SELECT ... TOOUTFILE FROM ...」所實現的功能，而且同時還會產生一個相關資料庫結構對應的建立腳本。

如以下範例：

root@sky:~# ls -l /tmp/mysqldump

total 0

root@sky:~# mysqldump -uroot -T/tmp/mysql多

root@sky:~# mysqldump -uroot -T/tmp/mysqldump test test_outfile~d " --fields-terminated-by=,

root@sky:~# ls -l /tmp/mysqldump

total 8

-rw-r--r-- 1 root root 1346 2008-10-14 222 :18 test_outfile.sql

-rw-rw-rw- 1 mysql mysql 2521 2008-10-14 22:18 test_outfile.txt

root@sky:# cat /tmp/myd. 21,"A","abcd"

350022,22,"B","abcd"

350023,23,"C","abcd"

350024,24,"C","abcd"

350024,24,"C","abcd"

350024,24,"D" 350025,25,"A","abcd"

... ...

root@sky:~# cat /tmp/mysqldump/test_outfile.sql

-- MySQL dump 10.11

---- Hostost : localhost Database: test

-- ----------------------------------------- --------------- Server version 5.0.51a-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHAR. CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;

/*!40111 , SQL_NOTES=0 */;

---- Table structure for table `test_outfile`

--DROP TABLE IF EXISTS `test_outfile`;

SET @saved_cs_ent = @clicharacterclient =

CREATE TABLE `test_outfile` (

`id` int(11) NOT NULL default '0',

`t_id` int(11) default NULL,

`a` char(1) default N mid` varchar(32) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;

/*!40103 SET SQL_MODE= @OLD_SQL_MODE */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=OLD_CHARAC/SETRES> =@OLD_COLLATION_CONNECTION */;

/ *!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2008-10-14 14:18:23

這樣的輸出結構對我們做為備份來使用是非常合適的，如果一次有非常合適的，如果一次有非常合適的多個表需要被dump，就會針對每個表都會產生兩個對應的檔案。

 以上就是Mysql資料備份之邏輯備份的內容，更多相關內容請關注PHP中文網（www.php.cn）！