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/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
                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
pointer=$(eval $MYSQLARG" -ss -e ‘show master status‘"|awk -F‘[.\t ]‘ ‘{print $2}‘)
echo $(date +%Y%m%d) ${pointer} >> $POINTFILE

# flush log
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

本文出自 “12208412” 博客，请务必保留此出处http://12218412.blog.51cto.com/12208412/1899497

从机上的MySQL备份脚本