xtra+binlog增量备份脚本

[TOC]

## 一.备份原理
### innobackupex原理
首先会启动一个xtrabackup_log后台检测的进程，实时检测mysql redo的变化，一旦发现redo有新的日志写入，立刻将日志写入到日志文件xtrabackup_log中
复制innodb的数据文件和系统表空间文件idbdata1到对应的以默认时间戳为备份目录的地方
复制结束后，执行flush table with read lock操作
复制.frm .myd .myi文件
并且在这一时刻获得binary log 的位置
将表进行解锁unlock tables
停止xtrabackup_log进程

### binlog原理
MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。一般来说开启binlog日志大概会有1%的性能损耗。binlog在恢复的时候，逐个执行sql语句，可以根据pos点和时间来指定恢复的范围。

DDL
主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

DML
主要的命令是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言

binlog日志包括两类文件：
1）二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件
2）二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。

### 特点
mysqldump优点：mysqldump的优点就是逻辑备份，把数据生成SQL形式保存，在单库，单表数据迁移，备份恢复等场景方便，SQL形式的备份文件通用，也方便在不同数据库之间移植。对于InnoDB表可以在线备份。

mysqldump缺点：mysqldump是单线程，数据量大的时候，备份时间长，甚至有可能在备份过程中非事务表长期锁表对业务造成影响(SQL形式的备份恢复时间也比较长)。mysqldump备份时会查询所有的数据，这可能会把内存中的热点数据刷掉

innobackupex优点：物理备份可以绕过MySQL Server层，加上本身就是文件系统级别的备份，备份速度块，恢复速度快，可以在线备份，支持并发备份，支持加密传输，支持备份限速

innobackupex缺点：要提取部分库表数据比较麻烦，不能按照基于时间点来恢复数据，并且不能远程备份，只能本地备份，增量备份的恢复也比较麻烦。如果使用innobackupex的全备+binlog增量备份就可以解决基于时间点恢复的问题

### 备份策略
根据需求，使用innobackupex全备份+innobackupex增量备份+binlog方式进行备份。
在出现问题时使用innobackupex快速的恢复

## 二.环境准备
### 开启binlog
编辑/etc/my.cnf文件添加在[mysqld]版块下添加如下变量，添加后重启服务。
```python
#开启，并且可以将mysql-bin改为其它的日志名
log-bin=mysql-bin

#添加id号，如果做主从，就不能一样
server-id=1

#超过200M将生产新的文件，最大和默认值是1GB
max_binlog_size=1G

#此参数表示binlog使用最大内存的数，默认1M。
max_binlog_cache_size=1M

#此参数表示binlog日志保留的时间，默认单位是天。
expire_logs_days=7
```

### 创建授权用户
`create user 'back'@'localhost' identified by '123456';`
`grant reload,lock tables,replication client,create tablespace,process,super on *.* to 'back'@'localhost' ;`
`grant create,insert,select on percona_schema.* to 'back'@'localhost';`

### 安装innobackupex
1.安装依赖
`yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev-devel`

2.下载安装
`wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm`
`yum -y install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm`

## 三.添加脚本
### 全量备份
创建备份目录
`mkdir -p /root/bin`
`mkdir -p  /bak/mysql-xback`

编写脚本，要指定备份命令的账号和密码
`vim  /root/bin/mybak-all.sh`
```python
#!/bin/bash
#全量备份，只备份一次

#指定备份目录
backup_dir="/bak/mysql-xback"

#检查
[[ -d ${backup_dir} ]] || mkdir -p ${backup_dir}
if [[ -d ${backup_dir}/all-backup ]];then
    echo "全备份已存在"
    exit 1
fi

#命令，需要设置
innobackupex --defaults-file=/etc/my.cnf --user=back --password='123456' --no-timestamp ${backup_dir}/all-backup &> /tmp/mysql-backup.log

tail -n 1  /tmp/mysql-backup.log | grep 'completed OK!'

if [[ $? -eq 0 ]];then
    echo "all-backup" > /tmp/mysql-backup.txt
else
    echo "备份失败"
    exit 1
fi
```

### 增量备份
编写脚本，要指定备份目录
`vim  /root/bin/mybak-section.sh`
```python
#!/bin/bash
#增量备份

#备份目录
backup_dir="/bak/mysql-xback"

#新旧备份
old_dir=`cat /tmp/mysql-backup.txt`
new_dir=`date +%F-%H-%M-%S`

#检查
if [[ ! -d ${backup_dir}/all-backup ]];then
    echo "还未全量备份"
    exit 1
fi

#命令
/usr/bin/innobackupex --user=back --password='123456' --no-timestamp --incremental --incremental-basedir=${backup_dir}/${old_dir} ${backup_dir}/${new_dir} &> /tmp/mysql-backup.log
tail -n 1  /tmp/mysql-backup.log | grep 'completed OK!'

if [[ $? -eq 0 ]];then
    echo "${new_dir}" > /tmp/mysql-backup.txt
else
    echo "备份失败"
    exit 1
fi
```

