x backup mysql_mysql备份恢复与xtrabackup备份

mysqldump优缺点

优点

使用场景：

10G以下的数据库操作简单

缺点

数据量范围：30G --> TB级别 的时候备份、恢复操作很慢，效率低

xtrabackup备份软件

使用场景：

1、数据量大，变换量小

2、数据量小，变化量大

介绍

Xtrabackup是由percona开源的免费数据库热备份软件，它能对InnoDB数据库和XtraDB存储引擎的数据库非阻塞地备份(对于MyISAM的备份同样需要加表锁)；mysqldump备份方式是采用的逻辑备份，其最大的缺陷是备份和恢复速度较慢，如果数据库大于50G，mysqldump备份就不太适合。

Xtrabackup安装完成后有4个可执行文件，其中2个比较重要的备份工具是innobackupex、xtrabackup

1)xtrabackup 是专门用来备份InnoDB表的，和mysql server没有交互；

2)innobackupex 是一个封装xtrabackup的Perl脚本，支持同时备份innodb和myisam，但在对myisam备份时需要加一个全局的读锁。

3)xbcrypt 加密解密备份工具

4)xbstream 流传打包传输工具，类似tar

5)物理备份工具，在同级数据量基础上，都要比逻辑备份性能好的多，特别是在数据量较大的时候，体现的更加明显。

Xtrabackup优点

1)备份速度快，物理备份可靠

2)备份过程不会打断正在执行的事务(无需锁表)

3)能够基于压缩等功能节约磁盘空间和流量

4)自动备份校验

5)还原速度快

6)可以流传将备份传输到另外一台机器上

7)在不增加服务器负载的情况备份数据

8)物理备份工具，在同级数据量基础上，都要比逻辑备份性能要好的多。几十G到不超过TB级别的条件下。但在同数据量级别，物理备份恢复数据上有一定优势。

备份原理

拷贝数据文件、拷贝数据页

对于innodb表可以实现热备。

(1)在数据库还有修改操作的时刻，直接将数据文件备走，此时，备份走的数据对于当前mysql来讲是不一致的。

(2)将备份过程中的redo和undo一并备走。

(3)为了恢复的时候，只要保证备份出来的数据页lsn能和redo lsn匹配，将来恢复的就是一致的数据。redo应用和undo应用。

对于myisam表实现自动锁表拷贝文件。

备份开始时首先会开启一个后台检测进程，实时检测mysql redo的变化，一旦发现有新的日志写入，立刻将日志记入后台日志文件xtrabackup_log中，之后复制innodb的数据文件一系统表空间文件ibdatax，复制结束后，将执行flush tables with readlock,然后复制.frm MYI MYD等文件，最后执行unlock tables,最终停止xtrabackup_log

## xtrabackup的安装

1、安装依赖关系

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo

yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

xtrabackup实践操作

全量备份

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123 /backup/xfull

恢复

1、/etc/init.d/mysqld stop

2、innobackupex --apply-log /backup/xfull/

