第 5 章 MySQL 備份與恢複

前言

資料庫的備份與恢複一直都是 DBA 工作中最為重要的部分之一，也是基本工作之一。任何正式環境的資料庫都必須有完整的備份計劃和恢複測試，本章内容将主要介紹 MySQL 資料庫的備份與恢複相關内容。

5.1 資料庫備份使用場景

你真的明白了自己所做的資料庫備份是要面對什麼樣的場景的嗎？

我想任何一位維護過資料庫的人都知道資料庫是需要備份的，也知道備份資料庫是資料庫維護必不可少的一件事情。那麼是否每一個人都知道自己所做的備份到底是為了應對哪些場景的呢？抑或者說我們每個人是否都很清楚的知道，為什麼一個資料庫需要作備份呢？讀到這裡，我想很多讀者朋友都會嗤之以鼻，“備份的作用不就是為了防止原資料丢失嗎，這誰不知道？”。确實，資料庫的備份很大程度上的作用，就是當我們的資料庫因為某些原因而造成部分或者全部資料丢失後，友善找回丢失的資料。但是，不同類型的資料庫備份，所能應付情況是不一樣的，而且，資料庫的備份同時也還具有其他很多的作用。而且我想，每個人對資料庫備份的作用的了解可能都會有部分差別。

下面我就列舉一下我個人了解的我們能夠需要用到資料庫備份的一些比較常見的情況吧。

一、資料丢失應用場景

1、人為操作失誤造成某些資料被誤操作；

2、軟體BUG造成資料部分或者全部丢失；

3、硬體故障造成資料庫資料部分或全部丢失；

4、安全漏洞被入侵資料被惡意破壞；

二、非資料丢失應用場景

5、特殊應用場景下基于時間點的資料恢複；

6、開發測試環境資料庫搭建；

7、相同資料庫的新環境搭建；

8、資料庫或者資料遷移；

上面所列出的隻是一些常見的應用場景而已，除了上面這幾種場景外，資料庫備份還會有很多其他應用場景，這裡就不一一列舉了。那麼各位讀者過曾經或是現在所做的資料庫備份到底是為了應對以上哪一種（或者幾種）場景？或者說，我們所做的資料庫備份能夠應對以上哪幾種應用場景？不知道這個問題大家是否有考慮過。

我們必須承認，沒有哪一種資料庫備份能夠解決所有以上列舉的幾種常見應用場景，即使僅僅隻是資料丢失的各種場景都無法通過某一種資料庫備份完美的解決，當然也就更不用說能夠解決所有的備份應用場景了。

比如當我們遇到磁盤故障，丢失了整個資料庫的所有資料，并且無法從已經出現故障的硬碟上面恢複出來的時候，我們可能必須通過一個實時或者有短暫時間差的複制備份資料庫存在。當然如果沒有這樣的一個資料庫，就必須要有最近時間的整個資料庫的實體或者邏輯備份資料，并且有該備份之後的所有實體或者邏輯增量備份，以期望盡可能将資料恢複到出現故障之前最近的時間點。而當我們遇到認為操作失誤造成資料被誤操作之後，我們需要有一個能恢複到錯誤操作時間點之前的瞬間的備份存在，當然這個備份可能是整個資料庫的備份，也可以僅僅隻是被誤操作的表的備份。而當我們要做跨平台的資料庫遷移的時候，我們所需要的又隻能是一個邏輯的資料庫備份，因為平台的差異可能使實體備份的檔案格式在兩個平台上無法相容。

既然沒有哪一種很多中資料庫備份能夠完美的解決所有的應用場景，而每個資料庫環境所需要面對的資料庫備份應用場景又可能各不一樣，可能隻是需要面對很多種場景中的某一種或幾種，那麼我們就非常有必要指定一個合适的備份方案和備份政策，通過最簡單的技術和最低廉的成本，來滿足我們的需求。

5.2 邏輯備份與恢複測試

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

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

資料庫邏輯備份就是備份軟體按照我們最初所設計的邏輯關系，以資料庫的邏輯結構對象為機關，将資料庫中的資料按照預定義的邏輯關聯格式一條一條生成相關的文本檔案，以達到備份的目的。

5.2.2 常用的邏輯備份

邏輯備份可以說是最簡單，也是目前中小型系統最常使用的備份方式。在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] --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程式的使用介紹再自行測試。

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 \',\' 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 2008-10-14 22:18 test_outfile.sql

