MySQL 備份與還原詳解

大綱

一、MySQL備份類型 二、MySQL備份都備份什麼？ 三、MySQL備份工具 四、MySQL備份政策 五、備份準備工作 六、備份政策具體示範

1.熱備份、溫備份、冷備份 （根據伺服器狀态）

熱備份：讀、寫不受影響；

溫備份：僅可以執行讀操作；

冷備份：離線備份；讀、寫操作均中止；

2.實體備份與邏輯備份 （從對象來分）

實體備份：複制資料檔案；

邏輯備份：将資料導出至文本檔案中；

3.完全備份、增量備份、差異備份 （從資料收集來分）

完全備份：備份全部資料；

增量備份：僅備份上次完全備份或增量備份以後變化的資料；

差異備份：僅備份上次完全備份以來變化的資料；

4.邏輯備份的優點：

在備份速度上兩種備份要取決于不同的存儲引擎

實體備份的還原速度非常快。但是實體備份的最小力度隻能做到表

邏輯備份儲存的結構通常都是純ASCII的，是以我們可以使用文本處理工具來處理

邏輯備份有非常強的相容性，而實體備份則對版本要求非常高

邏輯備份也對保持資料的安全性有保證

5.邏輯備份的缺點：

邏輯備份要對RDBMS産生額外的壓力，而裸備份無壓力

邏輯備份的結果可能要比源檔案更大。是以很多人都對備份的内容進行壓縮

邏輯備份可能會丢失浮點數的精度資訊

6.增量備份與差異備份差別

<a href="http://img1.51cto.com/attachment/201307/22/2033581_1374503571UX11.png"></a>

說明，差異備份要比增量備份占用的空間大，但恢複時比較友善！但我們一般都用增量備份！

我們備份，一般備份以下幾個部分：

1.資料檔案

2.日志檔案（比如事務日志，二進制日志）

3.存儲過程，存儲函數，觸發器

4.配置檔案（十分重要，各個配置檔案都要備份）

5.用于實作資料庫備份的腳本，資料庫自身清理的Croutab等……

如下圖，

<a href="http://img1.51cto.com/attachment/201307/22/2033581_13745035721Dy2.png"></a>

上面的所有備份工具對比，下面我們就來說一下，常用的備份工具，

1.Mysql自帶的備份工具

mysqldump 邏輯備份工具，支援所有引擎，MyISAM引擎是溫備，InnoDB引擎是熱備，備份速度中速，還原速度非常非常慢，但是在實作還原的時候，具有很大的操作餘地。具有很好的彈性。

mysqlhotcopy 實體備份工具，但隻支援MyISAM引擎，基本上屬于冷備的範疇，實體備份，速度比較快。

2.檔案系統備份工具

cp 冷備份，支援所有引擎，複制指令，隻能實作冷備，實體備份。使用歸檔工具，cp指令，對其進行備份的，備份速度快，還原速度幾乎最快，但是靈活度很低，可以跨系統，但是跨平台能力很差。

lvm 幾乎是熱備份，支援所有引擎，基于快照(LVM，ZFS)的實體備份，速度非常快，幾乎是熱備。隻影響資料幾秒鐘而已。但是建立快照的過程本身就影響到了資料庫線上的使用，是以備份速度比較快，恢複速度比較快，沒有什麼彈性空間，而且LVM的限制：不能對多個邏輯卷同一時間進行備份，是以資料檔案和事務日志等各種檔案必須放在同一個LVM上。而ZFS則非常好的可以在多邏輯卷之間備份。

3.其它工具

ibbackup 商業工具 MyISAM是溫備份，InnoDB是熱備份 ，備份和還原速度都很快，這個軟體它的每伺服器授權版本是5000美元。

xtrabackup 開源工具 MyISAM是溫備份，InnoDB是熱備份 ，是ibbackup商業工具的替代工具。

1.政策一：直接拷貝資料庫檔案（檔案系統備份工具 cp）（适合小型資料庫，是最可靠的）

