Percona XtraBackup 全备、增备

PostgreSQL_0

已于 2023-05-29 16:47:02 修改

阅读量777

点赞数

分类专栏： MySQL 文章标签：数学建模 mysql 数据库

于 2023-05-29 16:34:01 首次发布

本文链接：https://blog.csdn.net/weixin_44089900/article/details/130931249

版权

MySQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

该文详细介绍了如何使用Xtrabackup工具对MySQL数据库执行全量备份、部分库表备份、压缩备份以及并行压缩备份。同时，文中也展示了增量备份的流程，包括基于先前全量备份的多次增量操作，并且讲解了备份的恢复步骤。

摘要由CSDN通过智能技术生成

1、全备

1.1 整库备份

创建备份目录

mkdir -p /u01/mysql/backup/full

全备

xtrabackup --backup --datadir=/u01/mysql/data/mysql3306/data/ \
--target-dir=/u01/mysql/backup/full/`date +'%Y%m%d'`/

...
2023-05-29T13:27:44.185420+08:00 0 [Note] [MY-011825] [Xtrabackup] All tables unlocked
2023-05-29T13:27:44.185449+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying ib_buffer_pool to /u01/mysql/backup/full/20230529/ib_buffer_pool
2023-05-29T13:27:44.185496+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying ib_buffer_pool to /u01/mysql/backup/full/20230529/ib_buffer_pool
2023-05-29T13:27:44.190740+08:00 0 [Note] [MY-011825] [Xtrabackup] Backup created in directory '/u01/mysql/backup/full/20230529/'
2023-05-29T13:27:44.190755+08:00 0 [Note] [MY-011825] [Xtrabackup] MySQL binlog position: filename 'mysql_bin.000004', position '156'
2023-05-29T13:27:44.190793+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /u01/mysql/backup/full/20230529/backup-my.cnf
2023-05-29T13:27:44.190836+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /u01/mysql/backup/full/20230529/backup-my.cnf
2023-05-29T13:27:44.196292+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /u01/mysql/backup/full/20230529/xtrabackup_info
2023-05-29T13:27:44.196332+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /u01/mysql/backup/full/20230529/xtrabackup_info
2023-05-29T13:27:44.205845+08:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (1517550997) to (1517551007) was copied.
2023-05-29T13:27:44.410893+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

查看

[mysql@oldcenterdatabase full]$ ls /u01/mysql/backup/full/`date +'%Y%m%d'`/
backup-my.cnf   mysql               mysql.ibd           test      xtrabackup_binlog_info  xtrabackup_logfile
ib_buffer_pool  mysql_bin.000004    performance_schema  undo_001  xtrabackup_checkpoints  xtrabackup_tablespaces
ibdata1         mysql_binlog.index  sys                 undo_002  xtrabackup_info

1.2备份部分库表

mkdir -p /u01/mysql/backup/partial

$ xtrabackup --backup --datadir=/u01/mysql/data/mysql3306/data/ --tables="test.dbc*,test.t_gt*" \
--target-dir=/u01/mysql/backup/partial/`date +'%Y%m%d'`/
...
2023-05-29T13:47:15.990623+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

[mysql@oldcenterdatabase backup]$ ls /u01/mysql/backup/partial/`date +'%Y%m%d'`/
backup-my.cnf   ibdata1           mysql_binlog.index  test      undo_002                xtrabackup_checkpoints  xtrabackup_logfile
ib_buffer_pool  mysql_bin.000005  mysql.ibd           undo_001  xtrabackup_binlog_info  xtrabackup_info         xtrabackup_tablespaces

1.3 压缩备份

从Percona XtraBackup 8.0.31-24开始，使用qpress/QuickLZ压缩备份已被弃用，可能会在未来的版本中删除。我们建议使用** (LZ4,ZSTD)** 压缩算法。
在原来的基础上增加选项即可，例如：–compress

LZ4
压缩算法 --compress=lz4

$ xtrabackup --backup --compress=lz4 --datadir=/u01/mysql/data/mysql3306/data/ \
--target-dir=/u01/mysql/backup/full/`date +'%Y%m%d'`/

