问题描述:mysql在执行innobackupex增量备份时报错cannot open *****/xtrabackup_checkpoints,如下所示:
数据库:mysql 5.7.21
说明:此次测试环境为从节点.
1、异常重现
[mysql@mysql-leo-perslave ~]$ /usr/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --socket=/mysql/data/mysql.sock --user=root --password=mysql --port=3306 --slave-info --stream=tar --tmpdir=/mysql/bak/full --incremental-basedir=/mysql/bak/file/20230801 --incremental /mysql/bak/file/ 2>/mysql/bak/log_bak/backup.log |gzip ->/mysql/bak/file/192.168.133.117_20230801_3306.tar.gz

告警日志:
[mysql@mysql-leo-perslave log_bak]$ tail -5000f backup.log
xtrabackup: recognized server arguments: --datadir=/mysql/data --server-id=2 --log_bin=mysql-bin --innodb_flush_log_at_trx_commit=1 --tmpdir=/mysql/bak/full 
xtrabackup: recognized client arguments: --datadir=/mysql/data --server-id=2 --log_bin=mysql-bin --innodb_flush_log_at_trx_commit=1 --tmpdir=/mysql/bak/full 
230801 22:29:09 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

xtrabackup: Error: cannot open /mysql/bak/file//xtrabackup_checkpoints
xtrabackup: error: failed to read metadata from /mysql/bak/file//xtrabackup_checkpoints

2、异常分析
告警信息显示无法打开xtrabackup_checkpoints,此次异常原因为在执行增量备份前未对数据库进行全备.

3、解决方案
3.1、全量备份
--数据库全备
[mysql@mysql-leo-perslave ~]$ /usr/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --socket=/mysql/data/mysql.sock --user=root --password=mysql --port=3306 --slave-info --tmpdir=/mysql/bak/full /mysql/bak/file/ 2>/mysql/bak/log_bak/backup.log

--查生成的备份集
[mysql@mysql-leo-perslave file]$ pwd
/mysql/bak/file
[mysql@mysql-leo-perslave file]$ ll
total 4
drwxr-x---. 7 mysql mysql 4096 Aug  1 23:14 2023-08-01_23-14-21

--查xtrabackup_checkpoints信息
[mysql@mysql-leo-perslave file]$ cd 2023-08-01_23-14-21
[mysql@mysql-leo-perslave 2023-08-01_23-14-21]$ cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 7589242403
last_lsn = 7589242412
compact = 0
recover_binlog_info = 0

3.2、增量备份
--进行增量备份
[mysql@mysql-leo-perslave ~]$ /usr/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --socket=/mysql/data/mysql.sock --user=root --password=mysql --port=3306 --slave-info --tmpdir=/mysql/bak/full --incremental-basedir=/mysql/bak/file/2023-08-01_23-14-21 --incremental /mysql/bak/file/ 2>>/mysql/bak/log_bak/backup.log

说明:
--incremental /mysql/bak/file/                              指定增量备份位置
--incremental-basedir=/mysql/bak/file/2023-08-01_23-14-21   指定上一次全备或增备目录

--查新增备份集
[mysql@mysql-leo-perslave file]$ ll
total 8
drwxr-x---. 7 mysql mysql 4096 Aug  1 23:14 2023-08-01_23-14-21
drwxr-x---. 7 mysql mysql 4096 Aug  1 23:29 2023-08-01_23-29-52

[mysql@mysql-leo-perslave file]$ du -sch *
3.9G    2023-08-01_23-14-21
2.9M    2023-08-01_23-29-52

说明:如上所示,2023-08-01_23-29-52为增量备份集.

--查新增xtrabackup_checkpoints信息
[mysql@mysql-leo-perslave file]$ cd 2023-08-01_23-29-52
[mysql@mysql-leo-perslave 2023-08-01_23-29-52]$ cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 7589242403
to_lsn = 7589245996
last_lsn = 7589246012
compact = 0
recover_binlog_info = 0

--查告警日志
......
MySQL binlog position: filename 'mysql-bin.000005', position '2676', GTID of the last change '9621e6df-2ab4-11ee-8438-0050563d0a8c:1-1000023,
e29135f1-2bcb-11ee-bc4b-00505636addc:1-32'
MySQL slave binlog position: master host '192.168.133.116', purge list '9621e6df-2ab4-11ee-8438-0050563d0a8c:1-1000023, e29135f1-2bcb-11ee-bc4b-00505636addc:1-32', channel name: ''

230801 23:29:59 [00] Writing /mysql/bak/file/2023-08-01_23-29-52/backup-my.cnf
230801 23:29:59 [00]        ...done
230801 23:29:59 [00] Writing /mysql/bak/file/2023-08-01_23-29-52/xtrabackup_info
230801 23:29:59 [00]        ...done
xtrabackup: Transaction log of lsn (7589245996) to (7589246012) was copied.
230801 23:29:59 completed OK!

说明:mysql在有全备的基础上执行增量备份无异常发生.