Hello
I have been using xtrabackup for a few years, to take full backup of my 3-node MariaDB 10.1.28 Galera cluster, and it is working fine.
So today I decided that I want to optimize a bit, by taking incremental backup most of the time and full backup 1-4 times a day.
I have been taking full backup with xtrabackup > backup.xbstream and this my.cnf config:
[xtrabackup]
backup
galera-info
stream=xbstream
compress
user=xtrabackup
password=foobar
host=foo.bar
which take 1-3 minutes for a full ~ 600MB compressed backup.
I then tried taking a incremental backup, by taking the LSN number from the full backup log:
xtrabackup: The latest check point (for incremental): '379477920952'
xtrabackup --incremental-lsn 379477920952
(stream and compress was removed from the my.cnf)
But it takes forever (+40 min) and xtrabackup seems to generate “dummy” delta files for (table +??).
I can’t upload the delta file, so I have uploaded it to my homeserver: [url]http://188.228.31.139/huh/foo.ibd.delta[/url]
I have also enabled innodb_track_changed_pages, but that didn’t change anything.
Some log:
xtrabackup --incremental-lsn 379477920952 --target-dir=s
171015 18:16:56 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=foo.bar;port=3306;mysql_socket=/var/run/mysqld/mysqld.sock' as 'xtrabackup' (using password: YES).
171015 18:16:56 version_check Connected to MySQL server
171015 18:16:56 version_check Executing a version check against the server...
171015 18:16:56 version_check Done.
171015 18:16:56 Connecting to MySQL server host: foo.bar, user: xtrabackup, password: set, port: 3306, socket: /var/run/mysqld/mysqld.sock
Using server version 10.1.28-MariaDB-1~jessie
xtrabackup version 2.3.9 based on MySQL server 5.6.24 Linux (x86_64) (revision id: fde0e3e)
incremental backup from 379477920952 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1048576
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
xtrabackup: using O_DIRECT
171015 18:16:56 >> log scanned up to (379511019919)
xtrabackup: Generating a list of tablespaces
171015 18:16:57 >> log scanned up to (379511030753)
171015 18:16:58 >> log scanned up to (379511033570)
171015 18:16:59 >> log scanned up to (379511038904)
171015 18:17:00 >> log scanned up to (379511044028)
171015 18:17:01 >> log scanned up to (379511105074)
171015 18:17:02 >> log scanned up to (379511176462)
171015 18:17:03 >> log scanned up to (379511239990)
171015 18:17:04 >> log scanned up to (379511264291)
171015 18:17:05 >> log scanned up to (379511361036)
171015 18:17:06 >> log scanned up to (379511378892)
171015 18:17:07 >> log scanned up to (379511405634)
171015 18:17:08 >> log scanned up to (379511419473)
171015 18:17:09 >> log scanned up to (379511464009)
171015 18:17:10 >> log scanned up to (379511487627)
171015 18:17:11 >> log scanned up to (379511619769)
171015 18:17:12 >> log scanned up to (379511699828)
xtrabackup: using the changed page bitmap
171015 18:17:13 [01] Copying ./ibdata1 to /s/ibdata1.delta
171015 18:17:13 [01] ...done
171015 18:17:13 >> log scanned up to (379511702675)
171015 18:17:13 [01] Copying ./sp_bar/sp_bar_areas.ibd to /s/sp_bar/sp_bar_areas.ibd.delta
171015 18:17:13 [01] ...done
171015 18:17:14 [01] Copying ./sp_bar/sp_players.ibd to /s/sp_bar/sp_players.ibd.delta
171015 18:17:14 [01] ...done
171015 18:17:14 [01] Copying ./sp_bar/block_calendar.ibd to /s/sp_bar/block_calendar.ibd.delta
171015 18:17:14 [01] ...done
[.....]
Is this a bug in MariaDB, xtrabackup or perhaps a missing feature in MariaDB?
Regards Kristian Klausen