--dump完全備份

vi /tmp/mysql_full_bak.sh

#!/bin/sh

scriptsDir='pwd'

mysqlDir='/usr/local/mysql'

user=root

userPWD=root123

dataBackupDir=/tmp/mysqlbackup

eMailFile=$dataBackupDir/email.txt

eMail=chenhaibo@myhexin.com

logFile=$dataBackupDir/mysqlbackup.log

#DATE='date -I'

DATE=`date -I`

echo "" > $eMailFile

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

cd $dataBackupDir

dumpFile=mysql_$DATE.sql

GZDumpFile=mysql_$DATE.sql.tar.gz

#bakup

$mysqlDir/bin/mysqldump -u$user -p$userPWD \

--opt --default-character-set=utf8 --extended-insert=false \

--triggers -R --hex-blob --all-databases \

--flush-logs --delete-master-logs \

-x > $dumpFile

#tar

if [[ $? == 0 ]]; then

tar czf $GZDumpFile $dumpFile >> eMailFile 2>&1

echo "BackupFileName:$GZDumpFile" >> $eMailFile

echo "DataBase Backup Success!" >> $eMailFile

rm -rf $dumpFile

#delete previous daily backup files

cd $dataBackupDir/daily

rm -f *

# Delete old backup files(mtime>2).

#$scriptsDir/rmBackup.sh

#Move Backup Files To Backup Server.

#適合Linux(MySQL服務(wù)器)到Linux(備份服務(wù)器)

#$scriptsDir/rsyncBackup.sh

#if (( !$? )); then

#echo "Move Backup Files To Backup Server Success!" >> $eMailFile

# else

# echo "Move Backup Files To Backup Server Fail!" >> $eMailFile

# fi

#else

#echo "DataBase Backup Fail!" >> $emailFile

fi

#write log

echo "--------------------------------------------------------" >> $logFile

cat $eMailFile >> $logFile

#send imail

cat $eMailFile | mail -s "MySQL Backup" $eMail

增量備份

vi /tmp/mysql_daily_bak.sh

#!/bin/sh

scriptsDir='pwd'

mysqlDir='/usr/local/mysql'

dataDir=$mysqlDir/var

user=root

userPWD=root123

dataBackupDir=/tmp/mysqlbackup

dailyBackupDir=$dataBackupDir/daily

eMailFile=$dataBackupDir/email.txt

eMail=chenhaibo@myhexin.com

logFile=$dataBackupDir/mysqlbackup.log

DATE=`date -I`

HOSTNAME=`uname -n`

echo "" > $eMailFile

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

#刷新日志

$mysqlDir/bin/mysqladmin -u$user -p$userPWD flush-logs

cd $dataDir

filelist=`cat mysql-bin.index`

iCounter=0

for file in $filelist

do

iCounter=`expr $iCounter + 1`

done

nextNum=0

iFile=0

for file in $filelist

do

binLogName=`basename $file`

nextNum=`expr $nextNum + 1`

if [[ $nextNum == $iCounter ]]; then

echo "skip lastest!" > /dev/null

else

dest=$dailyBackupDir/$binLogName

#跳過(guò)已備份的二進(jìn)制日志文件

if [[ -e $dest ]]; then

echo "Skip exist $binLogName!" > /dev/null

else

# 備份日志文件到備份目錄

cp $binLogName $dailyBackupDir

if [[ $? == 0 ]]; then

iFile=`expr $iFile + 1`

echo "$binLogName Backup Success!" >> $eMailFile

fi

fi

fi

done

if [[ $iFile == 0 ]];then

echo "No Binlog Backup!" >> $eMailFile

else

echo "Backup $iFile File(s)." >> $eMailFile

echo "Backup MySQL Binlog OK!" >> $eMailFile

fi

刪除old文件

vi /tmp/rmBackup.sh

#!/bin/sh

# Name:rmBackup.sh

# PS:Delete old Backup.

# 定義備份目錄

dataBackupDir=/tmp/mysqlbackup

# 刪除mtime>2的日志備份文件

find $dataBackupDir -name "mysql_*.gz" -type f -mtime +2 -exec rm {} \; > /dev/null 2>&1

同步備份到備份服務(wù)器

vi /tmp/rsyncBackup.sh

#!/bin/sh

# Name:rsyncBackup.sh

#定義數(shù)據(jù)庫(kù)備份目錄

dataBackupDir=/tmp/mysqlbackup/

# 定義備份服務(wù)器上存放備份數(shù)據(jù)的目錄

backupServerDir=/root/mysqlbackup/

# 定義備份服務(wù)器

backupServer=172.16.107.133

# 同步備份文件到備份服務(wù)器

rsync -a --delete $dataBackupDir -e ssh $backupServer:$backupServerDir > /dev/null 2>&1

恢復(fù)服務(wù)器

全部恢復(fù)

mysqldump --user=root -p --all-databases > /backup/mysql.sql

DB恢復(fù)

/usr/local/mysql/bin/mysql -uroot -pUserPWD db_name < db_name.sql

增量恢復(fù)

1.對(duì)于任何可適用的更新日志，將它們作為 mysql 的輸入

% ls -t -r -1 HOSTNAME-bin* | xargs mysqlbinlog | mysql -uUser –pUserPWD

2.一般恢復(fù)

/usr/local/mysql/bin/mysqlbinlog mysql-bin.000052 | mysql -uUser -pUserPWD

定制任務(wù)

#crontab –e

10 4 * * 1-6 root /tmp/mysql_daily_bak.sh #禮拜一到禮拜六運(yùn)行每天備份腳本

10 4 * * 0 root /tmp/mysql_full_bak.sh #禮拜天執(zhí)行全備份的腳本

總結(jié)

以上是生活随笔為你收集整理的mysql增量备份保留策略_Mysql备份策略-完成备份+增量备份shell的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。