### binlog
创建备份目录
`mkdir -p  /bak/mysql-binback`

用于单点，备份binlog，要指定备份目录位置和其它变量
`vim /root/bin/mybak-binlog.sh`
```python
#!/bin/bash
#
# 注意：执行脚本前修改脚本中的变量
# 功能：cp方式增量备份
#
# 适用：centos6+
# 语言：中文
#
#使用：./xx.sh -uroot -p'123456'，将第一次增量备份后的binlog文件名写到/tmp/binlog-section中，若都没有，自动填写mysql-bin.000001
#过程：增量先刷新binlog日志，再查询/tmp/binlog-section中记录的上一次备份中最新的binlog日志的值
#      cp中间的binlog日志，并进行压缩。再将备份中最新的binlog日志写入。
#恢复：先进行全量恢复，再根据全量备份附带的time-binlog.txt中的记录逐个恢复。当前最新的Binlog日志要去掉有问题的语句，例如drop等。

#[变量]
#mysql这个命令所在绝对路径
my_sql="/usr/local/mysql/bin/mysql"

#mysqldump命令所在绝对路径
bak_sql="/usr/local/mysql/bin/mysqldump"

#binlog日志所在目录
binlog_dir=/usr/local/mysql/data

#mysql-bin.index文件所在位置
binlog_index=${binlog_dir}/mysql-bin.index

#备份到哪个目录
bak_dir=/bak/mysql-binback

#这个脚本的日志输出到哪个文件
log_dir=/tmp/mybak-binlog.log

#保存的天数，4周就是28天
save_day=10

#[自动变量]
#当前年
date_nian=`date +%Y-`

begin_time=`date +%F-%H-%M-%S`

#所有天数的数组
save_day_zu=($(for i in `seq 1 ${save_day}`;do date -d -${i}days "+%F";done))

#开始
/usr/bin/echo >> ${log_dir}
/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:开始增量备份" >> ${log_dir}

#检查
${my_sql} $* -e "show databases;" &> /tmp/info_error.txt
if [[ $? -ne 0 ]];then
    /usr/bin/echo "time:$(date +%F-%H-%M-%S) info:登陆命令错误" >> ${log_dir}
    /usr/bin/cat /tmp/info_error.txt #如果错误则显示错误信息
    exit 1
fi

#移动到目录
cd ${bak_dir}
bak_time=`date +%F-%H-%M`
bak_timetwo=`date +%F`

#刷新
${my_sql} $* -e "flush logs"
if [[ $? -ne 0 ]];then
    /usr/bin/echo "time:$(date +%F-%H-%M-%S) error:刷新binlog失败" >> ${log_dir}
    exit 1
fi

#获取开头和结尾binlog名字
last_bin=`cat /tmp/binlog-section`
next_bin=`tail -n 1 ${binlog_dir}/mysql-bin.index`
echo ${last_bin} |grep 'mysql-bin' &> /dev/null
if [[ $? -ne 0 ]];then
    echo "mysql-bin.000001" > /tmp/binlog-section #不存在则默认第一个
    last_bin=`cat /tmp/binlog-section`
fi

#截取需要备份的binlog行数
a=`/usr/bin/sort ${binlog_dir}/mysql-bin.index | uniq | grep -n ${last_bin} | awk -F':' '{print $1}'`
b=`/usr/bin/sort ${binlog_dir}/mysql-bin.index | uniq | grep -n ${next_bin} | awk -F':' '{print $1}'`

let b--

#输出最新节点
/usr/bin/echo "${next_bin}" > /tmp/binlog-section

#创建文件
rm -rf mybak-section-${bak_time}
/usr/bin/mkdir mybak-section-${bak_time}

for i in `sed -n "${a},${b}p" ${binlog_dir}/mysql-bin.index  | awk -F'./' '{print $2}'`
do
    if [[ ! -f ${binlog_dir}/${i} ]];then
        /usr/bin/echo "time:$(date +%F-%H-%M-%S) error:binlog文件${i} 不存在" >> ${log_dir}
        exit 1
    fi

cp -rf ${binlog_dir}/${i} mybak-section-${bak_time}/
    if [[ ! -f mybak-section-${bak_time}/${i} ]];then
        /usr/bin/echo "time:$(date +%F-%H-%M-%S) error:binlog文件${i} 备份失败" >> ${log_dir}
        exit 1
    fi
done

#压缩
if [[ -f mybak-section-${bak_time}.tar.gz ]];then
    /usr/bin/echo "time:$(date +%F-%H-%M-%S) info:压缩包mybak-section-${bak_time}.tar.gz 已存在" >> ${log_dir}
    /usr/bin/rm -irf mybak-section-${bak_time}.tar.gz
fi

/usr/bin/tar -cf mybak-section-${bak_time}.tar.gz mybak-section-${bak_time}
if [[ $? -ne 0 ]];then
    /usr/bin/echo "time:$(date +%F-%H-%M-%S) error:压缩失败" >> ${log_dir}
    exit 1
fi

#删除binlog文件夹
/usr/bin/rm -irf mybak-section-${bak_time}
if [[ $? -ne 0 ]];then
    /usr/bin/echo "time:$(date +%F-%H-%M-%S) info:删除sql文件失败" >> ${log_dir}
    exit 1
fi

#整理压缩的日志文件
for i in `ls | grep "^mybak-section.*tar.gz$"`
   do
    echo $i | grep ${date_nian} &> /dev/null
        if [[ $? -eq 0 ]];then
            a=`echo ${i%%.tar.gz}`
            b=`echo ${a:(-16)}` #当前日志年月日
            c=`echo ${b%-*}`
            d=`echo ${c%-*}`

#看是否在数组中，不在其中，并且不是当前时间，则删除。
            echo ${save_day_zu[*]} |grep -w $d &> /dev/null
            if [[ $? -ne 0 ]];then
                [[ "$d" != "$bak_timetwo" ]] && rm -rf $i
            fi
        else
            #不是当月的，其他类型压缩包，跳过
            continue
        fi
done

#结束
last_time=`date +%F-%H-%M-%S`
/usr/bin/echo "begin_time:${begin_time}   last_time:${last_time}" >> ${log_dir}
/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:增量备份完成" >> ${log_dir}
/usr/bin/echo >> ${log_dir}
```