-rw-rw-rw- 1 mysql mysql 2521 2008-10-14 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 2008-10-14 14:18:23

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

5.2.3 邏輯備份恢複方法

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

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

1、INSERT語句檔案的恢複：

對于INSERT語句形式的備份檔案的恢複是最簡單的，我們僅僅隻需要運作該備份檔案中的所有（或者部分）SQL指令即可。首先，如果需要做完全恢複，那麼我們可以通過使用“mysql < backup.sql”直接調用備份檔案執行其中的所有指令，将資料完全恢複到備份時候的狀态。如果已經使用mysql連接配接上了MySQL，那麼也可以通過在mysql中執行“source /path/backup.sql”或者“\. /path/backup.sql”來進行恢複。

2、純資料文本備份的恢複：

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

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

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

1、通過邏輯備份，我們可以通過執行相關SQL或者指令将資料庫中的相關資料完全恢複到備份時候所處的狀态，而不影響不相關的資料；

2、通過全庫的邏輯備份，我們可以在新的MySQL環境下完全重建出一個于備份時候完全一樣的資料庫，并且不受MySQL所處的平台類型限制；

3、通過特定條件的邏輯備份，我們可以将某些特定資料輕松遷移（或者同步）到其他的MySQL或者另外的資料庫環境；

4、通過邏輯備份，我們可以僅僅恢複備份集中的部分資料而不需要全部恢複。

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

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

2、邏輯備份無法

5.2.4 邏輯備份恢複測試

時有聽到某某的資料庫出現問題，而當其信心十足的準備拿之前所做好的資料庫進行恢複的時候才發現自己的備份集不可用，或者并不能達到自己做備份時候所預期的恢複效果。遇到這種情景的時候，恐怕每個人都會郁悶至極的。資料庫備份最重要最關鍵的一個用途就是當我們的資料庫出現某些異常狀況，需要對資料進行恢複的時候使用的。作為一個維護人員，我們是絕對不應該出現此類低級錯誤的。那我們到底該如何避免此類問題呢？隻有一個辦法，那就是周期性的進行模拟恢複測試，校驗我們的備份集是否真的有效，是否确實能夠按照我們的備份預期進行相應的恢複。

到這裡可能有人會問，恢複測試又該如何做呢，我們總不能真的将線上環境的資料進行恢複啊？是的，線上環境的資料确實不能被恢複，但是我們為什麼不能在測試環境或者其他的地方做呢？做恢複測試隻是為了驗證我們的備份是否有效，是否能達到我們的預期。是以在做恢複測試之前我們一定要先清楚的知道我們所做的備份到底是為了應用于什麼樣的場景的。就比如我們做了一個全庫的邏輯備份，目的可能是為了當資料庫出現邏輯或者實體異常的時候能夠恢複整個資料庫的資料到備份時刻，那麼我們惡的恢複測試就隻需要将整個邏輯備份進行全庫恢複，看是否能夠成功的重建一個完整的資料庫。至于恢複的資料是否和備份時刻一緻，就隻能依靠我們自己來人工判斷比較。此外我們可能還希望當某一個資料庫對象，比如某個表出現問題之後能夠盡快的恢複該表資料到備份時刻。那麼我們就可以針對單個指定表進行抽樣恢複測試。

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

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

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

由于我們有兩種邏輯備份格式，每種格式的恢複方法并不一樣，是以這裡将對兩種格式的邏輯備份的恢複都進行示例。

1、如果是INSERT語句的邏輯備份

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

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

mysql -uusername -p < backup.sql

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

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

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

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

當然上面所說的步驟都是在預設每一步都正常的前提下進行的，如果發現某一步有問題。假若在b步驟出現異常，無法繼續進行下去，我們首先需要根據出現的錯誤來排查是否是我們恢複指令有錯？是否我們的環境有問題等？等等。如果我們确認是備份檔案的問題，那麼說明我們的這個備份是無效的，說明測試失敗了。如果我們恢複過程很正常，但是在校驗的時候發現缺少資料庫對象，或者某些對象中的資料不正确，或者根本沒有資料。同樣說明我們的備份級無法滿足預期，備份失敗。當然，如果我們是在實際工作的恢複過程中遇到類似情況的時候，如果還有更早的備份集，我們必須退一步使用更早的備份集做相同的恢複操作。雖然更早的備份集中的資料可能會有些失真，但是至少可以部分恢複，而不至于丢失所有資料。

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

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語句備份的恢複完全一樣了，這裡就不再累述。

