為MySQL選擇合適的備份方式

為MySQL選擇合適的備份方式

MySQL資料庫的備份是極其重要的事情。如果沒有備份，遇到下列情況就會抓狂：

UPDATE or DELETE whitout where…table was DROPPed accidentally…INNODB was corrupt…entire datacenter loses power…

從資料安全的角度來說，伺服器磁碟都會做raid，MySQL本身也有主從、drbd等容災機制，但它們都無法完全取代備份。容災和高可用能幫我們有效應對物理的、硬體的、機械的故障，而對我們犯下的邏輯錯誤卻無能為力。每一種邏輯錯誤發生的機率都極低，但是當多種可能性疊加的時候，小機率事件就放大成很大的安全隱患，這時候備份的必要性就凸顯了。那麼在眾多的MySQL備份方式中，哪一種才是適合我們的呢？

常見的備份方式

MySQL本身為我們提供了mysqldump、mysqlbinlog遠程備份工具，percona也為我們提供了強大的Xtrabackup，加上開源的mydumper，還有基於主從同步的延遲備份、從庫冷備等方式，以及基於檔案系統快照的備份，其實選擇已經多到眼花繚亂。而備份本身是為了恢複，所以能夠讓我們在出現故障後迅速、準確恢複的備份方式，就是最適合我們的，當然，同時能夠省錢、省事，那就非常完美。下面就我理解的幾種備份工具進行一些比較，探討下它們各自的適用情境。

1. mysqldump & mydumper

mysqldump是最簡單的邏輯備份方式。在備份myisam表的時候，如果要得到一致的資料，就需要鎖表，簡單而粗暴。而在備份innodb表的時候，加上—master-data=1 —single-transaction 選項，在事務開始時刻，記錄下binlog pos點，然後利用mvcc來擷取一致的資料，由於是一個長事務，在寫入和更新量很大的資料庫上，將產生非常多的undo，顯著影響效能，所以要慎用。

• 優點：簡單，可針對單表備份，在全量匯出表結構的時候尤其有用。
• 缺點：簡單粗暴，單線程，備份慢而且恢複慢，跨IDC有可能遇到時區問題。
  mydumper是mysqldump的加強版。相比mysqldump：
• 內建支援壓縮，可以節省2-4倍的儲存空間。
• 支援並行備份和恢複，因此速度比mysqldump快很多，但是由於是邏輯備份，仍不是很快。

2. 基於檔案系統的快照

基於檔案系統的快照，是物理備份的一種。在備份前需要進行一些複雜的設定，在備份開始時刻獲得快照並記錄下binlog pos點，然後採用類似copy-on-write的方式，把快照進行轉儲。轉儲快照本身會消耗一定的IO資源，而且在寫入壓力較大的執行個體上，儲存被更改資料區塊的前印象也會消耗IO，最終表現為整體效能的下降。而且伺服器還要為copy-on-write快照預留較多的磁碟空間，這本身對資源也是一種浪費。因此這種備份方式我們使用的不多。

3. Xtrabackup

這或許是最為廣泛的備份方式。percona之所以家喻戶曉，Xtrabackup應該功不可沒。它實際上是物理備份+邏輯備份的組合。在備份innodb表的時候，它拷貝ibd檔案，並一刻不停的監視redo log的變化，append到自己的交易記錄檔。在拷貝ibd檔案過程中，ibd檔案本身可能被寫”花”，這都不是問題，因為在拷貝完成後的第一個prepare階段，Xtrabackup採用類似於innodb崩潰恢複的方法，把資料檔案恢複到與記錄檔一致的狀態，並把未提交的交易回復。如果同時需要備份myisam表以及innodb表結構等檔案，那麼就需要用flush tables with lock來獲得全域鎖，開始拷貝這些不再變化的檔案，同時獲得binlog位置，拷貝結束後釋放鎖，也停止對redo log的監視。
它的工作原理如下：