3、rm -rf /data/mysql/*

4、innobackupex --defaults-file=/etc/my.cnf --copy-back /backup/xfull/

5、chown -R mysql.mysql /data/mysql/

6、/etc/init.d/mysqld start

增量备份与恢复

innobackupex增量备份过程中的"增量"处理，其实主要是相对innodb而言，对myisam和其他存储引擎而言，它仍然是全拷贝(全备份)

增量备份从哪增量？

基于上一次的备份进行增量。

redo默认情况下是一组两个文件，并且有固定大小。其使用的文件是一种轮询使用方式，他不是永久的，文件随时可能被覆盖。

注意：千万不要在业务繁忙时做备份。

备份什么内容？

1、可以使用binlog作为增量

2、自带的增量备份，基于上次备份后的变化的数据页，还要备份在备份过程中的undo、redo变化

操作

1、先进行第一次全备

innobackupex --user=root --password=123 /bakcup/xfull

2、再进行增量备份。这个是在全备的基础上做的，需要指定全量备份的目录：/backup/xfull/ ；增量备份到 /backup/xinc1

innobackupex --user=root --password=123 --incremental --incremental-basedir=/backup/xfull/ /backup/xinc1

恢复

1、先应用全备日志

innobackupex --apply-log --redo-only /backup/xfull/

2、合并增量到全备中(一致性的合并)

innobackupex --apply-log --incremental-dir=/backup/xinc1 /backup/xfull/

innobackupex --apply-log /backup/xfull

3、合并完成进行恢复

使用innobackupex命令进行恢复(推荐)

innobackupex --defaults-file=/etc/my.cnf --copy-back /backup/xfull

chown -R mysql.mysql /date/mysql

数据库备份策略

每周的周日进行一次全备；周一到周六每天做上一天增量，每周轮询一次。

备份方案： xtrabackup全备+增量

备份策略(crontab)：

crontab -e

00 03 1 /root/allbak.sh &>/dev/null //每周一的凌晨3点执行完全备份

vim /root/allbak.sh

#!/bin/bash

[ ! -e /backup ]&& mkdir /backup //新建个文件夹专门放备份文件的

day=date +%F //定义日期是时间

user=root

pass=123456

innobackupex --user $user --password $pass /backup/allbak${day} --no-timestamp //用innobackupex做完全备份

mysql -u$user -p$pass -e "flush logs"

#启用binlog日志，每次完全备份之后，每周刷新一遍binlog日志

00 04 2-7 /root/newbak.sh & >/dev/null //每周二到周7的凌晨4点执行增量备份

#周一全备，如果是周二执行，判断dir1 存在，则增量备份，如果不存在，则判断昨天的增量，存在则执行周三的增量，如果都没有，则全备执行一次吧

vim /root/newbak.sh

#!/bin/bash

d1=date +%F //定义时间，用日期来区分

d2=date +%F -d "-1 days" //找到昨天的日期，好指明上一次备份的备份文件

dir1=/backup/allbak${d2} //昨天做的完全备份文件

dir2=/backup/newbak${d2} //昨天做的增量备份文件

user=root

pass=123456

[ ! -e /backup ]&& mkdir /backup //如果文件不存在，则创建文件夹

if [ -e ${dir1} ];then //判断昨天做的是完全备份

innobackupex --user $user --password $pass --incremental /backup/newbak${d1} --incremental-basedir=${dir1} --no-timestamp //指定昨天备份的完全备份文件

elif [ -e ${dir2} ];then //判断昨天做的是增量备份

innobackupex --user $user --password $pass --incremental /backup/newbak${d1} --incremental-basedir=${dir2} --no-timestamp //指定昨天备份的增量备份文件

else

innobackupex --user $user --password $pass /backup/allbak${d1} --no-timestamp

//昨天既没有做增量备份，又没有做完全备份，则做一次完全备份。

fi

实际应用

binlog日志 默认大小：1G左右，设置 ，配置文件里加一个 max_binlog_size = ？

数据创建阶段

1、创建备份需要的目录

mkdir full inc1 inc2

2、周日全备

innobackupex --user=root --password=123 --no-timestamp /backup/xbackup/full/

3、模拟数据变化

use oldboy

create table test(id int,name char(20),age int);

insert into test values(8,'outman',99);

insert into test values(9,'outgirl',100);

commit;

4、周一增量备份

innobackupex --user=root --password=123 --incremental --no-timestamp --incremental-basedir=/backup/xbackup/full/ /backup/xbackup/inc1

5、模拟数据变化

use oldboy

insert into test values(8,'outman1',119);

insert into test values(9,'outgirl1',120);

commit;

6、周二的增量备份

innobackupex --user=root --password=123 --incremental --no-timestamp --incremental-basedir=/backup/xbackup/inc1 /backup/xbackup/inc2

再插入新的行操作

use oldboy

insert into test values(10,'outman2',19);

insert into test values(11,'outgirl2',10);

commit;

模拟误操作事故

模拟场景，周二下午2点误删除test表

use oldboy;

drop table test;

准备恢复数据

1.准备xtrabackup备份，合并备份

innobackupex --apply-log --redo-only /backup/xbackup/full

innobackupex --apply-log --redo-only --incremental-dir=/backup/xbackup/inc1 /backup/xbackup/full

innobackupex --apply-log --incremental-dir=/backup/xbackup/inc2 /backup/xbackup/full

innobackupex --apply-log /backup/xbackup/full

2．确认binlog起点，准备截取binlog。

cd /backup/xbackup/inc2/

cat xtrabackup_binlog_info

mysql-bin.000001 1121

3.截取到drop操作之前的binlog

mysqlbinlog --start-position=1121 /tmp/mysql-bin.000003

找到drop之前的event和postion号做日志截取，假如 1437 这个可以用mysqlbinlog master-bin.000032|less 打开查看

详细信息：https://blog.csdn.net/u010433704/article/details/54962680?utm_source=blogxgwz8

mysqlbinlog --start-position=1121 --stop-position=1437 /tmp/mysql-bin.000003 >/tmp/incbinlog.sql

4．关闭数据库、备份二进制日志

/etc/init.d/mysqld stop

cd /application/mysql/data/

cp mysql-bin.000001 /tmp

5.删除MySQL所有数据

cd /application/mysql/data/

rm -rf *

恢复数据

1．将全量备份的数据恢复到数据目录下

innobackupex --copy-back /backup/xbackup/full/

chown -R mysql.mysql /application/mysql/data/

/etc/init.d/mysqld start

2.恢复binlog记录

set sql_log_bin=0

source /tmp/incbinlog.sql