#!/bin/bash#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 ' 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 binlog delete_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 ' ############################################## #Judge the mysql process is running or not. # #mysql stop return 1, mysql running return 0.############################################## #function db_ RUN () { if test -a $DB _pid;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&Nbsp;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 is exist " return 0 else echo "$BACKUPDIR is not exist, now Create it " mkdir -pv $BACKUPDIR return 1 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 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 + "%y-%m-%d %h:%m:%s") ', mysql db full 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 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 $BINLOG _list do $DB _ Bin/mysqlbinlog -u$db_user -p$db_password -p$db_port --start-datetime= "$START _backupbinlog _timepoint " $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 +"%y-%m-%d %h:%m:%s ") ', mysql db incresed backup successfully! " else echo "MYSQL DB INCResed backup failed! " fi}function bakup_cleaner () { if test -d $ backupdir_older;then echo "$BACKUPDIR _older is Exist "# return 0 else echo "$BACKUPDIR _older is not exist, now Create it " mkdir -pv $BACKUPDIR _older # return 1 fi find $BACKUPDIR -name "*.sql.gz" -mtime +7 -exec mv {} $ backupdir_older \;# find $BACKUPDIR _older -name "*.sql.gz" - mtime +14 -exec rm -fr {} \; }########################### ##########--------------main----------------#################################### #function main () { db_run run_process= ' echo $? ' if [[ $Run _process == 0 ]];then echo "----- ------START------------" echo $ (date +"%y-%m-%d %h:%m:%s ") echo "-----------------------" if [[ $TODAY == $FULL _bakday ]];then echo "start completed bakup ... " BACKDIR_EXSIT FULL_BAKUP #full backup to all db increase_bakup else echo "start increaing bakup  ..." BACKDIR_EXSIT INCREASE_BAKUP fi BAKUP_CLEANER echo "-------------------- ---" echo $ (date +"%y-%m-%d %h:%m:%s ") echo "-----------END------------" else echo "mysql is stopped, the db Can not be bacuped! "      FI} #starting runingMAIN >> $BACKUP _log
This article is from the "LINUX" blog, so be sure to keep this source http://wangpengtai.blog.51cto.com/3882831/1920594
MySQL Automation (full + incremental) backup script