由於mysql中不可避免的含有myisam表，同時innobackup並不備份表結構等檔案，因此想要完整的備份mysql執行個體，就少不了要執行flush tables with read lock，而這個語句會被任何查詢(包括select)阻塞，在阻塞過程中，它又反過來阻塞任何查詢(包括select)。如果碰巧備份執行個體上有長查詢先於flush tables with read lock執行，資料庫就會hang住。而當flush tables with read lock獲得全域鎖後，雖然查詢可以執行，但是仍會阻塞更新，所以，我們希望flush tables with read lock從發起到結束，持續的時間越短越好。
為瞭解決這個問題，有兩種比較有效方法：

1. 盡量不用myisam表。2. Xtrabackup增加了—rsync選項，通過兩次rsync來減少持有全域鎖的時間。

最佳化後的備份過程如下：

• 優點：線上熱備，全備+增備+流備，支援限速，支援壓縮，支援加密。
• 缺點：需要擷取全域鎖，如果遇到長查詢，等待時間將不可控，因此要做好監控，必要時殺死長查詢或自殺；遇到超大的執行個體，備份過程較長，redo log太大會影響恢複速度，這種情況下最好採用延遲備份。

4. mysqlbinlog 5.6

上述所有的備份方式，都只能把資料庫恢複到備份的某個時間點：mysqldump和mydumper，以及snapshot是備份開始的時間點；Xtrabackup是備份結束的時間點。要想實現point in time的恢複，還必須備份binlog。同時binlog也是實現增備的寶貴資源。
幸運的是，mysql 5.6為我們提供了遠程備份binlog的選項：
mysqlbinlog --raw --read-from-remote-server --stop-never
它會偽裝成mysql從庫，從遠程擷取binlog然後進行轉儲。這對線上主庫容量不夠無法儲存較多binlog的情境非常有用。但是，它畢竟不像真正的mysql從庫執行個體，狀態監控和同步都需要單獨部署。因此個人覺得採用blackhole來備份全量的binlog是更好的選擇。筆者曾經實現過一個自動搭建blackhole從庫的工具 

相關資料下載：

------------------------------------------分割線------------------------------------------

免費在 http://linux.bkjia.com/

使用者名稱與密碼都是www.bkjia.com

具體下載目錄在 /2014年資料/11月/26日/為MySQL選擇合適的備份方式

下載方法見

------------------------------------------分割線------------------------------------------

稍加修改，就可以完美搭建出blackhole從庫。一旦同步起來，基本一勞永逸，很少出問題，主從切換的時候跟著切了就行。

提示：

• 不要小看binlog的備份。當5.6的多線程複製大規模使用後，從庫追趕主庫命令點的耗時將被極大縮短，這樣我們把每天一次的全量備份改為每3天一次、甚至每周一次的全量備份，和持續的binlog增量備份。遇到故障需要恢複資料的時候，重放3、5天的binlog也是極快的。降低備份頻率最直接的好處是，省錢、省事。
• blackhole對於備份binlog是極好的。一方面可以長久的備份binlog用於恢複資料庫，另一方面，在其上配置半同步複製，可以有效防止主庫的binlog丟失。

總結

備份方式各有千秋，而對我們來說，面對數千執行個體，選擇合適的備份工具來實現統一配置、統一規劃，構建智能調度的備份雲平台才是王道。畢竟，多種備份方式共存的營運成本是不容忽視的。
從使用經驗來看，用Xtrabackup全備資料，用blackhole增備binlog，並定期對備份資料的有效性進行驗證，是當下比較好的選擇。

Linux 每天備份MySQL資料 

Java實現MySQLDatabase Backup 

MySQL備份恢複之XtraBackup 

MySQL資料備份與恢複

MySQL邏輯備份恢複

xtraBackup備份原理剖析 

MySQL管理之資料備份及恢複 

MySQL之Xtrabackup備份恢複資料庫

本文永久更新連結地址：