從機上的MySQL備份指令碼

標籤：備份 mysql rsync

1

#!/bin/bash##  use for backup MySQL slave data#                                       written by hch#######################################################CFGFILE=/etc/my.cnf                           ##BINFILE=$(gawk -F‘[ \t]*=[ \t]*‘ ‘$1~/log[-_]bin$/{print $2}‘ $CFGFILE | grep ${0#*_})#BINDIR=${BINFILE%/*}BINPREFIX=${BINFILE##*/}DATEFORMAT="date +‘%Y-%m-%d %H:%M:%S‘"LOGERROR_PREFIX="[ERROR   \`$DATEFORMAT\` ] :"LOGPROCESS_PREFIX="[PROCESS \`$DATEFORMAT\` ] :"LOGSUCCESS_PREFIX="[SUCCESS \`$DATEFORMAT\` ] :"BAKDIR=/backup/pharmbk/db222/incebak            ##FULLBAKDIR=/backup/pharmbk/db222/fullbak        ##LOGFILE=$BAKDIR/baklogPOINTFILE=$BAKDIR/pointfileDUMPFILE=InnoDBexportDATAPOCESS=0OSUSER=backupme                               ##OSGROUP=admins                                ##USER=backupmePASS=12345SOCK=/var/lib/mysql/prods/db222/mysql.sockPORT=3306if echo "$BINFILE" | grep -q ‘/‘;then        BINDIR=${BINFILE%/*}else        BINDIR=$(mysql -u$USER -p$PASS -S$SOCK -ss -e "show variables like ‘datadir‘" | awk ‘{print $2}‘)fiif [ ! -e $BAKDIR -o ! -d $BAKDIR ]then        mkdir -p $BAKDIRfiMYSQLARG="mysql -u$USER -p$PASS -S$SOCK"MYSQLDUMPARG="mysqldump -u$USER -p$PASS -S$SOCK"# only do rsync opif [ $# -eq 1 ]then        if [ "$1" == "rsync" -a -e "$FULLBAKDIR" ]        then                eval echo "$LOGPROCESS_PREFIX do rsync on `date`" >> $LOGFILE        echo "sudo rsync -av --include=$BINPREFIX.* --exclude=* $BINDIR/ $BAKDIR/" >> $LOGFILE                sudo rsync -av --include=$BINPREFIX.* --exclude=* $BINDIR"/" $BAKDIR"/" >> $LOGFILE 2>&1                exit        else                        eval echo "$LOGERROR_PREFIX full backup not done,do it first!" >> $LOGFILE                        exit        fi                        eval echo "$LOGERROR_PREFIX unrecognize args ,plz check again!" >> $LOGFILE                        exitfi#if [ ! -e "$FULLBAKDIR" ]#then#       eval echo "$LOGERROR_PREFIX full backup not start,do not rsync bin log!" >> $LOGFILE#        exit#fiecho "****************************" >> $LOGFILEeval echo "$LOGPROCESS_PREFIX $0: start backup on `date`" >> $LOGFILE# stop slavereval echo "$LOGPROCESS_PREFIX stop slave " >> $LOGFILEstop_slave=$MYSQLARG" -e ‘stop slave‘"echo "$stop_slave"|sh# sleep for a whilesleep 10# record the pointereval echo "$LOGPROCESS_PREFIX write binlog pointer" >> $LOGFILEpointer=$(eval $MYSQLARG" -ss -e ‘show master status‘"|awk -F‘[.\t ]‘ ‘{print $2}‘)echo $(date +%Y%m%d) ${pointer} >> $POINTFILE# flush logeval echo "$LOGPROCESS_PREFIX switch binlog to next" >> $LOGFILEflush_logs=$MYSQLARG" -e ‘flush logs‘"echo "$flush_logs"|sh# rsync binlog to bakdir (remove binlong file in BAKDIR which not exist in BINDIR)eval echo "$LOGPROCESS_PREFIX rsync binlog to $BAKDIR" >> $LOGFILEsudo rsync -av --delete-before --include=$BINPREFIX.* --exclude=* $BINDIR"/" $BAKDIR"/" >> $LOGFILE 2>&1if [ $? -eq 0 ]then        eval echo "$LOGSUCCESS_PREFIX rsync success" >> $LOGFILEelse        eval echo "$LOGERROR_PREFIX rsync file failed" >> $LOGFILEfi# see if the first day of this monthif [ $(date +%d) == "04" ]then# do full backupeval echo "$LOGPROCESS_PREFIX do full backup" >> $LOGFILEDATAPOCESS=1CURRENTDIR=`date +%Y%m`        if [ ! -e $FULLBAKDIR -o ! -d $FULLBAKDIR ]        then                 mkdir -p $FULLBAKDIR        fi        if [ ! -e $FULLBAKDIR/$CURRENTDIR -o ! -d $FULLBAKDIR/$CURRENTDIR ]        then                mkdir -p $FULLBAKDIR/$CURRENTDIR        fi        db_list=$MYSQLARG" -e ‘show schemas‘"        for db in `echo "$db_list"|sh|sed ‘1d;/information_schema/d;/performance_schema/d;‘`        do                eval echo "$LOGPROCESS_PREFIX Begin backup $db to $FULLBAKDIR/$CURRENTDIR" >> $LOGFILE                sudo mysqlhotcopy -u $USER -p $PASS -S $SOCK $db $FULLBAKDIR/$CURRENTDIR >> $LOGFILE 2>&1                if [ $? -eq 0 ]                then                          eval echo "$LOGSUCCESS_PREFIX Backup $db finish" >> $LOGFILE                else                          eval echo "$LOGERROR_PREFIX can not backup $db" >> $LOGFILE                fi         done# remove files belong to innodb        SQL="select CONCAT(TABLE_SCHEMA,‘.‘,TABLE_NAME) FROM INFORMATION_SCHEMA.                TABLES WHERE ENGINE=‘InnoDB‘"#$MYSQLARG -ss -e "$SQL" #| while read L        exec 3< <($MYSQLARG -ss -e "$SQL")        while read L <&3        do                TABLE=${L#*.}                DB=${L%.*}# do remove op                eval echo "$LOGPROCESS_PREFIX remove innodb raw file for $TABLE" >> $LOGFILE                \rm $FULLBAKDIR/$CURRENTDIR/$DB/"$TABLE."*# dump this table                eval echo "$LOGPROCESS_PREFIX export table $TABLE" >> $LOGFILE                $MYSQLDUMPARG --add-drop-table --single-transaction --opt --quick -B $DB --tables "$TABLE" >> $FULLBAKDIR/$CURRENTDIR/$DUMPFILE_$DB.sql        done# keep one full backup,so if last full backup data exist,remove it from backup directory after new full backup finish        LASTDIR=`date +%Y%m -d ‘1 month ago‘`        if [ -d $FULLBAKDIR/$LASTDIR ]        then                eval echo "$LOGPROCESS_PREFIX remove $LASTDIR backup data from disk" >> $LOGFILE                cd $FULLBAKDIR && rm -rf $LASTDIR        else                eval echo "$LOGPROCESS_PREFIX no last backup data exist" >> $LOGFILE        fifi# start slaveeval echo "$LOGPROCESS_PREFIX restart slave " >> $LOGFILEstart_slave=$MYSQLARG" -e ‘start slave‘"echo "$start_slave"|sheval echo "$LOGPROCESS_PREFIX end backup at `date`" >> $LOGFILEif [ $DATAPOCESS -eq 1 ]theneval echo "$LOGPROCESS_PREFIX begin process backup data at `date`" >> $LOGFILE# change bak dir owner ,so rm can operate normallysudo /bin/chown -R $OSUSER.$OSGROUP $FULLBAKDIR# zip backup data        eval echo "$LOGPROCESS_PREFIX zip data in $FULLBAKDIR/$CURRENTDIR" >> $LOGFILE        cd $FULLBAKDIR/$CURRENTDIR &&         for db in *        do                sudo tar zvcf $db.tar.gz $db >> $LOGFILE 2>&1                rm -rf $db        done        if [ $? -eq 0 ]        then                eval echo "$LOGSUCCESS_PREFIX zip done successfully" >> $LOGFILE        else                eval echo "$LOGERROR_PREFIX error happen in zip data" >> $LOGFILE                exit 1        fi# change bak dir owner ,so rm can operate normallysudo /bin/chown -R $OSUSER.$OSGROUP $FULLBAKDIRfi## END

本文出自 “12208412” 部落格，請務必保留此出處http://12218412.blog.51cto.com/12208412/1899497

從機上的MySQL備份指令碼