xtrabackup 8.0 备份恢复与原理介绍
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 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锁库表的过程比较短暂,我们不容易观察到