Shell script: MySQL full standby and Binlog incremental backup

Scripting features: MySQL library backup script, full standby and Binlog log backup

The script can be fully prepared according to the specified parameters, and the remaining time to back up the Binlog log

---------------

#!/bin/bash# description:trian server backup databases# revision on  2016-02-18# by colin######################################  function Description: This script applies to MySQL daily backup and upload # #   Instructions for use:# ./mysql_backup.sh /mysql/hlj/qqhr/#+  a parameter after the script indicates the storage path of the backup file on the log storage server ################# ################### #PATH =/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/binrun_log= '/var/log/cron_ Scripts_run.log ' [ ! -f ${run_log} ] && touch ${run_log}echogoodlog   () {    echo -e  "\033[32m ' date +%f"   "%T": "%N '  $*\033[0m"  >> ${RUN_LOG}}EchoBadLog  () {    echo -e  "\033[31m" date  +%f " "%T ":"%N '  $*\033[0m " >> ${RUN_LOG}}EchoGoodLog " Now, script:   ' basename $0 '  run. Cd_dir= "${1? usage: $ (basename $0)  /mysql/hlj/qqhr/} "runlog_max_num=100000runlog_max_delnum=5000sEnd_max_time=6send_while_sleep_time=30back_save_max_day=90############################## [0-6],0 said Sunday, 1-6 for Monday to Saturday #+ backup_full_day=6 said to be fully prepared on the 6th day of the week #backup_full_day=6############################ #CleanRunLog   () {    cleanlogfile=${1? " usage:  $FUNCNAME  log_file_name "}    temp_wc= ' CAT&NBSP;${CLEANLOGFILE}&NBSP;|WC  -l '     [  ' ${TEMP_WC} '  -gt  ' ${runlog_max_num} '  ] &&  {        sed -i  "1,${runlog_max_delnum}d"  ${ cleanlogfile} && echogoodlog  "Clean up the ${cleanlogfile} ..."      }    EchoGoodLog  "script:  ' basename $0 '  run  Done. "     exit 0}################################  The following two variables are set to a fixed value,#+  can be used for the corresponding simulation debugging # # Nowtime= ' 2016-09-01 ' #WEEK_DAY = ' 6 ' nowtime=$ (date +%y-%m-%d) week_day=$ (date  +%w) ############################ #FILEBAKDIR = '/data/store/dbback ' mysql_data_dir= '/data/mysql ' BIN_lOG_NAME= ' Mysql-bin ' dbuser= ' database user ' dbpasswd= ' database password ' filetype= ' mysql_bak ' station= ' echo $ (hostname)  |awk -f-   ' {print $3} ' |tr [a-z] [a-z] ' ################################ define the  ftp server#ftpserver= ' IP address ' ftpuser= ' user name ' ftppasswd= ' password ' sshport= ' Port ' ################################  Full MySQL Specifies the database # $1: the name of the database that needs to be backed up, #+ $2: Refreshes the Bin-log's tag y, and does not pass the parameter, the default value N. # usage: fullbackmysqldata rht_train y#fullbackmysqldata  () {     Local data_name=$1    flush_log=${2:-n}    back_sql_name= "$ Nowtime.${data_name}.sql "    back_tar_name=" ${back_sql_name%.sql}.tar.gz "     [ -d  "$FILEBAKDIR/${data_name}"  ] | |  mkdir   $FILEBAKDIR/$DATA _name -p    cd  $FILEBAKDIR/$DATA _NAME&NBsp;&& {        if [  "${FLUSH_LOG}"  =  ' Y '  ];then            mysqldump -u$ Dbuser -p$dbpasswd  --default-character-set=utf8 --flush-logs -r ${data_name}  > ${BACK_SQL_NAME}        else             mysqldump -u$dbuser -p$dbpasswd  -- default-character-set=utf8 -r ${data_name} > ${back_sql_name}         fi        if [ $? -eq 0  ];then            EchoGoodLog  "Backup:  ${data_name} was successfully. "             tar -zcf  ${back_tar_name} --remove-files ${back_sql_name}             if [ $? -eq 0 ];then                 [ -e  "${back_tar_name}"  ]  && EchoGoodLog  "tar: ${data_name} was successfully."             else                 EchoBadLog  "Tar: ${data_name}  was failed, please check ... "             fi        else             EchoBadLog  "Backup: ${data_name} was failed, please  check ... "        fi      } }###  Incremental Backup: Backup Binlog log #backmysqlbinlog  () {     local back_dir=$1    back_binlog_name= "$NOWTIME. B${week_day}.tar.gz "    [ -d ${back_dir} ] | |  mkdir -p ${back_dir}    cd ${back_dir} && mv - t ${back_dir} $ ( find ${MYSQL_DATA_DIR} -name  "mysql-bin.*"  | grep  -E  "[0-9]{6}"  )  && {        mysql  -u$DBUSER -p$DBPASSWD -e  "use mysql;flush logs;"       #  requires the user to have MySQL library permissions  #         tar -czf  $FILEBAKDIR/${back_binlog_name} --remove-files * &&  echogoodlog  "backup: ${back_binlog_name} was successfully."     } ||  EchoBadLog  "Cut: ${back_binlog_name} was failed, please check ..."      return 0}###  cleanup more than 90 days of backup #cleanoldfile () {    local clean_ dir=$1    [ -d  "${clean_dir}"  ] && cd ${clean_dir}  && {        for FILENAME in  ' Find  . -maxdepth 1 -name  "*.tar.gz"  -ctime +${BACK_SAVE_MAX_DAY} |  awk -f/  ' {print $2} '         do             rm  $FILENAME  && EchoGoodLog  "Clear :  ${clean_dir}/$FILENAME. "         done        }}###  ftp Automated upload function #ftp_log_dir= "/tmp/ftp_err" [ -d ${ftp_log_dir} ] | |  mkdir -p ${ftp_log_dir}ftp_error_log= "${ftp_log_dir}/ftp_temp_${filetype}_err$$.log" SendLog  () {     local sendlogfile=$1    ftp -ivn ${ftpserver} 21  >${ftp_error_log} << _eof_user ${ftpuser} ${ftppasswd}passivebinlcd ${ lcd_dir}cd  ${cd_dir}put ${sendlogfile}bye_eof_    ##     #  statistics The number of error logging lines in front of the FTP run output     #    log_count= ' grep -w   "^226"  ${ftp_error_log}|wc -l '     if [ ${log_count} -eq  1 ];then        EchoGoodLog  "Send: ${sendlogfile}  to ftp_server was successfully. "         return 0    else         EchoBadLog  "Send: ${sendlogfile} more than  $x  time. "         sleep 30         Return 1    fi}reuploadlist= "/var/log/reupload_list_${filetype}_${station}.log" TEMP_ Reuploadlist= "/var/log/temp_reupload_list_${filetype}_${station}.log" [ -f ${temp_reuploadlist}  ] && rm ${temp_reuploadlist}runsendlog  () {    local  sendlogname=$1    local x=1    local i=1     until [  "$i" &NBSP;-EQ&NBSP;0&NBSP;];d o        [  " $x " -gt " ${send_max_time} " ] && {             EchoBadLog  "Send: ${sendlogname} to ftp_server was  failed, please check ... "             echo  "${lcd_dir};; ${cd_dir};; ${sendlogfile} " >> ${TEMP_REUPLOADLIST}             break        }         SendLog  "${sendlogname}"         i= ' echo $? '         x= ' expr  $x  + 1 '     done}###   Fully prepared #[  "${week_day}"  -eq  "${backup_full_day}"  ] && {     xx=1    mysql_backup_list= '/var/log/mysqlbackuplist '      mysql -u$dbuser -p$dbpasswd -e  "show databases;"  | grep  "Rht_"  > ${mysql_backup_list}    last_while_num=$ (WC  -l < ${mysql_backup_list})     while read noteline     do        [  "${xx}"  -ge  "${last_while_num}"  ] &&  fullbackmysqldata  $noteline  y | |  FullBackMysqlData  $noteline         xx=$ ( expr  $xx  + 1 )         lcd_dir= "${filebakdir}/${noteline}"       #  declare variable lcd_dir to function Runsendlog use  #         runsendlog ${back_tar_name}        cleanoldfile ${lcd_ dir}    done < ${mysql_backup_list}}###  Incremental backup #lcd_dir= "${FILEBAKDIR}"          #  declare variable lcd_dir to function Runsendlog using   #BackMysqlBinLog   "${lcd_dir}/${week_day}"  && {    [ -e  "${lcd_dir}/${back _binlog_name} "&NBSP;]&NBSP;&AMP;&AMP;&NBSP;RUNSENDLOG&NBSP;${BACK_BINLOG_NAME}&NBSP;&Amp;& cleanoldfile ${lcd_dir}    [ -d  "${LCD_DIR}/${WEEK_DAY}"  ] && rm -rf  "${lcd_dir}/${week_day}"}###  retransmission last failed file #reuploadfile () {     temp_need_do_file=$1    reuploadlist_num=$ ( wc -l  < ${TEMP_NEED_DO_FILE} )     [  "${reuploadlist_num}"  -ge 1  ] && {        while read line         do             lcd_dir= ' echo ${line}|awk -f ";;"   ' {print $1} '             cd_dir= ' echo  ${line}|awk -f ";;"   ' {print $2} '              Reuploadfilename= ' echo ${line}|awk -f ";;"   ' {print $3} ''             [ -f  ' ${lcd_dir}/${ Reuploadfilename} " ] && runSendLog ${REUPLOADFILENAME}         done < ${TEMP_NEED_DO_FILE}    }     [ -e ${temp_need_do_file} ] && rm ${temp_need_do_file}}[ -s  ${reuploadlist} ] && reuploadfile ${reuploadlist}[ -f ${temp_ reuploadlist} ] && mv ${temp_reuploadlist} ${reuploadlist}[ -f ${ Ftp_error_log} ] && rm ${ftp_error_log}cleanrunlog ${run_log}

---------------

This article from "Mountain Road 18 Bend" blog, reproduced please contact the author!

Shell script: MySQL full standby and Binlog incremental backup