Xtrabackup 全备 增量 差异 恢复数据 自动化备份脚本

摘要：为最终恢复数据做准备。如果是差异备份全备和差异备份合并。恢复数据自动化备份脚本基础配置脚本文件全备份脚本文件增量备份脚本文件差异备份脚本文件

wget -O /etc/yum.repos.d /epel.repo http://mirrors.aliyun.com/repo/epel-6.repo  #配置epel源
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL #安装Xtrabackup软件需要的基础环境包
wget https://www.percona.com/downloads/XtraBackup/PerconaXtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
ls -l percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
ls -l `which xtrabackup innobackupex`

mkdir /var/bin-log/binlog -p
chown -R mysql.mysql /var/bin-log/binlog
egrep -v "#|^$" /etc/my.cnf 
向 /etc/my.cnf加入两行
log-bin=/var/bin-log/binlog/bin.log  #mysql5.6之前是log-bin之后是log_bin
expire_logs_days=7
binlog_format=mixed
max_binlog_size=100m
binlog_cache_size=4m
max_binlog_cache_size=512m
service mysqld restart

Xtrabackup命令，专门用于对InnoDB和XtraDB等事务引擎的数据库热备份的工具，不能用于备份myIsam等其他类型的引擎数据，它的主要特点是备份数据时完全不用锁表。

Innobackupex命令，将上述Xtrabackup命令使用perl脚本进行二次封装的工具，除了可以用于InnoDB和XtraDB等引擎之外，还可以备份MyISAM及多种引擎混合使用场景，它的主要特点是备份事务引擎数据而不用锁表，可以备份非事务引擎数据，但要锁表。

mkdir /application/mysql/data -p #创建一个备份目录
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --socket=/var/lib/mysql/mysql.sock --no-timestamp /application/mysql/data/full #全备

cd /application/mysql/data/full
ls -l

最终结果如下：
总用量 18472
drwxr-x--- 2 root root 12288 3月 22 02:15 3ideapc
-rw-r----- 1 root root 417 3月 22 02:15 backup-my.cnf #配置文件备份
-rw-r----- 1 root root 18874368 3月 22 02:14 ibdata1 #共享表空间备份
drwxr-x--- 2 root root 4096 3月 22 02:14 mysql
drwxr-x--- 2 root root 4096 3月 22 02:14 test
-rw-r----- 1 root root 15 3月 22 02:15 xtrabackup_binlog_info
-rw-r----- 1 root root 113 3月 22 02:15 xtrabackup_checkpoints #checkpoints信息 (复制时会以该文件对应的LSN号为依据，将大于给定的LSN号的页数据【增量数据】进行备份，因为要比对全备的LSN号，所以第一次增量备份是基于全备的，以后实施的每一次增量备份是基于上一次的增量备份。)
-rw-r----- 1 root root 556 3月 22 02:15 xtrabackup_info #xtrabackup信息
-rw-r----- 1 root root 2560 3月 22 02:15 xtrabackup_logfile #xtrabackup日志文件

增量备份首先从xtrabackup_binlog_info文件中找到并记录最后一个LSN，从LSN的位置开始复制InnoDB的redo日志到Xtrabackup_logfile,然后开始复制全部数据文件.ibd，等到全部数据复制完成之后，就停止复制logfile。
增量备份与全备的过程基本类似，区别就是第二步，仅复制InnoDB中变化的页数据，而非所有物理文件

.....     #省略增加数据
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --socket=/var/lib/mysql/mysql.sock --no-timestamp --incremental-basedir=/application/mysql/data/full --incremental /application/mysql/data/one_inc   #第一次增量备份基于全备目录
.......... #继续修改数据
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --socket=/var/lib/mysql/mysql.sock --no-timestamp --parallel=3 --incremental-basedir=/application/mysql/data/one_inc --incremental /application/mysql/data/two_inc  #第二次增量备份基于第一次增量备份目录

它与增量备份不同点，就是每次都是基于全备。所以还原的时候，只要将还原点与全备合并就好。

.....     #省略增加数据
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --socket=/var/lib/mysql/mysql.sock --no-timestamp --incremental-basedir=/application/mysql/data/full --incremental /application/mysql/data/one_inc   #第一次增量备份基于全备目录
.......... #继续修改数据
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --socket=/var/lib/mysql/mysql.sock --no-timestamp --parallel=3 --incremental-basedir=/application/mysql/data/full --incremental /application/mysql/data/two_inc  #第二次增量备份基于全备目录

分析数据库故障原因

