mysql數(shù)據(jù)庫完整備份方案

概述

mysql數(shù)據(jù)庫的備份方案雪隧，即要考慮到數(shù)據(jù)的完全性这溅，也要考慮到備份所占用的空間，因此適用的備份方案是結(jié)合全量和增量進(jìn)行備份载迄。

在數(shù)據(jù)完全方便讯柔，備份時(shí)應(yīng)能將本地備份數(shù)據(jù)上傳到異地的備份服務(wù)器上，以確保數(shù)據(jù)的安全护昧。一般情況下魂迄，可以在遠(yuǎn)程服務(wù)器上開啟FTP服務(wù)，在本地生成備份文件中捏卓，通過FTP上傳到遠(yuǎn)程的備份服務(wù)器中极祸。

對于本地服務(wù)器及遠(yuǎn)程服務(wù)器都要及時(shí)刪除歷史的數(shù)據(jù)備份文件慈格，以釋放存儲(chǔ)空間怠晴。如遠(yuǎn)程備份服務(wù)器保留90天的數(shù)據(jù)，而本地僅需保留10天或更短時(shí)間的備份文件浴捆。當(dāng)然蒜田，可以根據(jù)實(shí)際情況進(jìn)行歷史數(shù)據(jù)長期的保留。

此外选泻，當(dāng)數(shù)據(jù)庫空間可用容量下降到一定下限后冲粤，要能及時(shí)告警美莫，以加大磁盤空間，以便造成應(yīng)用數(shù)據(jù)占滿磁盤空間造成系統(tǒng)停機(jī)的悲劇梯捕。

備份方案設(shè)計(jì)

(1) 周一到周六 每天晚上23：45進(jìn)行增量備份厢呵，備份完成后刷新binlog,產(chǎn)生新日志文件；
(2) 周日晚上先進(jìn)行增量備份傀顾，再進(jìn)行全量備份襟铭，
全量備份到 ${baseDir}/full，增量到 ${baseDir}/daily
(3) 備份成功后短曾，將備份文件上傳到遠(yuǎn)程FTP服務(wù)器寒砖；
(4) 將本地大于30天的歷史備份數(shù)據(jù)刪除(當(dāng)然這個(gè)可以定義成一個(gè)變量)；
(5) 檢查數(shù)據(jù)盤的剩余空間是否下降到設(shè)定的下限值嫉拐，到達(dá)下限值時(shí)向管理員發(fā)送告警郵件哩都。
(6) 程序執(zhí)行會(huì)記錄日志，日志文件位于：${baseDir}/log 目錄下婉徘。

完整備份shell程序

dbbackup.sh的完整程序如下所示漠嵌。

