MySQL运维实战之备份和恢复（8.5）xtrabackup恢复增量备份

作者：俊达
恢复增量备份时，需要先对基础全量备份进行恢复，然后再依次按增量备份的时间进行恢复。

这个例子中，相关备份文件的目录结构如下：

/data/backup
├── full
│   ├── backup_full
│   ├── backup_full.log
│   ├── xtrabackup_checkpoints
│   └── xtrabackup_info
├── inc1
│   ├── backup_inc1.gz
│   ├── backup_inc1.log
│   ├── xtrabackup_checkpoints
│   └── xtrabackup_info
├── inc2
│   ├── backup_inc2.gz
│   ├── backup_inc2.log
│   ├── xtrabackup_checkpoints
│   └── xtrabackup_info

恢复全量备份

cd /data/backup/full

xbstream -x -v < backup_full

xtrabackup --prepare --apply-log-only --target-dir=. > prepare_full.log 2>&1

恢复全量备份时，需要加上apply-log-only参数。如果不加上apply-log-only参数，执行prepare的最后阶段，会回滚未提交的事务，但是这些事务可能在下一次增量备份时已经提交了。
查看日志，确认这一步骤执行成功（最后一行日志显示“completed OK!”）：

# tail prepare_full.log
2023-06-26T13:50:28.924817+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 30394297
2023-06-26T13:50:28.927081+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

这一步执行完成后，xtrabackup_checkpoints内容如下，backup_type为log-applied。

# cat xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 27517178
last_lsn = 30394287
flushed_lsn = 30328603
redo_memory = 0
redo_frames = 0

恢复第一个增量备份

cd /data/backup/inc1

gunzip backup_inc1.gz

## 需要先删除这两个文件，否则xbstream提取文件时有冲突
rm xtrabackup_checkpoints xtrabackup_info

## 提取文件
xbstream -x -v < backup_inc1


## 恢复增量备份时，切换到全量备份的目录执行
cd /data/backup/full
xtrabackup --prepare --apply-log-only --incremental-dir=/data/backup/inc1 --target-dir=. > prepare_inc1.log 2>&1

恢复增量备份时，加上apply-log-only参数，参数--incremental-dir设置为增量备份文件所做目录。
检查执行日志，确认增量备份恢复执行成功（日志最后一行显示“completed OK!”）：

# tail prepare_inc1.log
......
2023-06-26T14:04:14.658596+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /data/backup/inc1/binlog.000021 to ./binlog.000021
2023-06-26T14:04:14.663291+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /data/backup/inc1/binlog.index to ./binlog.index
2023-06-26T14:04:14.663414+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /data/backup/inc1/binlog.index to ./binlog.index
2023-06-26T14:04:14.667887+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

恢复第二个增量备份

cd /data/backup/inc2

gunzip backup_inc2.gz

## 需要先删除这两个文件，否则xbstream提取文件时有冲突
rm xtrabackup_checkpoints xtrabackup_info

## 提取文件
xbstream -x -v < backup_inc2


## 恢复增量备份时，切换到全量备份的目录执行
cd /data/backup/full
xtrabackup --prepare --incremental-dir=/data/backup/inc2 --target-dir=. > prepare_inc2.log 2>&1

恢复最后一个增量备份时，不需要再加上--apply-log-only。
检测执行日志，确认恢复成功。

# tail prepare_inc2.log
......
2023-06-26T14:11:46.742649+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /data/backup/inc2/binlog.000022 to ./binlog.000022
2023-06-26T14:11:46.742714+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /data/backup/inc2/binlog.000022 to ./binlog.000022
2023-06-26T14:11:46.748296+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /data/backup/inc2/binlog.index to ./binlog.index
2023-06-26T14:11:46.748363+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /data/backup/inc2/binlog.index to ./binlog.index
2023-06-26T14:11:46.753459+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

这一步执行完成后，xtrabackup_checkpoints文件内容如下：

# cat xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 42439917
last_lsn = 52717010
flushed_lsn = 52617342
redo_memory = 0
redo_frames = 0

backup_type为full-prepared，可以使用恢复出来的文件启动数据库。
到这一步之后，操作和恢复全量备份基本一样。

复制文件，启动数据库

复制文件：

## 复制文件
cd /data/backup/full
cp -r * /data/full_restore/data
cp binlog.* /data/full_restore/binlog/

sed -i -e 's/mysql8.0/full_restore/' /data/full_restore/binlog/binlog.index

## 移除无用的binlog相关文件
rm /data/full_restore/data/binlog.*

chown  -R mysql:mysql /data/full_restore

启动实例：

# mysqld_safe --defaults-file=/data/full_restore/my.cnf &
[1] 23586

2023-06-26T06:32:18.756919Z mysqld_safe Logging to '/data/full_restore/log/alert.log'.
2023-06-26T06:32:18.799475Z mysqld_safe Starting mysqld daemon with databases from /data/full_restore/data

查看启动日志：

[root@172-16-121-234 full]# tail -10 /data/full_restore/log/alert.log
......
2023-06-26T06:32:21.793703Z 0 [ERROR] [MY-010544] [Repl] Failed to open the relay log '/data/mysql8.0/relaylog/relaylog.000006' (relay_log_pos 407).
2023-06-26T06:32:21.793742Z 0 [ERROR] [MY-011059] [Repl] Could not find target log file mentioned in relay log info in the index file '/data/full_restore/relaylog/relaylog.index' during relay log initialization.
2023-06-26T06:32:21.797553Z 0 [ERROR] [MY-010426] [Repl] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
2023-06-26T06:32:21.797596Z 0 [ERROR] [MY-010529] [Repl] Failed to create or recover replication info repositories.
2023-06-26T06:32:21.797633Z 0 [Warning] [MY-010530] [Repl] Detected misconfiguration: replication channel '' was configured with AUTO_POSITION = 1, but the server was started with --gtid-mode=off. Either reconfigure replication using CHANGE MASTER TO MASTER_AUTO_POSITION = 0 FOR CHANNEL '', or change GTID_MODE to some value other than OFF, before starting the slave receiver thread.
2023-06-26T06:32:21.800913Z 0 [System] [MY-010931] [Server] /opt/mysql/bin/mysqld: ready for connections. Version: '8.0.32'  socket: '/data/full_restore/run/mysql.sock'  port: 6380  MySQL Community Server - GPL.

至此，增量备份恢复完成。
更多技术信息请查看云掣官网https://yunche.pro/?t=yrgw