MySQL backup script from the machine

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 \ '   ]&NBSP: "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  [$] 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 [  "$"  ==  "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 "&NBSP;] #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)  ==  " ]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 "&NBSP;&GT;&Gt;  $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 &NBSP;&AMP;&AMP;&NBSP;RM  -rf  $LASTDIR &NBSP;&NBSP;&NBsp;     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 &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;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

This article is from the "12208412" blog, please be sure to keep this source http://12218412.blog.51cto.com/12208412/1899497

MySQL backup script from the machine