问题描述: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在有全备的基础上执行增量备份无异常发生.
mysql在执行innobackupex增量备份时报错cannot open *****/xtrabackup_checkpoints
原创Liujun_Deng 博主文章分类:Mysql ©著作权
文章标签 增量备份 xtrabackup_checkpoin mysql增量备份 文章分类 MySQL 数据库
-
mysql innobackupex增量备份
mysql innobackupex增量备份备忘
mysql 增量备份 innobackupex -
mysql 增量备份的脚本 (innobackupex)
数据库,mysql innobackupex
数据库 mysql innobackupex -
xtrabackup增量备份恢复
MySQL xtrabackup 增量备份与恢复
MySQL 备份恢复 xtrabackup