实战：使用 xtrabackup 为 mysql 做定时增量备份（阿里云的云栖社区发微博每次都要审核半天都通过不了）

[williamfan]

原创：胖狗与飞鸟 qq14808540

生产环境架构：mysql5.6，一主一从。定期增量备份在 slave 端实施，slave 配置为 2 核 4G，挂在 SSD 数据盘。

xtrabackup 简介

包含两个工具：

xtrabackup：是用于热备份 innodb, xtradb 表中数据的工具，不能备份其他类型的表，也不能备份数据表结构； innobackupex：是将 xtrabackup 进行封装的 perl 脚本，提供了备份 myisam 表的能力。

安装：

yum install rsync numactl
rpm -ivh ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/libev-4.15-1.el6.rf.x86_64.rpm

#安装 xtrabackup
mkdir /home2/soft/percona-xtrabackup
cd /home2/soft
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.5/binary/redhat/6/x86_64/Percona-XtraBackup-2.4.5-re41c0be-el6-x86_64-bundle.tar

tar xvf Percona-XtraBackup-2.4.5-re41c0be-el6-x86_64-bundle.tar -C /home2/soft/percona-xtrabackup
rpm -ivh percona-xtrabackup-24-2.4.5-1.el6.x86_64.rpm 
rpm -ivh percona-xtrabackup-24-debuginfo-2.4.5-1.el6.x86_64.rpm 
rpm -ivh percona-xtrabackup-test-24-2.4.5-1.el6.x86_64.rpm

github 上有高手提供了封装脚本，用起来更方便：

cd /home2/soft
wget https://github.com/bshp/xtrabackup/archive/master.zip -O xtrabackup.zip
unzip xtrabackup.zip

创建用于导出的账号

mysql>
CREATE USER 'backup-user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, SHOW VIEW, RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'backup-user'@'localhost';
FLUSH PRIVILEGES;

全量备份（耗时较长，未使用）

mkdir /home2/tmp
mkdir /home2/log
#注意需要进入到 /home2/soft/xtrabackup/bash 目录执行
cd /home2/scripts/xtrabackup-master/bash
sh ./xb-backup-fs.sh --mysql-user=backup-user --mysql-passwd=showmethemoney123456 --tmp-dir=/home2/tmp --backup-repository=/home2/fullbackup --log-file=/home2/log/xb-backup-fs.log --verbose

增量备份

mkdir /home2/tmp
mkdir /home2/log

#首先执行第一次全量备份
cd /home2/scripts/xtrabackup-master/bash
sh ./xb-backup-incremental.sh --cycle-repository=/home2/cyclebackup/cycle --mysql-user=backup-user --mysql-passwd=showmethemoney123456 --tmp-dir=/home2/tmp --data-dir=/home2/cyclebackup/data-dir --log-file=/home2/log/xb-backup-fs.log --verbose

#再定期进行第二次增量备份
cd /home2/scripts/xtrabackup-master/bash
sh ./xb-backup-incremental.sh --cycle-repository=/home2/cyclebackup/cycle --mysql-user=backup-user --mysql-passwd=showmethemoney123456 --tmp-dir=/home2/tmp --data-dir=/home2/cyclebackup/data-dir --log-file=/home2/log/xb-backup-fs.log --verbose --increment

自动备份并同步至备份机

vi /home2/scripts/increase_backup.sh

cd /home2/scripts/xtrabackup-master/bash
sh ./xb-backup-incremental.sh --cycle-repository=/home2/cyclebackup/cycle --mysql-user=backup-user --mysql-passwd=password --tmp-dir=/home2/tmp --data-dir=/home2/cyclebackup/data-dir --log-file=/home2/log/xb-backup-fs.log --verbose --increment
/usr/bin/rsync -rtP /home2/cyclebackup [email protected]::mysql/ --password-file=/home2/scripts/rsync.pw

添加 crontab 条目，每小时执行一次

MAILTO=""
1 * * * * /home2/scripts/increase_backup.sh > /var/log/backup.log

在备份机上恢复增量备份

目录：/home1/mysql 增量备份的恢复需要有 3 个步骤

• 1 恢复完全备份
• 2 恢复增量备份到完全备份(开始恢复的增量备份要添加--redo-only 参数,到最后一次增量备份要去掉--redo-only)
• 3 对整体的完全备份进行恢复，回滚未提交的数据

#恢复 base 包及 1-5 号增量包
service mysqld stop
cd /home1/scripts/xtrabackup-master/bash
sh ./xb-restore-incremental.sh -b /home1/backup/mysql/cyclebackup/cycle/20170505_0959/backup_base_20170505_0959.tar.gz -i /home1/backup/mysql/cyclebackup/cycle/20170505_0959/INC/backup_inc_5_20170513_2001.tar.gz --tmp-dir=/home1/tmp --log-file=/home1/log/xb-restore-incremental.log

改进

对这个 shell 进行改进： 生产系统 mysql 占用了 16G 的数据，一次全量备份时间约 15 分钟，其中最后一步是将导出的数据压缩打包，消耗了大量的时间和磁盘 IO，备份服务器恢复时又需要解压。分别修改 xb-backup-incremental.sh 和 xb-restore-incremental.sh 绕开 base 的压缩和解压（但在还原备份时还是会先 cp 一个备份到 mysql 的 data-dir，会消耗 IO ）。直接放上 diff：