用于主从，备份relay-bin，要指定备份目录位置和其它变量
`vim  /root/bin/mybak-binlog.sh`
```python
#!/bin/bash
#
# 注意：执行脚本前修改脚本中的变量
# 功能：cp方式增量备份
#
# 适用：centos6+
# 语言：中文
#
#使用：./xx.sh -uroot -p'123456'

#[变量]
#mysql这个命令所在绝对路径
my_sql="/usr/local/mysql/bin/mysql"

#mysqldump命令所在绝对路径
bak_sql="/usr/local/mysql/bin/mysqldump"

#binlog日志所在目录
binlog_dir=/usr/local/mysql/data

#mysql-bin.index文件所在位置
binlog_index=${binlog_dir}/mysql-bin.index

#备份到哪个目录
bak_dir=/bak/mysql-binback

#这个脚本的日志输出到哪个文件
log_dir=/tmp/mybak-binlog.log

#保存的天数，4周就是28天
save_day=10

#[自动变量]
#当前年
date_nian=`date +%Y-`
begin_time=`date +%F-%H-%M-%S`

#所有天数的数组
save_day_zu=($(for i in `seq 1 ${save_day}`;do date -d -${i}days "+%F";done))

#开始
/usr/bin/echo >> ${log_dir}
/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:开始增量备份" >> ${log_dir}

#检查
${my_sql} $* -e "show databases;" &> /tmp/info_error.txt
if [[ $? -ne 0 ]];then
    /usr/bin/echo "time:$(date +%F-%H-%M-%S) info:登陆命令错误" >> ${log_dir}
    /usr/bin/cat /tmp/info_error.txt #如果错误则显示错误信息
    exit 1
fi

#移动到目录
cd ${bak_dir}
bak_time=`date +%F-%H-%M`
bak_timetwo=`date +%F`

#创建文件
rm -rf mybak-section-${bak_time}
/usr/bin/mkdir mybak-section-${bak_time}

for i in `ls ${binlog_dir}| grep relay-bin`
do

cp -rf ${binlog_dir}/${i} mybak-section-${bak_time}/
    if [[ ! -f mybak-section-${bak_time}/${i} ]];then
        /usr/bin/echo "time:$(date +%F-%H-%M-%S) error:binlog文件${i} 备份失败" >> ${log_dir}
        exit 1
    fi
done

#压缩
if [[ -f mybak-section-${bak_time}.tar.gz ]];then
    /usr/bin/echo "time:$(date +%F-%H-%M-%S) info:压缩包mybak-section-${bak_time}.tar.gz 已存在" >> ${log_dir}
    /usr/bin/rm -irf mybak-section-${bak_time}.tar.gz
fi

/usr/bin/tar -cf mybak-section-${bak_time}.tar.gz mybak-section-${bak_time}
if [[ $? -ne 0 ]];then
    /usr/bin/echo "time:$(date +%F-%H-%M-%S) error:压缩失败" >> ${log_dir}
    exit 1
fi

#删除binlog文件夹
/usr/bin/rm -irf mybak-section-${bak_time}
if [[ $? -ne 0 ]];then
    /usr/bin/echo "time:$(date +%F-%H-%M-%S) info:删除sql文件失败" >> ${log_dir}
    exit 1
fi

#整理压缩的日志文件
for i in `ls | grep "^mybak-section.*tar.gz$"`
   do
    echo $i | grep ${date_nian} &> /dev/null
        if [[ $? -eq 0 ]];then
            a=`echo ${i%%.tar.gz}`
            b=`echo ${a:(-16)}` #当前日志年月日
            c=`echo ${b%-*}`
            d=`echo ${c%-*}`

#看是否在数组中，不在其中，并且不是当前时间，则删除。
            echo ${save_day_zu[*]} |grep -w $d &> /dev/null
            if [[ $? -ne 0 ]];then
                [[ "$d" != "$bak_timetwo" ]] && rm -rf $i
            fi
        else
            #不是当月的，其他类型压缩包，跳过
            continue
        fi
done

#结束
last_time=`date +%F-%H-%M-%S`
/usr/bin/echo "begin_time:${begin_time}   last_time:${last_time}" >> ${log_dir}
/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:增量备份完成" >> ${log_dir}
/usr/bin/echo >> ${log_dir}
```