#!/bin/bash
##################################################################################
# [功能說明]
#==========================================================================
# 1.每天做增量數(shù)據(jù)備份,將binlog備份到${baseDir}/daily 目錄下；
# 2.在周天備份${dbname}的全量數(shù)據(jù),備份到${baseDir}/full 目錄下盖呼；
# 3.備份完成后刪除30天前的本地?cái)?shù)據(jù)献雅；
# 4.將當(dāng)次的備份文件上傳到FTP服務(wù)器中,FTP服務(wù)器地址為${ftpHost}
# 
#
# [使用說明]
#==========================================================================
# 1.打開/etc/crontab/ 添加如下定時(shí)任務(wù)，每天晚上23:45分執(zhí)行塌计；
# 45 23 * * * root /root/bin/dbbackup.sh
# (請先通過service crond status 確保crond服務(wù)已經(jīng)有啟動(dòng)挺身。)；
# 2.請根據(jù)環(huán)境設(shè)置好[參數(shù)列表]中的參數(shù)锌仅。
# 3.執(zhí)行日志記錄到${baseDir}/log/dbback.log日志文件中;
#==========================================================================
# [參數(shù)列表]
#==========================================================================
# 1. dbconf : 數(shù)據(jù)庫配置文件地址
# 2. username : 數(shù)據(jù)庫用戶名章钾；
# 3. password : 數(shù)據(jù)庫密碼；
# 4. dbname : 需備份的數(shù)據(jù)庫
# 5. baseDir : 備份數(shù)據(jù)存儲(chǔ)的基目錄热芹，全量數(shù)據(jù)存儲(chǔ)于 ${baseDir}/full,增量數(shù)據(jù)
# 存儲(chǔ)于${baseDir}/daily,日志文件位于 ${baseDir}/log.
# 6. ftpHost : FTP服務(wù)器地址
# 7. ftpUser : FTP用戶名
# 8. ftpPw : FTP密碼
# 
# 9. toMail : 接收告警的郵箱
# 10.serverName :服務(wù)器名稱贱傀，預(yù)警中會(huì)包括這個(gè)信息
# 11.diskPath : 數(shù)據(jù)盤的路徑
# 12.minDiskSize :當(dāng)磁盤剩余空間小于該值時(shí)發(fā)預(yù)警郵件,單位為G
#==========================================================================
# [注意事項(xiàng)]
# 1.請不要使用reset master 讓binlog文件名重新從***001開始否則會(huì)導(dǎo)致新增量文件名
# 與歷史文件重名，在執(zhí)行增量備份時(shí)伊脓，如果發(fā)現(xiàn)daily目錄有有重名的binlog文件府寒，將忽略之。
#
# [版本信息]
# 作 者：itstamen
# 創(chuàng)建日期：2020-07-10
# 變更日志：
##################################################################################


# ******************需要設(shè)置的變量參數(shù)**************************************
#mysql配置文件地址
dbconf=/etc/my.cnf
#mysql數(shù)據(jù)庫用戶名
username="root"
#mysql數(shù)據(jù)庫密碼
password="root"
#需要備份的數(shù)據(jù)庫
dbname="db1"
#備份文件存儲(chǔ)目錄
baseDir=/mnt/sdc/data/backup

#ftp服務(wù)器URL(如 ftp.yourhome.com或者IP)
ftpHost=xxx
#備份文件需要放置的FTP目錄报腔，如你希望將備份文件上傳到FTP的
# aaa/bbb/ 目錄下株搔，則這里寫 aaa/bbb/  
ftpDir=xxx
#ftp用戶
ftpUser=xxx
#ftp用戶密碼
ftpPw=xxx

# 數(shù)據(jù)庫的數(shù)據(jù)盤目錄，每次備份時(shí)會(huì)檢測其剩余空間是否小于${minDiskSize}GB,
# 如果小于纯蛾，則將發(fā)告警郵件到${toMail}郵箱中纤房。
dataDiskPath=/dev/vdb1
#當(dāng)剩余空間小于100G時(shí)每晚發(fā)預(yù)警郵件
minDiskSize=100
# 這個(gè)服務(wù)器的名稱，如：上海xxx客戶OA數(shù)據(jù)庫服務(wù)器001
serverName=xxx
#數(shù)據(jù)盤容量小于設(shè)定的下限值時(shí)翻诉，發(fā)送告警郵箱
toMail=xxx
#*************************************************************************

# 備份目錄
fullDir=${baseDir}/full
dailyDir=${baseDir}/daily
logDir=${baseDir}/log
logFile=${logDir}/dbback.log
ftpLogFile=${logDir}/ftp.log

# binlog的目錄及文件
binDir=$(grep -E "^[^#]" $dbconf |grep datadir |awk -F'=' '{print $2}')
binFile=${binDir}/mysql-bin.index

# 本次備份的文件列表炮姨，多文件用逗號分隔捌刮，這些文件將需要上傳到FPT服務(wù)器中
tempBakFiles = ""

## 日志輸出函數(shù)
function mylog(){
    echo [LOG]-$(date +"%Y-%m-%d %H:%M:%S") ${1} >> $logFile
}

