一、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:2017/5/2#Author:wangpengtai#Blog:http://wangpengtai.blog.51cto.com#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'#timevariablesforcompletedbackupFULL_BAKDAY='Sunday'TODAY=`date+%A`DATE=`date+%Y%m%d`############################timevariablesforbinlog#############################liftcycleforsavingbinlogDELETE_OLDLOG_TIME=$(date"-d14dayago"+%Y%m%d%H%M%S)#Thestarttimepointtobackupbinlog,theusageofmysqlbinlogis--start-datetime,--stop-datetime,timeformatis%Y%m%d%H%M%S,eg:20170502171054,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`#注意在my.cnf中配置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;thenreturn0elsereturn1fi}####################################################################################################Judgethebacupdirectoryisexsitnot.##Ifthemysqlbakupdirectorywasexsited,therewilledreturn0.##Ifthereisnoamysqlbakupdirectory,thefuctionwillcreatethedirectoryandreturnvalue1.####################################################################################################functionBACKDIR_EXSIT(){iftest-d$BACKUPDIR;then#echo"$BACKUPDIRwasexist."return0elseecho"$BACKUPDIRisnotexist,nowcreateit."mkdir-pv$BACKUPDIRreturn1fi}####################################################################################################Judgethebinlogisconfigedornot.##Ifthemysqlbakupdirectorywasexsited,therewilledreturn0.##Ifthereisnoamysqlbakupdirectory,thefuctionwillcreatethedirectoryandreturnvalue1.####################################################################################################functionBINLOG_EXSIT(){iftest-f$BINLOG_INDEX;then#echo"$BACKUPDIRwasexist."return0fi}####################################################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.gzFULL_HEALTH=`echo$?`if[[$FULL_HEALTH==0]];thenecho"At`date+%D\%T`:MySQLDBincresedbackupsuccessfully"elseecho"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.000009functionINCREASE_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.gzdone#$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.gzINCREASE_HEALTH=`echo$?`if[[$INCREASE_HEALTH==0]];thenecho"At`date+%D\%T`:MySQLDBincresedbackupsuccessfully"elseecho"MySQLDBincresedbackupfailed!"fi}functionOLDER_BACKDIR_EXSIT(){iftest-d$BACKUPDIR_OLDER;then#echo"$BACKUPDIR_OLDERwasexist."return0elseecho"$BACKUPDIR_OLDERisnotexist,nowcreateit."mkdir-pv$BACKUPDIR_OLDER#return1fi}functionBAKUP_CLEANER(){#movethebackupedfilethatcreatedtimeoutof7daystotheBACKUPDIR_OLDERdirectoryreturnkey=`find$BACKUPDIR-name"*.sql.gz"-mtime+7-execls-lh{}\;`returnkey_old=`find$BACKUPDIR_OLDER-name"*.sql.gz"-mtime+14-execls-lh{}\;`if[[$returnkey!='']];thenecho"----------------------"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,thescriptwillnotbakupdbRun_process=`echo$?`echo$?if[[$Run_process==0]];thenBINLOG_EXSITbinlog_index=`echo$?`if[[$binlog_index==0]];thenecho"**********START**********"echo$(date+"%y-%m-%d%H:%M:%S%A")echo"~~~~~~~~~~~~~~~~~~~~~~~"if[[$TODAY==$FULL_BAKDAY]];thenecho"Startcompletedbakup..."INCREASE_BAKUPFULL_BAKUP#fullbackuptoallDBBAKUP_CLEANERelseecho"Startincreaingbakup..."INCREASE_BAKUPfiecho"~~~~~~~~~~~~~~~~~~~~~~~"echo$(date+"%y-%m-%d%H:%M:%S%A")echo"**********END**********"elseecho"**********START**********"echo$(date+"%y-%m-%d%H:%M:%S%A")echo"~~~~~~~~~~~~~~~~~~~~~~~"echo"Sorry,MySQLbinlogwasnotconfiged,pleaseconfigthemy.cnffirstly!"echo"~~~~~~~~~~~~~~~~~~~~~~~"echo$(date+"%y-%m-%d%H:%M:%S%A")echo"**********END**********"fielseecho"**********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}#startingruningBACKDIR_EXSIT$BACKUP_LOGOLDER_BACKDIR_EXSIT$BACKUP_LOGMAIN>>$BACKUP_LOG

三、测试方法：

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

#!/bin/bashfordayin{1..30}dodate-s"2017-06-$day12:00:00"/bin/bash/root/bakup/mysql_backup.shdone

四、脚本使用方法：

crontab-e03***/bin/bash/root/bakup/mysql_bakup.sh>/dev/null2>&1空格#加个空格，不然有些机器不能执行脚本