xtrabackup增量备份

xtrabackup下载与安装

• 下载(我这里是官网找2.4版本,centos8)

[root@host ~]# mkdir xtrabackup
[root@host ~]# cd xtrabackup/
[root@host xtrabackup]# 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 16:26:07--  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.221
正在连接 downloads.percona.com (downloads.percona.com)|162.220.4.221|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：76595200 (73M) [application/x-tar]
正在保存至: “Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar”

Percona-XtraBackup-2.4.22-rc9 100%[==============================================>]  73.05M   746KB/s  用时 47s     

2021-05-09 16:26:57 (1.55 MB/s) - 已保存 “Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar” [76595200/76595200])

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

• 解压并安装

[root@host xtrabackup]# tar xf Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar 
[root@host xtrabackup]# 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@host xtrabackup]# dnf -y install percona-xtrabackup-24-2.4.22-1.el8.x86_64.rpm

将MySQL的套接字文件链接到/var/lib/mysql/mysql.scok下

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

增量备份

• 创建一个存放备份文件的目录方便等下放置备份文件

[root@host /]# mkdir backup
[root@host /]# cd backup
[root@host backup]# mkdir all
[root@host backup]# mkdir all1
[root@host backup]# mkdir all2

• 将密码和用户写入配置文件

[root@host ~]# vim ~/.my.cnf
[root@host ~]# cat ~/.my.cnf
[client]
user=root
password=xialuo123!
[innobackupex]
user=root
password=xialuo123!

• 先进行全备

查看数据库和表
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xkq                |
+--------------------+
5 rows in set (0.00 sec)

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

进行备份
[root@host ~]# xtrabackup --backup --target-dir /backup/all
[root@host ~]# ll /backup/all
总用量 12336
drwxr-x---. 2 root root        6 5月   9 17:02 2021-05-09_17-02-52
drwxr-x---. 6 root root      234 5月   9 17:03 2021-05-09_17-03-03
-rw-r-----. 1 root root      487 5月   9 17:04 backup-my.cnf
-rw-r-----. 1 root root        0 5月   9 17:04 ib_buffer_pool
-rw-r-----. 1 root root 12582912 5月   9 17:04 ibdata1
drwxr-x---. 2 root root     4096 5月   9 17:04 mysql
drwxr-x---. 2 root root     8192 5月   9 17:04 performance_schema
drwxr-x---. 2 root root     8192 5月   9 17:04 sys
drwxr-x---. 2 root root       58 5月   9 17:04 xkq
-rw-r-----. 1 root root       21 5月   9 17:04 xtrabackup_binlog_info
-rw-r-----. 1 root root      135 5月   9 17:04 xtrabackup_checkpoints
-rw-r-----. 1 root root      462 5月   9 17:04 xtrabackup_info
-rw-r-----. 1 root root     2560 5月   9 17:04 xtrabackup_logfile
[root@host ~]# cat /backup/all/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 5859918
last_lsn = 5859927
compact = 0
recover_binlog_info = 0
flushed_lsn = 5859927

• 向表中插入数据

mysql> use xkq;

mysql> insert into student (id,name,age) values (14,'hh',29);
Query OK, 1 row affected (0.01 sec)

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

• 在全备的基础上进行增备

[root@host /]# xtrabackup --backup --target-dir /backup/all1/ --incremental-basedir /backup/all/
[root@host backup]# ll all1
总用量 180
-rw-r-----. 1 root root    487 5月   9 17:09 backup-my.cnf
-rw-r-----. 1 root root      0 5月   9 17:09 ib_buffer_pool
-rw-r-----. 1 root root 131072 5月   9 17:09 ibdata1.delta
-rw-r-----. 1 root root     60 5月   9 17:09 ibdata1.meta
drwxr-x---. 2 root root   4096 5月   9 17:09 mysql
drwxr-x---. 2 root root   8192 5月   9 17:09 performance_schema
drwxr-x---. 2 root root   8192 5月   9 17:09 sys
drwxr-x---. 2 root root     88 5月   9 17:09 xkq
-rw-r-----. 1 root root     21 5月   9 17:09 xtrabackup_binlog_info
-rw-r-----. 1 root root    139 5月   9 17:09 xtrabackup_checkpoints
-rw-r-----. 1 root root    505 5月   9 17:09 xtrabackup_info
-rw-r-----. 1 root root   2560 5月   9 17:09 xtrabackup_logfile
[root@host all1]# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 5859918
to_lsn = 5860312
last_lsn = 5860321
compact = 0
recover_binlog_info = 0
flushed_lsn = 5860321

• 再次插入数据