5.3 實體備份與恢複測試

前面一節我們了解了如何使用MySQL的邏輯備份，并做了一個簡單的邏輯備份恢複示例，在這一節我們再一起了解一些MySQL的實體備份。

5.3.1 什麼樣的備份是資料庫實體課備份

在了解MySQL的實體備份之前，我們需要先了解一下，什麼是資料庫實體備份？既然是實體備份，那麼肯定是和資料庫的實體對象相對應的。就如同邏輯備份根據由我們根據業務邏輯所設計的資料庫邏輯對象所做的備份一樣，資料庫的實體備份就是對資料庫的實體對象所做的備份。

資料庫的實體對象主要由資料庫的實體資料檔案、日志檔案以及配置檔案等組成。在MySQL資料庫中，除了MySQL 系統共有的一些日志檔案和系統表的資料檔案之外，每一種存儲引擎自己還會有不太一樣的實體對象，在之前第一篇的“MySQL 實體檔案組成”中我們已經有了一個基本的介紹，在下面我們将詳細列出幾種常用的存儲引擎各自所對應的實體對象（實體檔案），以便在後面大家能夠清楚的知道各種存儲引擎在做實體備份的時候到底哪些檔案是需要備份的哪些又是不需要備份的。

5.3.2 MySQL實體備份所需檔案

MyISAM存儲引擎

MyISAM存儲引擎的所有資料都存放在MySQL配置中所設定的“datadir”目錄下。實際上不管我們使用的是MyISAM存儲引擎還是其他任何存儲引擎，每一個資料庫都會在“datadir”目錄下有一個檔案夾（包括系統資訊的資料庫mysql也是一樣）。在各個資料庫中每一個MyISAM存儲引擎表都會有三個檔案存在，分别為記錄表結構中繼資料的“.frm”檔案，存儲表資料的“.MYD”檔案，以及存儲索引資料的“.MYI”檔案。由于MyISAM屬于非事務性存儲引擎，是以他沒有自己的日志檔案。是以MyISAM存儲引擎的實體備份，除了備份MySQL系統的共有實體檔案之外，就隻需要備份上面的三種檔案即可。

Innodb存儲引擎

Innodb存儲引擎屬于事務性存儲引擎，而且存放資料的位置也可能與MyISAM存儲引擎有所不同，這主要取決于我們對Innodb的“”相關配置所決定。決定Innodb存放資料位置的配置為“innodb_data_home_dir”、“innodb_data_file_path”和“innodb_log_group_home_dir”這三個目錄位置指定參數，以及另外一個決定Innodb的表空間存儲方式的參數“innodb_file_per_table”。前面三個參數指定了資料和日志檔案的存放位置，最後一個參數決定Innodb是以共享表空間存放資料還是以獨享表空間方式存儲資料。這幾個參數的相關使用說明我們已經在第一篇的“MySQL存儲引擎介紹”中做了相應的解釋，在MySQL的官方手冊中也有較為詳細的說明，是以這裡就不再累述了。

如果我們使用了共享表空間的存儲方式，那麼Innodb需要備份備份“innodb_data_home_dir”和“innodb_data_file_path”參數所設定的所有資料檔案，“datadir”中相應資料庫目錄下的所有Innodb存儲引擎表的“.frm”檔案；

而如果我們使用了獨享表空間，那麼我們除了備份上面共享表空間方式所需要備份的所有檔案之外，我們還需要備份“datadir”中相應資料庫目錄下的所有“.idb”檔案，該檔案中存放的才是獨享表空間方式下Innodb存儲引擎表的資料。可能在這裡有人文，既然是使用獨享表空間，那我們為什麼還要備份共享表空間“才使用到”的資料檔案呢？其實這是很多人的一個共性誤區，以為使用獨享表空間的時候Innodb的所有資訊就都存放在“datadir”所設定資料庫目錄下的“.ibd”檔案中。實際上并不是這樣的，“.ibd”檔案中所存放的僅僅隻是我們的表資料而已，大家都很清楚，Innodb是事務性存儲引擎，他是需要undo和redo資訊的，而不管Innodb使用的是共享還是獨享表空間的方式來存儲資料，與事務相關的undo資訊以及其他的一些中繼資料資訊，都是存放在“innodb_data_home_dir”和“innodb_data_file_path”這兩個參數所設定的資料檔案中的。是以要想Innodb的實體備份有效，“innodb_data_home_dir”和“innodb_data_file_path”參數所設定的資料檔案不管在什麼情況下我們都必須備份。

