CentOS 7 下使用 Percona XtraBackup 备份和恢复 MySQL5.7

一、概述

XtraBackup 是 Percona 开源的免费数据库热备份软件，它能对InnoDB数据库和XtraDB存储引擎的数据库非阻塞地备份（对于MyISAM的备份同样需要加表锁）；mysqldump备份方式是采用的逻辑备份，其最大的缺陷是备份和恢复速度较慢，如果数据库大于50G，mysqldump备份就不太适合。

mysqldump优缺点

优点
使用场景：
10G以下的数据库操作简单

缺点
数据量范围：30G –> TB级别 的时候备份、恢复操作很慢，效率低

xtrabackup备份软件

使用场景：
1、数据量大，变换量小
2、数据量小，变化量大

Xtrabackup安装完成后有4个可执行文件，其中2个比较重要的备份工具是innobackupex、xtrabackup

1）xtrabackup 是专门用来备份InnoDB表的，和 MySQL server没有交互；
2）innobackupex 是一个封装xtrabackup的Perl脚本，支持同时备份innodb和myisam，但在对myisam备份时需要加一个全局的读锁。
3）xbcrypt 加密解密备份工具
4）xbstream 流传打包传输工具，类似tar
5）物理备份工具，在同级数据量基础上，都要比逻辑备份性能好的多，特别是在数据量较大的时候，体现的更加明显。

Xtrabackup优点
1）备份速度快，物理备份可靠

2）备份过程不会打断正在执行的事务（无需锁表）

3）能够基于压缩等功能节约磁盘空间和流量

4）自动备份校验

5）还原速度快

6）可以流传将备份传输到另外一台机器上

7）在不增加服务器负载的情况备份数据

8）物理备份工具，在同级数据量基础上，都要比逻辑备份性能要好的多。几十G到不超过TB级别的条件下。但在同数据量级别，物理备份恢复数据上有一定优势。

备份原理

拷贝数据文件、拷贝数据页

对于innodb表可以实现热备。

(1) 在数据库还有修改操作的时刻，直接将数据文件备走，此时，备份走的数据对于当前mysql来讲是不一致的。
(2) 将备份过程中的redo和undo一并备走。
(3) 为了恢复的时候，只要保证备份出来的数据页lsn能和redo lsn匹配，将来恢复的就是一致的数据。redo应用和undo应用。

对于myisam表实现自动锁表拷贝文件。

　　备份开始时首先会开启一个后台检测进程，实时检测mysql redo的变化，一旦发现有新的日志写入，立刻将日志记入后台日志文件xtrabackup_log中，之后复制innodb的数据文件一系统表空间文件ibdatax，复制结束后，将执行flush tables with readlock,然后复制.frm MYI MYD等文件，最后执行unlock tables,最终停止xtrabackup_log

二、背景

最近公司上线一个新项目，使用用 DELL 单片机和磁盘阵列，配置如下

名称	配置
操作系统	CentOS 7.7 x64
主机地址	172.20.8.132
主机名称	localhost
MySQL 版本	5.7.28
XtraBack 版本	2.4.16

三、安装

按照官网文档，通用 yum 在线方式安装，步骤如下：

1. 安装 Percona 的 yum 仓库，使用 root 账号执行下面命令：

   [root@localhost ~]# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
   Loaded plugins: fastestmirror, langpacks
   percona-release-latest.noarch.rpm                                                                                        |  17 kB  00:00:00     
   Examining /var/tmp/yum-root-1mopyU/percona-release-latest.noarch.rpm: percona-release-1.0-13.noarch
   Marking /var/tmp/yum-root-1mopyU/percona-release-latest.noarch.rpm to be installed
   Resolving Dependencies
   --> Running transaction check
   ---> Package percona-release.noarch 0:1.0-13 will be installed
   --> Finished Dependency Resolution
   
   Dependencies Resolved
   
   ================================================================================================================================================
    Package                            Arch                      Version                   Repository                                         Size
   ================================================================================================================================================
   Installing:
    percona-release                    noarch                    1.0-13                    /percona-release-latest.noarch                     20 k
   
   Transaction Summary
   ================================================================================================================================================
   Install  1 Package
   
   Total size: 20 k
   Installed size: 20 k
   Is this ok [y/d/N]: y
   Downloading packages:
   Running transaction check
   Running transaction test
   Transaction test succeeded
   Running transaction
     Installing : percona-release-1.0-13.noarch                                                                                                1/1 
   * Enabling the Percona Original repository
   <*> All done!
   The percona-release package now contains a percona-release script that can enable additional repositories for our newer products.
   
   For example, to enable the Percona Server 8.0 repository use:
   
     percona-release setup ps80
   
   Note: To avoid conflicts with older product versions, the percona-release setup command may disable our original repository for some products.
   
   For more information, please visit:
     https://www.percona.com/doc/percona-repo-config/percona-release.html
   
     Verifying  : percona-release-1.0-13.noarch                                                                                                1/1 
   
   Installed:
     percona-release.noarch 0:1.0-13                                                                                                               
   
   Complete!

