0

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

  1. Create test table and add data:

    create database table1;
    use table1;
    create table a (x int);
    insert into a values(1);
    
  2. Run full backup (/backup/scripts/full-backup-script.sh) and capture the LSN from xtrabackup_checkpoints:

    backup_type = full-backuped
    from_lsn = 0
    to_lsn = 8836358
    last_lsn = 8836370
    recover_binlog_info = 0
    
  3. 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
    
  4. 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?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.