xtrabackup的增量备份

 

安装xtrabackup

解压xtrabackup安装包并安装

[root@localhost ~]# cd /
[root@localhost /]# mkdir 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]# 
[root@localhost xtrabackup]# dnf -y install percona-xtrabackup-24-2.4.22-1.el8.x86_64.rpm
CentOS Stream 8 - AppStream           4.0 kB/s | 4.4 kB     00:01    
CentOS Stream 8 - AppStream           1.4 MB/s | 8.5 MB     00:05    
CentOS Stream 8 - BaseOS              4.5 kB/s | 3.9 kB     00:00    
CentOS Stream 8 - BaseOS              612 kB/s | 2.7 MB     00:04    
CentOS Stream 8 - Extras              2.2 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
 mariadb-connector-c-config noarch 3.1.11-2.el8_3  appstream     15 k
 perl-DBD-MySQL             x86_64 4.046-3.module_el8.3.0+419+c2dec72b
                                                   appstream    156 k
启用模块流:
 perl-DBD-MySQL                    4.046                             

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

总计：8.1 M
总下载：423 k
安装大小：32 M
下载软件包：
(1/4): mariadb-connector-c-config-3.1  36 kB/s |  15 kB     00:00    
(2/4): libev-4.24-6.el8.x86_64.rpm     93 kB/s |  52 kB     00:00    
(3/4): mariadb-connector-c-3.1.11-2.e 217 kB/s | 200 kB     00:00    
(4/4): perl-DBD-MySQL-4.046-3.module_ 300 kB/s | 156 kB     00:00    
----------------------------------------------------------------------
总计                                  250 kB/s | 423 kB     00:01     
运行事务检查
事务检查成功。
运行事务测试
事务测试成功。
运行事务
  准备中  :                                                       1/1 
  安装    : mariadb-connector-c-config-3.1.11-2.el8_3.noarch      1/5 
警告：/etc/my.cnf 已建立为 /etc/my.cnf.rpmnew 

  安装    : mariadb-connector-c-3.1.11-2.el8_3.x86_64             2/5 
  安装    : perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.   3/5 
  安装    : libev-4.24-6.el8.x86_64                               4/5 
  安装    : percona-xtrabackup-24-2.4.22-1.el8.x86_64             5/5 
  运行脚本: percona-xtrabackup-24-2.4.22-1.el8.x86_64             5/5 
  验证    : libev-4.24-6.el8.x86_64                               1/5 
  验证    : mariadb-connector-c-3.1.11-2.el8_3.x86_64             2/5 
  验证    : mariadb-connector-c-config-3.1.11-2.el8_3.noarch      3/5 
  验证    : perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.   4/5 
  验证    : percona-xtrabackup-24-2.4.22-1.el8.x86_64             5/5 
Installed products updated.

已安装:
  libev-4.24-6.el8.x86_64                                             
  mariadb-connector-c-3.1.11-2.el8_3.x86_64                           
  mariadb-connector-c-config-3.1.11-2.el8_3.noarch                    
  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 /]#  mkdir /backups
[root@localhost /]# mkdir /backups/all
[root@localhost /]#  mkdir /backups/lnc1
[root@localhost /]# mkdir /backups/lnc2

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

[root@localhost /]# vim ~/.my.cnf
[client]
user=root
password=Ha153624....
[innobackupex]
user=root
password=Passw0rd.

查看数据库内容

 

[root@localhost ~]# mysql -uroot -pPassw0rd.
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 3
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> show databases ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tj                 |
+--------------------+
5 rows in set (0.00 sec)

mysql> use tj;
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 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chengshuo   |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)

首先，进行全量备份

[root@localhost /]# xtrabackup --backup --target-dir /backups/all/
[root@localhost /]#  ll /backups/all/
总用量 12336
-rw-r-----. 1 root root      487 5月   9 20:44 backup-my.cnf
-rw-r-----. 1 root root      309 5月   9 20:44 ib_buffer_pool
-rw-r-----. 1 root root 12582912 5月   9 20:44 ibdata1
drwxr-x---. 2 root root     4096 5月   9 20:44 mysql
drwxr-x---. 2 root root     8192 5月   9 20:44 performance_schema
drwxr-x---. 2 root root     8192 5月   9 20:44 sys
drwxr-x---. 2 root root       58 5月   9 20:44 tj
-rw-r-----. 1 root root      135 5月   9 20:44 xtrabackup_checkpoints
-rw-r-----. 1 root root      417 5月   9 20:44 xtrabackup_info
-rw-r-----. 1 root root     2560 5月   9 20:44 xtrabackup_logfile
[root@localhost /]# cat /backups/all/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2758415
last_lsn = 2758424
compact = 0
recover_binlog_info = 0
flushed_lsn = 2758424

