1.完全备份

#192.168.88.8
#准备坏境Mysql8.0
mysql> create database db1;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


[root@C8 ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.26-18/binary/redhat/8/x86_64/percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpm

[root@C8 ~]# yum install -y ./percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpm

[root@C8 ~]# mkdir /backup

[root@C8 ~]# xtrabackup -uroot -p123456   --backup --target-dir=/backup/data    #完全备份数据

[root@C8 ~]# ll /backup/data/
total 72756
-rw-r-----. 1 root root      475 Oct 23 22:56 backup-my.cnf
-rw-r-----. 1 root root      156 Oct 23 22:56 C8-bin.000005
-rw-r-----. 1 root root       16 Oct 23 22:56 C8-bin.index
drwxr-x---. 2 root root       20 Oct 23 22:56 db1
-rw-r-----. 1 root root     6083 Oct 23 22:56 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Oct 23 22:56 ibdata1
drwxr-x---. 2 root root      143 Oct 23 22:56 mysql
-rw-r-----. 1 root root 28311552 Oct 23 22:56 mysql.ibd
drwxr-x---. 2 root root     8192 Oct 23 22:56 performance_schema
drwxr-x---. 2 root root       28 Oct 23 22:56 sys
-rw-r-----. 1 root root 16777216 Oct 23 22:56 undo_001
-rw-r-----. 1 root root 16777216 Oct 23 22:56 undo_002
-rw-r-----. 1 root root       18 Oct 23 22:56 xtrabackup_binlog_info
-rw-r-----. 1 root root      102 Oct 23 22:56 xtrabackup_checkpoints
-rw-r-----. 1 root root      467 Oct 23 22:56 xtrabackup_info
-rw-r-----. 1 root root     2560 Oct 23 22:56 xtrabackup_logfile
-rw-r-----. 1 root root       39 Oct 23 22:56 xtrabackup_tablespaces

 scp -r /backup 192.168.88.18:/    #传输备份数据到恢复主机

#192.168.88.18
[root@C18 ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.26-18/binary/redhat/8/x86_64/percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpm

[root@C18 ~]# yum install -y ./percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpm

#预准备：确保数据一致，提交完成的事务，回滚未完成的事务
[root@C18 ~]# xtrabackup --prepare --target-dir=/backup/data

#拷贝备份数据到mysql数据目录，需提前删除mysql数据目录
[root@C18 ~]# xtrabackup --copy-back --target-dir=/backup/data

#更改权限
[root@C18 ~]# chown -R mysql. /var/lib/mysql
 
[root@C18 ~]# systemctl start mysqld.service
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

2.增量备份+binlog恢复

#在以上基础实现，增量备份恢复
#192.168.88.8

#第一次修改数据
mysql> use db1
Database changed
mysql> create table std  (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| std           |
+---------------+
1 row in set (0.00 sec)

#第一次增量备份
[root@C8 ~]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/data

#第二次修改数据
mysql> insert into std values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from std;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

#第二次增量备份
[root@C8 ~]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1

#第三次修改数据
mysql> insert into std values (4),(5),(6),(7);

#查看增量备份时间后的binlog日记 --start-position= 点
mysqlbinlog /var/lib/mysql/C8-bin.000007

CigB19+9jw==
'/*!*/;
# at 125
#211023 23:41:48 server id 8  end_log_pos 156 CRC32 0x8179c6e8 	Previous-GTIDs   #我第二次增量备份的时间
# [empty]
# at 156
#211024  0:00:16 server id 8  end_log_pos 235 CRC32 0x24331521 	Anonymous_GTID	last_committed=0	sequence_number=1   rbr_only=yes	original_committed_timestamp=1635004816739336	immediate_commit_timestamp=1635004816739336	transaction_length=292



#传输备份数据
[root@C8 ~]# scp -r /backup 192.168.88.18:/

#导出为备份
mysqlbinlog --start-position=156 /var/lib/mysql/C8-bin.000007 > back.sql
scp back.sql 192.168.88.18:


#192.168.88.18
#预准备
[root@C18 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/data
#合并第1次增量备份到完全备份
[root@C18 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/data --incremental-dir=/backup/inc1
#合并第2次增量备份到完全备份
[root@C18 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/data --incremental-dir=/backup/inc2

#还原数据到数据库目录
[root@C18 ~]# xtrabackup --copy-back --target-dir=/backup/data

[root@C18 ~]# chown -R mysql. /var/lib/mysql

[root@C18 ~]# systemctl start mysqld.service

#恢复第二次增量备份后的数据
mysql> source /root/back.sql
mysql> use db1
Database changed
mysql> select * from std;
+------+
| id   |
+------+
|    1 |
|    2 |
|    4 |
|    5 |
|    6 |
|    7 |
+------+
6 rows in set (0.00 sec)