Xtrabackup安装指南-创新互联

下文内容主要给大家带来Xtrabackup安装指南，所讲到的知识，与书籍略有不同，都是创新互联专业技术人员在与用户接触过程中，总结出来的，具有一定的经验分享价值，希望给广大读者带来帮助。

创新互联专注为客户提供全方位的互联网综合服务，包含不限于成都做网站、网站建设、莲湖网络推广、小程序定制开发、莲湖网络营销、莲湖企业策划、莲湖品牌公关、搜索引擎seo、人物专访、企业宣传片、企业代运营等，从售前售中售后，我们都将竭诚为您服务，您的肯定，是我们大的嘉奖；创新互联为所有大学生创业者提供莲湖建站搭建服务，24小时服务热线：028-86922220，官方网址：www.cdcxhl.com

Xtrabackup安装指南

文件准备

[root@wuxiang11 ~]# cd percona-xtrabackup/
[root@wuxiang11 percona-xtrabackup]# ls
libev-4.15-1.el6.rf.x86_64.rpm  percona-release-0.1-4.noarch.rpm  percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

开始安装依赖文件

[root@wuxiang11 percona-xtrabackup]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm 
warning: libev-4.15-1.el6.rf.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 6b8d79e6: NOKEY
Preparing...         ########################################### [100%]
  1:libev          ########################################### [100%]

安装-01

[root@wuxiang11 percona-xtrabackup]# rpm -ivH percona-release-0.1-4.noarch.rpm
Preparing packages for installation...
percona-release-0.1-4

[root@wuxiang11 percona-xtrabackup]# yum list | grep percona

马上就安装完成

[root@wuxiang11 percona-xtrabackup]# yum install percona-xtrabackup-24

Loaded plugins: fastestmirror, security
Setting up Install Process
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package percona-xtrabackup-24.x86_64 0:2.4.11-1.el6 will be installed
--> Processing Dependency: perl(DBD::mysql) for package: percona-xtrabackup-24-2.4.11-1.el6.x86_64
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.013-3.el6 will be installed
--> Processing Dependency: perl(DBI::Const::GetInfoType) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Processing Dependency: perl(DBI) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Running transaction check
---> Package Percona-Server-shared-51.x86_64 0:5.1.73-rel14.12.625.rhel6 will be installed
---> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===============================================================================================================================================================================================
 Package                      Arch               Version                        Repository                    Size
===============================================================================================================================================================================================
Installing:
 percona-xtrabackup-24               x86_64              2.4.11-1.el6                      percona-release-x86_64              8.1 M
Installing for dependencies:
 Percona-Server-shared-51              x86_64              5.1.73-rel14.12.625.rhel6               percona-release-x86_64              2.1 M
 perl-DBD-MySQL                   x86_64              4.013-3.el6                      centos6.7_64                   134 k
 perl-DBI                      x86_64              1.609-4.el6                      centos6.7_64                   705 k

Transaction Summary
===============================================================================================================================================================================================
Install    4 Package(s)

Total download size: 11 M
Installed size: 12 M
Is this ok [y/N]: y
Downloading Packages:
(1/4): Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64.rpm                                                   | 2.1 MB   00:02   
(2/4): percona-xtrabackup-24-2.4.11-1.el6.x86_64.rpm                                                           | 8.1 MB   00:01   
(3/4): perl-DBD-MySQL-4.013-3.el6.x86_64.rpm                                                               | 134 kB   00:00   
(4/4): perl-DBI-1.609-4.el6.x86_64.rpm                                                                  | 705 kB   00:00   
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                              2.5 MB/s |  11 MB   00:04   
warning: rpmts_HdrFromFdno: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-Percona
Importing GPG key 0xCD2EFD2A:
 Userid : Percona MySQL Development Team 
 Package: percona-release-0.1-4.noarch (installed)
 From  : /etc/pki/rpm-gpg/RPM-GPG-KEY-Percona
Is this ok [y/N]: y
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
** Found 3 pre-existing rpmdb problem(s), 'yum check' output follows:
2:postfix-2.6.6-6.el6_5.x86_64 has missing requires of libmysqlclient.so.16()(64bit)
2:postfix-2.6.6-6.el6_5.x86_64 has missing requires of libmysqlclient.so.16(libmysqlclient_16)(64bit)
2:postfix-2.6.6-6.el6_5.x86_64 has missing requires of mysql-libs
  Installing : perl-DBI-1.609-4.el6.x86_64                                                                         1/4 
  Installing : Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64                                                          2/4 
  Installing : perl-DBD-MySQL-4.013-3.el6.x86_64                                                                      3/4 
  Installing : percona-xtrabackup-24-2.4.11-1.el6.x86_64                                                                  4/4 
  Verifying  : percona-xtrabackup-24-2.4.11-1.el6.x86_64                                                                  1/4 
  Verifying  : perl-DBD-MySQL-4.013-3.el6.x86_64                                                                      2/4 
  Verifying  : Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64                                                          3/4 
  Verifying  : perl-DBI-1.609-4.el6.x86_64                                                                         4/4 

Installed:
  percona-xtrabackup-24.x86_64 0:2.4.11-1.el6                                                                          

