Why from_lsn still equals to to_lsn when I inserted some data into db before incremental backup?

MySQL & MariaDB Percona XtraBackup
1

Hey, guys, I’m using MariaDB 10.5.11 and mariabackup.

After I created a full backup, I inserted some data into the MariaDB, and then I do incremental backup for testing.
But I found that xtrabackup_checkpoints have the same from_lsn and to_lsn even if I inserted some data before incremental backup.
That seems weird to me.

Env:
CentOS 7
MariaDB 10.5.11 with bundled mariabackup

Steps to reproduce:

  1. Create a full backup
-- create database foobar;
-- use foobar;
-- CREATE TABLE t(id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL);
$ mysql -e 'select count(*) from foobar.t \G'
*************************** 1. row ***************************
count(*): 10

$ mariabackup --defaults-file=/etc/my.cnf \
    --socket=/var/lib/mysql/mysql.sock \
    --user=root --password=123456 --parallel=2 \
    --compress --compress-threads=2 \
    --backup --target-dir /tmp/backup-test/0

$ cat /tmp/backup-test/0/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 56844
last_lsn = 56856
  1. Insert some data into MariaDB
# 10 times
$ mysql -e 'insert into foobar.t values()'

$ mysql -e 'select count(*) from foobar.t \G'
*************************** 1. row ***************************
count(*): 20
  1. Do an incremental backup
$ mariabackup --defaults-file=/etc/my.cnf \
    --socket=/var/lib/mysql/mysql.sock \
    --user=root --password=123456 --parallel=2 \
    --compress --compress-threads=2 \
    --incremental-basedir /tmp/backup-test/0 \
    --backup --target-dir /tmp/backup-test/1

$ cat /tmp/backup-test/1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 56844
to_lsn = 56844
last_lsn = 60504

As you can see, the last_lsn had been changed, yet the from_lsn and to_lsn remain equals.

1 Like
2

I noticed that when I restart MariaDB server and take a new(0 as base, 2 as target) incremental backup(same db data as previous incremental backup), the from_lsn and to_lsn now differs.

Is this something related to the dirty paging(cache) thing?

1 Like
3

Anyone may help with this?

1 Like
4

Hi @foobar

from_lsn an to_lsn are based on checkpointing (at least for PXB, I believe mariabackup, which is based on pxb is the same).

So this means you have this data on your redo log, and the redo follow thread from the backup read up to that point (last_lsn), but the checkpoint has not advanced during your backup (from_lsn, to_lsn).

Nothing to worry about.

1 Like