xtrabackup 备份恢复实操

待修正

1、安装依赖

yum install -y perl-DBD-MySQL per-DBI perl-Time-HiRes libaio*

yum -y install perl-Digest-MD5

wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/libev-4.15-1.el6.rf.x86_64.rpm

rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm

2，rpm安装

https://pan.baidu.com/s/1sl4jByP

rpm -ivh percona-xtrabackup-24-2.4.6-2.el6.x86_64.rpm

 

3、设置参数，开启slave

delay_key_write=OFF (myism相关)

4、全量备份，使用compress，会生成qp文件，恢复时需要解压

innobackupex --defaults-file=/home/mysql_data/mysql5.7.12-13306/etc/my.cnf --user=abc --password=abc --compress   --compress-threads=8 --no-timestamp /home/backup.mysql/20190213/xbs/xtrabackup/

 

 

5、增量备份

#增量备份1 innobackupex --defaults-file=/home/mysql_data/mysql5.7.12-13306/etc/my.cnf --user=abc --password=abc --no-timestamp --incremental /home/backup.mysql/20190213/xbs/2019021317/ --incremental-basedir=/home/backup.mysql/20190213/xbs/xtrabackup/

 

6、还原一 prepare全量备份

先stop mysql或者kill mysql

解压全备文件

innobackupex --defaults-file=/home/mysql_data/mysql5.7.12-test/etc/my.cnf --user=abc --password=abc --decompress /home/backup.mysql/20190215/xbs/xtrabackup/

###qpress软件安装：yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm && yum install qpress

innobackupex --defaults-file=/home/mysql_data/mysql5.7.12-test/etc/my.cnf --user=abc --password=abc --apply-log --redo-only /home/backup.mysql/20190215/xbs/xtrabackup/

 

7、恢复全量备份

innobackupex --defaults-file=/home/mysql_data/mysql5.7.12-test/etc/my.cnf --user=abc --password=abc --copy-back --rsync /home/backup.mysql/20190215/xbs/xtrabackup/

8、start mysql,然后对比检查表数据量

select count(*) from XXX

 

9、prapare库增量备份

innobackupex --defaults-file=/home/mysql_data/mysql5.7.12-test/etc/my.cnf --user=abc --password=abc --apply-log --redo-only /home/backup.mysql/20190215/xbs/xtrabackup/ --incremental-dir=/home/backup.mysql/20190215/xbs/2019021317/

innobackupex --defaults-file=/home/mysql_data/mysql5.7.12-test/etc/my.cnf --user=abc --password=abc --apply-log /home/backup.mysql/20190215/xbs/xtrabackup/