此外，除了上面所說的資料檔案之外，Innodb還有自己存放redo資訊和相關事務資訊的日志檔案在“innodb_log_group_home_dir”參數所設定的位置。是以要想Innodb實體備份能夠有效使用，我們還比需要備份“innodb_log_group_home_dir”參數所設定的位置的所有日志檔案。

NDB Cluster存儲引擎

NDB Cluster 存儲引擎（其實也可以說是 MySQL Cluster）的實體備份需要備份的檔案主要有一下三類：

1、中繼資料（Metadata）：包含所有的資料庫以及表的定義資訊；

2、表資料（Table Records）：儲存實際資料的檔案；

3、事務日志資料（Transaction Log）：維持事務一緻性和完整性，以及恢複過程中所需要的事務資訊。

不論是通過停機冷備份，還是通過 NDB Cluster 自行提供的線上聯機備份工具，或者是第三方備份軟體來進行備份，都需要備份以上三種實體檔案才能構成一個完整有效的備份集。當然，相關的配置檔案，尤其是管理節點上面的配置資訊，同樣也需要備份。

5.3.3 各存儲引擎常用實體備份方法

由于不同存儲引擎所需要備份的實體對象（檔案）并不一樣，且每個存儲引擎對資料檔案的一緻性要求也不一樣是以各個存儲引擎在進行實體備份的時候所使用的備份方法也有差別。當然，如果我們是要做冷備份（停掉資料庫之後的備份），我們所需要做的事情都很簡單，那就是直接copy所有資料檔案和日志檔案到備份集需要存放的位置即可，不管是何種存儲引擎都可以這樣做。由于冷備份方法簡單，實作容易，是以這裡就不詳細說明了。

在我們的實際應用環境中，是很少有能夠讓我們可以停機做日常備份的情況的，我們隻能在資料庫提供服務的情況下來完成資料庫備份。這也就是我們俗稱的熱實體備份了。下面我們就針對各個存儲引擎單獨說明各自最常用的線上（熱）實體備份方法。

MyISAM存儲引擎

上面我們介紹了MyISAM存儲引擎檔案的實體檔案比較集中，而且不支援事務沒有redo和undo日志，對資料一緻性的要求也并不是特别的高，是以MyISAM存儲引擎表的實體備份也比較簡單，隻要将MyISAM的實體檔案copy出來即可。但是，雖然MyISAM存儲引擎沒有事務支援，對資料檔案的一緻性要求沒有Innodb之類的存儲引擎那麼嚴格，但是MyISAM存儲引擎的同一個表的資料檔案和索引檔案之間是有一緻性要求的。當MyISAM存儲引擎發現某個表的資料檔案和索引檔案不一緻的時候，會标記該表處于不可用狀态，并要求你進行修複動作，當然，一般情況下的修複都會比較容易。但是，即使資料庫存儲引擎本身對資料檔案的一緻性要求并不是很苛刻，我們的應用也允許資料不一緻嗎？我想答案肯定是否定的，是以我們自己必須至少保證資料庫在備份時候的資料是處于某一個時間點的，這樣就要求我們必須做到在備份MyISAM資料庫的實體檔案的時候讓MyISAM存儲引擎停止寫操作，僅僅提供讀服務，其根本實質就是給資料庫表加鎖來阻止寫操作。

MySQL自己提供了一個使用程式mysqlhotcopy，這個程式就是專門用來備份MyISAM存儲引擎的。不過如果你有除了MyISAM之外的其他非事務性存儲引擎，也可以通過合适的參數設定，或者微調該備份腳本，也都能通過mysqlhotcopy程式來完成相應的備份任務，基本用法如下：

mysqlhotcopy db_name[./table_regex/] [new_db_name | directory]

從上面的基本使用方法我們可以看到，mysqlhotcopy出了可以備份整個資料庫，指定的某個表，還可以通過正規表達式來比對某些表名來針對性的備份某些表。備份結果就是指定資料庫的檔案夾下包括所有指定的表的相應實體檔案。