## 拷貝binlog到daily目錄
function increBackup(){
    mylog "開始增量備份.."

    #(1)拷貝binlog前先鎖定表，以防備份期間數(shù)據(jù)的寫入
    mysql -u${username} -p${password} -e "flush tables with read lock;" 2>/dev/null

    #(2)將緩存中的數(shù)據(jù)更改寫到binlog中舒岸，并生產(chǎn)一個(gè)新的mysql-bin.00000*文件
    mysqladmin -u${username} -p${password} flush-logs 2>/dev/null
    counter=$(wc -l $binFile | awk '{print $1}')
    nextNum=0
    
    #(3)將除剛產(chǎn)生的binlog文件外的所有文件拷貝到daily備份目錄中
    for file in $(cat $binFile)
    do
        baseFile=$(basename $file)
        nextNum=$(($nextNum + 1))
        if [ $nextNum -eq $counter ]
        then
            mylog "忽略${baseFile}."
        else
            destFile=${dailyDir}/${baseFile}
            if test ! -e $destFile
            then
                cp ${binDir}/${baseFile} $destFile

                tempBakFiles=${tempBakFiles}" "$destFile
                mylog "備份${baseFile}."
            else
                mylog "${baseFile}已存在,忽略備份."
            fi
        fi
    done

    #(4)備份完成后绅作，釋放鎖
    mysql -u${username} -p${password} -e "unlock tables;" 2>/dev/null

    mylog "增量備份完成."
}

#將文件上傳到FTP
#直接上傳絕對路徑的文件會(huì)報(bào)文件找不到
uploadFile()
{
 ftp -i -n $ftpHost 21 2>${ftpLogFile} << _EOF_
 user $ftpUser $ftpPw
    binary
 cd $ftpDir
    prompt
    lcd $1
 put $2
 bye
_EOF_
    #統(tǒng)計(jì)前面FTP運(yùn)行輸出的錯(cuò)誤日志記錄行數(shù)
    _fileName=$1/$2
    logCount=$(cat ${ftpLogFile} | wc -l)
    [ ${logCount} -eq 0 ] && mylog 上傳成功:$2 || mylog 上傳失敗:$2
    [ -f ${ftpLogFile} ] && rm ${ftpLogFile}
}

# 檢查磁盤空間，如果小于下限值蛾派，則發(fā)送預(yù)警郵件
checkDiskSpace()
{
    # 分析數(shù)據(jù)盤的
    diskSize=$(df $dataDiskPath | grep / | awk '{print $4}')
    diskSizeGB=$(($diskSize/1024/1024))

   if [ $diskSizeGB -lt $minDiskSize ]
   then
      mailContent=${serverName}數(shù)據(jù)盤剩余空間為${diskSizeGB}GB,不足${minDiskSize},請及時(shí)擴(kuò)容棚蓄。
      echo "${mailContent}" | mail -s "[預(yù)警]${serverName}數(shù)據(jù)盤空間不足" ${toMail}
   fi
    
}

# 當(dāng)目錄不存在創(chuàng)建之
mkdir -p $fullDir
mkdir -p $dailyDir
mkdir -p $logDir

w=$(date +%w)
nowDate=$(date +%Y%m%d)
#是星期天，執(zhí)行全量數(shù)據(jù)備份
if [ $w -eq 0 ] 
then
    mylog "開始全量備份.."

    #定義全量備份文件名
    dumpFile=${dbname}_${nowDate}.sql
    

    #(1) 在全量備份前先執(zhí)行增量備份
    increBackup

    #(2) 全量備份（對所有數(shù)據(jù)庫備份碍脏，除了數(shù)據(jù)庫goodthing里的village表）

    
    mysqldump -u${username} -p${password} --databases ${dbname} \
    --ignore-table ${dbname}.SYS_LOGON \
    --ignore-table ${dbname}.SYS_LOGON_HIS \
    --ignore-table ${dbname}.SYS_LOG_MENU \
    --ignore-table ${dbname}.SYS_LOG_MENU_HIS \
    --single-transaction > ${fullDir}/$dumpFile 2>/dev/null

    # 備份文件壓縮
    cd $fullDir
    gzip -f $dumpFile

    tempBakFiles=${tempBakFiles}" "${dumpFile}.gz
    mylog "備份文件：${dumpFile}.gz"
    mylog "全量備份完成."
