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.
至此，增量备份恢复完成。
