安装xtrabackup,数据备份及恢复

解压安装

[root@localhost /]# cd xtrabackup/
[root@localhost xtrabackup]# ls
Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar
[root@localhost xtrabackup]# tar xf Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar 
[root@localhost 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@localhost xtrabackup]# dnf -y install percona-xtrabackup-24-2.4.22-1.el8.x86_64.rpm
CentOS Stream 8 - AppStream                      6.3 kB/s | 4.4 kB     00:00    
CentOS Stream 8 - AppStream                      1.1 MB/s | 8.5 MB     00:07    
CentOS Stream 8 - BaseOS                         4.7 kB/s | 3.9 kB     00:00    
CentOS Stream 8 - BaseOS                         2.0 MB/s | 2.7 MB     00:01    
CentOS Stream 8 - Extras                         2.3 kB/s | 1.5 kB     00:00    
依赖关系解决。
=================================================================================
 软件包                架构   版本                            仓库          大小
=================================================================================
安装:
 percona-xtrabackup-24 x86_64 2.4.22-1.el8                    @commandline 7.7 M
安装依赖关系:
 libev                 x86_64 4.24-6.el8                      appstream     52 k
 mariadb-connector-c   x86_64 3.1.11-2.el8_3                  appstream    200 k
 perl-DBD-MySQL        x86_64 4.046-3.module_el8.3.0+419+c2dec72b
                                                              appstream    156 k
启用模块流:
 perl-DBD-MySQL               4.046                                             

事务概要
=================================================================================
安装  4 软件包

总计：8.1 M
总下载：408 k
安装大小：32 M
下载软件包：
(1/3): libev-4.24-6.el8.x86_64.rpm               186 kB/s |  52 kB     00:00    
(2/3): perl-DBD-MySQL-4.046-3.module_el8.3.0+419 457 kB/s | 156 kB     00:00    
(3/3): mariadb-connector-c-3.1.11-2.el8_3.x86_64 507 kB/s | 200 kB     00:00    
---------------------------------------------------------------------------------
总计                                             412 kB/s | 408 kB     00:00     
运行事务检查
事务检查成功。
运行事务测试
事务测试成功。
运行事务
  准备中  :                                                                  1/1 
  安装    : mariadb-connector-c-3.1.11-2.el8_3.x86_64                        1/4 
  安装    : perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.x86_64        2/4 
  安装    : libev-4.24-6.el8.x86_64                                          3/4 
  安装    : percona-xtrabackup-24-2.4.22-1.el8.x86_64                        4/4 
  运行脚本: percona-xtrabackup-24-2.4.22-1.el8.x86_64                        4/4 
  验证    : libev-4.24-6.el8.x86_64                                          1/4 
  验证    : mariadb-connector-c-3.1.11-2.el8_3.x86_64                        2/4 
  验证    : perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.x86_64        3/4 
  验证    : percona-xtrabackup-24-2.4.22-1.el8.x86_64                        4/4 
Installed products updated.

已安装:
  libev-4.24-6.el8.x86_64                                                        
  mariadb-connector-c-3.1.11-2.el8_3.x86_64                                      
  percona-xtrabackup-24-2.4.22-1.el8.x86_64                                      
  perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.x86_64                      

完毕！

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

[root@localhost xtrabackup]# mkdir /var/lib/mysql
[root@localhost xtrabackup]#  ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock

创建备份目录

[root@localhost xtrabackup]# cd /
[root@localhost /]# mkdir /backups
[root@localhost /]# mkdir /backups/all
[root@localhost /]# mkdir /backups/lnc1
[root@localhost /]# mkdir /backups/lnc2

将登录密码写在配置文件中

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

增量备份
查看数据库内容

mysql> use huang;
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> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   20 |
|  2 | jerry    |   23 |
|  3 | wangqing |   25 |
|  4 | tj       |   68 |
|  5 | tj       |   92 |
|  6 | teng     |   86 |
+----+----------+------+
6 rows in set (0.00 sec)

首先，进行全量备份