else
    increBackup
fi

# 清除30天前的備份數(shù)據(jù)
find ${fullDir} -mtime +30 |xargs rm -f
find ${dailyDir} -mtime +31 |xargs rm -f
mylog "刪除30天前的日志文件"

# 將今天備份文件上傳到FTP服務(wù)器中
bakFileArr=($tempBakFiles) 
for bakFile in ${bakFileArr[@]}
do
   echo "upload file:[$bakFile]..."
   tempFileDir=$(dirname $bakFile)
   tempFileName=$(basename $bakFile)
   uploadFile $tempFileDir $tempFileName
done 

# 檢查服務(wù)器剩余空間是否達(dá)到警界線,如果空間不足則發(fā)告警郵件
checkDiskSpace

exit 0
# END

相關(guān)配置

以上備份命令要正常運(yùn)行梭依，必須進(jìn)行FTP及MAIL客戶端的相關(guān)安裝及配置。

FTP客戶端安裝

#查看是否安裝 FTP客戶端
yum list ftp* 

#如果沒有安裝 典尾，則安裝之
yum install ftp -y

MAIL客戶端安裝及配置

通過mailx使用遠(yuǎn)程smtp進(jìn)行郵件發(fā)送役拴，首先安裝mailx

# 查看mail客戶端軟件有沒有安裝 
yum list mail*

#如果沒有安裝，則安裝之
yum install mailx

一般不建議搭建自己的郵件發(fā)送服務(wù)钾埂，直接通過qq河闰，163等的第三方郵件發(fā)送服務(wù)即可。由于阿里云褥紫，華為云等云一般會(huì)禁止通過25端口進(jìn)行郵件發(fā)送姜性，但允許通過465

vim /etc/mail.rc

在文件末尾添加：

set from=yourmail@163.com
set smtp=smtps://smtp.163.com:465
set ssl-verify=ignore
set nss-config-dir=/root/.certs
set smtp-auth-user=yourmail@163.com
set smtp-auth-password=XXXXXXXX
set smtp-auth=login

此外，還需要獲取163服務(wù)器的證書文件髓考，以便SSL通訊加密：

echo -n | openssl s_client -connect smtp.163.com:465 | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > ~/.certs/163.crt
certutil -A -n "GeoTrust Global CA" -t "C,," -d ~/.certs -i ~/.certs/163.crt
certutil -A -n "GeoTrust SSL CA" -t "C,," -d ~/.certs -i ~/.certs/163.crt
certutil -L -d /root/.certs
certutil -A -n "GeoTrust SSL CA - G3" -t "Pu,Pu,Pu" -d ~/.certs/ -i ~/.certs/163.crt

注意smtp-auth-password不是163郵箱的登錄密碼部念，而是授權(quán)密碼，參見如下獲取之：

163mailAuthPw.jpg

當(dāng)然您也可以使用qq郵箱服務(wù)器來發(fā)送氨菇，具體可參見：https://www.cnblogs.com/ithomer/p/9907935.html

小結(jié)

