xtrabackup 8.0 备份恢复与原理介绍

标签：8.0 -- data 备份 xtrabackup backup percona

https://www.modb.pro/db/102109

1.备份工具特性对比

2.工作过程
Percona XtraBackup是基于InnoDB的崩溃恢复功能的，它复制InnoDB数据文件，这导致数据内部不一致;然后，它对文件执行崩溃恢复，使它们再次成为一致的、可用的数据库。这是因为InnoDB维护一个重做日志，也称为事务日志。这包含了对InnoDB数据的每次更改的记录。当InnoDB启动时，它会检查数据文件和事务日志，并执行两个步骤。一是将提交的事务日志条目应用于数据文件，二是对修改了数据但没有提交的任何事务执行撤销操作。

Percona XtraBackup的工作原理是在启动时记住日志序列号(LSN)，然后复制数据文件。这样做需要一些时间，所以如果文件正在更改，那么它们将反映数据库在不同时间点的状态。与此同时，Percona XtraBackup运行一个后台进程，该进程监视事务日志文件，并从其中复制所做的更改。Percona XtraBackup需要不断地复制变更的事务日志。 Percona XtraBackup会记录自开始执行以来对数据文件的每次更改的事务日志。

Percona XtraBackup在可用的地方使用备份锁作为FLUSH TABLES WITH的轻量级替代读锁。这个特性在Percona Server 5.6+中可用。MySQL 8.0允许通过lock instance FOR backup语句获取实例级的备份锁。

Percona xtrabbackup备份完所有InnoDB/XtraDB数据和日志后，才会对MyISAM和其他非InnoDB表进行锁操作。Percona XtraBackup使用自动复制非innodb数据以避免阻塞修改InnoDB表的DML操作。
开始复制MyISAM表。完成此操作后，将开始备份文件。它将备份.frm、. mrg、. myd、. myi、. arm、. arz、. csm、. csv、.sdi和.par文件。

之后，xtrabackup将使用LOCK BINLOG FOR BACKUP来阻止所有可能改变二进制日志位置或通过SHOW MASTER/SLAVE STATUS 输出Exec_Master_Log_Pos或Exec_Gtid_Set的操作。然后xtrabackup将完成REDO日志文件的复制并获取二进制日志位置点。完成后，xtrabackup将解锁二进制日志和表。

大概流程如下：

3.安装percona-xtrabackup

  下载percona-xtrabackup 的rpm包
[root@localhost local]#  wget  wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.22-15/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.22-15.1.el7.x86_64.rpm
--2021-08-19 17:20:15--   wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.22-15/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.22-15.1.el7.x86_64.rpm
Resolving www.percona.com (www.percona.com)... 172.67.8.157, 104.22.8.28, 104.22.9.28, ...
Connecting to www.percona.com (www.percona.com)|172.67.8.157|:443... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location:  wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.22-15/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.22-15.1.el7.x86_64.rpm [following]
--2021-08-19 17:20:16--   wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.22-15/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.22-15.1.el7.x86_64.rpm
Resolving downloads.percona.com (downloads.percona.com)... 74.121.199.231, 162.220.4.221, 162.220.4.222
Connecting to downloads.percona.com (downloads.percona.com)|74.121.199.231|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 13892468 (13M) [application/octet-stream]
Saving to: ‘percona-xtrabackup-80-8.0.22-15.1.el7.x86_64.rpm’

100%[==========================================================================================================================>] 13,892,468  1.22MB/s   in 15s    

2021-08-19 17:20:34 (907 KB/s) - ‘percona-xtrabackup-80-8.0.22-15.1.el7.x86_64.rpm’ saved [13892468/13892468]

 本地安装percona-xtrabackup
 [root@localhost local]# yum localinstall percona-xtrabackup-80-8.0.22-15.1.el7.x86_64.rpm
Loaded plugins: fastestmirror
Examining percona-xtrabackup-80-8.0.22-15.1.el7.x86_64.rpm: percona-xtrabackup-80-8.0.22-15.1.el7.x86_64
Marking percona-xtrabackup-80-8.0.22-15.1.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package percona-xtrabackup-80-8.0.22-15.1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================================================
 Package                                 Arch                     Version                         Repository                                                   Size
====================================================================================================================================================================
Installing:
 percona-xtrabackup-80                   x86_64                   8.0.4-1.el7                     /percona-xtrabackup-80-8.0.4-1.el7.x86_64                    65 M

Transaction Summary
====================================================================================================================================================================
Install  1 Package

Total size: 65 M
Installed size: 65 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : percona-xtrabackup-80-8.0.22-15.1.el7.x86_64                                                                                                        1/1 
  Verifying  : percona-xtrabackup-80-8.0.22-15.1.el7.x86_64                                                                                                       1/1 

