Percona XtraBackup 备份和还原数据库

一、Percona XtraBackup 备份原理

1、完全备份

2、增量备份

3、数据还原备份

二、Percona XtraBackup 剖析

1、全备

2、增量备份

三、Percona Xtrabackup的基本操作

1、Percona Xtrabackup的安装
(1)下载Xtracbackup软件(我的这个是el7平台的)

https://pan.baidu.com/s/1i5OSx7Z      #这个是我自己的云盘地址
官网：https://www.percona.com/

(2)安装依赖包及软件

[root@linux-node2 ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
[root@linux-node2 ~]# rpm -ivh libev-4.15-6.el7.x86_64.rpm
[root@linux-node2 ~]# rpm -ivh percona-xtrabackup-2.3.4-1.el7.x86_64.rpm

2、Percona Xtrabackup的备份还原数据

(1)完全备份
[root@linux-node2 ~]# grep "datadir" /etc/my.cnf    #查看数据目录所在的位置
datadir=/usr/local/mysql/data
[root@linux-node2 ~]# innobackupex --user=root --password=123 /tmp/mysql-backup/
[root@linux-node2 ~]# ls /tmp/mysql-backup/    #查看全备份的内容
2017-10-17_13-08-59

(2)增量备份

[root@linux-node2 ~]# innobackupex --user=root --password=123 --incremental /tmp/mysql-backup/ --incremental-basedir=/tmp/mysql-backup/2017-10-17_13-08-59/
[root@linux-node2 ~]# innobackupex --user=root --password=123 --incremental /tmp/mysql-backup/ --incremental-basedir=/tmp/mysql-backup/2017-10-17_13-12-19/
[root@linux-node2 ~]# ls /tmp/mysql-backup/
2017-10-17_13-08-59  2017-10-17_13-12-19  2017-10-17_13-13-13

(3)还原数据
①停止mysql服务，然后清空数据文件目录，恢复完成后再设置权限

[root@linux-node2 ~]# systemctl stop mysql
[root@linux-node2 ~]# mv /usr/local/mysql/data /usr/local/mysql/data.bak     #这个是模拟不小心将数据删掉
[root@linux-node2 ~]# mkdir /var/lib/mysql/data
[root@linux-node2 ~]# chown mysql. /usr/local/mysql/data -R

②还原数据

[root@linux-node2 ~]# innobackupex --apply-log --redo-only /tmp/mysql-backup/2017-10-17_13-08-59/    #读取全备份的事务日志，不回滚
[root@linux-node2 ~]# innobackupex --apply-log --redo-only /tmp/mysql-backup/2017-10-17_13-08-59/  \
--incremental-dir=/tmp/mysql-backup/2017-10-17_13-12-19/  #将第一次增量备份的事务日志导入到读取全备份的事务日志中，不回滚
[root@linux-node2 ~]# innobackupex --apply-log --redo-only /tmp/mysql-backup/2017-10-17_13-08-59/  \
--incremental-dir=/tmp/mysql-backup/2017-10-17_13-13-13/  #将第二次增量备份的事务日志导入到读取全备份的事务日志中，不回滚
[root@linux-node2 ~]# innobackupex --apply-log /tmp/mysql-backup/2017-10-17_13-08-59/      #回滚事务日志
[root@linux-node2 ~]# innobackupex --copy-back /tmp/mysql-backup/2017-10-17_13-08-59/      #还原数据
[root@linux-node2 ~]# chown mysql. /var/lib/mysql/data -R   #修改权限
[root@linux-node2 ~]# systemctl start mysql  #启动服务

---

备注： 从官网下载安装包

centos6环境
[root@linux-node2 ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/6/x86_64/Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar
[root@linux-node2 ~]# tar -xf Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar
[root@linux-node2 ~]# yum -y install percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm
[root@linux-node2 ~]# which xtrabackup
/usr/bin/xtrabackup

centos7环境
[root@linux-node2 ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/Percona-XtraBackup-2.4.9-ra467167cdd4-el7-x86_64-bundle.tar
[root@linux-node2 ~]# tar -xf Percona-XtraBackup-2.4.9-ra467167cdd4-el7-x86_64-bundle.tar
[root@linux-node2 ~]# yum -y install percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
[root@linux-node2 ~]# which xtrabackup
/usr/bin/xtrabackup

源码包
[root@linux-node2 ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/source/tarball/percona-xtrabackup-2.4.9.tar.gz