xtrabackup实现全量+增量+binlog恢复库

实验环境

Centos7

1.下载并安装xtrabackup包 

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.23/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm

 2.在主机做完全备份到/backup目录（备份目录为一级目录时可自动创建目录）

 xtrabackup -uroot -p123456 --backup --target-dir=/backup/

 3.在目标主机上还原mysql

 把备份文件直接scp至目标主机

[root@localhost ~]# scp -r /backup root@192.168.0.116:/

在目标主机上还原

#预准备：确保数据一致，提交完成的事务，回滚未完成的事务
[root@localhost ~]# xtrabackup --prepare --target-dir=/backup
#复制到数据库目录
注意：数据库目录必须为空，MySQL服务不能启动
[root@localhost ~]# xtrabackup --copy-back --target-dir=/backup
#还原属性
[root@localhost ~]# chown -R mysql:mysql /var/lib/mysql

#启动服务
[root@localhost ~]# systemctl start  mysqld

查看是否已经还原成功

[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

增量备份以及还原

新增数据

mysql> create database testdb1;
Query OK, 1 row affected (0.01 sec)

mysql> create database testdb2;
Query OK, 1 row affected (0.00 sec)

第一次增量

xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/

新增数据

mysql> create database testdb3;
Query OK, 1 row affected (0.01 sec)

mysql> create database testdb4;
Query OK, 1 row affected (0.00 sec)

 拷贝到目标主机

[root@localhost ~]# scp -r /backup root@192.168.0.116:/backup1

#备份过程生成三个备份目录

[root@localhost ~]# ls -al /backup1
total 12352
drwxr-x---.  8 root root      261 Aug 29 07:51 .
dr-xr-xr-x. 19 root root     4096 Aug 29 07:51 ..
-rw-r-----.  1 root root      487 Aug 29 07:51 backup-my.cnf
-rw-r-----.  1 root root      316 Aug 29 07:51 ib_buffer_pool
-rw-r-----.  1 root root 12582912 Aug 29 07:51 ibdata1
drwxr-x---.  8 root root     4096 Aug 29 07:51 inc1
drwxr-x---. 10 root root     4096 Aug 29 07:51 inc2
drwxr-x---.  2 root root     4096 Aug 29 07:51 mysql
drwxr-x---.  2 root root     8192 Aug 29 07:51 performance_schema
drwxr-x---.  2 root root     8192 Aug 29 07:51 sys
drwxr-x---.  2 root root      112 Aug 29 07:51 testdb
-rw-r-----.  1 root root       22 Aug 29 07:51 xtrabackup_binlog_info
-rw-r-----.  1 root root      135 Aug 29 07:51 xtrabackup_checkpoints
-rw-r-----.  1 root root      476 Aug 29 07:51 xtrabackup_info
-rw-r-----.  1 root root     2560 Aug 29 07:51 xtrabackup_logfile

备份主机开始还原

1）预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
[root@localhost ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup12）合并第1次增量备份到完全备份
[root@localhost ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup1 --incremental-dir=/backup1/inc1
3）合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
[root@localhost ~]# xtrabackup --prepare --target-dir=/backup1 --incrementaldir=/backup1/inc2
4）复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
[root@localhost ~]# xtrabackup --copy-back --target-dir=/backup1
5）还原属性： 
chown -R mysql.mysql /var/lib/mysql

 6）启动服务：
[root@centos7 ~]#systemctl start mysqld 

查看是否还原

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
| testdb1            |
| testdb2            |
| testdb3            |
| testdb4            |
+--------------------+