Installed:
 percona-xtrabackup-80-8.0.22-15.1.el7                                                                                                                      

Complete!

验证是否成功
[root@localhost local]# xtrabackup --version
xtrabackup version 8.0.22-15 based on MySQL server 8.0.22 Linux (x86_64) (revision id: fea8a0e)

4.测试备份与恢复

percona-xtrabackup 8.0以后只能对mysql8.0后的版本做备份恢复，对mysql8.0 以前的版本做备份恢复会报错。
报错如下：

4.1 全量备份与恢复
xtrabackup --user=backup_user --password=backup_user -S/tmp/mysql_sandbox21223.sock --backup --target-dir=/data/mysql8_backup

全量恢复
xtrabackup --prepare --target-dir=/data/mysql8_backup

应用完成后，备份目录恢复的文件全部移到原mysql数据库的数据目录下，原来的mysql数据目录清空。
cd /data/mysql8_backup
mv * /root/sandboxes/rsandbox_8_0_22/master/data
原来的数据目录权限改为mysql权限
chown -R mysql:mysql /root/sandboxes/rsandbox_8_0_22/master/data .
如果不想move数据文件就用
xtrabackup --copy-back --target-dir=/data/mysql8_backup
最后重启数据库即可 (service mysqld start)

4.2 增量备份与恢复
先做一次全量备份
xtrabackup --user=backup_user --password=backup_user -S/tmp/mysql_sandbox21223.sock --backup --target-dir=/data/mysql8_backup/base
做第一次增量备份
xtrabackup --user=backup_user --password=backup_user -S/tmp/mysql_sandbox21223.sock --backup --target-dir=/data/mysql8_backup/inc1 --incremental-basedir=/data/mysql8_backup

做第二次增量备份
xtrabackup --user=backup_user --password=backup_user -S/tmp/mysql_sandbox21223.sock --backup --target-dir=/data/mysql8_backup/inc2 --incremental-basedir=/data/mysql8_backup/inc1

增量恢复，先要做一次全备的恢复，要加参数 --apply-log-only
xtrabackup --prepare --apply-log-only --target-dir=/data/mysql8_backup

恢复第一次增量备份
xtrabackup --prepare --apply-log-only --target-dir=/data/mysql8_backup --incremental-dir=/data/mysql8_backup/inc1

恢复最后一个增量，不需要–apply-log-only
xtrabackup --prepare --target-dir=/data/mysql8_backup/base --incremental-dir=/data/mysql8_backup/inc2

最后，按上面全量备份与恢复的移到数据文件启动数据库即可。

4.3 并行压缩备份与恢复
xtrabackup --user=backup_user --password=backup_user -S/tmp/mysql_sandbox21223.sock --backup --compress --compress-threads=4 --target-dir=/data/mysql8_backup/compressed/

备份后的文件是以qp作为后缀。
压缩后的恢复
xtrabackup --decompress --target-dir=/data/mysql8_backup/compressed/
…
210821 10:52:48 [01] decompressing ./backup-my.cnf.qp
210821 10:52:48 [01] decompressing ./xtrabackup_info.qp
210821 10:52:48 [01] decompressing ./xtrabackup_tablespaces.qp
210821 10:52:48 completed OK!
恢复
xtrabackup --prepare --target-dir=/data/mysql8_backup/compressed/

4.4 流式备份与恢复
xtrabackup --user=backup_user --password=backup_user -S/tmp/mysql_sandbox21223.sock --backup --stream=xbstream --target-dir=/data/mysql8_backup/xbs/ >/data/mysql8_backup/xbs/fullback.xbstream
先解压再恢复
xbstream -x < fullback.xbstream
xtrabackup --prepare --target-dir=/data/mysql8_backup/xbs/

4.5 流备份到远程机器
xtrabackup --user=backup_user --password=backup_user -S/tmp/mysql_sandbox21223.sock --backup --stream=xbstream --target-dir=./ | ssh [email protected] " cat - > /data/mysql8_back/fullback.xbstream"

4.6 部分表的备份与恢复
xtrabackup --user=backup_user --password=backup_user -S/tmp/mysql_sandbox21223.sock --backup --target-dir=/data/mysql8_backup/partial/ --tables="^sbtest[.].*"

恢复
xtrabackup --prepare --export --target-dir=/data/mysql8_backup/partial/
5.总结
xtrabackup备份线上环境时，请在业务低谷时执行，因为xtrabackup会锁库表。xtrabackup锁库表的过程比较短暂，我们不容易观察到

标签：8.0,--,data,备份,xtrabackup,backup,percona
From： https://www.cnblogs.com/cheyunhua/p/16716469.html

xtrabackup 8.0 备份恢复与原理介绍

相关文章

赞助商

阅读排行