Dependency Installed:
  Percona-Server-shared-51.x86_64 0:5.1.73-rel14.12.625.rhel6            perl-DBD-MySQL.x86_64 0:4.013-3.el6            perl-DBI.x86_64 0:1.609-4.el6           

Complete!

检验是否安装成功

[root@wuxiang11 percona-xtrabackup]# rpm -qa|grep xtrabackup
percona-xtrabackup-24-2.4.11-1.el6.x86_64
[root@wuxiang11 percona-xtrabackup]#

备份思路

Xtrabackup提供了全量备份和增量备份两种方式，全量就不解释了，增量是指其可以只备份指定位置后的新增数据。本文介绍的增量备份方法使用到了LSN(Log Sequence Number)，可以从备份成功文件夹的xtrabackup_checkpoints文件中获取，其中to_lsn就是下次增量备份的起始位置。
结合两者，我们可以得到下图的备份思路。
Xtrabackup安装指南

解释如下：

由于全量备份和增量备份的命令参数不同，所以首先要判断是否需要全量备份。
如果是全量备份，那么便执行命令，备份成功后，记录xtrabackup_checkpoints文件中的to_lsn，以便后面进行增量备份
如果是增量备份，那么首先读取上次备份时记录下的to_lsn，如果读取失败，报错并结束。否则执行相应命令，备份成功后记录to_lsn。

编写一个简单的脚本：db-backup.sh，如下所示：

#!/bin/sh

# xtrabackup的相关配置
INNOBACKUPEX="innobackupex "
MY_CNF="/home/config/mysql/3307.backup.cnf"
MY_USER="xtrabackup"
MY_PASSWORD="xtrabackup"
MY_SOCKET="/home/socket/mysql/3307.sock"

# 远程备份机 文件名配置
REMOTE_HOST="dbbackup"
REMOTE_DIR="/home/backup/mysql/test"
LOCAL_LSN_FILE="~/.to_lsn_important"
DATE_NAME=`date +%Y-%m-%d-%H-%M-%S`
REMOTE_FILE=$DATE_NAME.tar.gz
LOCK_FILE="~/.mysql.backup.lock"
LOCAL_BACKUP_DIR="/home/backup/mysql/test/$DATE_NAME"

# 输出帮助信息
function usage()
{
   echo "Usage:"
   echo "-f db will be backuped fully with this parameter. If not , incrementally. "
}

#防止同时执行两个备份命令，发生冲突
if [ -f $LOCK_FILE ] ;then
   echo 'Mysql backup lockfile is locked!'
   exit 0
fi

full=0

while getopts "fh" arg #选项后面的冒号表示该选项需要参数
do
   case $arg in
     f)  
       full=1
       ;;
     h)  # 输出帮助信息
       usage
       exit 0
       ;;
   esac
done

echo "backup dir is $REMOTE_DIR/$REMOTE_FILE"

# backup up db to remote host
echo "start to backup db!"`date +%Y-%m-%d-%H-%M-%S`
if [ "$full"x = "1"x ] ;then
   # 全量备份
   echo '1' > $LOCK_FILE
   $INNOBACKUPEX --defaults-file=$MY_CNF --user=$MY_USER --password=$MY_PASSWORD --socket=$MY_SOCKET ./ --stream=tar | gzip | ssh $REMOTE_HOST "cat - > $REMOTE_DIR/FULL-$REMOTE_FILE"
   ssh $REMOTE_HOST "cd $REMOTE_DIR;rm -f xtrabackup_checkpoints;tar zxfi $REMOTE_DIR/FULL-$REMOTE_FILE xtrabackup_checkpoints "
   toLSN=$( ssh $REMOTE_HOST "cat $REMOTE_DIR/xtrabackup_checkpoints|grep to_lsn|awk -F= '{gsub(/ /,\"\",\$2);print \$2}'" )
   if [ $toLSN ] ;then
     echo $toLSN > $LOCAL_LSN_FILE
   else
     echo 'no lsn from remote host!please check!'
   fi