### 重写备份
创建备份目录
`mkdir -p /bak/xback`

编写脚本，要指定备份目录位置
`vim /root/bin/mybak-rewrite.sh`
```python
#!/bin/bash
#xbak备份脚本
#每周六执行一次
#10 4 * * 6 /bin/bash /root/bin/mybak-rewrite.sh

#清理并备份
[[ -d /bak/xback ]] || mkdir -p /bak/xback
cd /bak/xback
rm -rf *.tar.gz

[[ -d bak/mysql-xback ]] || echo "bak-dir not found"
cd /bak/mysql-xback
tar -cf XtraBackup.tar.gz *
mv XtraBackup.tar.gz /bak/xback
rm -rf  /bak/mysql-xback/*

#全备份一次
bash /root/bin/mybak-all.sh
```

### 备份周期
添加权限
`chmod +x /root/bin/*`

先进行一次innobackupex全量备份，后面的增量均在全量的基础上备份。
`bash /root/bin/mybak-all.sh`

每天2点进行一次innobackupex增量备份+binlog日志备份
每周4点将之前的innobackupex备份打包，并启动新的全量备份
crontab -e
```python
30 2 * * * /bin/bash /root/bin/mybak-binlog.sh -uback -p'123456'
40 2 * * * /bin/bash /root/bin/mybak-section.sh
10 4 * * 6 /bin/bash /root/bin/mybak-rewrite.sh
```

## 四.容灾测试
写入测试数据
创建脚本，脚本将创建一个single库，s1表，持续写入数据。
`vim /root/bin/mysql_test.sh`
```python
#!/bin/bash
#混合测试数据库脚本
#将创建一个single数据库，其中创建一个s1表
#如果数据库存在，将会写入数据，可以在写入部分sleep 1 来让数据持续写入
#使用方法 ./xx.sh -uroot -p'123456'

#检查
mysql $* -e "show databases;" &> /tmp/info_error.txt
if [[ $? -ne 0 ]];then
    echo "time:$(date +%F-%H-%M-%S) info:登陆命令错误"
    cat /tmp/info_error.txt #如果错误则显示错误信息
    echo
    echo "./xx.sh -uroot -p'123456'"
    exit 1
fi

#检查库是否存在
mysql $* -e "use single;" &> /tmp/info_error.txt
if [[ $? -eq 0 ]];then
    mysql $* -e "use single;select * from s1 where id=1;"
    if [[ $? -ne 0 ]];then
        mysql $* -e "use single;drop table s1;"
        mysql $* -e "use single;create table s1(id int AUTO_INCREMENT PRIMARY KEY,name char(20),age int);"
    fi
else
    mysql $* -e "create database single;"
    mysql $* -e "use single;create table s1(id int AUTO_INCREMENT PRIMARY KEY,name char(20),age int);"
fi

#name随机数
random_name() {
    local zu=(q w e r t y u i o p a s d f g h j k l z x c v b n m)

for i in `seq 1 5`
    do
        local a=`echo $[RANDOM%24]`
        echo -n ${zu[a]}
    done
}

#age随机数
random_age() {
    local a=`echo $[RANDOM%99]`
    echo $a
}

#写入部分
for i in `seq 1 1000`
do
    b=`random_name`
    c=`random_age`
    sleep 2
    mysql $* -e "use single;insert into s1(name,age) values('${b}',${c});"
done
```