[root@localhost /]# xtrabackup --backup --target-dir /backups/all/
...
xtrabackup: Transaction log of lsn (4288171) to (4288180) was copied.
210509 02:24:41 completed OK!
[root@localhost /]# ll /backups/all/
总用量 12340
-rw-r-----. 1 root root      487 5月   9 02:24 backup-my.cnf
drwxr-x---. 2 root root       58 5月   9 02:24 huang
-rw-r-----. 1 root root      307 5月   9 02:24 ib_buffer_pool
-rw-r-----. 1 root root 12582912 5月   9 02:24 ibdata1
drwxr-x---. 2 root root     4096 5月   9 02:24 mysql
drwxr-x---. 2 root root     8192 5月   9 02:24 performance_schema
drwxr-x---. 2 root root     8192 5月   9 02:24 sys
-rw-r-----. 1 root root       21 5月   9 02:24 xtrabackup_binlog_info
-rw-r-----. 1 root root      135 5月   9 02:24 xtrabackup_checkpoints
-rw-r-----. 1 root root      464 5月   9 02:24 xtrabackup_info
-rw-r-----. 1 root root     2560 5月   9 02:24 xtrabackup_logfile
[root@localhost /]# cat /backups/all/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 4288171
last_lsn = 4288180
compact = 0
recover_binlog_info = 0
flushed_lsn = 4288180

向表中插入数据

mysql> insert into student(name,age) values('yang',56),('xukeqi',72);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   20 |
|  2 | jerry    |   23 |
|  3 | wangqing |   25 |
|  4 | tj       |   68 |
|  5 | tj       |   92 |
|  6 | teng     |   86 |
|  7 | yang     |   56 |
|  8 | xukeqi   |   72 |
+----+----------+------+
8 rows in set (0.00 sec)

在全备的基础上进行增备

[root@localhost /]# xtrabackup --backup --target-dir /backups/lnc1/ --incremental-basedir /backups/all/
[root@localhost /]# ll /backups/lnc1/
总用量 344
-rw-r-----. 1 root root    487 5月   9 02:27 backup-my.cnf
drwxr-x---. 2 root root     88 5月   9 02:27 huang
-rw-r-----. 1 root root    307 5月   9 02:27 ib_buffer_pool
-rw-r-----. 1 root root 294912 5月   9 02:27 ibdata1.delta
-rw-r-----. 1 root root     60 5月   9 02:27 ibdata1.meta
drwxr-x---. 2 root root   4096 5月   9 02:27 mysql
drwxr-x---. 2 root root   8192 5月   9 02:27 performance_schema
drwxr-x---. 2 root root   8192 5月   9 02:27 sys
-rw-r-----. 1 root root     21 5月   9 02:27 xtrabackup_binlog_info
-rw-r-----. 1 root root    139 5月   9 02:27 xtrabackup_checkpoints
-rw-r-----. 1 root root    507 5月   9 02:27 xtrabackup_info
-rw-r-----. 1 root root   2560 5月   9 02:27 xtrabackup_logfile
[root@localhost /]#  cat /backups/lnc1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 4288171
to_lsn = 4290175
last_lsn = 4290184
compact = 0
recover_binlog_info = 0
flushed_lsn = 4290184

再次插入数据

mysql> insert into student (name,age) values('yangmiemie',22),('tengmama',22);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | tom        |   20 |
|  2 | jerry      |   23 |
|  3 | wangqing   |   25 |
|  4 | tj         |   68 |
|  5 | tj         |   92 |
|  6 | teng       |   86 |
|  7 | yang       |   56 |
|  8 | xukeqi     |   72 |
|  9 | yangmiemie |   22 |
| 10 | tengmama   |   22 |
+----+------------+------+
10 rows in set (0.00 sec)

然后在第一次增备的基础上做第二次增备

