首页 > 编程学习 > xtrabackup 8.0 备份恢复与原理介绍

xtrabackup 8.0 备份恢复与原理介绍

发布时间:2022/9/21 17:38:01

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

 

1.备份工具特性对比
image.png
image.png
image.png

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将解锁二进制日志和表。

大概流程如下:
image.png

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 以前的版本做备份恢复会报错。
报错如下:
image.png

4.1 全量备份与恢复
xtrabackup --user=backup_user --password=backup_user -S/tmp/mysql_sandbox21223.sock --backup --target-dir=/data/mysql8_backup
image.png
全量恢复
xtrabackup --prepare --target-dir=/data/mysql8_backup
image.png
应用完成后,备份目录恢复的文件全部移到原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
image.png
做第二次增量备份
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
image.png
增量恢复,先要做一次全备的恢复,要加参数 --apply-log-only
xtrabackup --prepare --apply-log-only --target-dir=/data/mysql8_backup
image.png
恢复第一次增量备份
xtrabackup --prepare --apply-log-only --target-dir=/data/mysql8_backup --incremental-dir=/data/mysql8_backup/inc1
image.png
image.png

恢复最后一个增量,不需要–apply-log-only
xtrabackup --prepare --target-dir=/data/mysql8_backup/base --incremental-dir=/data/mysql8_backup/inc2
image.png
最后,按上面全量备份与恢复的移到数据文件启动数据库即可。

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/
image.png
备份后的文件是以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 root@192.168.1.111 " 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锁库表的过程比较短暂,我们不容易观察到

Copyright © 2010-2022 mfbz.cn 版权所有 |关于我们| 联系方式|豫ICP备15888888号