mysql备份恢复脚本

1	全库备份	mysqlbackup -uroot -p --protocol=TCP --port=3322 --datadir=/test/mymon/data/mysqldata/mymon/ --backup-dir=/test/mymon/rdbms/mpmymon/tmp/backup0 backup-and-apply-log
2	增量备份	mysqlbackup -uroot -p --protocol=TCP --port=3322 --datadir=/test/mymon/data/mysqldata/mymon/ --incremental --incremental-backup-dir=/test/mymon/rdbms/mpmymon/tmp/backup1 --incremental-base=dir:/test/mymon/rdbms/mpmymon/tmp/backup0 backup

9	应用增备到全备	mysqlbackup -uroot -p --protocol=TCP --port=3322 --backup-dir=/test/mymon/rdbms/mpmymon/tmp/backup0  --incremental-backup-dir=/test/mymon/rdbms/mpmymon/tmp/backup1  apply-incremental-backup
10	全库恢复	mysqlbackup  --defaults-file=/data01/myhome/mysql/my.cnf  --backup-dir=/test/mymon/rdbms/mpmymon/tmp/backup0

#backup_mysql.sh
#!/bin/sh
export PATH=$PATH:/test/mysql/base/bin

hostname=`hostname -s | tr -s 'A-Z' 'a-z'`


full_backup ()
{
 if [ -d full ] 
 then
  mv full full_bk
 fi
 mysqlbackup --login-path=root \
             --socket=/test/my${PORT}/var/mysql.sock \
             --backup-dir=/test/myback/$hostname/${PORT}/full \
             --backup-image=/test/myback/$hostname/${PORT}/full/mybackup.mbi \
             --compress backup-to-image >full_backup_last.log 2>&1
 
 cat full_backup_last.log >> full_backup.log
 FULL_OK=`tail -1 full_backup_last.log |grep "mysqlbackup completed OK"  |wc -l`
 if [ $FULL_OK -eq 1 ] 
 then
  rm -rf full_bk
  echo `date` >> full_backup.log >> full_backup.log
  echo " delete old_full_backup !" >> full_backup.log
 else
  rm -rf full
  mv full_bk full
  echo `date` >> full_backup.log >> full_backup.log
  echo " backup error delete this full_backup,please check the full backup !!!" >> full_backup.log
 fi
}

incr_backup ()
{
 if [ -d incr ] 
 then
  mv incr incr_bk
 fi
 if [ -d full ] 
 then
  mysqlbackup --login-path=root --socket=/test/my${PORT}/var/mysql.sock --incremental --incremental-backup-dir=/test/myback/$hostname/${PORT}/incr --incremental-base=dir:full backup > incr_backup_last.log 2>&1
  cat incr_backup_last.log >> incr_backup.log
  INCR_OK=`tail -1 incr_backup_last.log |grep "mysqlbackup completed OK"  |wc -l`
  if [ $INCR_OK -eq 1 ] 
  then
   rm -rf incr_bk
   echo `date` >> incr_backup.log >> incr_backup.log
   echo " delete old_incr_backup !" >> incr_backup.log
  else
   rm -rf incr
   echo `date` >> incr_backup.log
   echo " backup error delete this incr_backup,please check the incremental backup !!!" >> incr_backup.log
  fi
  else
   echo "There was no full backup found, incr_backup fail,please check!"  >> incr_backup.log
 fi
}
binlog_backup()
{
BINLOG_DIR=/test/my$PORT/log
mkdir -p binlog
mkdir -p .conf

echo `date` >> binlog_backup.log
echo "Begin backup binlog " >> binlog_backup.log

find  binlog/* -name "mysql-bin.*" |grep -vw "mysql-bin.index" |awk -F"/" '{print $NF}'> .conf/old
find  $BINLOG_DIR/* -name "mysql-bin.*" |grep -vw "mysql-bin.index" |sed '$d'|awk -F"/" '{print $NF}' > .conf/new
FILETODO=`diff .conf/old .conf/new|grep ">"|awk '{print $2}'`


for loop in `echo $FILETODO`
do
name=$loop
cat $BINLOG_DIR/$name|gzip > /test/myback/$hostname/${PORT}/binlog/$name.gz
if [ $? -eq 0 ]
then
echo "backup $name sucessful... " >> binlog_backup.log
else
echo "backup $name fail... " >> binlog_backup.log
fi
done
echo `date` >> binlog_backup.log
echo "End backup binlog " >> binlog_backup.log
}


binlog_purge()
{
date >> purge_files.log
find  /test/myback/$hostname/${PORT}/binlog/* -name "mysql-bin.*" -mtime +6 | xargs rm
}

PORT_SET=`ps -ef|grep -w mysqld|grep -vw grep|awk '{print $NF}'|awk -F= '{print $NF}'`
MYSQL=/test/mysql/base/bin/mysql
WEEK_DAY=`date +%w`

for PORT in $PORT_SET
do
mkdir -p /test/myback/$hostname/${PORT}/binlog
cd /test/myback/$hostname/${PORT}

READ_ONLY=`$MYSQL --login-path=root --socket=/test/my${PORT}/var/mysql.sock -Ns -e "select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES where VARIABLE_NAME='READ_ONLY';"`
if [ $READ_ONLY = 'OFF' ]
then
continue
fi
if [ $WEEK_DAY = 6 ] || [ ! -d full ]
then
echo "need full bk"
 full_backup
 binlog_backup
 binlog_purge
 else
echo "need incr bk"
 incr_backup
 binlog_backup
 binlog_purge
fi
done
exit 0