mysqlhotcopy是一個用perl編寫的使用程式，其主要實作原理實際上就是通過先LOCK住表，然後執行FLUSH TABLES動作，該正常關閉的表正常關閉，将該fsync的資料都fsync，然後通過執行OS級别的複制（cp等）指令，将需要備份的表或者資料庫的所有實體檔案都複制到指定的備份集位置。

此外，我們也可以通過登入資料庫中手工加鎖，然後再通過作業系統的指令來複制相關檔案執行熱實體備份，且在完成檔案copy之前，不能退出加鎖的session（因為退出會自動解鎖），如下：

root@localhost : test 08:36:35> FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected (0.00 sec)

不退出mysql，在新的終端下做如下備份：

mysql@sky:/data/mysql/mydata$ cp -R test /tmp/backup/test

mysql@sky:/data/mysql/mydata$ ls -l /tmp/backup/

total 4

drwxr-xr-x 2 mysql mysql 4096 2008-10-19 21:57 test

mysql@sky:/data/mysql/mydata$ ls -l /tmp/backup/test

total 39268

-rw-r----- 1 mysql mysql 8658 2008-10-19 21:57 hotcopy_his.frm

-rw-r----- 1 mysql mysql 36 2008-10-19 21:57 hotcopy_his.MYD

-rw-r----- 1 mysql mysql 1024 2008-10-19 21:57 hotcopy_his.MYI

-rw-r----- 1 mysql mysql 8586 2008-10-19 21:57 memo_test.frm

... ...

-rw-rw---- 1 mysql mysql 8554 2008-10-19 22:01 test_csv.frm

-rw-rw---- 1 mysql mysql 0 2008-10-19 22:01 test_csv.MYD

-rw-rw---- 1 mysql mysql 1024 2008-10-19 22:01 test_csv.MYI

-rw-r----- 1 mysql mysql 8638 2008-10-19 21:57 test_myisam.frm

-rw-r----- 1 mysql mysql 20999600 2008-10-19 21:57 test_myisam.MYD

-rw-r----- 1 mysql mysql 10792960 2008-10-19 21:57 test_myisam.MYI

-rw-r----- 1 mysql mysql 8638 2008-10-19 21:57 test_outfile.frm

-rw-r----- 1 mysql mysql 2400 2008-10-19 21:57 test_outfile.MYD

-rw-r----- 1 mysql mysql 1024 2008-10-19 21:57 test_outfile.MYI

... ...

然後再在之前的執行鎖定指令的session中解鎖

root@localhost : test 10:00:57> unlock tables;

Query OK, 0 rows affected (0.00 sec)

這樣就完成了一次實體備份，而且大家也從檔案清單中看到了，備份中還有CSV存儲引擎的表。

Innodb存儲引擎

Innodb存儲引擎由于是事務性存儲引擎，有redo日志和相關的undo資訊，而且對資料的一緻性和完整性的要求也比MyISAM要嚴格很多，是以Innodb的線上（熱）實體備份要比MyISAM複雜很多，一般很難簡單的通過幾個手工指令來完成，大都是通過專門的Innodb線上實體備份軟體來完成。

Innodb存儲引擎的開發者（Innobase公司）開發了一款名為ibbackup的商業備份軟體，專門實作Innodb存儲引擎資料的線上實體備份功能。該軟體可以在 MySQL 線上運作的狀态下，對資料庫中使用 Innodb 存儲引擎的表進行備份，不過僅限于使用 Innodb 存儲引擎的表。

由于這款軟體并不是開源免費的産品，我個人也很少使用，主要也是下載下傳的試用版試用而已，是以這裡就不詳細介紹了，各位讀者朋友可以通過 Innobase 公司官方網站擷取詳細的使用手冊進行試用

NDB Cluster存儲引擎

NDB Cluster存儲引擎也是一款事務性存儲引擎，和Innodb一樣也有redo日志。NDB Cluter存儲引擎自己提供了備份功能，可以通過相關的指令實作。當然，停機冷備的方法也是有效的。

線上聯機備份步驟如下：

1、連接配接上管理伺服器；

2、在管理節點上面執行 “START BACKUP” 指令；

3、在管理節點上發出備份指令之後，管理節點會通知所有資料節點開始進行備份，并回報通知結果。