2. 启用 Percona 的 yum 仓库

   [root@localhost ~]#  percona-release enable-only tools release                      
   * Disabling all Percona Repositories
   * Enabling the Percona Tools repository
   <*> All done!

3. 安装 Percona XtraBackup

   [root@localhost databases]# yum list | grep percona                             
   percona-release.noarch                      1.0-13                     @/percona-release-latest.noarch
   percona-backup-mongodb.x86_64               1.0.0-1.el7                tools-release-x86_64
   percona-mysql-shell.x86_64                  8.0.15-1.el7               tools-release-x86_64
   percona-mysql-shell-debuginfo.x86_64        8.0.15-1.el7               tools-release-x86_64
   percona-toolkit.x86_64                      3.1.0-2.el7                tools-release-x86_64
   percona-toolkit-debuginfo.x86_64            3.0.13-1.el7               tools-release-x86_64
   percona-xtrabackup-24.x86_64                2.4.16-1.el7               tools-release-x86_64
   percona-xtrabackup-24-debuginfo.x86_64      2.4.16-1.el7               tools-release-x86_64
   percona-xtrabackup-80.x86_64                8.0.8-1.el7                tools-release-x86_64
   percona-xtrabackup-80-debuginfo.x86_64      8.0.8-1.el7                tools-release-x86_64
   percona-xtrabackup-test-24.x86_64           2.4.16-1.el7               tools-release-x86_64
   percona-xtrabackup-test-80.x86_64           8.0.8-1.el7                tools-release-x86_64
   
   
   
   [root@localhost databases]# yum install -y percona-xtrabackup-24.x86_64
   Loaded plugins: fastestmirror, langpacks
   Loading mirror speeds from cached hostfile
    * base: mirrors.tuna.tsinghua.edu.cn
    * extras: mirrors.cqu.edu.cn
    * updates: mirrors.cqu.edu.cn
   Resolving Dependencies
   --> Running transaction check
   ---> Package percona-xtrabackup-24.x86_64 0:2.4.16-1.el7 will be installed
   --> Finished Dependency Resolution
   
   Dependencies Resolved
   
   ================================================================================================================================================
    Package                                 Arch                     Version                          Repository                              Size
   ================================================================================================================================================
   Installing:
    percona-xtrabackup-24                   x86_64                   2.4.16-1.el7                     tools-release-x86_64                   7.6 M
   
   Transaction Summary
   ================================================================================================================================================
   Install  1 Package
   
   Total download size: 7.6 M
   Installed size: 7.6 M
   Downloading packages:
   percona-xtrabackup-24-2.4.16-1.el7.x86_64.rpm                                                                            | 7.6 MB  00:01:49     
   Running transaction check
   Running transaction test
   Transaction test succeeded
   Running transaction
     Installing : percona-xtrabackup-24-2.4.16-1.el7.x86_64                                                                                    1/1 
     Verifying  : percona-xtrabackup-24-2.4.16-1.el7.x86_64                                                                                    1/1 
   
   Installed:
     percona-xtrabackup-24.x86_64 0:2.4.16-1.el7                                                                                                   
   
   Complete!

