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

一、MySQL的日常备份方案：

全备+增量备份：

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

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

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

这是备份周期演示表：

Sun 3:00------Mon 3:00-----------------Tue 3:00----------Wed 3:00----------Thu 3:00----------Fri 3:00----------Sat 3:00----------Sun 3:00(flush)Sun full---(flush)Sun->Mon binlog---(flush)Mon->Tue---(flush)Tue->Wed---(flush)Wed->Thu---(flush)Thu->Fri---(flush)Fri->Sat---(flush)Sun full---(flush)Sun->Mon binlog---(flush)Mon->Tue---(flush)Tue->Wed---(flush)Wed->Thu---(flush)Thu->Fri---(flush)Fri->Sat---(flush)Sun full

二、备份脚本：

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

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

vim /root/mysql_bakup.sh#!/bin/bash#Date:2017/5/2#Author:wangpengtai#Blog:http://wangpengtai.blog.51cto.com#At Sunday, we will backup the completed databases and the incresed binary log during Saturday to Sunday.#In other weekdays, we only backup the increaing binary log at that day!#################################the globle variables for MySQL#################################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'#time variables for completed backupFULL_BAKDAY='Sunday'TODAY=`date +%A`DATE=`date +%Y%m%d`############################time variables for binlog#############################liftcycle for saving binlogDELETE_OLDLOG_TIME=$(date "-d 14 day ago" +%Y%m%d%H%M%S)#The start time point to backup binlog, the usage of mysqlbinlog is --start-datetime, --stop-datetime, time format is %Y%m%d%H%M%S, eg:20170502171054, time zones is  [start-datetime, stop-datetime)#The date to start backup binlog is yesterday at this very moment!START_BACKUPBINLOG_TIMEPOINT=$(date "-d 1 day ago" +"%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'###############################################Judge the mysql process is running or not.  ##mysql stop return 1, mysql running return 0.###############################################function DB_RUN(){    if test -a $DB_PID && test -a $DB_SOCK;then        return 0    else        return 1    fi}####################################################################################################Judge the bacup directory is exsit not.                                                          ##If the mysqlbakup directory was exsited, there willed return 0.                                  ## If there is no a mysqlbakup directory, the fuction will create the directory and return value 1.####################################################################################################function BACKDIR_EXSIT(){    if test -d $BACKUPDIR;then#        echo "$BACKUPDIR was exist."        return 0    else        echo "$BACKUPDIR is not exist, now create it."        mkdir -pv $BACKUPDIR        return 1    fi}####################################################################################################Judge the binlog is configed or not.                                                          ##If the mysqlbakup directory was exsited, there willed return 0.                                  ## If there is no a mysqlbakup directory, the fuction will create the directory and return value 1.####################################################################################################function BINLOG_EXSIT(){    if test -f $BINLOG_INDEX;then#        echo "$BACKUPDIR was exist."        return 0    fi}####################################################The full backup for all Databases                ##This function is use to backup the all databases.####################################################function FULL_BAKUP(){    echo "At `date +%D\ %T`: Starting full backup the MySQL DB ... "#    rm -fr $BACKUPDIR/db_fullbak_$DATE.sql  #for test !!    $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`: MySQL DB incresed backup successfully"    else        echo "MySQL DB full backup failed!"    fi}#python# >>> with open('/data/mysql/log/mysql-bin.index','r') as obj:# ...    for i in obj:# ...       print os.path.basename(i)# ...# mysql-bin.000006# mysql-bin.000007# mysql-bin.000008# mysql-bin.000009function INCREASE_BAKUP(){    echo "At `date +%D\ %T`: Starting increased backup the MySQL DB ... "    $DB_BIN/mysqladmin -u$DB_USER -p$DB_PASSWORD -P$DB_PORT flush-logs    $DB_BIN/mysql -u$DB_USER -p$DB_PASSWORD -P$DB_PORT -e "purge master logs before ${DELETE_OLDLOG_TIME}"    for i in `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`: MySQL DB incresed backup successfully"    else        echo "MySQL DB incresed backup failed!"    fi}function OLDER_BACKDIR_EXSIT(){    if test -d $BACKUPDIR_OLDER;then#        echo "$BACKUPDIR_OLDER was exist."        return 0    else        echo "$BACKUPDIR_OLDER is not exist, now create it."        mkdir -pv $BACKUPDIR_OLDER#        return 1    fi}function BAKUP_CLEANER(){    #move the backuped file that created time out of 7 days to the BACKUPDIR_OLDER directory    returnkey=`find $BACKUPDIR -name "*.sql.gz" -mtime +7 -exec ls -lh {} \;`    returnkey_old=`find $BACKUPDIR_OLDER -name "*.sql.gz" -mtime +14 -exec ls -lh {} \;`    if [[ $returnkey != '' ]];then        echo "----------------------"        echo "Moving the older backuped file out of 7 days to $BACKUPDIR_OLDER."        echo "The moved file list is:"        find $BACKUPDIR -name "*.sql.gz" -mtime +7 -exec mv {} $BACKUPDIR_OLDER \;        echo "-----------------------"    elif [[ $returnkey_old != '' ]];then        #delete the backuped file that created time out of 14 days from BACKUPDIR_OLDER directory.        echo "Delete the older backuped file out of 14 days from $BACKUPDIR_OLDER."        echo "The deleted files list is:"        find $BACKUPDIR_OLDER -name "*.sql.gz" -mtime +14 -exec rm -fr {} \;    fi}#####################################--------------main----------------#####################################function MAIN(){    DB_RUN #Judge the process is run or not, if not run, the script will not bakup db    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 "Start completed bakup ..."                INCREASE_BAKUP                FULL_BAKUP    #full backup to all DB                BAKUP_CLEANER            else                echo "Start increaing bakup ..."                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, MySQL binlog was not configed, please config the my.cnf firstly!"            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, MySQL was not running, the db could not be backuped!"        echo "~~~~~~~~~~~~~~~~~~~~~~~"        echo $(date +"%y-%m-%d %H:%M:%S %A")        echo "**********END**********"    fi}#starting runingBACKDIR_EXSIT $BACKUP_LOGOLDER_BACKDIR_EXSIT $BACKUP_LOGMAIN >> $BACKUP_LOG

三、测试方法：

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

#!/bin/bashfor day in {1..30}do    date -s "2017-06-$day 12:00:00"    /bin/bash /root/bakup/mysql_backup.shdone

四、脚本使用方法：

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