原创：胖狗与飞鸟 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：
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
<
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)
消耗时间：
real53m12.712s
user3m26.451s
sys2m8.757s
886
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