执行脚本，持续写入测试数据
`bash /root/bin/mysql_test.sh -uroot -p'123456'`

### 模拟备份
1.开启另一个终端，检查数据是否在写入
`mysql -uroot -p'123456' -e "use single;select count(*) from s1;"`

2.进行全备份，返回ok正确
`cd /root/bin`
`bash mybak-all.sh`

3.模拟第一天晚上，因为在持续写入数据，等一会再进行增量备份，并备份binlog
`bash mybak-section.sh`
`bash mybak-binlog.sh -uback -p'123456'`

4.等一会，模拟第二天晚上
`bash mybak-section.sh`
`bash mybak-binlog.sh -uback -p'123456'`

### 错误恢复
1.模拟出现问题了，停止写入
ctl + c

2.查看当前总数据，为129条
`mysql -uroot -p'123456' -e "use single;select count(*) from s1;"`

3.模拟误删除数据库
`mysql -uroot -p'123456' -e "drop database single;"`

4.对最新的状态备份binlog
`bash mybak-binlog.sh -uback -p'123456'`

5.关闭mysql
`systemctl stop mysql`

6.删除数据目录，否则无法恢复
`rm -fr /usr/local/mysql/data/*`

7.对全备份进行封装，返回ok正确
`innobackupex --apply-log --redo-only /bak/mysql-xback/all-backup/`

8.合并第一次模拟到全备份，最后一个参数是指定全备份
`innobackupex --apply-log --redo-only --incremental-dir=/bak/mysql-xback/2018-12-12-14-51-26/  /bak/mysql-xback/all-backup/`

9.合并第二次模拟到全备份
`innobackupex --apply-log --redo-only --incremental-dir=/bak/mysql-xback/2018-12-12-14-52-47/  /bak/mysql-xback/all-backup/`

10.开始恢复
`innobackupex --copy-back /bak/mysql-xback/all-backup/`

11.添加权限并启动
`chown -R mysql.mysql /usr/local/mysql/data/`
`systemctl start mysql`

12.查看数据恢复到125条，正常前是129条
`mysql -uroot -p'123456' -e "use single;select count(*) from s1;"`

13.查看备份时的pos点
`cat /bak/mysql-xback/2018-12-12-14-52-47/xtrabackup_info  | grep binlog_pos`

显示如下
binlog_pos = filename 'mysql-bin.000003', position '1509'

14.解压最后2个binlog压缩包
`cd /bak/mysql-binback/`
`tar -xf mybak-section-2018-12-12-15-07.tar.gz`
`tar -xf mybak-section-2018-12-12-15-15.tar.gz`

15.进入第二个包，它是在最后一次增量备份后才执行的binlog
`cd mybak-section-2018-12-12-15-07`

将1509pos点之前的行删除，每个binlog文件前18行要保留
`mysqlbinlog mysql-bin.000003 > 03.log`
`cat 03.log | grep -n 1509`
![file](http://linkdevops.oss-cn-beijing.aliyuncs.com/wp-content/uploads/2020/05/image-1589164019071.png)

恢复数据
`cat 03.log | mysql -uroot -p'123456'`

当前总是为128
`mysql -uroot -p'123456' -e "use single;select count(*) from s1;"`

16.进入倒数第一个包，它存放出问题之前的数据
`cd mybak-section-2018-12-12-15-15`

将drop这个有问题的指令之后的行删除
`mysqlbinlog mysql-bin.000004 > 04.log`
`cat 04.log | grep -n drop`
![file](http://linkdevops.oss-cn-beijing.aliyuncs.com/wp-content/uploads/2020/05/image-1589164026727.png)

恢复数据
`cat 04.log | mysql -uroot -p'123456'`

当前总是为129
`mysql -uroot -p'123456' -e "use single;select count(*) from s1;"`

## 五.重写测试
进入到脚本目录
`cd /root/bin`

执行重写脚本
`bash mybak-rewrite.sh`

可以看到原先目录只有一个全备份
`ls /bak/mysql-xback/`

原先的全备份和增量备份的打包
`ls -lh /bak/xback/`