mysql> insert into student (id,name,age) values (15,'vv',19);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangwing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | jot         |   30 |
| 13 | roo         |   40 |
| 14 | hh          |   29 |
| 15 | vv          |   19 |
+----+-------------+------+

• 在第一次的增备上进行第二次增备

[root@host /]# xtrabackup --backup --target-dir /backup/all2/ --incremental-basedir /backup/all1/
[root@host /]# ll backup/all2
总用量 232
-rw-r-----. 1 root root    487 5月   9 17:30 backup-my.cnf
-rw-r-----. 1 root root    368 5月   9 17:30 ib_buffer_pool
-rw-r-----. 1 root root 180224 5月   9 17:30 ibdata1.delta
-rw-r-----. 1 root root     60 5月   9 17:30 ibdata1.meta
drwxr-x---. 2 root root   4096 5月   9 17:30 mysql
drwxr-x---. 2 root root   8192 5月   9 17:30 performance_schema
drwxr-x---. 2 root root   8192 5月   9 17:30 sys
drwxr-x---. 2 root root     88 5月   9 17:30 xkq
-rw-r-----. 1 root root     21 5月   9 17:30 xtrabackup_binlog_info
-rw-r-----. 1 root root    139 5月   9 17:30 xtrabackup_checkpoints
-rw-r-----. 1 root root    506 5月   9 17:30 xtrabackup_info
-rw-r-----. 1 root root   2560 5月   9 17:30 xtrabackup_logfile
[root@host /]# cat backup/all2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 5860312
to_lsn = 5861639
last_lsn = 5861648
compact = 0
recover_binlog_info = 0
flushed_lsn = 5861648

恢复备份

• 模拟误删

[root@host /]# mysql

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

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

• 防止全备和增量备份日志回滚(最后一次的增备除外)

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

• 防止最后一次增备日志回滚

[root@host /]# xtrabackup --prepare --target-dir /backup/all/ --incremental-dir /backup/all2/
[root@host /]# cat /backup/all/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 5861639
last_lsn = 5861648
compact = 0
recover_binlog_info = 0
flushed_lsn = 5861648

[root@host /]# cat /backup/all1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 5859918
to_lsn = 5860312
last_lsn = 5860321
compact = 0
recover_binlog_info = 0
flushed_lsn = 5860321

[root@host /]# cat /backup/all2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 5860312
to_lsn = 5861639
last_lsn = 5861648
compact = 0
recover_binlog_info = 0
flushed_lsn = 5861648

• 恢复数据

[root@host /]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@host /]# rm -rf /opt/mysql-data/*
[root@host /]#  xtrabackup --copy-back --target-dir /backup/all/

[root@host /]# chown -R mysql.mysql /opt/mysql-data
[root@host /]# ll /opt/mysql-data
总用量 122920
drwxr-x---. 2 mysql mysql        6 5月   9 17:44 2021-05-09_17-03-03
-rw-r-----. 1 mysql mysql        0 5月   9 17:44 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 5月   9 17:44 ibdata1
-rw-r-----. 1 mysql mysql 50331648 5月   9 17:44 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 5月   9 17:44 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 5月   9 17:44 ibtmp1
drwxr-x---. 2 mysql mysql     4096 5月   9 17:44 mysql
drwxr-x---. 2 mysql mysql     8192 5月   9 17:44 performance_schema
drwxr-x---. 2 mysql mysql     8192 5月   9 17:44 sys
drwxr-x---. 2 mysql mysql       58 5月   9 17:44 xkq
-rw-r-----. 1 mysql mysql       21 5月   9 17:44 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 mysql mysql      506 5月   9 17:44 xtrabackup_info
-rw-r-----. 1 mysql mysql        1 5月   9 17:44 xtrabackup_master_key_id
[root@host /]#  service mysqld start
Starting MySQL.Logging to '/opt/mysql-data/host.err'.
. SUCCESS! 
[root@host /]# mysql
mysql> show databases;
+------------------------------+
| Database                     |
+------------------------------+
| information_schema           |
| #mysql50#2021-05-09_17-03-03 |
| mysql                        |
| performance_schema           |
| sys                          |
| xkq                          |
+------------------------------+
6 rows in set (0.00 sec)

mysql> use xkq;
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> show tables;
+---------------+
| Tables_in_xkq |
+---------------+
| student       |
+---------------+
1 row in set (0.00 sec)

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangwing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | jot         |   30 |
| 13 | roo         |   40 |
| 14 | hh          |   29 |
| 15 | vv          |   19 |
+----+-------------+------+
14 rows in set (0.00 sec)