mysql 增量备份脚本_MySQL自动化（全量+增量）备份脚本

一、MySQL的日常备份方案：

全备+增量备份：

1、周日凌晨三点进行全备；

2、周一到周日增量备份。

不是往常的周日全备份，周一到周六增量备份，这样如果周日数据库在完全备份前出问题，恢复完成后，会少周日一天的数据量，所以七天增量备份，周日全备可以更好的保全数据。

这是备份周期演示表：Sun3:00------Mon3:00-----------------Tue3:00----------Wed3:00----------Thu3:00----------Fri3:00----------Sat3:00----------Sun3:00

(flush)Sunfull---(flush)Sun->Monbinlog---(flush)Mon->Tue---(flush)Tue->Wed---(flush)Wed->Thu---(flush)Thu->Fri---(flush)Fri->Sat---(flush)Sunfull---(flush)Sun->Monbinlog---(flush)Mon->Tue---(flush)Tue->Wed---(flush)Wed->Thu---(flush)Thu->Fri---(flush)Fri->Sat---(flush)Sunfull

二、备份脚本：

模块化定制，可以随意移动，调节备份策略！

变量栏的帐号密码，文件路径根据自己实际环境可以进行修改，自由度比较高，模块函数全变量，适用度较高，但是可能还有不完善的地方，欢迎提出，谢谢！vim/root/mysql_bakup.sh

#!/bin/bash

#Date:/5/2

#Author:wangpengtai

#Blog:http://wangpengtai.

#AtSunday,wewillbackupthecompleteddatabasesandtheincresedbinarylogduringSaturdaytoSunday.

#Inotherweekdays,weonlybackuptheincreaingbinarylogatthatday!

################################

#thegloblevariablesforMySQL#

################################

DB_USER='root'

DB_PASSWORD='123456'

DB_PORT='3306'

BACKUPDIR='/tmp/mysqlbakup'

BACKUPDIR_OLDER='/tmp/mysqlbakup_older'

DB_PID='/data/mysql/log/mysqld.pid'

DB_SOCK='/data/mysql/log/mysql.sock'

LOG_DIR='/data/mysql/log'

BACKUP_LOG='/tmp/mysqlbakup/backup.log'

DB_BIN='/usr/local/mysql/bin'

#timevariablesforcompletedbackup

FULL_BAKDAY='Sunday'

TODAY=`date+%A`

DATE=`date+%Y%m%d`

###########################

#timevariablesforbinlog#

###########################

#liftcycleforsavingbinlog

DELETE_OLDLOG_TIME=$(date"-d14dayago"+%Y%m%d%H%M%S)

#Thestarttimepointtobackupbinlog,theusageofmysqlbinlogis--start-datetime,--stop-datetime,timeformatis%Y%m%d%H%M%S,eg:0502171054,timezonesis[start-datetime,stop-datetime)

#Thedatetostartbackupbinlogisyesterdayatthisverymoment!

START_BACKUPBINLOG_TIMEPOINT=$(date"-d1dayago"+"%Y-%m-%d%H:%M:%S")

#BINLOG_LIST=`cat/data/mysql/log/mysql-bin.index`

#注意在f中配置binlog文件位置时需要使用绝对路径，一定想成好习惯，不要给别人挖坑！！

#####################举例########################

#[mysqld]

#log_bin=/var/lib/mysql/mysql-bin

#####################举例########################

BINLOG_INDEX='/data/mysql/log/mysql-bin.index'

##############################################

#Judgethemysqlprocessisrunningornot.#

#mysqlstopreturn1,mysqlrunningreturn0.#

##############################################

functionDB_RUN(){

iftest-a$DB_PID&&test-a$DB_SOCK;then

return0

else

return1

fi

}

###################################################################################################

#Judgethebacupdirectoryisexsitnot.#

#Ifthemysqlbakupdirectorywasexsited,therewilledreturn0.#

#Ifthereisnoamysqlbakupdirectory,thefuctionwillcreatethedirectoryandreturnvalue1.#

###################################################################################################

functionBACKDIR_EXSIT(){

iftest-d$BACKUPDIR;then

#echo"$BACKUPDIRwasexist."

return0

else

echo"$BACKUPDIRisnotexist,nowcreateit."

mkdir-pv$BACKUPDIR

return1

fi

}

###################################################################################################

#Judgethebinlogisconfigedornot.#

#Ifthemysqlbakupdirectorywasexsited,therewilledreturn0.#

#Ifthereisnoamysqlbakupdirectory,thefuctionwillcreatethedirectoryandreturnvalue1.#

###################################################################################################

functionBINLOG_EXSIT(){

iftest-f$BINLOG_INDEX;then

#echo"$BACKUPDIRwasexist."

return0

fi

}

###################################################

#ThefullbackupforallDatabases#

#Thisfunctionisusetobackupthealldatabases.#

###################################################

functionFULL_BAKUP(){

echo"At`date+%D\%T`:StartingfullbackuptheMySQLDB..."

#rm-fr$BACKUPDIR/db_fullbak_$DATE.sql#fortest!!

$DB_BIN/mysqldump--lock-all-tables--flush-logs--master-data=2-u$DB_USER-p$DB_PASSWORD-P$DB_PORT-A|gzip>$BACKUPDIR/db_fullbak_$DATE.sql.gz

FULL_HEALTH=`echo$?`

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

echo"At`date+%D\%T`:MySQLDBincresedbackupsuccessfully"

else

echo"MySQLDBfullbackupfailed!"

fi

}