當你使用直接備份方法時，必須保證表不在被使用。如果伺服器在你正在拷貝一個表時改變它，拷貝就失去意義。保證你的拷貝完整性的最好方法是關閉伺服器，拷貝檔案，然後重新開機伺服器。如果你不想關閉伺服器，要在執行表檢查的同時鎖定伺服器。如果伺服器在運作，相同的制約也适用于拷貝檔案，而且你應該使用相同的鎖定協定讓伺服器“安靜下來”。當你完成了備份時，需要重新開機伺服器(如果關閉了它)或釋放加在表上的鎖定(如果你讓伺服器運作)。要用直接拷貝檔案把一個資料庫從一台機器拷貝到另一台機器上，隻是将檔案拷貝到另一台伺服器主機的适當資料目錄下即可。要確定檔案是MyIASM格式或兩台機器有相同的硬體結構，否則你的資料庫在另一台主機上有奇怪的内容。你也應該保證在另一台機器上的伺服器在你正在安裝資料庫表時不通路它們。

2.政策二：mysqldump備份資料庫（完全備份+增加備份，速度相對較慢，适合中小型資料庫）（MyISAM是溫備份，InnoDB是熱備份）

mysqldump 是采用SQL級别的備份機制，它将資料表導成 SQL 腳本檔案，在不同的 MySQL 版本之間更新時相對比較合适，這也是最常用的備份方法。mysqldump 比直接拷貝要慢些。對于中等級别業務量的系統來說，備份政策可以這麼定：第一次完全備份，每天一次增量備份，每周再做一次完全備份，如此一直重複。而對于重要的且繁忙的系統來說，則可能需要每天一次全量備份，每小時一次增量備份，甚至更頻繁。為了不影響線上業務，實作線上備份，并且能增量備份，最好的辦法就是采用主從複制機制(replication)，在 slave 機器上做備份。

3.政策三：lvs快照從實體角度實作幾乎熱備的完全備份，配合二進制日志備份實作增量備份，速度快适合比較煩忙的資料庫

前提：

資料檔案要在邏輯卷上；

此邏輯卷所在卷組必須有足夠空間使用快照卷；

資料檔案和事務日志要在同一個邏輯卷上；

步驟：

(1).打開會話，施加讀鎖，鎖定所有表；

<code>mysql&gt; FLUSH TABLES WITH READ LOCK;</code>

<code>mysql&gt; FLUSH LOGS;</code>

(2).通過另一個終端，儲存二進制日志檔案及相關位置資訊；

<code>mysql -uroot -p -e </code><code>'SHOW MASTER STATUS\G'</code> <code>&gt; </code><code>/path/to/master</code><code>.info</code>

(3).建立快照卷

<code>lvcreate -L </code><code># -s -p r -n LV_NAME /path/to/source_lv</code>

(4).釋放鎖

<code>mysql&gt; UNLOCK TABLES;</code>

(5).挂載快照卷，備份

<code>mount</code>

<code>cp</code>

(6).删除快照卷；

(7).增量備份二進制日志；

4.政策四：xtrabackup 備份資料庫，實作完全熱備份與增量熱備份（MyISAM是溫備份，InnoDB是熱備份），由于有的資料在設計之初，資料目錄沒有存放在LVM上，是以不能用LVM作備份，則用xtrabackup代替來備份資料庫

說明：Xtrabackup是一個對InnoDB做資料備份的工具，支援線上熱備份(備份時不影響資料讀寫)，是商業備份工具InnoDB Hotbackup或ibbackup的一個很好的替代品。

Xtrabackup有兩個主要的工具：xtrabackup、innobackupex

xtrabackup 隻能備份InnoDB和XtraDB兩種資料表，而不能備份MyISAM資料表。

innobackupex 是參考了InnoDB Hotbackup的innoback腳本修改而來的.innobackupex是一個perl腳本封裝，封裝了xtrabackup。主要是為了友善的 同時備份InnoDB和MyISAM引擎的表，但在處理myisam時需要加一個讀鎖。并且加入了一些使用的選項。如slave-info可以記錄備份恢複後作為slave需要的一些資訊，根據這些資訊，可以很友善的利用備份來重做slave。

特點：

備份過程快速、可靠；

備份過程不會打斷正在執行的事務；

能夠基于壓縮等功能節約磁盤空間和流量；

自動實作備份檢驗；

還原速度快；

5.政策五：主從複制（replication）實作資料庫實時備份（叢集中常用）

6.總結

單機備份是完全備份（所有資料庫檔案）+增量備份（備份二進制日志）相結合！

叢集中備份是完全備份（所有資料庫檔案）+增量備份（備份二進制日志）+主從複制（replication）相結合的方法！

1.檢視伺服器狀态，