4、管理節點在通知發出備份指令之前會生成一個備份号來唯一定位這次備份所産生的備份集。當各資料節點收到備份指令之後，就會開始進行備份操作。

5、當所有資料節點都完成備份之後，管理節點才會回報“備份完成”的資訊給用戶端。

由于 NDB Cluster 的備份，備份指令是從管理節點發起，且并不會等待備份完成就會傳回，是以也沒辦法直接通過 “Ctrl + c” 或者其他方式來中斷備份程序，是以 NDB Cluster 提供了相應的指令來中斷目前正在進行的備份操作，如下：

1、登入管理節點

2、執行 “ABORT BACKUP backup_id”，指令中的 backup_id 即之前發起備份指令的時候所産生的備份号。

3、管理結帶你上會用消息“放棄訓示的備份backup_id”确認放棄請求，注意，則時候其實并沒有收到資料節點對請求的實際回應。

4、然後管理節點才會将中斷備份的指令發送到所有資料節點上面，然後當各個資料節點都中斷備份并删除了目前産生的備份檔案之後，才會傳回“備份backup_id因＊＊＊而放棄”。至此，中斷備份操作完成。

通過 NDB Cluster 存儲引擎自己的備份指令來進行備份之後，會将前面所提到的三種檔案存放在參與備份的節點上面，且被存放在三個不同的檔案中，類似如下：

BACKUP-backup_id.node_id.ctl，内容包含相關的控制資訊和中繼資料的控制檔案。每個節點均會将相同的表定義（對于 Cluster 中的所有表）儲存在自己的該檔案中。

BACKUP-backup_id-n.node_id.data，資料備份檔案，被分成多個不同的片段來儲存，在備份過程中，不同的節點将儲存不同的備份資料所産生的片段，每個節點儲存的檔案都會有資訊指明資料所屬表的部分，且在備份片段檔案最後還包含了最後的校驗資訊，以確定備份能夠正确恢複。

BACKUP-backup_id.node_id.log，事務日志備份檔案中僅包含已送出事務的相關資訊，且僅儲存已在備份中儲存的表上的事務，各個階段所儲存的日志資訊也不一樣，因為僅僅針對各節點所包含的資料記錄相關的日志資訊。

上面的備份檔案命名規則中，backup_id 是指備份号，不同的備份集會針對有一個不同的備份号，node_id 則是指明該備份檔案屬于哪個資料節點，而在資料檔案的備份檔案中的 n 則是指明片段号。

5.3.4 各存儲引擎常用實體備份恢複方法

和之前邏輯備份一樣，光有備份是沒有意義的，還需要能夠将備份有效的恢複才行。實體備份和邏輯備份相比最大的優勢就是恢複速度快，因為主要是實體檔案的拷貝，将備份檔案拷貝到需要恢複的位置，然後進行簡單的才做即可。

MyISAM 存儲引擎

MyISAM 存儲引擎由于其特性，實體備份的恢複也比較簡單。

如果是通過停機冷備份或者是在運作狀态通過鎖定寫入操作後的備份集來恢複，僅僅隻需要将該備份集直接通過作業系統的拷貝指令将相應的資料檔案複制到對應位置來覆寫現有檔案即可。

如果是通過 mysqlhotcopy 軟體來進行的線上熱備份，而且相關的備份資訊也記錄進入了資料庫中相應的表，其恢複操作可能會需要結合備份表資訊來進行恢複。

Innodb 存儲引擎

對于冷備份，Innodb 存儲引擎進行恢複所需要的操作和其他存儲引擎沒有什麼差别，同樣是備份集檔案（包括資料檔案和日志檔案）複制到相應的目錄即可。但是對于通過其他備份軟體所進行的備份，就需要根據備份軟體本身的要求來進行了。比如通過 ibbackup 來進行的備份，同樣也需要通過他來進行恢複才可以，具體的恢複方法請通過該軟體的使用手冊來進行，這裡就不詳細介紹了。

NDB Cluster 存儲引擎

對于停機冷備，恢複方法和其他存儲引擎也沒有太多差別，隻不過有一點需要特别注意的就是恢複的時候必須要将備份集中檔案恢複到對應的資料節點之少，否則無法正确完成恢複過程。

