问题描述：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在有全备的基础上执行增量备份无异常发生.