[root@localhost /]# xtrabackup --backup --target-dir /backups/lnc2/ --incremental-basedir /backups/lnc1/
[root@localhost /]# ll /backups/lnc1/
总用量 344
-rw-r-----. 1 root root    487 5月   9 02:27 backup-my.cnf
drwxr-x---. 2 root root     88 5月   9 02:27 huang
-rw-r-----. 1 root root    307 5月   9 02:27 ib_buffer_pool
-rw-r-----. 1 root root 294912 5月   9 02:27 ibdata1.delta
-rw-r-----. 1 root root     60 5月   9 02:27 ibdata1.meta
drwxr-x---. 2 root root   4096 5月   9 02:27 mysql
drwxr-x---. 2 root root   8192 5月   9 02:27 performance_schema
drwxr-x---. 2 root root   8192 5月   9 02:27 sys
-rw-r-----. 1 root root     21 5月   9 02:27 xtrabackup_binlog_info
-rw-r-----. 1 root root    139 5月   9 02:27 xtrabackup_checkpoints
-rw-r-----. 1 root root    507 5月   9 02:27 xtrabackup_info
-rw-r-----. 1 root root   2560 5月   9 02:27 xtrabackup_logfile
[root@localhost /]# cat /backups/lnc1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 4288171
to_lsn = 4290175
last_lsn = 4290184
compact = 0
recover_binlog_info = 0
flushed_lsn = 4290184

恢复备份
使用prepare和apply-log-only参数防止全备和增量备份日志回滚（除了最后一次的增量备份外）

[root@localhost /]# xtrabackup --prepare --apply-log-only --target-dir /backups/all/
[root@localhost /]# xtrabackup --prepare --apply-log-only --target-dir /backups/all/ --incremental-dir /backups/lnc1/
......
210509 02:32:44 completed OK!

prepare最后一次增量备份这里不使用apply-log-only参数

[root@localhost /]# xtrabackup --prepare --target-dir /backups/all/ --incremental-dir /backups/lnc2/
[root@localhost /]# cat /backups/all/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 4290710
last_lsn = 4290719
compact = 0
recover_binlog_info = 0
flushed_lsn = 4290719
[root@localhost /]# cat /backups/lnc1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 4288171
to_lsn = 4290175
last_lsn = 4290184
compact = 0
recover_binlog_info = 0
flushed_lsn = 4290184
[root@localhost /]# cat /backups/lnc2/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 4290175
to_lsn = 4290710
last_lsn = 4290719
compact = 0
recover_binlog_info = 0
flushed_lsn = 4290719

模拟误删数据库

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

mysql> drop database huang;
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@localhost /]# service mysqld stop
Shutting down MySQL.... SUCCESS! 
[root@localhost /]#  rm -rf /opt/mysql_data/*
[root@localhost /]#  xtrabackup --copy-back --target-dir /backups/all/
[root@localhost /]# chown -R mysql.mysql /opt/mysql_data
[root@localhost /]# ll /opt/mysql_data
总用量 122924
drwxr-x---. 2 mysql mysql       58 5月   9 02:35 huang
-rw-r-----. 1 mysql mysql      307 5月   9 02:35 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 5月   9 02:35 ibdata1
-rw-r-----. 1 mysql mysql 50331648 5月   9 02:35 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 5月   9 02:35 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 5月   9 02:35 ibtmp1
drwxr-x---. 2 mysql mysql     4096 5月   9 02:35 mysql
drwxr-x---. 2 mysql mysql     8192 5月   9 02:35 performance_schema
drwxr-x---. 2 mysql mysql     8192 5月   9 02:35 sys
-rw-r-----. 1 mysql mysql       21 5月   9 02:35 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 mysql mysql      508 5月   9 02:35 xtrabackup_info
-rw-r-----. 1 mysql mysql        1 5月   9 02:35 xtrabackup_master_key_id
[root@localhost /]# service mysqld start
Starting MySQL.Logging to '/opt/mysql_data/localhost.localdomain.err'.
. SUCCESS! 


mysql> use huang;
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> select * from student;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | tom        |   20 |
|  2 | jerry      |   23 |
|  3 | wangqing   |   25 |
|  4 | tj         |   68 |
|  5 | tj         |   92 |
|  6 | teng       |   86 |
|  7 | yang       |   56 |
|  8 | xukeqi     |   72 |
|  9 | yangmiemie |   22 |
| 10 | tengmama   |   22 |
+----+------------+------+
10 rows in set (0.00 sec)