一、xtrabackup8.0.4安装
1.1 、下载需要的安装包。
https://www.percona.com/downloads/XtraBackup/LATEST/
image.png
1.2 、安装所需要epel源。
RHEL/CentOS 6:
# yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm
RHEL/CentOS 7:
# yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
1.3、安装依赖包。
# yum install -y libev
1.4、安装。
# yum install -y percona-xtrabackup-80-8.0.4-1.el7.x86_64.rpm
二、xtrabackup8.0.4的全量备份与恢复。
2.1、连接(可不进行)。
# /usr/bin/xtrabackup --user=suke --password=123 --backup --target -dir=/data/mysql/
其他参数：
--port The port to use when connecting to the database server with TCP/IP. --socket The socket to use when connecting to the local database. --host The host to use when connecting to the database server with TCP/IP. 例如：--host=10.250.0.66 --user=root --password=xxxyyy --port=3306
2.2、授权用户。
mysql> CREATE USER 'suke'@'localhost' IDENTIFIED BY '123';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'suke'@'localhost';
mysql> FLUSH PRIVILEGES;
2.3、创建全备。
# /usr/bin/xtrabackup --defaults-file=/etc/my.cnf --user=suke --password=123 --socket=/tmp/mysql.sock --backup --target-dir=/data/mysql/backup/full
ps： 或者：使用参数--datadir替换掉参数--defaults-file.
例如：
# /usr/bin/xtrabackup --user=suke --password=123 --socket=/tmp/mysql.sock --datadir=/usr/local/mysql/data --backup --target-dir=/data/mysql/backup/full
2.4、全备恢复。
2.4.1 、关闭数据库。
# /usr/local/mysql/bin/mysqldadmin -uroot -p123 shutdown
2.4.2、 清除数据库数据目录datadir
备份或者直接删除。
# cd /usr/local/mysql/data
# cp -r * /opt
# cd /usr/local/mysql/data
# rm -rf *
2.4. 3 、准备恢复。
# /usr/bin/xtrabackup --defaults-file=/etc/my.cnf --user=suke --password=123 --prepare --target-dir=/home/mysql/backup/full
2.4. 4、 开始恢复。
2.4.4.1、保留备份文件。
--copy-back。
# /usr/bin/xtrabackup --defaults-file=/etc/my.cnf --user=suke --password=123 --copy-back --target-dir=/home/mysql/backup/full
2.4.4.2、不保留备份文件。
--move-back。
# /usr/bin/xtrabackup --defaults-file=/etc/my.cnf --user=suke --password=123 --move-back --target-dir=/home/mysql/backup/full
2.4.4.3 、如果不想使用xtrabackup命令，那么可以使用rsync或者cp。
# rsync -avrP /home/mysql/backup/full /usr/local/mysql/data
# cp -r /home/mysql/backup/full /usr/local/mysql/data
** ps:务必检查文件属主，属组。**
2.4.5、备份目录解析。
image.png
其中淡蓝色字体 mysql/， performance_schema/， sys/ 下存放的是数据库文件。
backup-my.cnf，备份命令用到的配置选项信息；
image.png
xtrabackup_binlog_info，mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位 置；
image.png
xtrabackup_checkpoints，备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序 列号)范围信息；
image.png
xtrabackup_info，记录备份的基本信息，uuid、备份命令、备份时间、binlog、LSN、以及其他加密压缩等信息。
image.png
xtrabackup_logfile，备份的重做日志文件。
三、xtrabackup8.0.4的增量备份与恢复。
3.1、 在全备的基础上创建增量备份。
从全备文件的xtrabackup_checkpoints中，有一行to_lsn ,备份结束时的last_lsn,如下图从lsn=19107798开始变化， 基于全备我们进行增量备份。
# /usr/bin/xtrabackup --user=suke --password=123 --socket=/tmp/mysql.sock --backup -target-dir=/home/mysql/backup/inc1 --incremental-basedir=/home/mysql/backup/full
image.png
# /usr/bin/xtrabackup --user=suke --password=123 --socket=/tmp/mysql.sock --backup -target-dir=/home/mysql/backup/inc2 --incremental-basedir=/home/mysql/backup/inc1
..........
..........
3.2 、增量备份恢复。
3.2.1、 先prepare全备。
# /usr/bin/xtrabackup --prepare --apply-log-only --target-dir=/home/mysql/backup/full
3.2.2、 然后prepare增量备份。
原理大概是先把每个增量备份都 恢复到完全备份当中，然后对整个全备进行恢复。
# /usr/bin/xtrabackup --prepare --apply-log-only --target-dir=/home/mysql/backup/full --incremental-basedir=/home/mysql/backup/inc1
# /usr/bin/xtrabackup --prepare --apply-log-only --target-dir=/home/mysql/backup/full --incremental-basedir=/home/mysql/backup/inc2
3.2.3、 然后再prepare全备。
# /usr/bin/xtrabackup --prepare --target-dir=/home/mysql/backup/full
3.2.4、进行全备恢复。
# /usr/bin/xtrabackup --defaults-file=/etc/my.cnf --user=suke --password=123 --copy-back --target-dir=/home/mysql/backup/full
四、xtrabackup备份脚本。
4.1全备脚本。
#!/bin/bash
#date:2019/01/07
#auther:suketongxue
XTRABACKUPEX=xtrabackup
XTRABACKUPEXFULL=/usr/bin/$XTRABACKUPEX
TODAY=`date +%Y%m%d%H%M`
YESTERDAY=`date -d"yesterday" +%Y%m%d%H%M`
USEROPTIONS="--user=root --password=123456"
TMPFILE="/home/iap/xtrabackup_$TODAY.$$.tmp"
SOCKET='/data/mysql/mysql3306.sock'
MYCNF=/etc/my.cnf
MYSQL=/usr/local/mysql/bin/mysql
MYSQLADMIN=/usr/local/mysql/bin/mysqladmin
BACKUPDIR=/data/mysqlbackup/backup # 备份的主目录
FULLBACKUPDIR=$BACKUPDIR/full # 全库备份的目录
FULLZIP=$BACKUPDIR/fullzip # 全库包备份的目录
# Grab start time
#############################################################################
# error message and exit!
#############################################################################
error()
{
echo "$1" 1>&2
exit 1
}
# Check options before proceeding
if [ ! -x $XTRABACKUPEXFULL ]; then
error "$XTRABACKUPEXFULL does not exist."
fi
if [ ! -d $BACKUPDIR ]; then
error "Backup destination folder: $BACKUPDIR does not exist."
fi
if [ -z "`$MYSQLADMIN $USEROPTIONS status | grep 'Uptime'`" ] ; then
error "HALTED: MySQL does not appear to be running."
fi
if ! `echo 'exit' | $MYSQL -s $USEROPTIONS` ; then
error "HALTED: Supplied mysql username or password appears to be incorrect (not copied here for security, see script)."
fi
# Some info output
echo "----------------------------"
echo
echo "$0: MySQL backup script"
echo "started: `date`"
echo "----------------------------"
# Create full backup directories if they are not exist.
for i in $FULLBACKUPDIR $FULLZIP
do
if [ ! -d $i ]; then
mkdir -pv $i
fi
done
# Compress and upload the previous day's backup!
echo "compress the backup and scp to remote machine!"
$XTRABACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS --socket=$SOCKET --backup --target-dir=$FULLBACKUPDIR 2>&1
cd $FULLZIP
tar -zcvf $YESTERDAY.tar.gz $FULLBACKUPDIR/*
scp -P22 $YESTERDAY.tar.gz iap@10.120.0.92:/data/mysqlbackup/backup
if [ $? = 0 ]; then
cd $FULLBACKUPDIR
rm -rf *
echo "Running new full backup."
else
echo "Error with scp."
fi
if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then
echo "$XTRABACKUPEX failed:"; echo
echo "---------- ERROR OUTPUT from $XTRABACKUPEX ----------"
# cat $TMPFILE
# rm -f $TMPFILE
cd
#exit 1
fi
# get the backup directory!
THISBACKUP=`awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" $TMPFILE`
echo "THISBACKUP=$THISBACKUP"
#rm -f $TMPFILE
echo "Databases backed up successfully to: $THISBACKUP"
# Cleanup
echo "delete tar files of 7 days ago"
find $FULLZIP/ -mtime +7 -name "*.tar.gz" -exec rm -rf {} \;
echo
echo "completed: `date`"
#exit 0
4.2、增量备份脚本。
#!/bin/bash
# date:2019/01/07
# auther:suketongxue
XTRABACKUPEX=xtrabackup
XTRABACKUPEXFULL=/usr/bin/$XTRABACKUPEX
TODAY=`date +%Y%m%d%H%M` BEGINTIME=`date +"%Y-%m-%d %H:%M:%S"`
YESTERDAY=`date -d"yesterday" +%Y%m%d%H%M` USEROPTIONS="--user=root --password=123456" TMPFILE="/home/mysql/xtrabackup_$TODAY.$$.tmp" SOCKET='/tmp/mysql.sock'
MYCNF=/etc/my.cnf MYSQL=/usr/local/mysql/bin/mysql MYSQLADMIN=/usr/local/mysql/bin/mysqladmin
BACKUPDIR=/home/mysql/backup # 备份的主目录
INCRBACKUPDIR=$BACKUPDIR/incr # 增量备份的目录
FULLBACKUPDIR=$BACKUPDIR/full # 全库备份的目录
#INCRZIP=$BACKUPDIR/incrzip # 增量包备份的目录
# Grab start time #############################################################################
# error message and exit! #
#############################################################################
error()
{
echo "$1" 1>&2 exit 1
}
# Check options before proceeding if [ ! -x $XTRABACKUPEXFULL ]; then
error "$XTRABACKUPEXFULL does not exist." fi
if [ ! -d $BACKUPDIR ]; then
error "Backup destination folder: $BACKUPDIR does not exist." fi
if [ -z "`$MYSQLADMIN $USEROPTIONS status | grep 'Uptime'`" ] ; then error "HALTED: MySQL does not appear to be running."
fi
if ! `echo 'exit' | $MYSQL -s $USEROPTIONS` ; then
error "HALTED: Supplied mysql username or password appears to be incorrect (not copied here for security, see script)."
# Some info output
echo "----------------------------"
echo
echo "$0: MySQL backup script" echo "started: `date`"
echo "----------------------------"
# Create full backup directories if they are not exist. for i in $INCRBACKUPDIR
do
if [ ! -d $i ]; then
mkdir -pv $i
fi
done #fullbacuup
if [ ! -d "$FULLBACKUPDIR" ];then
echo " start full backup at $BEGINTIME to directory full " >>$TMPFILE mkdir $FULLBACKUPDIR
$XTRABACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS --socket=$SOCKET --backup --target- dir=$FULLBACKUPDIR 2>&1
break;
elif [ ! -d "$INCRBACKUPDIR/incr1" ];then
echo " start 1 incremental backup at $BEGINTIME to directory incr1 " >>$TMPFILE mkdir $INCRBACKUPDIR/inc1
$XTRABACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS --socket=$SOCKET --backup --target- dir=$INCRBACKUPDIR/inc1 --incremental-basedir=$FULLBACKUPDIR 2>&1
break;
elif [ ! -d "$INCRBACKUPDIR/incr2" ];then
echo " start 2 incremental backup at $BEGINTIME to directory incr2 " >>$TMPFILE mkdir $INCRBACKUPDIR/inc2
$XTRABACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS --socket=$SOCKET --backup --target- dir=$INCRBACKUPDIR/inc2 --incremental-basedir=$INCRBACKUPDIR/inc1 2>&1
break;
elif [ ! -d "$INCRBACKUPDIR/incr3" ];then
echo " start 3 incremental backup at $BEGINTIME to directory incr3 " >>$TMPFILE mkdir $INCRBACKUPDIR/inc3
$XTRABACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS --socket=$SOCKET --backup --target- dir=$INCRBACKUPDIR/inc3 --incremental-basedir=$INCRBACKUPDIR/inc2 2>&1
break;
elif [ ! -d "$INCRBACKUPDIR/incr4" ];then
echo " start 4 incremental backup at $BEGINTIME to directory incr4 " >>$TMPFILE mkdir $INCRBACKUPDIR/inc4
$XTRABACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS --socket=$SOCKET --backup --target- dir=$INCRBACKUPDIR/inc4 --incremental-basedir=$INCRBACKUPDIR/inc3 2>&1
break;
elif [ ! -d "$INCRBACKUPDIR/incr5" ];then
echo " start 5 incremental backup at $BEGINTIME to directory incr5 " >>$TMPFILE mkdir $INCRBACKUPDIR/inc5
$XTRABACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS --socket=$SOCKET --backup --target- dir=$INCRBACKUPDIR/inc5 --incremental-basedir=$INCRBACKUPDIR/inc4 2>&1
break;
elif [ ! -d "$INCRBACKUPDIR/incr6" ];then
echo " start 6 incremental backup at $BEGINTIME to directory incr6 " >>$TMPFILE mkdir $INCRBACKUPDIR/inc6
$XTRABACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS --socket=$SOCKET --backup --target- dir=$INCRBACKUPDIR/inc6 --incremental-basedir=$INCRBACKUPDIR/inc5 2>&1
break;
elif [ ! -d "$INCRBACKUPDIR/incr7" ];then
echo " start 7 incremental backup at $BEGINTIME to directory incr7 " >>$TMPFILE mkdir $INCRBACKUPDIR/inc7
$XTRABACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS --socket=$SOCKET --backup --target- dir=$INCRBACKUPDIR/inc7 --incremental-basedir=$INCRBACKUPDIR/inc6 2>&1
break; fi
if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then
echo "$XTRABACKUPEX failed:"; echo
echo "---------- ERROR OUTPUT from $XTRABACKUPEX ----------"
cat $TMPFILE #rm -f $TMPFILE cd
fi
# get the backup directory!
THISBACKUP=`awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }"
$TMPFILE`
echo "THISBACKUP=$THISBACKUP"
#rm -f $TMPFILE
echo "Databases backed up successfully to: $THISBACKUP" # Cleanup
echo "delete tar files of 7 days ago"
find $INCRBACKUPDIR/ -mtime +7 -type d -exec rm -rf {} \; echo
echo "completed: `date`"
未完！
网友评论