xtrabakup增量备份

xtrbakup的安装包下载

[root@czh ~]# mkdir xtrabakup
[root@czh ~]# cd xtrabakup/
[root@czh xtrabakup]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/8/x86_64/Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar
--2021-05-09 13:04:32--  https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/8/x86_64/Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar
正在解析主机 downloads.percona.com (downloads.percona.com)... 162.220.4.222
正在连接 downloads.percona.com (downloads.percona.com)|162.220.4.222|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：76595200 (73M) [application/x-tar]
正在保存至: “Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar”

Percona-XtraBackup- 100%[==================>]  73.05M  3.38MB/s  用时 84s     

2021-05-09 13:05:57 (893 KB/s) - 已保存 “Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar” [76595200/76595200])

[root@czh xtrabakup]# ls
Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar

解压并且安装
[root@czh xtrabakup]# tar xf Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar 
[root@czh xtrabakup]# ls
Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar
percona-xtrabackup-24-2.4.22-1.el8.x86_64.rpm
percona-xtrabackup-24-debuginfo-2.4.22-1.el8.x86_64.rpm
percona-xtrabackup-24-debugsource-2.4.22-1.el8.x86_64.rpm
percona-xtrabackup-test-24-2.4.22-1.el8.x86_64.rpm
percona-xtrabackup-test-24-debuginfo-2.4.22-1.el8.x86_64.rpm
[root@czh xtrabakup]# dnf -y install percona-xtrabackup-24-2.4.22-1.el8.x86_64.rpm
CentOS 

将mysql的套接字文件连接到/var/lib/mysql/mysql.sock

[root@czh ~]# mkdir /var/lib/mysql
[root@czh ~]# ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock
[root@czh ~]# cd /var/lib/mysql
[root@czh mysql]# ls
mysql.sock

进行增量备份

首先创建存放备份数据的目录

[root@czh ~]cd /
[root@czh /]# mkdir backups
[root@czh /]# cd backups/
[root@czh backup]# mkdir all
[root@czh backup]# mkdir all1
[root@czh backup]# mkdir all2

将密码写入配置文件

[root@czh backup]# cat ~/.my.cnf 
[client]
user=root
password=Czh1.com
[innobaukupex]
user=root
password=Czh1.com

首先查看数据库里有什么
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| chenzihan          |
| czh                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

进行全备
mysql> select * from czh.student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
10 rows in set (0.00 sec)

在进行全备
[root@czh /]# xtrabackup --backup --target-dir /backups/all/
xtrabackup: recognized server arguments: --datadir=/opt/mysql_data 
xtrabackup: recognized client arguments: --user=root --password=* --backup=1 --target-dir=/backups/all/ 
210509 13:45:27  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
210509 13:45:27  version_check Connected to MySQL server
210509 13:45:27  version_check Executing a version check against the server...
210509 13:45:27  version_check Done.
···········
···········
210509 13:45:28 [00]        ...done
xtrabackup: Transaction log of lsn (2835743) to (2835752) was copied.
210509 13:45:29 completed OK!
[root@czh /]# cd backups/
[root@czh backups]# ls
all  all1  all2
[root@czh backups]# ll all
总用量 12336
-rw-r-----. 1 root root      487 5月   9 13:45 backup-my.cnf
drwxr-x---. 2 root root       58 5月   9 13:45 chenzihan
drwxr-x---. 2 root root       58 5月   9 13:45 czh
-rw-r-----. 1 root root      362 5月   9 13:45 ib_buffer_pool
-rw-r-----. 1 root root 12582912 5月   9 13:45 ibdata1
drwxr-x---. 2 root root     4096 5月   9 13:45 mysql
drwxr-x---. 2 root root     8192 5月   9 13:45 performance_schema
drwxr-x---. 2 root root     8192 5月   9 13:45 sys
-rw-r-----. 1 root root      135 5月   9 13:45 xtrabackup_checkpoints
-rw-r-----. 1 root root      417 5月   9 13:45 xtrabackup_info
-rw-r-----. 1 root root     2560 5月   9 13:45 xtrabackup_logfile

在表中添加数据

mysql> use czh;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into student(name,age) values('tommao',10);
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | tommao      |   10 |
+----+-------------+------+
11 rows in set (0.00 sec)

在全备的基础上进行增备

