xtrabackup备份mysql

1. 安装qpress ( 解压工具，恢复数据库时使用）http://www.quicklz.com/ 下载tar xvf qpress-11-linux-x64.tarchmod +x qpressmv qpress /usr/bin2. 安装必要rpm包yum install perl-DBD-MySQLyum install perl-Digest-MD5下载与操作系统对应的libev包并安装http://rpmfind.net/linux/rpm2html/search.php?query=libev.so.4%28%29%2864bit%29&submit=Search+...&system=&arch=例如 Redhat6 执行下列命令，安装libevrpm -ivh libev-4.03-3.el6.x86_64.rpm3. 下载兼容MySQL和OS版本的xtrabackup并安装https://www.percona.com/downloads/XtraBackup/LATEST/例如：rpm -ivh percona-xtrabackup-24-2.4.7-1.elX.x86_64.rpmln -s /usr/bin/innobackupex /usr/local/xtrabackup/bin/innobackupex4. 挂载NFS文件系统mkdir /home/bkmysqlmount -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/fstab10.128.13.1:/MySQL_Backup/ay-dj-db01 /home/bkmysql nfs rw,soft,intr 0 05. 在NFS上创建备份及日志目录mkdir -p /home/bkmysql/fullmkdir -p /home/bkmysql/incrmkdir -p /home/bkmysql/log6. 修改并上传备份脚本bkmysql.sh修改bkmysql.sh中的变量，数据库密码，端口上传bkmysql.sh到/home/bkmysql/7. 设置crontab创建定制备份任务crontab -e0 0 * * * /home/bkmysql/bkmysql.sh >> /home/bkmysql/bkmysql.log 2>&1backup脚本：#!/bin/sh##backup MYSQL db base-on xtrabackup 2.3.3# MYSQL environment variableexport MYSQL_BASE=/home/mysqlexport MYSQL_HOME=$MYSQL_BASE/product/5.7/mysql-1export DATADIR=/data/mysql/dataexport TMPDIR=/tmpexport PATH=$MYSQL_HOME/bin:$PATH# backup cmdINNODB_DIR=/usr/local/xtrabackup/binINNODBBAK=$INNODB_DIR/innobackupexPARAM="--parallel=4 --compress --compress-threads=4"# MYSQL db infoMYSQL_INFO="--host=localhost  --user=root --password=xxx  --port=3306"  MYSQL_CNF=/etc/my.cnf# time variableDAT=`date +%Y%m%d%H%M`# backup dirBAK_DIR=/home/bkmysqlFBAK_DIR=$BAK_DIR/fullIBAK_DIR=$BAK_DIR/incr# backup logLOG_DIR=$BAK_DIR/logFLOG=$LOG_DIR/$DAT.f.logILOG=$LOG_DIR/$DAT.i.log# records last backup secondsLAST_BAK=$FBAK_DIR/last_fbak_time# full backup interval 259200(3 * 24 * 3600) secondsFBAK_INTERVAL=258200# save number of full backupKEEP_FBAK_NUM=1# define error function for quit when errorfunction 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."    fifiif [ ! -d $FBAK_DIR ]; then    echo "Full backup dir:$FBAK_DIR don't exist."    echo "Create full backup dir:$FBAK_DIR."    echo    mkdir -p $FBAK_DIRfiif [ ! -d $IBAK_DIR ]; then    echo "Incr backup dir:$IBAK_DIR don't exist."    echo "Create incr backup dir:$IBAK_DIR."    echo    mkdir -p $IBAK_DIRfiif [ ! -d $LOG_DIR ]; then    echo "Backup log dir:$LOG_DIR don't exist."    echo "Create log dir:$LOG_DIR."    echo    mkdir -p $LOG_DIRfiMYSQL_STATUS=`mysqladmin $MYSQL_INFO ping`if [ "$MYSQL_STATUS" != "mysqld is alive" ];then    error "MySQL is not running."fiecho "-----------------------------------------"echo "MySQL backup scripts: $0"echo# first backup ,create time fileif [ ! -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_BAKfi# calculate the time interval of the last full backupBAK_TIME=`date +%s`LAST_BAK_TIME=`head -1 $LAST_BAK`BAK_INTERVAL=`expr $BAK_TIME - $LAST_BAK_TIME `# interval >=3 day full backup# intervalif [ $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************"    fielse    # 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************"    fifi# delete expired backup# number of already backupFBAK_NUM=`find $FBAK_DIR -mindepth 1 -maxdepth 1 -type d | wc -l`# delete backupif [ $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    doneelse    echo    echo "Not find expired backup."fiecho "-----------------------------------------"exit 0