4. 若要删除 Percona XtraBackup ，使用 root 账号或者 sudo 执行：

   [root@localhost databases]# rpm -qa|grep percona
   percona-xtrabackup-80-8.0.8-1.el7.x86_64
   percona-release-1.0-13.noarch
   [root@localhost databases]#  yum remove percona-xtrabackup-80-8.0.8-1.el7.x86_64
   Loaded plugins: fastestmirror, langpacks
   Resolving Dependencies
   --> Running transaction check
   ---> Package percona-xtrabackup-80.x86_64 0:8.0.8-1.el7 will be erased
   --> Finished Dependency Resolution
   
   Dependencies Resolved
   
   ================================================================================================================================================
    Package                                 Arch                     Version                         Repository                               Size
   ================================================================================================================================================
   Removing:
    percona-xtrabackup-80                   x86_64                   8.0.8-1.el7                     @tools-release-x86_64                    54 M
   
   Transaction Summary
   ================================================================================================================================================
   Remove  1 Package
   
   Installed size: 54 M
   Is this ok [y/N]: y
   Downloading packages:
   Running transaction check
   Running transaction test
   Transaction test succeeded
   Running transaction
     Erasing    : percona-xtrabackup-80-8.0.8-1.el7.x86_64                                                                                     1/1 
     Verifying  : percona-xtrabackup-80-8.0.8-1.el7.x86_64                                                                                     1/1 
   
   Removed:
     percona-xtrabackup-80.x86_64 0:8.0.8-1.el7                                                                                                    
   
   Complete!

5. 检查是否成功安装 Percona XtraBackup

   [root@localhost databases]# xtrabackup -v
   xtrabackup: recognized server arguments: --datadir=/data1/databases/mysql/data 
   xtrabackup version 2.4.16 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c807cfa)

xtrabackup实践操作

全量备份

innobackupex --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=root --password=123456  /data1/databases/backup/xfull/ 

查看备份

[root@localhost databases]# ls /data1/databases/backup/xfull/ -lh
total 4.0K
drwxr-x---. 5 root root 4.0K Nov 28 22:08 2019-11-28_22-02-20

恢复

service mysql stop
innobackupex --apply-log /data1/databases/backup/xfull/2019-11-28_22-02-20/
 rm /data1/databases/mysql/data -rf
innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data1/databases/backup/xfull/2019-11-28_22-02-20/
chown -R mysql:mysql /data1/databases/mysql/
service mysql  start

增量备份与恢复

innobackupex增量备份过程中的”增量”处理，其实主要是相对innodb而言，对myisam和其他存储引擎而言，它仍然是全拷贝(全备份)
增量备份从哪增量？
基于上一次的备份进行增量。
redo默认情况下是一组两个文件，并且有固定大小。其使用的文件是一种轮询使用方式，他不是永久的，文件随时可能被覆盖。

注意：千万不要在业务繁忙时做备份。

备份什么内容？

1、可以使用binlog作为增量

2、自带的增量备份，基于上次备份后的变化的数据页，还要备份在备份过程中的undo、redo变化

操作
1、先进行第一次全备

innobackupex --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=root --password=123456 /data1/databases/backup/xfull/ 

2、再进行增量备份。这个是在全备的基础上做的，需要指定全量备份的目录：/data1/databases/backup/xfull/ ；增量备份到 /data1/databases/backup//xinc1

innobackupex --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=root --password=123456 --incremental --incremental-basedir=/data1/databases/backup/xfull/2019-11-28_22-02-20/ /data1/databases/backup/xinc1

恢复
1、先应用全备日志

innobackupex --apply-log --redo-only /data1/databases/backup/xfull/2019-11-28_22-02-20/

2、合并增量到全备中（一致性的合并）

innobackupex --apply-log -redo-only --incremental-dir=/data1/databases/backup/xinc1/2019-11-28_22-22-57/ /data1/databases/backup/xfull/2019-11-28_22-02-20/
innobackupex --apply-log --incremental-dir=/data1/databases/backup/xinc1/2019-11-28_22-23-11/ /data1/databases/backup/xfull/2019-11-28_22-02-20/
innobackupex --apply-log /data1/databases/backup/xfull/2019-11-28_22-02-20/

3、合并完成进行恢复
使用innobackupex命令进行恢复(推荐)

innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data1/databases/backup/xfull/2019-11-28_22-02-20/
chown -R mysql:mysql /data1/databases/mysql/

数据库备份策略
每周的周日进行一次全备；周一到周六每天做上一天增量，每周轮询一次。
备份方案： xtrabackup全备+增量

