xtrabackup备份mysql

1. 安装qpress ( 解压工具，恢复数据库时使用）
下载
tar xvf qpress-11-linux-x64.tar
chmod +x qpress
mv qpress /usr/bin

2. 安装必要rpm包
yum install perl-DBD-MySQL
yum install perl-Digest-MD5
下载与操作系统对应的libev包并安装
%28%29%2864bit%29&submit=Search+...&system=&arch=
例如 Redhat6 执行下列命令，安装libev
rpm -ivh libev-4.03-3.el6.x86_64.rpm


3. 下载兼容MySQL和OS版本的xtrabackup并安装

例如：
rpm -ivh percona-xtrabackup-24-2.4.7-1.elX.x86_64.rpm
ln -s /usr/bin/innobackupex /usr/local/xtrabackup/bin/innobackupex


4. 挂载NFS文件系统
mkdir /home/bkmysql
mount -t nfs -o rw,soft,intr 10.128.13.1:/MySQL_Backup/ay-dj-db01 /home/bkmysql    --> ay-dj-db01为mysql的主机名

5. 添加NFS自动挂载
-- vi /etc/fstab
10.128.13.1:/MySQL_Backup/ay-dj-db01 /home/bkmysql nfs rw,soft,intr 0 0


5. 在NFS上创建备份及日志目录
mkdir -p /home/bkmysql/full
mkdir -p /home/bkmysql/incr
mkdir -p /home/bkmysql/log


6. 修改并上传备份脚本bkmysql.sh
修改bkmysql.sh中的变量，数据库密码，端口
上传bkmysql.sh到/home/bkmysql/

7. 设置crontab创建定制备份任务
crontab -e
0 0 * * * /home/bkmysql/bkmysql.sh >> /home/bkmysql/bkmysql.log 2>&1


backup脚本：
#!/bin/sh
#
#backup MYSQL db base-on xtrabackup 2.3.3

# MYSQL environment variable
export MYSQL_BASE=/home/mysql
export MYSQL_HOME=$MYSQL_BASE/product/5.7/mysql-1
export DATADIR=/data/mysql/data
export TMPDIR=/tmp
export PATH=$MYSQL_HOME/bin:$PATH

# backup cmd
INNODB_DIR=/usr/local/xtrabackup/bin
INNODBBAK=$INNODB_DIR/innobackupex
PARAM="--parallel=4 --compress --compress-threads=4"

# MYSQL db info
MYSQL_INFO="--host=localhost  --user=root --password=xxx  --port=3306"  
MYSQL_CNF=/etc/my.cnf

# time variable
DAT=`date +%Y%m%d%H%M`

# backup dir
BAK_DIR=/home/bkmysql
FBAK_DIR=$BAK_DIR/full
IBAK_DIR=$BAK_DIR/incr

# backup log
LOG_DIR=$BAK_DIR/log
FLOG=$LOG_DIR/$DAT.f.log
ILOG=$LOG_DIR/$DAT.i.log

# records last backup seconds
LAST_BAK=$FBAK_DIR/last_fbak_time

# full backup interval 259200(3 * 24 * 3600) seconds
FBAK_INTERVAL=258200

# save number of full backup
KEEP_FBAK_NUM=1

# define error function for quit when error
function error()
{
    echo "$1" 1>&2
    exit 1
}

if [ ! -e $INNODBBAK ]; then
    error "Backup cmd: $INNODBBAK don't find."
else
    if [ ! -x $INNODBBAK ]; then
        error "Backup cmd: $INNODBBAK don't executable."
    fi
fi

if [ ! -d $FBAK_DIR ]; then
    echo "Full backup dir:$FBAK_DIR don't exist."
    echo "Create full backup dir:$FBAK_DIR."
    echo
    mkdir -p $FBAK_DIR
fi

if [ ! -d $IBAK_DIR ]; then
    echo "Incr backup dir:$IBAK_DIR don't exist."
    echo "Create incr backup dir:$IBAK_DIR."
    echo
    mkdir -p $IBAK_DIR
fi

if [ ! -d $LOG_DIR ]; then
    echo "Backup log dir:$LOG_DIR don't exist."
    echo "Create log dir:$LOG_DIR."
    echo
    mkdir -p $LOG_DIR
fi

MYSQL_STATUS=`mysqladmin $MYSQL_INFO ping`
if [ "$MYSQL_STATUS" != "mysqld is alive" ];then
    error "MySQL is not running."
fi


echo "-----------------------------------------"
echo "MySQL backup scripts: $0"
echo

# first backup ,create time file
if [ ! -e $LAST_BAK ]; then
    echo
    echo
    echo "*****************************************"
    echo "This is first backup. Must full backup."
    echo "0" > $LAST_BAK
    echo "Don't delete or move this file! Thank you!" >> $LAST_BAK
    echo "Time:`date +%F' '%T' '%A`" >> $LAST_BAK
fi

# calculate the time interval of the last full backup
BAK_TIME=`date +%s`
LAST_BAK_TIME=`head -1 $LAST_BAK`
BAK_INTERVAL=`expr $BAK_TIME - $LAST_BAK_TIME `

# interval >=3 day full backup
# interval < 3 day incr backup
if [ $BAK_INTERVAL -ge  $FBAK_INTERVAL ]; then

     # full backup
     echo "************Begin Full Backup************"
     echo
     echo "Begin time: `date +%F' '%T`"
     $INNODBBAK --defaults-file=$MYSQL_CNF $MYSQL_INFO $PARAM $FBAK_DIR > $FLOG 2>&1

     # record full backup status
     if [ ! -z "`tail -1 $FLOG | grep 'completed OK!'`" ]; then
        echo
        echo "Full Backup Successed."
        echo "`date +%s`" > $LAST_BAK
        echo "Don't delete or move this file! Thank you!" >> $LAST_BAK
        echo "Time: `date +%F' '%T'  '%A`" >> $LAST_BAK

        # delete log befor 1 days
        find $LOG_DIR -type f -name "*.log" -mtime +1  | xargs rm -f
        echo
        echo "End time: `date +%F' '%T`"
        echo   
        echo "See detail for backup log: $FLOG"
        echo
        echo "*************End Full Backup*************"
    else
        echo
        echo "Time:`date +%F' '%T' '%A`"
        echo
        echo "See detail for backup log: $FLOG"
        echo
        echo "************Full Backup Failed************"
    fi

else

    # incr backup
    echo "************Begin Incr Backup************"
    echo
    echo "Begin time: `date +%F' '%T`"

    LAST_FBAK=`find $FBAK_DIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`
    INCR_DIR=$IBAK_DIR/$LAST_FBAK
    mkdir -p $INCR_DIR

    # if exists newest incr backup
    LAST_BAK_DIR=`find $INCR_DIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n"  | sort -nr | head -1`
    if [ ! $LAST_BAK_DIR ] ; then

        # incr backup base on last full backup if not exists
        INCR_BASEDIR=$FBAK_DIR/$LAST_FBAK
        echo
        echo "This incr backup base on Full Backup: $INCR_BASEDIR"
    else

        # incr backup base on last incr backup if exists
        INCR_BASEDIR=$INCR_DIR/$LAST_BAK_DIR
        echo
        echo "This incr backup base on Incr Backup: $INCR_BASEDIR"
    fi

    # incr backup
    $INNODBBAK --defaults-file=$MYSQL_CNF $MYSQL_INFO $PARAM --incremental $INCR_DIR --incremental-basedir $INCR_BASEDIR > $ILOG 2>&1

    # record incr backup status
    if [ ! -z "`tail -1 $ILOG | grep 'completed OK!'`" ]; then
        BAK_STATUS=1
        echo
        echo "Incr backup successed."
        echo
        echo "End time: `date +%F' '%T`"
        echo
        echo "See detail for backup log: $ILOG"
        echo
        echo "*************End Incr Backup*************"
    else
        echo
        echo "Time:`date +%F' '%T' '%A`"
        echo
        echo "See detail for backup log: $ILOG"
        echo
        echo "************Full Backup Failed************"
    fi
fi

# delete expired backup
# number of already backup
FBAK_NUM=`find $FBAK_DIR -mindepth 1 -maxdepth 1 -type d | wc -l`

# delete backup
if [ $FBAK_NUM -gt  $KEEP_FBAK_NUM ]; then
    EXPIRED_FNUM=`expr $FBAK_NUM - $KEEP_FBAK_NUM`

    for rmfile in $(find $FBAK_DIR -mindepth 1 -maxdepth 1 -type d | sort -n | head -$EXPIRED_FNUM)
    do
        echo
        echo "Delete expired full backup:$rmfile."
        rm -rf $rmfile
        IBAK_FILE=`echo "$rmfile" | sed 's/full/incr/'`
        if [ -d $IBAK_FILE ]; then
            echo
            echo "Delete expired incr backup:$IBAK_FILE."
            rm -rf $IBAK_FILE
        fi
    done
else
    echo
    echo "Not find expired backup."
fi

echo "-----------------------------------------"
exit 0