[root@czh /]# xtrabackup --backup --target-dir /backups/all1/ --incremental-basedir /backups/all/
xtrabackup: recognized server arguments: --datadir=/opt/mysql_data 
xtrabackup: recognized client arguments: --user=root --password=* --backup=1 --target-dir=/backups/all1/ --incremental-basedir=/backups/all/ 
············
············
210509 13:54:17 completed OK!
[root@czh /]# ll backups/all1
总用量 148
-rw-r-----. 1 root root   487 5月   9 13:54 backup-my.cnf
drwxr-x---. 2 root root    88 5月   9 13:54 chenzihan
drwxr-x---. 2 root root    88 5月   9 13:54 czh
-rw-r-----. 1 root root   362 5月   9 13:54 ib_buffer_pool
-rw-r-----. 1 root root 98304 5月   9 13:54 ibdata1.delta
-rw-r-----. 1 root root    60 5月   9 13:54 ibdata1.meta
drwxr-x---. 2 root root  4096 5月   9 13:54 mysql
drwxr-x---. 2 root root  8192 5月   9 13:54 performance_schema
drwxr-x---. 2 root root  8192 5月   9 13:54 sys
-rw-r-----. 1 root root   139 5月   9 13:54 xtrabackup_checkpoints
-rw-r-----. 1 root root   460 5月   9 13:54 xtrabackup_info
-rw-r-----. 1 root root  2560 5月   9 13:54 xtrabackup_logfile

再次插入数据

mysql> use czh;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into student(name,age) values('jerrylaosu',10);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | tommao      |   10 |
| 13 | jerrylaosu  |   10 |
+----+-------------+------+
12 rows in set (0.00 sec)

进行第二次增备

[root@czh /]# xtrabackup --backup --target-dir /backups/all2/ --incremental-basedir /backups/all1/
210509 14:01:10 completed OK!
[root@czh /]# ll backups/all2
总用量 116
-rw-r-----. 1 root root   487 5月   9 14:01 backup-my.cnf
drwxr-x---. 2 root root    88 5月   9 14:01 chenzihan
drwxr-x---. 2 root root    88 5月   9 14:01 czh
-rw-r-----. 1 root root   362 5月   9 14:01 ib_buffer_pool
-rw-r-----. 1 root root 65536 5月   9 14:01 ibdata1.delta
-rw-r-----. 1 root root    60 5月   9 14:01 ibdata1.meta
drwxr-x---. 2 root root  4096 5月   9 14:01 mysql
drwxr-x---. 2 root root  8192 5月   9 14:01 performance_schema
drwxr-x---. 2 root root  8192 5月   9 14:01 sys
-rw-r-----. 1 root root   139 5月   9 14:01 xtrabackup_checkpoints
-rw-r-----. 1 root root   461 5月   9 14:01 xtrabackup_info
-rw-r-----. 1 root root  2560 5月   9 14:01 xtrabackup_logfile
[root@czh /]# 

恢复备份

误删数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| chenzihan          |
| czh                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> drop database czh;
Query OK, 1 row affected (0.01 sec)

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

防止全备和增量备份日志回滚（最后一次的增量备份除外）

[root@czh backups]# xtrabackup --prepare --apply-log-only --target-dir /backups/all/
[root@czh backups]# xtrabackup --prepare --apply-log-only --target-dir /backups/all/ --incremental-dir /backups/all1/

防止最后一次的增量备份回滚

[root@czh backups]#  xtrabackup --prepare --target-dir /backups/all/ --incremental-dir /backups/all2
[root@czh backups]# cat all/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 2836111
last_lsn = 2836120
compact = 0
recover_binlog_info = 0
flushed_lsn = 2836120
[root@czh backups]# cat all1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2835743
to_lsn = 2835917
last_lsn = 2835926
compact = 0
recover_binlog_info = 0
flushed_lsn = 2835926
[root@czh backups]# cat all2/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2835917
to_lsn = 2836111
last_lsn = 2836120
compact = 0
recover_binlog_info = 0
flushed_lsn = 2836120

恢复数据库

[root@czh ~]# service mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@czh ~]# rm -rf /opt/mysql_data/*
[root@czh ~]# xtrabackup --copy-back --target-dir /backups/all/xtrabackup: recognized server arguments: --datadir=/opt/mysql_data 
210509 14:11:19 completed OK!
[root@czh ~]# chown -R mysql.mysql /opt/mysql_data/
[root@czh ~]# ll /opt/mysql_data/
总用量 122920
drwxr-x---. 2 mysql mysql       58 5月   9 14:11 chenzihan
drwxr-x---. 2 mysql mysql       58 5月   9 14:11 czh
-rw-r-----. 1 mysql mysql      362 5月   9 14:11 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 5月   9 14:11 ibdata1
-rw-r-----. 1 mysql mysql 50331648 5月   9 14:11 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 5月   9 14:11 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 5月   9 14:11 ibtmp1
drwxr-x---. 2 mysql mysql     4096 5月   9 14:11 mysql
drwxr-x---. 2 mysql mysql     8192 5月   9 14:11 performance_schema
drwxr-x---. 2 mysql mysql     8192 5月   9 14:11 sys
-rw-r-----. 1 mysql mysql      461 5月   9 14:11 xtrabackup_info
-rw-r-----. 1 mysql mysql        1 5月   9 14:11 xtrabackup_master_key_id
[root@czh ~]# service mysqld start 
Starting MySQL.Logging to '/opt/mysql_data/czh.err'.
. SUCCESS! 
[root@czh ~]# mysql 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33 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> select * from czh.student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | tommao      |   10 |
| 13 | jerrylaosu  |   10 |
+----+-------------+------+
12 rows in set (0.01 sec)