**ZSTD **Zstandard (ZSTD)
Zstandard （ZSTD）压缩算法是一项技术预览功能。在生产中使用 ZSTD 之前，我们建议您测试从环境中的物理备份还原生产，并使用备用备份方法实现冗余。
Percona XtraBackup 8.0.30-23增加了对压缩算法的支持。是一种快速无损压缩算法，针对实时压缩场景和更好的压缩率。Zstandard (ZSTD)ZSTD

$ xtrabackup --backup --compress=zstd --datadir=/u01/mysql/data/mysql3306/data/ \
--target-dir=/u01/mysql/backup/full/`date +'%Y%m%d'`/

3.4 并行压缩，并且流式备份

如果要加快压缩速度，可以使用并行压缩，可以使用选项启用。以下示例将使用四个线程进行压缩：–compress-threads

并行压缩备份
$ xtrabackup --backup --compress=lz4 --compress-threads=4 \
--datadir=/u01/mysql/data/mysql3306/data/ --target-dir=/u01/mysql/backup/full/`date +'%Y%m%d'`/

[mysql@oldcenterdatabase full]$ ls /u01/mysql/backup/full/`date +'%Y%m%d'`/
backup-my.cnf.lz4   mysql                   mysql.ibd.lz4       test          xtrabackup_binlog_info.lz4  xtrabackup_logfile.lz4
ib_buffer_pool.lz4  mysql_bin.000006.lz4    performance_schema  undo_001.lz4  xtrabackup_checkpoints      xtrabackup_tablespaces.lz4
ibdata1.lz4         mysql_binlog.index.lz4  sys                 undo_002.lz4  xtrabackup_info.lz4



并且流式压缩备份
$ xtrabackup --backup --stream=xbstream --compress=lz4 --compress-threads=4 \
--datadir=/u01/mysql/data/mysql3306/data/ > /u01/mysql/backup/full/xbk-`date +'%Y%m%d'`.xbstream

[mysql@oldcenterdatabase full]$ ll /u01/mysql/backup/full
total 325500
-rw-rw-r-- 1 mysql mysql 333309015 May 29 14:10 xbk-20230529.xbstream
drwxr-x--- 2 mysql mysql         6 May 29 14:10 xtrabackup_backupfiles

3.5 全量恢复

##prepare过程
 xtrabackup --prepare --target-dir=/u01/mysql/backup/full/ --no-server-version-check

##copy-back过程，先关闭数据库
 xtrabackup --copy-back --target-dir=/u01/mysql/backup/full/ \
--datadir=/u01/mysql/data/mysql3306/data/
 chown -R mysql:mysql /u01/mysql/data/mysql3306/data

##===================================================================================

2、增量

2.1 增量备份

Xtrabackup还支持增量备份，即在上一次备份的基础上，只备份发生新变化的数据。发起增量备份前，得先有一份全量备份，才能有所谓的增量。

mkdir -p /u01/mysql/backup/base
第一次全备
xtrabackup --backup --datadir=/u01/mysql/data/mysql3306/data/ \
--target-dir=/u01/mysql/backup/base/`date +'%Y%m%d'`/

mkdir -p /u01/mysql/backup/inc1
第一次增备
xtrabackup --backup --target-dir=/u01/mysql/backup/inc1/`date +'%Y%m%d%H'`/ \
--incremental-basedir=/u01/mysql/backup/base/`date +'%Y%m%d'`/

mkdir -p /u01/mysql/backup/inc2
第二次增备
from_lsn是备份的起始 LSN，对于增量备份，它必须与上一个/基本备份的最后一个检查点相同（如果它是最后一个检查点）。to_lsn
现在可以使用此目录作为另一个增量备份的基础：
xtrabackup --backup --target-dir=/u01/mysql/backup/inc2/`date +'%Y%m%d%H'`/ \
--incremental-basedir=/u01/mysql/backup/inc1/`date +'%Y%m%d%H'`/

