MySQL備份與還原系列一：備份類型

標籤：Database Backup   sql語句   physical   伺服器   資料表   

一、mysql備份類型

1、按照mysql伺服器狀態

• cold    離線備份，讀、寫操作均中止

• warm    僅可執行讀操作

• hot     讀、寫操作不受影響

2、按照資料一致性

• consistent

• inconsistent

3、按照備份資料格式

• logical     備份sql語句，在恢複的時候執行備份的sql語句實現資料庫資料的重現

• physical    檔案系統層面直接拷貝資料檔案，但真正備份的時候自然不是cp這麼簡單

4、資料存放區方式

• full            完全備份

• increamenta    增量備份   

• differential   差異備份

差異備份的定義：備份自上一次完全備份之後有變化的資料。

增量備份的定義：備份自上一次備份（包括完全備份，增量備份，差異備份）之後有變化的資料。

二、mysql備份工具

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/40/C4/wKiom1PPXZvCj5iJAAIb5TLCNys024.jpg" title="9.png" alt="wKiom1PPXZvCj5iJAAIb5TLCNys024.jpg" />

我主要按照邏輯備份還是物理備份分成兩類進行說明：

1、邏輯備份

• mysqldump 

  mysqldump是採用SQL層級的備份機制，將資料表導成SQL指令檔，是最常用的邏輯備份方法。

  ALL        WARM    支援所有引擎，MyISAM引擎是邏輯溫備，可手動加鎖

  INNODB    HOT     邏輯熱備，僅用於INODB引擎

• SELECT INTO OUTFILE

  進行SQL層級的表備份，邏輯

  
  

2、物理備份

• ibbackup

  INNODBHOT對於INNODB引擎，熱備，物理備份

  ALLWARM    其他引擎，溫備

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

• xtrabackup

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

• snapshot(LVM,ZFS)

  ALLALMOST     物理 HOT

  熱備、支援所有基於本地磁碟的儲存引擎、快速備份、低開銷、容易保持完整性、快速恢複等。

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

• mysqlhotcopy

  僅MYISAMMOSTLY COLD(LVM,snapshot)

  mysqlhotcopy是一個perl程式，是lock tables、flush tables 和cp或scp來快速備份資料庫。它是備份資料庫或單個表的最快的途徑，但它只能運行在資料庫檔案（包括資料表檔案、資料檔案、索引檔案）所在的機器上。

  物理備份工具，但只支援MyISAM引擎，基本上屬於冷備的範疇，物理備份，速度比較快。

  mysqldump可以備份各種類型的資料表，但是mysqlhotcopy只適合備份MyISAM和ISAM的資料表。所以使用mysqlhotcopy之前，你必須確認你的資料表是不是有其他的儲存引擎(storage engines)的。

• tar，cp冷備

  使用直接拷貝資料庫檔案的方式進行打包備份，需要注意的是執行步驟：鎖表、備份、解表。恢複也很簡單，直接拷貝到之前的資料庫檔案的存放目錄即可。注意：對於Innodb引擎的表來說，還需要備份記錄檔，即ib_logfile*檔案。因為當Innodb表損壞時，就可以依靠這些記錄檔來恢複。cp 冷備份，支援所有引擎，複製命令，只能實現冷備，物理備份。使用歸檔工具，cp命令，對其進行備份的，備份速度快，還原速度幾乎最快，但是靈活度很低，可以跨系統，但是跨平台能力很差。

• 使用mysql主從複製

  mysql的複製是指將主要資料庫的DDL和DML操作通過二進位檔案（bin-log）傳送到從伺服器上，然後在從伺服器上對這些日誌做重新執行的操作，從而使得從伺服器和主伺服器保持資料的同步。

三、備份和恢複策略

備份/恢複策略：

1. 要定期做 mysql 備份，並考慮系統可以承受的恢復。

2. 確保 mysql 開啟 log-bin，有了 binarylog，mysql 才可以在必要的時候做完整恢複，或基於時間點的恢複，或基於位置的恢複。

3. 要經常做備份恢複測試，確保備份是有效，並且是可以恢複的。

因為MySQL表儲存為檔案方式，很容易備份。要想保持備份的一致性，對相關表執行LOCK TABLES操作，然後對錶執行FLUSH TABLES。

你只需要讀鎖定；這樣當你複製資料庫目錄中的檔案時，允許其它客戶繼續查詢表。需要FLUSH TABLES語句來確保開始備份前將所有啟用的索引頁寫入硬碟。

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

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

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

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

策略三：lvs快照從物理角度實現幾乎熱備的完全備份，配合二進位記錄備份實現增量備份，速度快適合比較煩忙的資料庫

前提：

• 資料檔案要在邏輯卷上；

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

• 資料檔案和交易記錄要在同一個邏輯卷上；

策略四：xtrabackup 備份資料庫，實現完全熱備份與增量熱備份（MyISAM是溫備份，InnoDB是熱備份）

說明：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。

特點：

• 備份過程快速、可靠；

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

• 能夠基於壓縮等功能節約磁碟空間和流量；

• 自動實現備份檢驗；

• 還原速度快；

策略五：主從複製（replication）實現資料庫即時備份（叢集中常用）

參考：

http://freeloda.blog.51cto.com/2033581/1254883

本文出自 “Share your knowledge” 部落格，請務必保留此出處http://skypegnu1.blog.51cto.com/8991766/1501442

MySQL備份與還原系列一：備份類型