else
   # 增量备份
   if [ -f $LOCAL_LSN_FILE ] ;then
     toLSN=`cat $LOCAL_LSN_FILE`
   fi

   if [ ! $toLSN ] ;then
     echo 'last LSN is not set !please check!'
     exit 0
   fi

   echo '1' > $LOCK_FILE
   mkdir -p $LOCAL_BACKUP_DIR
   echo "last to lsn is "$toLSN
   $INNOBACKUPEX --parallel=6 --defaults-file=$MY_CNF --user=$MY_USER --password=$MY_PASSWORD --socket=$MY_SOCKET --incremental --incremental-lsn=$toLSN $LOCAL_BACKUP_DIR 2>/tmp/innobackexLog
   toLSN=$( cd $LOCAL_BACKUP_DIR/*; cat xtrabackup_checkpoints|grep to_lsn|awk -F= '{gsub(/ /,"",$2);print $2}' )
   echo "new to lsn is "$toLSN;
   if [ $toLSN ] ;then
     echo $toLSN > $LOCAL_LSN_FILE
     cd $LOCAL_BACKUP_DIR/*;tar zc .|ssh $REMOTE_HOST "cat - > $REMOTE_DIR/$REMOTE_FILE"
     echo "save file to $REMOTE_HOST @ $REMOTE_DIR/$REMOTE_FILE"
   else
     echo 'no lsn from local backup file!delete remote backup file!'$LOCAL_BACKUP_DIR/$REMOTE_FILE
   fi
   echo "remove $LOCAL_BACKUP_DIR"
   rm -rf $LOCAL_BACKUP_DIR
fi

rm -f $LOCK_FILE
echo "end to backup db!"`date +%Y-%m-%d-%H-%M-%S`

将上述脚本配置好后，放到mysql主机上，执行 sh db-backup.sh -f进行全量备份，之后将sh backup.sh添加到crontab中每小时增量备份一次就可以了。

注意--parallel=6使用是有条件的，参考文档

恢复思路

一旦线上数据库发生宕机之类的灾难性事故，备份数据就要立马发挥作用了。打开备份文件夹一看，如上面的图，每小时一个压缩文件，时间久了，茫茫多，上百个压缩文件，简直是一定的，这必须得来个脚本自动化恢复数据才成。思路很简单，见下图：

Xtrabackup安装指南

解释如下：

先解压，没啥说的。
找到全量备份的数据，因为增量备份也是以此为基准进行的。上面备份的时候，全量备份文件命名以FULL开头便是为了此处便于查找。
先恢复全量备份数据，然后按照时间顺序恢复增量备份的数据。
将数据恢复到mysql中
启动mysql，连上看看有没问题。

简单的实现脚本，我们叫它restore.sh，如下所示

#!/bin/sh
BACK_DIR="/home/backup/mysql/test"
RESTORE_DIR="/home/tmpback/test"

# xtrabackup恢复时使用的配置文件可能与数据库配置文件不同
RESTORE_MY_CNF="/home/config/mysql/3307.backup.cnf"

# 启动mysql需要的配置文件，按需设定
MY_CNF="/home/config/mysql/3307.cnf"
MY_DATA_DIR="/home/data/mysql/3307"
MY_SOCK_DIR="/home/socket/mysql/"
MY_LOG_DIR="/home/logs/mysqld/3307"

#extract all tar file
fullFileName=""
for file in $( ls $BACK_DIR | grep "tar.gz" )
do
   fileName=${file%.tar.gz}
   full=${file%%-*}
   if [ "${full}"x = "FULL"x ] ;then
     fullFileName=$fileName
   fi
   DEST_DIR="$RESTORE_DIR/$fileName"
   if [ -d $DEST_DIR ] ;then
     echo "$DEST_DIR exists!"
   else
     mkdir -p $DEST_DIR
     echo "start to extract file $file to $DEST_DIR"
     tar zxif $BACK_DIR/$file -C $RESTORE_DIR/$fileName
     echo "end to extract file "$file
   fi
done

echo "full backup dir is "$fullFileName

echo "**start to repare full backup dir"

# 恢复全量数据
echo "innobackupex --apply-log --redo-only --use-memory=4G $RESTORE_DIR/$fullFileName"
innobackupex --apply-log --redo-only --use-memory=4G "$RESTORE_DIR/$fullFileName"

echo "**end to repare full backup dir"

# 恢复增量数据
for file in $( ls $RESTORE_DIR|grep -v "FULL" )
do
   echo "**start to repare $file"
   echo "innobackupex --apply-log --redo-only --use-memory=4G $RESTORE_DIR/$fullFileName --incremental-dir=$RESTORE_DIR/$file"
   innobackupex --apply-log --redo-only --use-memory=4G "$RESTORE_DIR/$fullFileName" --incremental-dir="$RESTORE_DIR/$file"

   echo "**end to repare $file"

done

echo "**start to copy back data to mysql"
rm -rf $MY_DATA_DIR
mkdir $MY_DATA_DIR
mkdir $MY_SOCK_DIR
mkdir $MY_LOG_DIR

#将数据恢复到mysql中
echo "innobackupex --defaults-file=$RESTORE_MY_CNF --copy-back $RESTORE_DIR/$fullFileName"
innobackupex --defaults-file=$RESTORE_MY_CNF --copy-back $RESTORE_DIR/$fullFileName
echo "**end to copy back data to mysql"

chown -R mysql:mysql $MY_DATA_DIR
chown -R mysql:mysql $MY_SOCK_DIR
chown -R mysql:mysql $MY_LOG_DIR

echo "All data has been restored! Try to start mysql now"

echo "mysqld_multi --defaults-file=$MY_CNF start 1"

使用方法很简单，配置好后，直接运行sh restore.sh就可以了。

对于以上关于Xtrabackup安装指南，如果大家还有更多需要了解的可以持续关注我们创新互联的行业推新，如需获取专业解答，可在官网联系售前售后的，希望该文章可给大家带来一定的知识更新。

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

分享文章：Xtrabackup安装指南-创新互联
路径分享：http://kswjz.com/article/deieoi.html

扫二维码与项目经理沟通

我们在微信上24小时期待你的声音

解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流

Xtrabackup安装指南-创新互联