MYSQL自動備份策略的選擇與實踐

王滔發表於2013-09-05

目前流行幾種備份方式:

1、邏輯備份:使用mysql自帶的mysqldump工具進行備份。備份成sql檔案形式。
優點:最大好處是能夠與正在執行的mysql自動協同工作,
在執行期間可以確保備份是當時的點,它會自動將對應操作的表鎖定,不允許其他使用者修改(只能訪問)。可能會阻止修改操作。sql檔案通用方便移植。

缺點:備份的速度比較慢。如果是資料量很多的時候。就很耗時間。如果資料庫伺服器處在提供給使用者服務狀態,在這段長時間操作過程中,意味著要鎖定表(一般是讀鎖定,只能讀不能寫入資料)。那麼服務就會影響的。


備註:所謂的與mysql伺服器能夠自動協同工作,實際上是指加引數來控制mysql伺服器,比如鎖定所有表只能進行讀,不能進行寫操作。

--lock-all-tables


2、物理備份:直接拷貝mysql的資料目錄。

直接拷貝只適用於myisam型別的表。這種型別的表是與機器獨立的。但實際情況是,你設計資料庫的時候不可能全部使用myisam型別表。你也不可能:因為myisam型別表與機器獨立,方便移植,於是就選擇這種表,這並不是選擇它的理由。

缺點:你不能去操作正在執行的mysql伺服器(在拷貝的過程中有使用者通過應用程式訪問更新資料,這樣就無法備份當時的資料)可能無法移植到其他機器上去。


更多的情況是,你會根據業務特點(比如你需要支援事務機制就必須使用innodb),查詢速度和服務效能來選擇表型別的。


必須保證表不被使用中
如果伺服器在你正在拷貝一個表時(某個使用者在進行update、insert操作)改變它,拷貝的資料就失去了備份的意義(沒法準確還原到當時的點)。


如果資料庫表在檔案系統備份過程中被修改,進入備份的表檔案主語不一致的狀態,而對以後的恢復表將失去意義。

保證你的拷貝完整性的最好方法是:關閉伺服器,拷貝檔案,然後重啟伺服器。
或者是,要鎖定對應的表(對前端使用者造成訪問問題)。

 

解釋直接拷貝檔案,為什麼不具備可移植性?


mysqldump 產生可移植到其他機器、甚至具有不同硬體結構的機器上的文字檔案。直接拷貝檔案不能夠移植到其他機器上,除非要拷貝的表使用MyISAM 儲存格式。ISAM 表只能在具有相同硬體結構的機器之間進行拷貝。例如,將檔案從S PARC 的Solaris 機器拷貝到Intel 的Solaris 機器(或者相反)是行不通的。由MySQL3.23 引進的MyISAM 表儲存格式可以解決這個問題,因為該格式與機器獨立。因此,如果以下兩個條件都滿足的話,直接拷貝檔案可以移植到具有不同硬體結構的機器上:即另一臺機器上也必須執行MySQL3.23 以上的版本,並且檔案必須表示成MyISAM 表,而不是ISAM 表。

 


3、雙機熱備份。

mysql資料庫沒有增量備份的機制。當資料量太大的時候備份是一個很大的問題。還好mysql資料庫提供了一種主從備份的機制(也就是雙機熱備)
優點:適合資料量大的時候。現在明白了。大的網際網路公司對於mysql資料備份,都是採用熱機備份。搭建多臺資料庫伺服器,進行主從複製。

主從複製經常遇到的問題就是,如何保證資料不堵塞,不延遲。這個問題還是可以容忍的,有一些方案可以改善。畢竟有得有失的。這已經是很省心省力的方式了。

 

================================================

 

我目前應該使用什麼樣的備份策略的權衡:

物理備份,恢復快,當然最好是儲存在一個機器上。我現在是用物理備份還是邏輯備份為好呢?
考慮到以後會遷移平臺。為了保證通用性。恢復速度1分鐘左右的差距我是可以容忍的(sql檔案備份方式,恢復速度沒有物理配備快,物理備份直接拷貝覆蓋掉原來檔案即可)。所以我為了跨平臺,我更加願意使用邏輯備份。儲存sql檔案形式。

雙熱機備份方式,目前硬體沒有多個。技術人員有限,需要人力去維護,比較麻煩。所以排除在外。

 

 

方案:
1、總體策略:寫個定時執行任務。定時在晚上或凌晨自動備份(考慮資料庫伺服器在執行中不能停機)
程式碼中做成備份成功後,把以前的刪掉。避免很多資料佔據磁碟。

2、考慮到初期資料量這麼小。使用mysqldump進行備份吧。設定在凌晨幾點(4-6點這個時候基本上沒什麼人訪問)的時候自動備份。


3、使用邏輯備份方式:恢復速度1分鐘左右的差距我是可以容忍的。所以我為了跨平臺,我更加願意使用邏輯備份。儲存sql檔案形式。

4、每天都進行備份。由於是在凌晨的時候mysqldump去鎖定,訪問資料庫伺服器。對伺服器幾乎沒什麼影響。所以每天都可以備份。每天都一個sql檔案。那麼將會很多檔案。
所以,每次備份成功後。刪除以前的檔案。保留最近一個星期的備份sql檔案。


備份工具的路徑:/usr/bin/mysqldump
備份資料儲存路徑:/data/backdata/

 

5、備份指令碼的編寫

思路:

5.1 在shell指令碼中呼叫mysqldump生成備份檔案(這個工具可以生成sql檔案到磁碟上去)

5.2 為了方便以後查詢。每次備份的記錄記錄成日誌形式。幾點進行了備份操作,生成了什麼檔名稱。這樣可以方便以後查閱哪天是否沒有成功備份

刪除的檔案作為日誌資訊也記錄下來。

5.3  讓linux下的crontab程式呼叫指令碼執行。

命令:crontab -e

開啟的檔案中加入程式碼:0 05 * * * 指令碼的路徑/mysqlback.sh

 

 

 

mysqlback.sh的內容:

 

# /bin/bash
DB_NAME="****"
DB_USER="****"
DB_PASS="****"
BIN_DIR="/usr/bin"
BACK_DIR="/data/backdata"
DATE="mysql-`date +'%Y%m%d-%H:%M:%S'`"
LogFile="$BACK_DIR"/dbbakup.log #日誌記錄儲存的目錄
BackNewFile=$DATE.sql

$BIN_DIR/mysqldump --opt --force -u$DB_USER  -p$DB_PASS $DB_NAME > $BACK_DIR/$DATE.sql


echo -----------------------"$(date +"%y-%m-%d %H:%M:%S")"----------------------- >> $LogFile



echo  createFile:"$BackNewFile" >> $LogFile


#find "/data/backdata/" -cmin +1 -type f -name "*.sql" -print > deleted.txt
#-ctime表示建立時間,這裡表示刪除建立時間為多少天之前的檔案,也就是結果只保留多少天的資料
find "/data/backdata/" -ctime +7 -type f -name "*.sql" -print > deleted.txt

echo -e "delete files:\n" >> $LogFile

#迴圈刪除匹配到的檔案
cat deleted.txt | while read LINE
do
    rm -rf $LINE
    echo $LINE>> $LogFile
done


echo "---------------------------------------------------------------" >> $LogFile

 

 

相關文章