cp -a /var/bin-log/binlog/* /application/mysql/logs  #移走binlog增量文件，防止被二次破坏

iptables -I input -p tcp --dport 3306 ! -s 本机Ip -j DROP #不是本机Ip禁止访问数据库
cp -a /var/bin-log/binlog/* /application/mysql/logs  #移走binlog增量文件，防止被二次破坏

数据恢复的步骤是先恢复全备目录，然后恢复第一次增量数据，然后第二次，第三次......

非最后一次合并增量数据一定要加--read-only参数，只应用redo日志恢复数据，而不执行undo回滚未提交的数据。等到最后一次增量备份合并完成之后再进行Undo日志回滚数据。

innobackupex --apply-log --redo-only /application/mysql/data/full/ #应用redo日志恢复全备数据
innobackupex --apply-log --redo-only --incremental-dir=/application/mysql/data/one_inc /application/mysql/data/full/   #合并第一次的增量数据到全备数据目录
innobackupex --apply-log --redo-only --incremental-dir=/application/mysql/data/two_inc /application/mysql/data/full/   #合并第二次的增量数据到全备数据目录
innobackupex --apply-log /application/mysql/data/full/  #取消--redo-only目的是应用Undo日志回滚数据。为最终恢复数据做准备。

innobackupex --apply-log --redo-only /application/mysql/data/full/ #应用redo日志恢复全备数据
innobackupex --apply-log --redo-only --incremental-dir=/application/mysql/data/two_inc /application/mysql/data/full/   #想还原到第二次。就合并第二次的增量数据到全备数据目录
innobackupex --apply-log /application/mysql/data/full/  #取消--redo-only目的是应用Undo日志回滚数据。为最终恢复数据做准备。

cat /application/mysql/data/two_inc/xtrabackup_binlog_info #查看最后一次增量/差异 的binlog位置信息。这个位置信息以后的binlog才是我们需要的，以前的数据在xtrabackup的全量和增量已经有了。假如结果为bin.000001 486,那么就是从bin.000001文件的486的位置点开始恢复增量数据。
ll /var/bin-log/binlog/bin.*      #看问题出现后的binlog，加入这条命令的结果有bin.000002,bin.000003......将上条命令的bin.000001以及之后的文件都是我们需要恢复的
cd /var/bin-log/binlog
mysqlbinlog -d test bin.000001 bin.000002 bin.000003 --start-position=486 -r bin.sql  #用mysqlbinlog解析二进制数据导入到sql文件中,**-d后面加数据库**，-r后面加要被写入的sql文件


----------
如果是drop

grep -w drop bin.sql  #过滤掉drop单词的行
sed -i "/drop database test/d" bin.sql  #删除被drop数据库的test语句
grep -w drop bin.sql


----------

service mysqld stop
ss -lnt|grep 3306
cd /application/mysql
mkdir -p /application/mysql/data_ori1
mv /var/lib/mysql/* /application/mysql/data_ori1
cd /var/lib/mysql
rm -rf *   #原始数据库数据文件要为空
mv /application/mysql/data/full/* /var/lib/mysql/  #full恢复过一次，不能使用第二次。
chown -R mysql.mysql /var/lib/mysql
service mysqld restart 
cd /var/bin-log/binlog
mysql test 
自动化备份脚本
基础配置脚本文件/root/mysql.sh
#! /bin/bash

HOSTNAME="localhost"
USERNAME="root"
PASSWORD="123456"
MYSQL=/usr/bin/mysql

DATAMYSQL=/var/lib/mysql
DATE=`date +%Y%m-%V`
BACKUPDIR=/application/mysql/data
SETMYSQL=/etc/my.cnf
SOCK=/var/lib/mysql/mysql.sock
INNOBACKUPEX=/usr/bin/innobackupex
[ ! -d $BACKUPDIR ] && mkdir $BACKUPDIR -p

全备份脚本文件
#! /bin/bash

source /root/mysql.sh
[ ! -d $BACKUPDIR/$DATE/full ] && mkdir -p $BACKUPDIR/$DATE/full && chmod -R 777 $BACKUPDIR/$DATE/full
$INNOBACKUPEX --defaults-file=$SETMYSQL --user=$USERNAME --password=$PASSWORD --socket=$SOCK --no-timestamp $BACKUPDIR/$DATE/full
增量备份脚本文件
#! /bin/bash

source /root/mysql.sh
I=0

[ ! -d $BACKUPDIR/$DATE/full ] && exit

for file in $( ls $BACKUPDIR/$DATE )
do
  let "J=(++I)";
done
if [ $J -eq 1 ];then
  BASEDIR=full
else
  BASEDIR=$[J-1]"_inc"
fi
NEWDIR=${J}"_inc"
$INNOBACKUPEX --defaults-file=$SETMYSQL --user=$USER --password=$PASSWORD --socket=$SOCK --no-timestamp --incremental-basedir=$BACKUPDIR/$DATE/$BASEDIR --incremental $BACKUPDIR/$DATE/$NEWDIR

差异备份脚本文件
#! /bin/bash

source /root/mysql.sh
I=0

[ ! -d $BACKUPDIR/$DATE/full ] && exit

for file in $( ls $BACKUPDIR/$DATE )
do
  let "J=(++I)";
done

NEWDIR=${J}"_inc"
$INNOBACKUPEX --defaults-file=$SETMYSQL --user=$USER --password=$PASSWORD --socket=$SOCK --no-timestamp --incremental-basedir=$BACKUPDIR/$DATE/full --incremental $BACKUPDIR/$DATE/$NEWDIR