备份策略（crontab）：

crontab -e
# 每周一的凌晨3点执行完全备份
0 3 * * 1 /data1/databases/shell/allbak.sh  >/dev/null 

vim /root/allbak.sh
#!/bin/bash
tmpPath=/data1/databases/backup/xfull
compressPath=/data1/databases/backup/compress/$(date '+%Y/%m/')
# 定义日期是时间
day=`date +%F` 
user=root
pass=123456
host=127.0.0.1
# 新建个文件夹临时存放备份文件的
[ ! -e "${tmpPath}/${day}-full" ]&& mkdir -p "${tmpPath}/${day}-full"
[ ! -e ${compressPath} ]&& mkdir -p ${compressPath}
# 删除历史备份临时文件
[ -e ${tmpPath} ]&& rm -rf "${tmpPath}/*"
# 用innobackupex做完全备份
innobackupex --host=$host --user=$user --password=$pass "${tmpPath}/${day}-full" --no-timestamp 
cd "${tmpPath}"
echo "$(date '+%Y-%m-%d %H:%M:%S')" > "${day}-full/time.txt"
echo "本次全量备份时间： $(date '+%Y-%m-%d %H:%M:%S')" >> "${day}-full/full.txt"
tar -czvf "${compressPath}/$(date '+%Y-%m-%d_%H_%M_%S')-full.tar.gz"  "${day}-full"
# 启用binlog日志，每次完全备份之后，每周刷新一遍binlog日志
mysql -h$host  -u$user -p$pass -e "flush logs"

# 每周二到周日的凌晨4点执行增量备份
0 4 * * 2-7 /data1/databases/shell/newbak.sh  >/dev/null 


# 周一全备，如果是周二执行，判断dir1 存在，则增量备份，如果不存在，则判断昨天的增量，存在则执行周三的增量，如果都没有，则全备执行一次吧
vi /data1/databases/shell/newbak.sh
#!/bin/bash
# 定义时间，用日期来区分
d1=`date +%F` 
# 找到昨天的日期，好指明上一次备份的备份文件
d2=`date +%F -d "-1 days" `
# 昨天做的完全备份文件
dir1="/data1/databases/backup/xfull/${d2}-full" 
#昨天做的增量备份文件
dir2="/data1/databases/backup/xincrement/${d2}-increment" 
# 备份压缩文件存储路径
compressPath=/data1/databases/backup/compress/$(date '+%Y/%m/')
host=127.0.0.1
user=root
pass=123456
# 如果文件不存在，则创建文件夹
[ ! -e $(dirname $dir1 ) ]&& mkdir -p $(dirname $dir1 )
[ ! -e $(dirname $dir2 ) ]&& mkdir -p $(dirname $dir2 )
[ ! -e ${compressPath} ]&& mkdir -p "${compressPath}" 
# 判断昨天做的是增量备份
if [ -e ${dir2} ];then 
        # 指定昨天备份的增量备份文件
        innobackupex --host=$host --user=$user --password=$pass --incremental "$(dirname $dir2)/${d1}-increment" --incremental-basedir="${dir2}" --no-timestamp 
        cd "$(dirname $dir2)"
        echo "$(date '+%Y-%m-%d %H:%M:%S')" > "${d1}-increment/time.txt"
        echo "本次增量量备份时间： $(date '+%Y-%m-%d %H:%M:%S')" > "${d1}-increment/increment.txt"
        echo "基于上次增量备份时间： $(cat $dir2/time.txt)" >> "${d1}-increment/increment.txt"
        tar -czvf "${compressPath}/$(date '+%Y-%m-%d_%H_%M_%S')-increment.tar.gz"  "${d1}-increment"
        echo " 指定昨天备份的增量备份文件"
        # 删除上次的增量备份的临时文件
        [ -e "${dir2}" ]&& rm -rf "${dir2}"
