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

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

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

二、常用的邏輯備份

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

①生成 INSERT 語句備份

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

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

Dumping definition and data mysql database or table
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
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”或者“ --master-data＝2”，則 CHANGE_MASTER 語句會以註釋的形式存在。這個選項在實施 slave 的線上搭建的時候是非常有用的，即使不是進行線上搭建 slave，也可以在某些情況下做恢復的過程中透過備份的 binlog 做進一步恢復操作。

在某些場景下，我們可能只是為了將某些特殊的資料匯出到其他資料庫中，而又不希望透過先建臨時表的方式來實現，我們還可以在透過 mysqldump 程式的“ —where='where-condition'”來實現，但只能在僅 dump 一個表的情況下使用。

其實除了以上一些使用訣竅之外，mysqldump 還提供了其他很多有用的選項供大家在不同的場景下只用，如透過“ --no-data”僅僅 dump 資料庫結構建立指令碼，透過“ --no-create-info”去掉 dump 檔案中建立表結構的命令等等，感興趣的讀者朋友可以詳細閱讀 mysqldump 程式的使用介紹再自行測試。

②生成特定格式的純文字備份資料檔案備份

除了透過生成 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 ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> FROM test_outfile limit 100;
Query OK, 100 rows affected (0.00 sec)
root@localhost : test 10:02:11> exit
Bye
root@sky:/tmp# cat dump.text
350021,21,"A","abcd"
350022,22,"B","abcd"
350023,23,"C","abcd"
350024,24,"D","abcd"
350025,25,"A","abcd"
... ...

2.透過 mysqldump 匯出

可能我們都知道 mysqldump 可以將資料庫中的資料以 INSERT 語句的形式生成相關備份檔案，其實除了生成 INSERT 語句之外，mysqldump 還同樣能實現上面“ SELECT ... TO OUTFILE FROM ...”所實現的功能，而且同時還會生成一個相關資料庫結構對應的建立指令碼。

如以下示例：

