从机上的MySQL备份脚本

#!/bin/bash
 
 
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/baklog
POINTFILE=$BAKDIR/pointfile
DUMPFILE=InnoDBexport
DATAPOCESS=0
OSUSER=backupme                               ##
OSGROUP=admins                                ##
 
 
USER=backupme
PASS=12345
SOCK=/var/lib/mysql/prods/db222/mysql.sock
PORT=3306
 
if 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}')
fi
 
if [ ! -e $BAKDIR -o ! -d $BAKDIR ]
then
        mkdir -p $BAKDIR
fi
 
MYSQLARG="mysql -u$USER -p$PASS -S$SOCK"
MYSQLDUMPARG="mysqldump -u$USER -p$PASS -S$SOCK"
 
# only do rsync op
 
if [ $# -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
          #这里--include 和--exclude都不能加绝对路径.
                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
                        exit
fi
 
#if [ ! -e "$FULLBAKDIR" ]
#then
#       eval echo "$LOGERROR_PREFIX full backup not start,do not rsync bin log!" >> $LOGFILE
#        exit
#fi
 
echo "****************************" >> $LOGFILE
eval echo "$LOGPROCESS_PREFIX $0: start backup on `date`" >> $LOGFILE
 
# stop slaver
 
eval echo "$LOGPROCESS_PREFIX stop slave " >> $LOGFILE
stop_slave=$MYSQLARG" -e 'stop slave'"
#注意
echo "$stop_slave"|sh
 
# sleep for a while
 
sleep 10
 
# record the pointer
 
eval echo "$LOGPROCESS_PREFIX write binlog pointer" >> $LOGFILE
#注意-ss参数
pointer=$(eval $MYSQLARG" -ss -e 'show master status'"|awk -F'[.\t ]' '{print $2}')
echo $(date +%Y%m%d) ${pointer} >> $POINTFILE
 
# flush log闭当前的二进制日志文件并创建一个新文件，
#  新的二进制日志文件的名字在当前的二进制文件的编号上加1。 
eval echo "$LOGPROCESS_PREFIX switch binlog to next" >> $LOGFILE
flush_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" >> $LOGFILE
sudo rsync -av --delete-before --include=$BINPREFIX.* --exclude=* $BINDIR"/" $BAKDIR"/" >> $LOGFILE 2>&1
 
if [ $? -eq 0 ]
then
        eval echo "$LOGSUCCESS_PREFIX rsync success" >> $LOGFILE
else
        eval echo "$LOGERROR_PREFIX rsync file failed" >> $LOGFILE
fi
 
# see if the first day of this month
 
if [ $(date +%d) == "04" ]
then
# do full backup
 
eval echo "$LOGPROCESS_PREFIX do full backup" >> $LOGFILE
DATAPOCESS=1
CURRENTDIR=`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
        fi
 
fi
 
# start slave
 
eval echo "$LOGPROCESS_PREFIX restart slave " >> $LOGFILE
start_slave=$MYSQLARG" -e 'start slave'"
echo "$start_slave"|sh
 
eval echo "$LOGPROCESS_PREFIX end backup at `date`" >> $LOGFILE
 
if [ $DATAPOCESS -eq 1 ]
then
eval echo "$LOGPROCESS_PREFIX begin process backup data at `date`" >> $LOGFILE
 
# change bak dir owner ,so rm can operate normally
sudo /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 normally
sudo /bin/chown -R $OSUSER.$OSGROUP $FULLBAKDIR
 
fi
 
## END