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

安装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`

配置binlog

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  #第二次增量备份基于全备目录

数据库出现故障

分析数据库故障原因

drop数据库

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

update

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日志回滚数据。为最终恢复数据做准备。

开始恢复binlog日志数据

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 <bin.sql   #恢复binlog数据

自动化备份脚本

基础配置脚本文件/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