mysql 异地备份脚本_【实用脚本】使用xtrabackup实现不落地异地备份

脚本说明：使用xtrabackup将数据库推送到其他服务器存放，数据库本地不会落盘，节省磁盘空间

#!/bin/bash

USER=dbbackup

PWD=dbbackup123

HOST=192.168.38.53

MYCNF=/etc/my.cnf

PORT=3306

TO_HOST=10.3.0.226

TO_DIR=/data/mysql_bak/

TAG="mysqlfull"

DATE=$(date +%F)

BASEDIR=/data/dbbak3306

BAKFILE=${TAG}_${DATE}_${PORT}.xbstream

LOGFILE=${TAG}_${DATE}.log

##check_backup

ssh $TO_HOST "test -e $TO_DIR/$BAKFILE && echo 'backupfile $BAKFILE exists, check if already had been backuped' && exit 1 ||echo 'backupfile $BAKFILE not exists , begin to backup....'" >>$BASEDIR/$LOGFILE

## backup+compress+scp

echo "-- Begin mysql xtrabackup backup at $(date +%F\ %T) --" >>$BASEDIR/$LOGFILE

/usr/bin/xtrabackup --defaults-file=$MYCNF --backup --user=$USER --password=$PWD --host=$HOST --port=$PORT --no-timestamp --stream=xbstream 2>>"$BASEDIR/$LOGFILE"|lz4 -B4|ssh $TO_HOST "cat - >$TO_DIR/$BAKFILE"

if [ $? -eq 0 ]

then

邮件通知代码

else

echo "backupfile compress and scp to $TO_HOST failed" >> $BASEDIR/$LOGFILE

邮件通知代码

exit 1

fi

scp $BASEDIR/$LOGFILE $TO_HOST:$TO_DIR >> $BASEDIR/$LOGFILE

echo "-- End mysql xtrabackup backup at $(date +%F\ %T) --" >> $BASEDIR/$LOGFILE