root@sky:~# ls -l /tmp/mysqldump
total 0
root@sky:~# mysqldump -uroot -T/tmp/mysqldump test test_outfile --fields�enclosed-by=\" --fields-terminated-by=,
root@sky:~# ls -l /tmp/mysqldump
total 8
-rw-r--r-- 1 root root 1346 2021-4-20 22:18 test_outfile.sql
-rw-rw-rw- 1 mysql mysql 2521 2021-4-20 22:18 test_outfile.txt
root@sky:~# cat /tmp/mysqldump/test_outfile.txt
350021,21,"A","abcd"
350022,22,"B","abcd"
350023,23,"C","abcd"
350024,24,"D","abcd"
350025,25,"A","abcd"
... ...
root@sky:~# cat /tmp/mysqldump/test_outfile.sql
-- MySQL dump 10.11
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.0.51a-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `test_outfile`
--
DROP TABLE IF EXISTS `test_outfile`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `test_outfile` (
`id` int(11) NOT NULL default '0',
`t_id` int(11) default NULL,
`a` char(1) default NULL,
`mid` varchar(32) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2021-4-20 14:18:23

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

三、邏輯備份恢復方法

僅僅有了備份還是不夠啊，我們得知道如何去使用這些備份，現在我們就看看上面所做的邏輯備份的恢復方法：

由於所有的備份資料都是以我們最初資料庫結構的設計相關的形式所儲存，所以邏輯備份的恢復也相對比較簡單。當然，針對兩種不同的邏輯備份形式，恢復方法也稍有區別。下面我們就分別針對這兩種邏輯備份檔案的恢復方法做一個簡單的介紹。

①INSERT 語句檔案的恢復

對於 INSERT 語句形式的備份檔案的恢復是最簡單的，我們僅僅只需要執行該備份檔案中的所有（或者部分）SQL 命令即可。首先，如果需要做完全恢復，那麼我們可以透過使用 “ mysql < backup.sql”直接呼叫備份檔案執行其中的所有命令，將資料完全恢復到備份時候的狀態。如果已經使用 mysql 連線上了 MySQL，那麼也可以透過在 mysql 中執行“ source/path/backup.sql”或者“ \. /path/backup.sql”來進行恢復。

②純資料文字備份的恢復

如果是上面第二中形式的邏輯備份，恢復起來會稍微麻煩一點，需要一個表一個表透過相關命令來進行恢復，當然如果透過指令碼來實現自動多表恢復也是比較方便的。恢復方法也有兩個，一是透過 MySQL 的“ LOAD DATA INFILE”命令來實現，另一種方法就是透過 MySQL 提供的使用工具 mysqlimport 來進行恢復。

邏輯備份能做什麼？不能做什麼？

在清楚瞭如何使用邏輯備份進行相應的恢復之後，我們需要知道我們可以利用這些邏輯備份做些什麼。

透過邏輯備份，我們可以透過執行相關 SQL 或者命令將資料庫中的相關資料完全恢復到備份時候所處的狀態，而不影響不相關的資料；
透過全庫的邏輯備份，我們可以在新的 MySQL 環境下完全重建出一個於備份時候完全一樣的資料庫，並且不受 MySQL 所處的平臺型別限制；
透過特定條件的邏輯備份，我們可以將某些特定資料輕鬆遷移（或者同步）到其他的 MySQL 或者另外的資料庫環境；
透過邏輯備份，我們可以僅僅恢復備份集中的部分資料而不需要全部恢復。

在知道了邏輯備份能做什麼之後，我們必須還要清楚他不能做什麼，這樣我們自己才能清楚的知道這樣的一個備份能否滿足自己的預期，是否確實是自己想要的。

邏輯備份無法讓資料恢復到備份時刻以外的任何一個時刻；

四、邏輯備份恢復測試

有時聽到某某的資料庫出現問題，而當其信心十足的準備拿之前所做好的資料庫進行恢復的時候才發現自己的備份集不可用，或者並不能達到自己做備份時候所預期的恢復效果。遇到這種情景的時候，恐怕每個人都會鬱悶至極的。資料庫備份最重要最關鍵的一個用途就是當我們的資料庫出現某些異常狀況，需要對資料進行恢復的時候使用的。

作為一個維護人員，我們是絕對不應該出現此類低階錯誤的。那我們到底該如何避免此類問題呢？

只有一個辦法，那就是週期性的進行模擬恢復測試，校驗我們的備份集是否真的有效，是否確實能夠按照我們的備份預期進行相應的恢復。

到這裡可能有人會問，恢復測試又該如何做呢，我們總不能真的將線上環境的資料進行恢復啊？

是的，線上環境的資料確實不能被恢復，但是我們為什麼不能在測試環境或者其他的地方做呢？

做恢復測試只是為了驗證我們的備份是否有效，是否能達到我們的預期。

所以在做恢復測試之前我們一定要先清楚的知道我們所做的備份到底是為了應用於什麼樣的場景的。

就比如我們做了一個全庫的邏輯備份，目的可能是為了當資料庫出現邏輯或者物理異常的時候能夠恢復整個資料庫的資料到備份時刻，那麼我們惡的恢復測試就只需要將整個邏輯備份進行全庫恢復，看是否能夠成功的重建一個完整的資料庫。

至於恢復的資料是否和備份時刻一致，就只能依靠我們自己來人工判斷比較。

此外我們可能還希望當某一個資料庫物件，比如某個表出現問題之後能夠儘快的恢復該表資料到備份時刻。那麼我們就可以針對單個指定表進行抽樣恢復測試。

下面我們就假想資料庫主機崩潰，硬體損壞，造成資料庫資料全部丟失，來做一次全庫恢復的測試示例：

當我們的資料庫出現硬體故障，資料全部丟失之後，我們必須儘快找到一臺新的主機以頂替損壞的主機來恢復相應的服務。在恢復服務之前，我們首先需要重建損壞的資料庫。假設我們已經拿到了一臺新的主機，MySQL 軟體也已經安裝就位，相關設定也都已經調整好，就等著恢復資料庫了。

我們需要取回離崩潰時間最近的一次全庫邏輯備份檔案，複製到準備的新主機上，啟動已經安裝好的 MySQL。

由於我們有兩種邏輯備份格式，每種格式的恢復方法並不一樣，所以這裡將對兩種格式的邏輯備份的恢復都進行示例。

①如果是 INSERT 語句的邏輯備份

a、準備好備份檔案，copy 到某特定目錄，如“/tmp”下；

b、透過執行如下命令執行備份集中的相關命令：

mysql -uusername -p < backup.sql

或者先透過 mysql 登入到資料庫中，然後再執行如下命令：

root@localhost : (none) 09:59:40> source /tmp/backup.sql

c、再到資料庫中檢查相應的資料庫物件，看是否已經齊全；

d、抽查幾個表中的資料進行人工校驗，並通知開啟應用內部測試校驗，當所有校驗都透過之後，即可對外提供服務了。

當然上面所說的步驟都是在預設每一步都正常的前提下進行的，如果發現某一步有問題。假若在 b 步驟出現異常，無法繼續進行下去，我們首先需要根據出現的錯誤來排查是否是我們恢復命令有錯？是否我們的環境有問題等？等等。

如果我們確認是備份檔案的問題，那麼說明我們的這個備份是無效的，說明測試失敗了。如果我們恢復過程很正常，但是在校驗的時候發現缺少資料庫物件，或者某些物件中的資料不正確，或者根本沒有資料。同樣說明我們的備份級無法滿足預期，備份失敗。

當然，如果我們是在實際工作的恢復過程中遇到類似情況的時候，如果還有更早的備份集，我們必須退一步使用更早的備份集做相同的恢復操作。雖然更早的備份集中的資料可能會有些失真，但是至少可以部分恢復，而不至於丟失所有資料。

②如果我們是備份的以特殊分隔符分隔的純資料文字檔案

a、第一步和 INSERT 備份檔案沒有區別，就是將最接近崩潰時刻的備份檔案準備好；

b、透過特定工具或者命令將資料匯入如到資料庫中：

由於資料庫結構建立指令碼和純文字資料備份檔案分開存放，所以我們首先需要執行資料庫結構建立指令碼，然後再匯入資料。結構建立指令碼的方法和上面第一種備份的恢復測試中的 b 步驟完全一樣。

有了資料庫結構之後，我們就可以匯入備份資料了，如下：

mysqlimport --user=name --password=pwd test --fields-enclosed-by=\" --fields-terminated-by=, /tmp/test_outfile.txt

或者

LOAD DATA INFILE '/tmp/test_outfile.txt' INTO TABLE test_outfile FIELDS TERMINATED BY '"' ENCLOSED BY ',';

後面的步驟就和備份檔案為 INSERT 語句備份的恢復完全一樣了，這裡就不再累述。

阿里面試官：知道 MySQL 邏輯備份與恢復測試麼？