在 slave 上修改 xb-backup-incremental.sh：

diff xb-backup-incremental-old.sh xb-backup-incremental.sh 
174a175
>   _xb_base_dir="${archive_repository}/backup_base_${timestamp}"
212a214,216
> xb_base_move_dir() {
>   mv "${FLAGS_tmp_dir}/backup" "${_xb_base_dir}"
> }
239c243,248
<   xb_archive_backup || bail "An exception occured while trying to archive the backup."
---
>   #不压缩 base，只压缩增量
>   if [[ ${FLAGS_increment} -eq ${FLAGS_true} ]]; then
>     xb_archive_backup || bail "An exception occured while trying to archive the backup."
>   else
>     xb_base_move_dir || bail "An exception occured while trying to move the base backup."
>   fi
246d254
< 

在备份及上修改 xb-restore-incremental.sh：

diff ./xb-restore-incremental-old.sh ./xb-restore-incremental.sh 
104c104,105
< 	${_archiver_bin} -xvpzf ${FLAGS_base_backup} -C ${FLAGS_data_dir}
---
> 	#${_archiver_bin} -xvpzf ${FLAGS_base_backup} -C ${FLAGS_data_dir}
> 	cp -Rf ${FLAGS_base_backup}/* ${FLAGS_data_dir}/
107c108,109
< 	${_archiver_bin} -xpzf ${FLAGS_base_backup} -C ${FLAGS_data_dir}
---
> 	#${_archiver_bin} -xpzf ${FLAGS_base_backup} -C ${FLAGS_data_dir}
> 	cp -Rf ${FLAGS_base_backup}/* ${FLAGS_data_dir}/*
181a184
>     #这里不再需要解压 base，因为 base 不会有压缩包，是直接从 base_dir 拷贝一份到 /home1/mysql
183,184c186
<     cmd "cp -Rf /home1/mysql-base-backup ${FLAGS_data_dir}"
<     msg_ok "Base backup uncompressed and prepared."
---
>     msg_ok "Base backup copyed and prepared."

恢复命令中-b 参数设为 base 目录即可（原来是 base 打包的 zip 路径）：

sh ./xb-restore-incremental.sh -b /home1/backup/mysql/cyclebackup/cycle/20170517_1642/backup_base_20170517_1642 -i /home1/backup/mysql/cyclebackup/cycle/20170517_1642/INC/backup_inc_2_20170517_1705.tar.gz --tmp-dir=/home1/tmp --log-file=/home1/log/xb-restore-incremental.log --verbose

20170520 改进：删掉 5 天前的 base 及其后续增量，重新打 base，一共 3 个定时备份脚本进行滚动

cd /home2/scripts/xtrabackup-master/bash

if [ -e /home2/mysql_$1/cyclebackup/cycle/*/INC/ ]
then
        expire_num=`/bin/find /home2/mysql_$1/cyclebackup/cycle/*/INC/ -type f -mtime +3 | /usr/bin/wc -l`
        if [ -n expire_num ]
        then
                /bin/rm -Rf /home2/mysql_$1/cyclebackup/*
        fi
fi
if [ ! -e /home2/mysql_$1/cyclebackup/data-dir/xb_incremental_cycle_data.txt ]
then
        echo "base backup creating..."
        /bin/mkdir /home2/mysql_$1/cyclebackup/cycle
        /bin/mkdir /home2/mysql_$1/cyclebackup/data-dir
        sh ./xb-backup-incremental.sh --cycle-repository=/home2/mysql_$1/cyclebackup/cycle --mysql-user=backup-user --mysql-passwd=password --tmp-dir=/home2/tmp --data-dir=/home2/mysql_$1/cyclebackup/data-dir --log-file=/home2/log/xb-backup-fs.log --verbose
        echo "base backup created."
fi

echo "increasing backup creating..."
sh ./xb-backup-incremental.sh --cycle-repository=/home2/mysql_$1/cyclebackup/cycle --mysql-user=backup-user --mysql-passwd=password --tmp-dir=/home2/tmp --data-dir=/home2/mysql_$1/cyclebackup/data-dir --log-file=/home2/log/xb-backup-fs.log --verbose --increment
echo "increasing backup created."
echo "rsync starting."
/usr/bin/rsync -rtP --delete /home2/mysql_$1/cyclebackup [email protected]::mysql_$1/ --password-file=/home2/scripts/rsync.pw
echo "rsync end."

crontab 注意在 3 日后添加第二行，6 日后添加第三行

MAILTO=""
1 1 * * * sh /home2/scripts/increase_backup.sh bak01 > /var/log/backup.log
1 2 * * * sh /home2/scripts/increase_backup.sh bak02 > /var/log/backup.log
1 3 * * * sh /home2/scripts/increase_backup.sh bak03 > /var/log/backup.log

2017.05.25 补充

测试恢复了 5 日（ 5.20-5.25 ）备份 硬件资源：ECS 1 核 CPU，2GB 内存 数据大小：13G base 未压缩备份+4.7G 增量备份(124 个 tar.gz) 消耗时间： real 53m12.712s user 3m26.451s sys 2m8.757s

咦，为啥没法修改主题？

increase_backup.sh 脚本有个错：
if [ -n expire_num ]
应该改成：
if [ expire_num -gt 0 ]

还有节点好像弄错了，应该发到 linux 或 MySQL 节点