MySQL 備份與還原詳解

標籤：

相關閱讀：

MySQL備份和恢複具體實施 http://www.linuxidc.com/Linux/2012-12/76257.htm

MySQL備份與恢複的三種方法總結 http://www.linuxidc.com/Linux/2012-12/75428.htm

MySQL備份還原(視圖、預存程序) http://www.linuxidc.com/Linux/2012-01/52665.htm

 

一、MySQL備份類型

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

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

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

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

 

2.物理備份與邏輯備份 （從對象來分）

• 物理備份：複製資料檔案；

• 邏輯備份：將資料匯出至文字檔中；

 

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

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

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

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

 

4.邏輯備份的優點：

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

• 物理備份的還原速度非常快。但是物理備份的最小力度只能做到表

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

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

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

 

5.邏輯備份的缺點：

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

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

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

 

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

 

二、MySQL備份都備份什嗎？

一般備份以下幾個部分：

1.資料檔案

2.記錄檔（比如交易記錄，二進位日誌）

3.預存程序，儲存函數，觸發器

4.設定檔（十分重要，各個設定檔都要備份）

5.用於實現Database Backup的指令碼，資料庫自身清理的Croutab等……

 

三、MySQL備份工具

常用的備份工具：

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商業工具的替代工具。

 

四、MySQL備份策略

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

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

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

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

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

前提：

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

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

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

步驟：

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

 

12	mysql> FLUSH TABLES WITH READ LOCK;mysql> FLUSH LOGS;

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

1	mysql -uroot -p -e ‘SHOW MASTER STATUS\G‘ > /path/to/master.info

(3).建立快照卷

1	lvcreate -L # -s -p r -n LV_NAME /path/to/source_lv

(4).釋放鎖

1	mysql> UNLOCK TABLES;

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

12

mountcp

(6).刪除快照卷；

 

或者用現成的整合命令工具mylvmbackup(可以整合上面的命令集合，自動完成備份)

mylvmbackup --user=dba --password=xxx  --mycnf=/etc/my.cnf   --vgname=testvg  --lvname=testlv --backuptype=tar --lvsize=100M --backupdir=/var/lib/backup

 

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）相結合的方法！

 

 

資料會完整備份到/root/mybackup/xtrabackup/中目錄名字為當前的日期，xtrabackup會備份所有的InnoDB表，MyISAM表只是複製表結構檔案、以及MyISAM、MERGE、CSV和ARCHIVE表的相關檔案，同時還會備份觸發器和資料庫配置資訊相關的檔案。除了儲存資料外還產生了一些xtrabackup需要的資料檔案，詳解如下：

• xtrabackup_checkpoints 備份類型（如完全或增量）、備份狀態（如是否已經為prepared狀態）和LSN(記錄序號)範圍資訊；每個InnoDB頁(通常為16k大小)都會包含一個記錄序號，即LSN。LSN是整個資料庫系統的系統版本號碼，每個頁面相關的LSN能夠表明此頁面最近是如何發生改變的。

• xtrabackup_binlog_info mysql伺服器當前正在使用的二進位記錄檔及至備份這一刻為止二進位日誌事件的位置。

• xtrabackup_binary 備份中用到的xtrabackup的可執行檔。

• backup-my.cnf 備份命令用到的配置選項資訊。

• xtrabackup_logfile 記錄標準輸出資訊xtrabackup_logfile

 

17.總結

如果在增量備份後資料庫出現故障，我們需要通過完整備份+到現在為止的所有增量備份+最後一次增量備份到現在的二進位日誌來恢複。

18.附註

單獨備份：

1	innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=test/root/mybackup

備份並打包壓縮：

1	innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=test--stream=tar /root/mybackup/ | gzip > /root/mybackup/testdb.tar.gz

帶時間戳記：

1	innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=test--stream=tar /root/mybackup/ | gzip > /root/mybackup/`date +%F`_testdb.tar.gz

備份資訊輸出重新導向到檔案：

1	innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=test--stream=tar /root/mybackup/ 2>/root/mybackup/test.log | gzip 1>/root/mybackup/test.tar.gz

說明：

1234567

--stream #指定流的格式，目前只支援tar--database=test #單獨對test資料庫做備份 ，若是不添加此參數那就那就是對全庫做2>/root/mybackup/test.log #輸出資訊寫入日誌中1>/root/mybackup/test.tar.gz #打包壓縮儲存到該檔案中解壓 tar -izxvf 要加-i參數，官方解釋 innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream.在備份完成後，資料尚且不能用於恢複操作，因為備份的資料中可能會包含尚未提交的事務或已經提交但尚未同步至資料檔案中的事務。此時資料檔案仍處理不一致狀態。“準備”的主要作用正是通過復原未提交的事務及同步已經提交的事務至資料檔案也使得資料檔案處於一致性狀態。

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

 

 

轉自：http://www.linuxidc.com/Linux/2013-07/87990.htm

 

MySQL 備份與還原詳解