I'm automating both full and incremental backups for a MariaDB instance running in Kubernetes. However, I'm experiencing an issue where the LSN
(Log Sequence Number) isn't updating as expected with each incremental backup, even though I’m using the --extra-lsndir
option to store checkpoint information locally.
According to the MariaDB documentation, the LSN
should update with each incremental backup, but it doesn’t seem to work as intended in my setup. Below are the scripts I'm using for both full and incremental backups:
Full Backup Script
LSN_DIR="/backup/lsn_full"
# Execute the backup using mariadb-backup
time mariabackup --backup --ssl=false \
--host="$CURRENT_IP" \
--port="$MYSQL_PORT" \
--user="$MYSQL_USER" \
--password="$MYSQL_PASSWORD" \
--open-files-limit=90000 \
--extra-lsndir="$LSN_DIR" \
--slave-info --stream=xbstream \
--no-lock --safe-slave-backup | \
zstd - -2 | \
aws --no-verify-ssl s3 cp - s3://mariadb/full/full_backup_$(date '+%Y%m%d%H%M%S').backup-zstd-l2.xb.zst --expected-size 17592186044416
rm -rf /backup/incr_*
Incremental Backup Script
# Identify the latest LSN checkpoint directory
LAST_LSN_DIR=$(find "/backup" -type d -name "lsn_inc_*" | sort | tail -n 1)
[[ -z "$LAST_LSN_DIR" ]] && LAST_LSN_DIR="/backup/lsn_full" # Fallback to full backup if no incremental exists
# Define the new incremental LSN directory
NEW_LSN_DIR="/backup/lsn_inc_$(date +%Y%m%d_%H%M)"
mkdir -p "$NEW_LSN_DIR"
# Execute the backup using mariadb-backup
time mariabackup --backup --ssl=false \
--host="$CURRENT_IP" \
--port="$MYSQL_PORT" \
--user="$MYSQL_USER" \
--password="$MYSQL_PASSWORD" \
--open-files-limit=90000 \
--incremental-basedir="$LAST_LSN_DIR" \
--extra-lsndir="$NEW_LSN_DIR" \
--slave-info --stream=xbstream \
--no-lock --safe-slave-backup | \
zstd - -2 | \
aws --no-verify-ssl s3 cp - s3://mariadb/incr/incr_backup_$(date '+%Y%m%d%H%M%S').backup-incr-l2.xb.zst --expected-size 17592186044416
cp -r "$INCREMENTAL_LSN_DIR/xtrabackup_checkpoints" "$LSN_DIR/xtrabackup_checkpoints"
Steps I'm Following to Test Incremental Backups
Create test table and add data:
create database table1; use table1; create table a (x int); insert into a values(1);
Run full backup (
/backup/scripts/full-backup-script.sh
) and capture the LSN fromxtrabackup_checkpoints
:backup_type = full-backuped from_lsn = 0 to_lsn = 8836358 last_lsn = 8836370 recover_binlog_info = 0
Make additional transactions and run the first incremental backup (
/backup/scripts/incr-backup-script.sh
):backup_type = incremental from_lsn = 8836358 to_lsn = 8836358 last_lsn = 8842468 recover_binlog_info = 0
Make further transactions, then run the second incremental backup:
backup_type = incremental from_lsn = 8836358 to_lsn = 8836358 last_lsn = 8931386 recover_binlog_info = 0
However, the from_lsn
and to_lsn
values aren’t updating correctly. Each incremental backup captures all changes since the last full backup, not the changes since the previous incremental backup.
Question: Why isn’t the LSN
updating correctly with each incremental backup, and how can I configure this to capture only transactions made since the last incremental backup?