# 判断昨天做的是全量备份
elif [ -e ${dir1} ];then 
          # 删除上次的增量备份的临时文件
          [ -e "$(dirname $dir2)" ]&& rm -rf "$(dirname $dir2)/*"
          # 指定昨天备份的完全备份文件
          innobackupex --host=$host --user=$user --password=$pass --incremental "$(dirname $dir2)/${d1}-increment" --incremental-basedir=${dir1} --no-timestamp 
          cd "$(dirname $dir2)"
          echo "$(date '+%Y-%m-%d %H:%M:%S')" > "${d1}-increment/time.txt"
          echo "本次增量量备份时间： $(date '+%Y-%m-%d %H:%M:%S')" > "${d1}-increment/increment.txt"
          echo "基于上次全量备份时间： $(cat $dir1/time.txt)" >> "${d1}-increment/increment.txt"
          tar -czvf "${compressPath}/$(date '+%Y-%m-%d_%H_%M_%S')-increment.tar.gz"  "${d1}-increment"
          echo " 指定昨天备份的完全备份文件 "
else
# 昨天既没有做增量备份，又没有做完全备份，则做一次完全备份。
        # 删除上次的全量备份的临时文件
        [ -e "$(dirname $dir1)" ]&& rm -rf "$(dirname $dir1)/*"
        # 做一次完全备份。
        innobackupex --host=$host --user=$user --password=$pass "$(dirname $dir1)/${d1}-full" --no-timestamp 
        cd "$(dirname $dir1)"
        echo "$(date '+%Y-%m-%d %H:%M:%S')" > "${d1}-full/time.txt"
        echo "本次全量备份时间： $(date '+%Y-%m-%d %H:%M:%S')" > "${d1}-full/full.txt"
        tar -czvf "${compressPath}/$(date '+%Y-%m-%d_%H_%M_%S')-full.tar.gz"  "${d1}-full"
        echo " 做一次完全备份"
fi

echo "  ${dir1} ${dir2} ${compressPath}"

实际应用

binlog日志 默认大小：1G左右，设置 ，配置文件里加一个 max_binlog_size = ？

数据创建阶段

1、创建备份需要的目录

mkdir full inc1 inc2

2、周日全备

innobackupex --user=root --password=123 --no-timestamp /backup/xbackup/full/

3、模拟数据变化

use oldboy
create table test(id int,name char(20),age int);
insert into test values(8,'outman',99);
insert into test values(9,'outgirl',100);
commit;

4、周一增量备份

innobackupex --user=root --password=123 --incremental --no-timestamp --incremental-basedir=/backup/xbackup/full/ /backup/xbackup/inc1

5、模拟数据变化

use oldboy
insert into test values(8,'outman1',119);
insert into test values(9,'outgirl1',120);
commit;

6、周二的增量备份

innobackupex --user=root --password=123 --incremental --no-timestamp --incremental-basedir=/backup/xbackup/inc1 /backup/xbackup/inc2

1. 再插入新的行操作

   use oldboy
   insert into test values(10,'outman2',19);
   insert into test values(11,'outgirl2',10);
   commit;

模拟误操作事故

模拟场景，周二下午2点误删除test表

use oldboy;
drop table test;

准备恢复数据

1.准备xtrabackup备份，合并备份

innobackupex --apply-log --redo-only /backup/xbackup/full
innobackupex --apply-log --redo-only --incremental-dir=/backup/xbackup/inc1 /backup/xbackup/full
innobackupex --apply-log --incremental-dir=/backup/xbackup/inc2 /backup/xbackup/full
innobackupex --apply-log /backup/xbackup/full

2．确认binlog起点，准备截取binlog。

cd /backup/xbackup/inc2/
cat xtrabackup_binlog_info
mysql-bin.000001 1121

3.截取到drop操作之前的binlog

mysqlbinlog --start-position=1121 /tmp/mysql-bin.000003

找到drop之前的event和postion号做日志截取，假如 1437 这个可以用 mysqlbinlog master-bin.000032|less

mysqlbinlog --start-position=1121 --stop-position=1437 /tmp/mysql-bin.000003 >/tmp/incbinlog.sql

4．关闭数据库、备份二进制日志

/etc/init.d/mysqld stop
cd /application/mysql/data/
cp mysql-bin.000001 /tmp

5.删除MySQL所有数据

cd /application/mysql/data/
rm -rf *

恢复数据

1．将全量备份的数据恢复到数据目录下

innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /backup/xbackup/full/
chown -R mysql:mysql /application/mysql/data/
/etc/init.d/mysqld start

2.恢复binlog记录

set sql_log_bin=0
source /tmp/incbinlog.sql