這個(gè)mysql備份程序儡炼，可以通過參數(shù)設(shè)置，應(yīng)用于不同的應(yīng)用環(huán)境中查蓉，靈活性較高乌询。還可以進(jìn)行如下的優(yōu)化：
(1) 將參數(shù)抽取到一個(gè)配置文件中，這樣可以不更改程序豌研，只設(shè)置配置文件的參數(shù)即可妹田；
(2) 日志對各種錯(cuò)誤的記錄還不夠全，如備份失敗鹃共，郵件發(fā)送失敗等鬼佣；
(3) 在發(fā)生錯(cuò)誤時(shí)，可以自動(dòng)將錯(cuò)誤日志郵件發(fā)送出來及汉。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末沮趣，一起剝皮案震驚了整個(gè)濱河市屯烦，隨后出現(xiàn)的幾起案子坷随，更是在濱河造成了極大的恐慌房铭，老刑警劉巖，帶你破解...
    沈念sama閱讀 210,914評論 6贊 490
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件温眉，死亡現(xiàn)場離奇詭異缸匪，居然都是意外死亡，警方通過查閱死者的電腦和手機(jī)类溢，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 89,935評論 2贊 383
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門凌蔬，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人闯冷，你說我怎么就攤上這事砂心。” “怎么了蛇耀？”我有些...
    開封第一講書人閱讀 156,531評論 0贊 345
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵辩诞，是天一觀的道長。 經(jīng)常有香客問我纺涤，道長译暂，這世上最難降的妖魔是什么？ 我笑而不...
    開封第一講書人閱讀 56,309評論 1贊 282
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任撩炊，我火速辦了婚禮外永，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘拧咳。我一直安慰自己伯顶，他們只是感情好，可當(dāng)我...
    茶點(diǎn)故事閱讀 65,381評論 5贊 384
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布骆膝。 她就那樣靜靜地躺著砾淌，像睡著了一般。 火紅的嫁衣襯著肌膚如雪谭网。 梳的紋絲不亂的頭發(fā)上汪厨，一...
    開封第一講書人閱讀 49,730評論 1贊 289
  • 城市分裂傳說
    那天，我揣著相機(jī)與錄音愉择，去河邊找鬼劫乱。 笑死，一個(gè)胖子當(dāng)著我的面吹牛锥涕，可吹牛的內(nèi)容都是我干的衷戈。 我是一名探鬼主播，決...
    沈念sama閱讀 38,882評論 3贊 404
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼层坠，長吁一口氣：“原來是場噩夢啊……” “哼殖妇！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起破花，我...
    開封第一講書人閱讀 37,643評論 0贊 266
  • 萬榮殺人案實(shí)錄
    序言：老撾萬榮一對情侶失蹤谦趣，失蹤者是張志新（化名）和其女友劉穎疲吸，沒想到半個(gè)月后，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體前鹅，經(jīng)...
    沈念sama閱讀 44,095評論 1贊 303
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡摘悴，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,448評論 2贊 325
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了舰绘。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片蹂喻。...
    茶點(diǎn)故事閱讀 38,566評論 1贊 339
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡，死狀恐怖捂寿，靈堂內(nèi)的尸體忽然破棺而出口四，到底是詐尸還是另有隱情，我是刑警寧澤秦陋，帶...
    沈念sama閱讀 34,253評論 4贊 328
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布窃祝，位于F島的核電站，受9級特大地震影響踱侣，放射性物質(zhì)發(fā)生泄漏粪小。R本人自食惡果不足惜，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,829評論 3贊 312
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一抡句、第九天 我趴在偏房一處隱蔽的房頂上張望探膊。 院中可真熱鬧，春花似錦待榔、人聲如沸逞壁。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,715評論 0贊 21
  • 一樁弒父案锐锣，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽腌闯。三九已至，卻和暖如春雕憔，著一層夾襖步出監(jiān)牢的瞬間姿骏，已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 31,945評論 1贊 264
  • 情欲美人皮
    我被黑心中介騙來泰國打工斤彼， 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留分瘦，地道東北人。 一個(gè)月前我還...
    沈念sama閱讀 46,248評論 2贊 360
  • 代替公主和親
    正文 我出身青樓琉苇，卻偏偏與公主長得像嘲玫，于是被迫代替她去往敵國和親。 傳聞我的和親對象是個(gè)殘疾皇子并扇，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,440評論 2贊 348