向表中插入数据

[root@localhost /]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
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> use tj;
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('teng',18);
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 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chengshuo   |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | teng        |   18 |
+----+-------------+------+
12 rows in set (0.00 sec)

然后，在全备的基础上进行增备

[root@localhost /]# xtrabackup --backup --target-dir /backups/lnc1/ --incremental-basedir /backups/all/
[root@localhost /]# ll /backups/lnc1/
总用量 148
-rw-r-----. 1 root root   487 5月   9 20:55 backup-my.cnf
-rw-r-----. 1 root root   309 5月   9 20:55 ib_buffer_pool
-rw-r-----. 1 root root 98304 5月   9 20:55 ibdata1.delta
-rw-r-----. 1 root root    60 5月   9 20:55 ibdata1.meta
drwxr-x---. 2 root root  4096 5月   9 20:55 mysql
drwxr-x---. 2 root root  8192 5月   9 20:55 performance_schema
drwxr-x---. 2 root root  8192 5月   9 20:55 sys
drwxr-x---. 2 root root    88 5月   9 20:55 tj
-rw-r-----. 1 root root   139 5月   9 20:55 xtrabackup_checkpoints
-rw-r-----. 1 root root   460 5月   9 20:55 xtrabackup_info
-rw-r-----. 1 root root  2560 5月   9 20:55 xtrabackup_logfile

再次插入数据

[root@localhost /]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
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> use tj
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('tj',18);
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 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chengshuo   |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | teng        |   18 |
| 13 | tj          |   18 |
+----+-------------+------+
13 rows in set (0.00 sec)

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

[root@localhost /]# xtrabackup --backup --target-dir /backups/lnc2/ --incremental-basedir /backups/lnc1/
[root@localhost /]# ll /backups/lnc2/
总用量 116
-rw-r-----. 1 root root   487 5月   9 21:01 backup-my.cnf
-rw-r-----. 1 root root   309 5月   9 21:01 ib_buffer_pool
-rw-r-----. 1 root root 65536 5月   9 21:01 ibdata1.delta
-rw-r-----. 1 root root    60 5月   9 21:01 ibdata1.meta
drwxr-x---. 2 root root  4096 5月   9 21:01 mysql
drwxr-x---. 2 root root  8192 5月   9 21:01 performance_schema
drwxr-x---. 2 root root  8192 5月   9 21:01 sys
drwxr-x---. 2 root root    88 5月   9 21:01 tj
-rw-r-----. 1 root root   139 5月   9 21:01 xtrabackup_checkpoints
-rw-r-----. 1 root root   461 5月   9 21:01 xtrabackup_info
-rw-r-----. 1 root root  2560 5月   9 21:01 xtrabackup_logfile

恢复备份

模拟误删数据库

[root@localhost /]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
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> drop database tj;
Query OK, 1 row affected (0.13 sec)

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

 

使用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/

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 = 2758769
last_lsn = 2758778
compact = 0
recover_binlog_info = 0
flushed_lsn = 2758778
[root@localhost /]#  cat /backups/lnc1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2758415
to_lsn = 2758585
last_lsn = 2758594
compact = 0
recover_binlog_info = 0
flushed_lsn = 2758594
[root@localhost /]# cat /backups/lnc2/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2758585
to_lsn = 2758769
last_lsn = 2758778
compact = 0
recover_binlog_info = 0
flushed_lsn = 2758778

 

恢复数据

[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
总用量 122920
-rw-r-----. 1 mysql mysql      309 5月   9 21:08 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 5月   9 21:08 ibdata1
-rw-r-----. 1 mysql mysql 50331648 5月   9 21:08 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 5月   9 21:08 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 5月   9 21:08 ibtmp1
drwxr-x---. 2 mysql mysql     4096 5月   9 21:08 mysql
drwxr-x---. 2 mysql mysql     8192 5月   9 21:08 performance_schema
drwxr-x---. 2 mysql mysql     8192 5月   9 21:08 sys
drwxr-x---. 2 mysql mysql       58 5月   9 21:08 tj
-rw-r-----. 1 mysql mysql      461 5月   9 21:08 xtrabackup_info
-rw-r-----. 1 mysql mysql        1 5月   9 21:08 xtrabackup_master_key_id
[root@localhost /]# service mysqld start
Starting MySQL.Logging to '/opt/mysql_data/localhost.localdomain.err'.
.. SUCCESS! 
[root@localhost /]# 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> use tj;
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 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chengshuo   |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | teng        |   18 |
| 13 | tj          |   18 |
+----+-------------+------+
13 rows in set (0.00 sec)