而通過 NDB Cluster 所提供的備份指令來生成的備份集，需要使用專用的備份恢複軟體 ndb_restore 來進行。ndb_restore 軟體将從備份集中讀取出備份相關的控制資訊，而且 ndb_restore 軟體必須在單獨的資料節點上面分别進行。是以當初備份進行過程中有多少資料節點，現在就需要運作多少次 ndb_restore。而且，首次通過 ndb_restore 來進行恢複的話，還必須恢複中繼資料，也就是會重建所有的資料庫和表。

5.5 備份政策的設計思路

備份是否完整，能否滿足要求，關鍵還是需要看所設計的備份政策是否合理，以及備份操作是否确實按照所設計的備份政策進行了。

針對于不同的用途，所需要的備份類型是不一樣的，是以需要的備份政策有各有不同。如為了應對本章最開始所描述的線上應用的資料丢失的問題，我們的備份就需要快速恢複，而且最好是僅僅需要增量恢複就能找回所需資料。對于這類需求，最好是有線上的，且部分延遲恢複的備用資料庫。因為這樣可以在最短時間内找回所需要的資料。甚至在某些硬體裝置出現故障的時候，将備用庫直接開發對外提供服務都可以。當然，在資源缺乏的情況下，可能難以找到足夠的備用硬體裝置來承擔這個備份責任的時候，我們也可以通過實體備份來解決，畢竟實體備份的恢複速度要比邏輯備份的快很多。

而對于那些非資料丢失的應用場景，大多數時候恢複時間的要求并不是太高，隻要可以恢複出一個完整可用的資料庫就可以了。是以不論是實體備份還是邏輯備份，影響都不大。

從我個人經驗來看，可以根據不同的需求不同的級别通過如下的幾個思路來設計出合理的備份政策：

1、對于較為核心的線上應用系統，比需要有線上備用主機通過 MySQL 的複制進行相應的備份，複制線程可以一直開啟，恢複線程可以每天恢複一次，盡量讓備機的資料延後主機在一定的時間段之内。這個延後的時間多長合适主要是根據實際需求決定，一般來說延後一天是一個比較正常的做法。

2、對于重要級别稍微低一些的應用，恢複時間要求不是太高的話，為了節約硬體成本，不必要使用線上的備份主機來單獨運作備用 MySQL，而是通過每一定的時間周期内進行一次實體全備份，同時每小時（或者其他合适的時間段）内将産生的二進制日志進行備份。這樣雖然沒有第一種備份方法恢複快，但是資料的丢失會比較少。恢複所需要的時間由全備周期長短所決定。

3、而對于恢複基本沒有太多時間要求，但是不希望太多資料丢失的應用場景，則可以通過每一定時間周期内進行一次邏輯全備份，同時也備份相應的二進制日志。使用邏輯備份而不使用實體備份的原因是因為邏輯備份實作簡單，可以完全線上聯機完成，備份過程不會影響應用提供服務。

4、對于一些搭建臨時資料庫的備份應用場景，則僅僅隻需要通過一個邏輯全備份即可滿足需求，都不需要用二進制日志來進行恢複，因為這樣的需求對資料并沒有太苛刻的要求。

上面的四種備份政策都還比較較粗糙，甚至不能算是一個備份政策。目的隻是希望能給大家一個指定備份政策的思路。各位讀者朋友可以根據這個思路根據實際的應用場景，指定出各種不同的備份政策。

5．6 小結

總的來說，MySQL 的備份與恢複都不是太複雜，方法也比較單一。姑且不說邏輯備份，對于實體備份來說，确實是還不夠完善。缺少一個開源的比較好的線上熱實體備份軟體，一直是 MySQL 一個比較大的遺憾，也是所有 MySQL 使用者比較郁悶的事情。

當然，沒有開源的備份軟體使用，非開源的商業軟體也還是有的，如比較著名的 Zmanda 備份恢複軟體，功能就比較全面，使用也不太複雜，在商業的 MySQL 備份恢複軟體市場上有較高的占有率。而且，Zmanda 同時還提供社群版本的免費下載下傳使用。

不過，稍微讓人有所安慰的是 MySQL 在實際應用場景中大多是有一台或者多台 Slave 機器來作為熱備的。在需要進行備份的時候通過 Slave 來進行備份也不是太難，而且通過暫時停止 Slave 上面的 SQL 線程，即可讓 Slave 機器停止所有資料寫入操作，然後就可以進行線上進行備份操作了。是以即使買不起商用軟體或者不太想買關系也不是太大。