第一次全备
[mysql@oldcenterdatabase 20230529]$ cat /u01/mysql/backup/base/20230529/xtrabackup_info
uuid = 832781ca-fdee-11ed-bfd4-fa163e7d38e5
name =
tool_name = xtrabackup
tool_command = --backup --datadir=/u01/mysql/data/mysql3306/data/ --target-dir=/u01/mysql/backup/base/20230529/
tool_version = 8.0.28-20
ibbackup_version = 8.0.28-20
server_version = 8.0.27
start_time = 2023-05-29 15:00:26
end_time = 2023-05-29 15:00:36
lock_time = 1
binlog_pos = filename 'mysql_bin.000010', position '156'
innodb_from_lsn = 0         --------<--全备0开始
innodb_to_lsn = 1517551143  --------<--本次全备的LSN
partial = N
incremental = N             --------<--N表示全备
format = file
compressed = N
encrypted = N

[mysql@oldcenterdatabase 20230529]$ cat /u01/mysql/backup/base/20230529/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1517551143
last_lsn = 1517551143
flushed_lsn = 1517551143


第一次全备
[mysql@oldcenterdatabase 2023052915]$ cat /u01/mysql/backup/inc1/2023052915/xtrabackup_info
uuid = c788fa6c-fdee-11ed-bfd4-fa163e7d38e5
name =
tool_name = xtrabackup
tool_command = --backup --target-dir=/u01/mysql/backup/inc1/2023052915/ --incremental-basedir=/u01/mysql/backup/base/20230529/
tool_version = 8.0.28-20
ibbackup_version = 8.0.28-20
server_version = 8.0.27
start_time = 2023-05-29 15:02:21
end_time = 2023-05-29 15:02:31
lock_time = 0
binlog_pos = filename 'mysql_bin.000011', position '156'
innodb_from_lsn = 1517551143     --------<--全备的LSN
innodb_to_lsn = 1517551163       --------<--本次增备后的LSN
partial = N
incremental = Y                  --------<--Y表示增备
format = file
compressed = N
encrypted = N

[mysql@oldcenterdatabase 2023052915]$ cat /u01/mysql/backup/inc1/2023052915/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1517551143
to_lsn = 1517551163
last_lsn = 1517551163
flushed_lsn = 1517551163


第二次增备
[mysql@oldcenterdatabase 2023052915]$ cat /u01/mysql/backup/inc2/2023052915/xtrabackup_info
uuid = 7ea27539-fdf1-11ed-bfd4-fa163e7d38e5
name =
tool_name = xtrabackup
tool_command = --backup --target-dir=/u01/mysql/backup/inc2/2023052915/ --incremental-basedir=/u01/mysql/backup/inc1/2023052915/
tool_version = 8.0.28-20
ibbackup_version = 8.0.28-20
server_version = 8.0.27
start_time = 2023-05-29 15:21:47
end_time = 2023-05-29 15:21:57
lock_time = 1
binlog_pos = filename 'mysql_bin.000012', position '156'
innodb_from_lsn = 1517551163    --------<--上一次增备的lsn
innodb_to_lsn = 1517551183      --------<--本次增备后的LSN
partial = N
incremental = Y                 --------<--Y表示增备
format = file
compressed = N
encrypted = N

[mysql@oldcenterdatabase 2023052915]$ cat /u01/mysql/backup/inc2/2023052915/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1517551163
to_lsn = 1517551183
last_lsn = 1517551183
flushed_lsn = 1517551183

2.2 增量恢复

恢复增备
xtrabackup --prepare --apply-log-only --target-dir=/u01/mysql/backup/base/`date +'%Y%m%d'`/
##将第一次增备加载至全备中
xtrabackup --prepare --apply-log-only --target-dir=/u01/mysql/backup/base/`date +'%Y%m%d'`/ \
--incremental-dir=/u01/mysql/backup/inc1/`date +'%Y%m%d%H'`/
##将第二次增备加载至全备中
xtrabackup --prepare --target-dir=/u01/mysql/backup/base/`date +'%Y%m%d'`/ \
--incremental-dir=/u01/mysql/backup/inc2/`date +'%Y%m%d%H'`/

##恢复的过程
xtrabackup --copy-back --target-dir=/u01/mysql/backup/base/`date +'%Y%m%d'`/ \
--datadir=/u01/mysql/data/mysql3306/data/
chown -R mysql:mysql /u01/mysql/data/mysql3306/data/
#=========================================================