mysql物理innobackupex备份脚本和自动备份脚本

目录

备份命令

恢复命令

自动备份脚本

---

innobackupex是一款MySQL备份工具，备份速度快(通过直接copy物理文件)，而且支持压缩、流式传输、加密等功能

新安装的数据库自带innobackupex，如果找不到命令，需安装percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm

若遇到问题，请查看MySQL备份工具 Xtrabackup安装_J-贾的博客-优快云博客

备份命令

innobackupex --defaults-file=/export/servers/data/my3306/my.cnf --user=备份用户名 --password=备份密码 --slave-info 备份名称（all_bak）

恢复命令

Prepare准备，通过回滚未提交的事务及同步已经提交的事务至数据文件数据文件处于一致性状态

innobackupex --defaults-file=/export/servers/data/my3306/my.cnf --user=root --apply-log /export/servers/data/mybackup/备份路径

还原数据，原理即拷贝数据文件至data目录：

innobackupex --defaults-file=/export/servers/data/my3306/my.cnf --user=root --copy-back /export/servers/data/mybackup/备份路径

自动备份脚本

#!/bin/bash
if [ $# = 0 ];
then
    echo "Please enter mysql port"
    exit
fi

##传入需要备份实例的端口号   *************
mysql_port=$1
INNOBACKUPEX=/usr/bin/innobackupex
INNOBACKUPEXFULL=/usr/bin/xtrabackup
MYCNF=/export/servers/data/my${mysql_port}/my.cnf   #（mysql配置文件路径）
MYSOCK=/export/servers/data/my${mysql_port}/run/mysqld.sock  #
USER=备份用户名
PASSWORD=备份密码

MYSQL_BASE=`grep basedir  $MYCNF | awk -F'=' '{print $2}' | sed 's/\/$//' `
BINLOG_CMD="$MYSQL_BASE/bin/mysqlbinlog"
MYSQL_CMD="$MYSQL_BASE/bin/mysql"

#备份盘尽量和数据盘分开
TARBACKUPDIR='/export/backupmysql/backup_'$mysql_port'/data' # 全库备份的目录
LOGS_DIR='/export/backupmysql/backup_'$mysql_port'/logs' # 日志目录
TMPFILE="/tmp/innobackupex-runner.$$.tmp"
BINLOGBACKDIR='/export/backupmysql/backup_'$mysql_port'/binlog' #binlog备份目录

BACKUP_DATE=$(date +%y%m%d_%H_%M)

LOGFILE=$LOGS_DIR/'mysql_xtrabackup_'$mysql_port'_'$BACKUP_DATE'.log'

for i in $TARBACKUPDIR $LOGS_DIR;
do
  if [ ! -d $i ] ;then
     mkdir -p $i
  fi
done

#############################clean data #######################

find $TARBACKUPDIR -name "*tar.gz*"  -mtime +7  -exec rm -rf {} \;

#find  $BINLOGBACKDIR  -name "$BINLOG_NAME*"  -mtime +14| xargs rm -f {}

############################# check dev free space #######################
check_dev_usage(){
#检查磁盘使用率
DEV_USAGE=`df -h $TARBACKUPDIR | grep -E "[0-9]%" | awk '{if ($4~/%/) print $4; else print $5}' |sed 's/%//g'`

if [ ${DEV_USAGE} -ge 80 ]
then
   echo "dele"
   find $TARBACKUPDIR -name "*tar.gz*"  -mtime +4  -exec rm -rf {} \;
   #find  $BINLOGBACKDIR  -name "$BINLOG_NAME*"  -mtime +12 -exec rm -rf {} \;
   #Need super privileges
   #$MYSQL_CMD -u$USER -p$PASSWORD --socket=$MYSOCK -e "PURGE BINARY LOGS BEFORE \"$EXPIRE_DATE\";"
   DEV_USAGE=`df -h $TARBACKUPDIR | grep -E "[0-9]%" | awk '{if ($4~/%/) print $4; else print $5}' |sed s/%//g`
   if [ ${DEV_USAGE} -ge 80 ]
   then
      echo "The capacity of  disk is too small,it cannot to be backup"
      exit 11
   fi
fi
}

############################# backup database #######################
backup_database(){
echo "[`date +"%Y-%m-%d %H:%M:%S"`] start innobackupex" >> $LOGFILE

$INNOBACKUPEXFULL --defaults-file=$MYCNF --user=$USER  --password="$PASSWORD"  --backup  --lock-ddl-per-table --slave-info   --socket=$MYSOCK --target-dir=$TARBACKUPDIR/$BACKUP_DATE > $TMPFILE 2>&1
if tail -1 $TMPFILE | grep 'completed OK!' ;then
        echo "[`date +"%Y-%m-%d %H:%M:%S"`] end innobackupex successfull" >> $LOGFILE
else

        echo "[`date +"%Y-%m-%d %H:%M:%S"`] end innobackupex fail" >> $LOGFILE

        exit
fi
}

##############################tar backup  files ###########################
tar_backupfile(){
cd $TARBACKUPDIR
THISBACKUP=$BACKUP_DATE
TARNAME=$TARBACKUPDIR/'mysql_xtrabackup_'$mysql_port'_'$BACKUP_DATE'.tar.gz'

#echo THISBACKUP==$THISBACKUP
#echo TARNAME==$TARNAME

rm -f $TMPFILE

echo "[`date +"%Y-%m-%d %H:%M:%S"`] start tar $TARDIR " >> $LOGFILE
tar -czf $TARNAME $THISBACKUP
if [ $? == 0 ];then
        echo "[`date +"%Y-%m-%d %H:%M:%S"`] end tar $TARDIR successfull" >> $LOGFILE
else
        echo "[`date +"%Y-%m-%d %H:%M:%S"`] end tar $TARDIR  fail" >> $LOGFILE
        exit
fi

rm -fr $THISBACKUP
}
##############################backup binlog ###########################

main(){
   check_dev_usage
   backup_database
   tar_backupfile
#   backup_binlog

}
main