#python

#>>>withopen('/data/mysql/log/mysql-bin.index','r')asobj:

#...foriinobj:

#...printos.path.basename(i)

#...

#mysql-bin.000006

#mysql-bin.000007

#mysql-bin.000008

#mysql-bin.000009

functionINCREASE_BAKUP(){

echo"At`date+%D\%T`:StartingincreasedbackuptheMySQLDB..."

$DB_BIN/mysqladmin-u$DB_USER-p$DB_PASSWORD-P$DB_PORTflush-logs

$DB_BIN/mysql-u$DB_USER-p$DB_PASSWORD-P$DB_PORT-e"purgemasterlogsbefore${DELETE_OLDLOG_TIME}"

foriin`cat$BINLOG_INDEX|awk-F'/''{print$NF}'`

do

$DB_BIN/mysqlbinlog-u$DB_USER-p$DB_PASSWORD-P$DB_PORT--start-datetime="$START_BACKUPBINLOG_TIMEPOINT"$LOG_DIR/$i|gzip>>$BACKUPDIR/db_daily_$DATE.sql.gz

done

#$DB_BIN/mysqlbinlog-u$DB_USER-p$DB_PASSWORD-P$DB_PORT--start-datetime="$START_BACKUPBINLOG_TIME"$LOG_DIR/mysql-bin.[0-9]*|gzip>>$BACKUPDIR/db_daily_$DATE.sql.gz

INCREASE_HEALTH=`echo$?`

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

echo"At`date+%D\%T`:MySQLDBincresedbackupsuccessfully"

else

echo"MySQLDBincresedbackupfailed!"

fi

}

functionOLDER_BACKDIR_EXSIT(){

iftest-d$BACKUPDIR_OLDER;then

#echo"$BACKUPDIR_OLDERwasexist."

return0

else

echo"$BACKUPDIR_OLDERisnotexist,nowcreateit."

mkdir-pv$BACKUPDIR_OLDER

#return1

fi

}

functionBAKUP_CLEANER(){

#movethebackupedfilethatcreatedtimeoutof7daystotheBACKUPDIR_OLDERdirectory

returnkey=`find$BACKUPDIR-name"*.sql.gz"-mtime+7-execls-lh{}\;`

returnkey_old=`find$BACKUPDIR_OLDER-name"*.sql.gz"-mtime+14-execls-lh{}\;`

if[[$returnkey!='']];then

echo"----------------------"

echo"Movingtheolderbackupedfileoutof7daysto$BACKUPDIR_OLDER."

echo"Themovedfilelistis:"

find$BACKUPDIR-name"*.sql.gz"-mtime+7-execmv{}$BACKUPDIR_OLDER\;

echo"-----------------------"

elif[[$returnkey_old!='']];then

#deletethebackupedfilethatcreatedtimeoutof14daysfromBACKUPDIR_OLDERdirectory.

echo"Deletetheolderbackupedfileoutof14daysfrom$BACKUPDIR_OLDER."

echo"Thedeletedfileslistis:"

find$BACKUPDIR_OLDER-name"*.sql.gz"-mtime+14-execrm-fr{}\;

fi

}

####################################

#--------------main----------------#

####################################

functionMAIN(){

DB_RUN#Judgetheprocessisrunornot,ifnotrun,thescriptwillnotbakupdb

Run_process=`echo$?`

echo$?

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

BINLOG_EXSIT

binlog_index=`echo$?`

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

echo"**********START**********"

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

echo"~~~~~~~~~~~~~~~~~~~~~~~"

if[[$TODAY==$FULL_BAKDAY]];then

echo"Startcompletedbakup..."

INCREASE_BAKUP

FULL_BAKUP#fullbackuptoallDB

BAKUP_CLEANER

else

echo"Startincreaingbakup..."

INCREASE_BAKUP

fi

echo"~~~~~~~~~~~~~~~~~~~~~~~"

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

echo"**********END**********"

else

echo"**********START**********"

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

echo"~~~~~~~~~~~~~~~~~~~~~~~"

echo"Sorry,MySQLbinlogwasnotconfiged,ffirstly!"

echo"~~~~~~~~~~~~~~~~~~~~~~~"

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

echo"**********END**********"

fi

else

echo"**********START**********"

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

echo"~~~~~~~~~~~~~~~~~~~~~~~"

echo"Sorry,MySQLwasnotrunning,thedbcouldnotbebackuped!"

echo"~~~~~~~~~~~~~~~~~~~~~~~"

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

echo"**********END**********"

fi

}

#startingruning

BACKDIR_EXSIT$BACKUP_LOG

OLDER_BACKDIR_EXSIT$BACKUP_LOG

MAIN>>$BACKUP_LOG

三、测试方法：

使用了一个测试脚本，修改日期，达到一个月的演示效果。#!/bin/bash

fordayin{1..30}

do

date-s"-06-$day12:00:00"

/bin/bash/root/bakup/mysql_backup.sh

done

四、脚本使用方法：crontab-e

03***/bin/bash/root/bakup/mysql_bakup.sh>/dev/null2>&1空格

#加个空格，不然有些机器不能执行脚本