第5章 備份與恢復

前言


? 數據庫的備份與恢復一直都是 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 -u username -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 機器停止所有數據寫入操作，然后就可

以進行在線進行備份操作了。所以即使買不起商用軟件或者不太想買關系也不是太大。

?

摘自：《MySQL性能調優與架構設計》簡朝陽

轉載請注明出處：

作者：JesseLZJ
出處：http://jesselzj.cnblogs.com

轉載于:https://www.cnblogs.com/jesselzj/p/4714016.html

總結

以上是生活随笔為你收集整理的MySQL性能调优与架构设计——第5章 备份与恢复的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。