Innobackupex incremental backup size

[leserek]

Hi,
I have a problem with incremental backup. I dont have password for mysql-server, and doing full backup this command: innobackupex --user=root /backup_mysql

file xtrabackup_checkpoints:

backup_type = full-prepared
from_lsn = 0
to_lsn = 44556
last_lsn = 44556

then doing incremental backup command:
innobackupex --incremental /backup_mysql/ --incremental-basedir=/backup_mysql/2014-04-24_15-00-12/

backup performed without errors, but this size is the same as full backup, why?

file xtrabackup_checkpoints: backup_type = incremental
from_lsn = 44556
to_lsn = 44556
last_lsn = 44556

[scott.nemes]

Try following this guide and doing it again to make sure you are doing things in the right order:
http://www.percona.com/doc/percona-xtrabackup/2.1/howtos/recipes_ibkx_inc.html

Looking at your incremental xtrabackup_checkpoints, it’s going from and to the same lsn. So either there was no new data changes to backup, or the process you are following is wrong. So I would first make sure you actually have data changes from the full backup (i.e. insert a new record if this is a test database), and then follow the above guide to make sure you are following the correct process.

[leserek]

Hello,
I’ve done the whole backup from the beginning according to your link. The effect is the same, xtrabackup_checkpoints files are identical. Write data to the database all the time because, one of database it is a network monitor logs.

Below the result of full and incremental backup: innobackupex --user=root /backup_mysql/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

140428 10:08:56 innobackupex: Starting mysql with options: --user=‘root’ --unbuffered –
140428 10:08:56 innobackupex: Connected to database with mysql child process (pid=9152)
140428 10:09:02 innobackupex: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints “completed OK!”.

innobackupex: Using mysql Ver 14.14 Distrib 5.1.69, for redhat-linux-gnu (x86_64) using readline 5.1
innobackupex: Using mysql server version Copyright © 2000, 2013, Oracle and/or its affiliates. All rights reserved.

innobackupex: Created backup directory /backup_mysql/2014-04-28_10-09-02
140428 10:09:02 innobackupex: Starting mysql with options: --user=‘root’ --unbuffered –
140428 10:09:02 innobackupex: Connected to database with mysql child process (pid=9175)
140428 10:09:04 innobackupex: Connection to database server closed

140428 10:09:04 innobackupex: Starting ibbackup with command: xtrabackup --defaults-group=“mysqld” --backup --suspend-at-end --target-dir=/backup_mysql/2014-04-28_10-09-02 --tmpdir=/cache
innobackupex: Waiting for ibbackup (pid=9181) to suspend
innobackupex: Suspend file ‘/backup_mysql/2014-04-28_10-09-02/xtrabackup_suspended’

xtrabackup version 2.0.8 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 587)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 536870912

log scanned up to (44556)
[01] Copying ./ibdata1 to /backup_mysql/2014-04-28_10-09-02/ibdata1
[01] …done

log scanned up to (44556)
xtrabackup: Creating suspend file ‘/backup_mysql/2014-04-28_10-09-02/xtrabackup_suspended’ with pid ‘9181’

140428 10:09:06 innobackupex: Continuing after ibbackup has suspended
140428 10:09:06 innobackupex: Starting mysql with options: --user=‘root’ --unbuffered –
140428 10:09:06 innobackupex: Connected to database with mysql child process (pid=9195)

log scanned up to (44556)
log scanned up to (44556)
140428 10:09:08 innobackupex: Starting to lock all tables…

log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
140428 10:09:18 innobackupex: All tables locked and flushed to disk

140428 10:09:18 innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of ‘/var/lib/mysql’
innobackupex: Backing up file ‘/var/lib/mysql/rsyslogdb/SystemEvents.frm’
innobackupex: Backing up file ‘/var/lib/mysql/rsyslogdb/SystemEvents.MYI’

log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
innobackupex: Backing up file ‘/var/lib/mysql/rsyslogdb/SystemEventsProperties.MYD’
innobackupex: Backing up file ‘/var/lib/mysql/rsyslogdb/SystemEventsProperties.frm’
innobackupex: Backing up file ‘/var/lib/mysql/rsyslogdb/db.opt’
innobackupex: Backing up file ‘/var/lib/mysql/rsyslogdb/SystemEventsProperties.MYI’
innobackupex: Backing up file ‘/var/lib/mysql/rsyslogdb/SystemEvents.MYD’

log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
innobackupex: Backing up files ‘/var/lib/mysql/postfix/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (40 files)
innobackupex: Backing up files '/var/lib/mysql/radiusdb/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (28 files)
innobackupex: Backing up files ‘/var/lib/mysql/phpbb_optimist/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (193 files)

log scanned up to (44556)
innobackupex: Backing up files ‘/var/lib/mysql/mysql/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (69 files)
innobackupex: Backing up files '/var/lib/mysql/wordpress/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (118 files)

log scanned up to (44556)
innobackupex: Backing up files ‘/var/lib/mysql/meganet_new/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (82 files)
innobackupex: Backing up files '/var/lib/mysql/meganet/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (82 files)

log scanned up to (44556)
innobackupex: Backing up files ‘/var/lib/mysql/bacula/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (76 files)

log scanned up to (44556)
innobackupex: Backing up file ‘/var/lib/mysql/bweb/db.opt’
innobackupex: Backing up files ‘/var/lib/mysql/redmine/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (145 files)
innobackupex: Backing up files '/var/lib/mysql/loganalyzer/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (34 files)
innobackupex: Backing up file ‘/var/lib/mysql/lego/db.opt’
innobackupex: Backing up files ‘/var/lib/mysql/zabbix/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (325 files)

log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
innobackupex: Backing up files ‘/var/lib/mysql/roundcube_flux/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (49 files)
innobackupex: Backing up files '/var/lib/mysql/mailscanner/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (43 files)

log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
innobackupex: Backing up files ‘/var/lib/mysql/ipplan/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (76 files)

log scanned up to (44556)
140428 10:11:28 innobackupex: Finished backing up non-InnoDB tables and files

140428 10:11:28 innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): ‘44556’
xtrabackup: Stopping log copying thread.
…>> log scanned up to (44556)

xtrabackup: Creating suspend file ‘/backup_mysql/2014-04-28_10-09-02/xtrabackup_suspended’ with pid ‘9181’
xtrabackup: Transaction log of lsn (44556) to (44556) was copied.
140428 10:11:32 innobackupex: All tables unlocked
140428 10:11:32 innobackupex: Waiting for ibbackup (pid=9181) to finish
140428 10:11:32 innobackupex: Connection to database server closed

innobackupex: Backup created in directory ‘/backup_mysql/2014-04-28_10-09-02’
innobackupex: MySQL binlog position: filename ‘’, position
140428 10:11:32 innobackupex: completed OK!

[leserek]

and incremental:

innobackupex --incremental /backup_mysql/ --incremental-basedir=/backup_mysql/2014-04-28_10-09-02/ --user=root

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

140428 10:30:09 innobackupex: Starting mysql with options: --user=‘root’ --unbuffered –
140428 10:30:09 innobackupex: Connected to database with mysql child process (pid=10951)
140428 10:30:15 innobackupex: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints “completed OK!”.

innobackupex: Using mysql Ver 14.14 Distrib 5.1.69, for redhat-linux-gnu (x86_64) using readline 5.1
innobackupex: Using mysql server version Copyright © 2000, 2013, Oracle and/or its affiliates. All rights reserved.

innobackupex: Created backup directory /backup_mysql/2014-04-28_10-30-15
140428 10:30:15 innobackupex: Starting mysql with options: --user=‘root’ --unbuffered –
140428 10:30:15 innobackupex: Connected to database with mysql child process (pid=10974)
140428 10:30:17 innobackupex: Connection to database server closed

140428 10:30:17 innobackupex: Starting ibbackup with command: xtrabackup --defaults-group=“mysqld” --backup --suspend-at-end --target-dir=/backup_mysql/2014-04-28_10-30-15 --tmpdir=/cache --incremental-basedir=’/backup_mysql/2014-04-28_10-09-02/’
innobackupex: Waiting for ibbackup (pid=10980) to suspend
innobackupex: Suspend file ‘/backup_mysql/2014-04-28_10-30-15/xtrabackup_suspended’

xtrabackup version 2.0.8 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 587)
incremental backup from 44556 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 536870912

log scanned up to (44556)
[01] Copying ./ibdata1 to /backup_mysql/2014-04-28_10-30-15/ibdata1.delta
[01] …done

log scanned up to (44556)
xtrabackup: Creating suspend file ‘/backup_mysql/2014-04-28_10-30-15/xtrabackup_suspended’ with pid ‘10980’

140428 10:30:19 innobackupex: Continuing after ibbackup has suspended
140428 10:30:19 innobackupex: Starting mysql with options: --user=‘root’ --unbuffered –
140428 10:30:19 innobackupex: Connected to database with mysql child process (pid=10994)

log scanned up to (44556)
log scanned up to (44556)
140428 10:30:21 innobackupex: Starting to lock all tables…

log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
140428 10:30:31 innobackupex: All tables locked and flushed to disk

140428 10:30:31 innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of ‘/var/lib/mysql’
innobackupex: Backing up file ‘/var/lib/mysql/rsyslogdb/SystemEvents.frm’
innobackupex: Backing up file ‘/var/lib/mysql/rsyslogdb/SystemEvents.MYI’

log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
innobackupex: Backing up file ‘/var/lib/mysql/rsyslogdb/SystemEventsProperties.MYD’
innobackupex: Backing up file ‘/var/lib/mysql/rsyslogdb/SystemEventsProperties.frm’
innobackupex: Backing up file ‘/var/lib/mysql/rsyslogdb/db.opt’
innobackupex: Backing up file ‘/var/lib/mysql/rsyslogdb/SystemEventsProperties.MYI’
innobackupex: Backing up file ‘/var/lib/mysql/rsyslogdb/SystemEvents.MYD’

log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
innobackupex: Backing up files ‘/var/lib/mysql/postfix/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (40 files)

log scanned up to (44556)
log scanned up to (44556)
innobackupex: Backing up files ‘/var/lib/mysql/radiusdb/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (28 files)
innobackupex: Backing up files '/var/lib/mysql/phpbb_optimist/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (193 files)
innobackupex: Backing up files ‘/var/lib/mysql/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (69 files)

log scanned up to (44556)
innobackupex: Backing up files ‘/var/lib/mysql/wordpress/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (118 files)
innobackupex: Backing up files '/var/lib/mysql/meganet_new/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (82 files)
innobackupex: Backing up files ‘/var/lib/mysql/meganet/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (82 files)
innobackupex: Backing up files '/var/lib/mysql/bacula/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (76 files)

log scanned up to (44556)
log scanned up to (44556)
innobackupex: Backing up file ‘/var/lib/mysql/bweb/db.opt’
innobackupex: Backing up files ‘/var/lib/mysql/redmine/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (145 files)
innobackupex: Backing up files '/var/lib/mysql/loganalyzer/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (34 files)
innobackupex: Backing up file ‘/var/lib/mysql/lego/db.opt’
innobackupex: Backing up files ‘/var/lib/mysql/zabbix/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (325 files)

log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
innobackupex: Backing up files ‘/var/lib/mysql/roundcube_flux/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (49 files)
innobackupex: Backing up files '/var/lib/mysql/mailscanner/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (43 files)

log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
log scanned up to (44556)
innobackupex: Backing up files ‘/var/lib/mysql/ipplan/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (76 files)
140428 10:32:52 innobackupex: Finished backing up non-InnoDB tables and files

140428 10:32:52 innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): ‘44556’
xtrabackup: Stopping log copying thread.
.>> log scanned up to (44556)

xtrabackup: Creating suspend file ‘/backup_mysql/2014-04-28_10-30-15/xtrabackup_suspended’ with pid ‘10980’
xtrabackup: Transaction log of lsn (44556) to (44556) was copied.
140428 10:32:56 innobackupex: All tables unlocked
140428 10:32:56 innobackupex: Waiting for ibbackup (pid=10980) to finish
140428 10:32:56 innobackupex: Connection to database server closed

innobackupex: Backup created in directory ‘/backup_mysql/2014-04-28_10-30-15’
innobackupex: MySQL binlog position: filename ‘’, position
140428 10:32:56 innobackupex: completed OK!

[scott.nemes]

Are all of the tables you actually use MyISAM?

www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/incremental_backups_innobackupex.html

Warning
This procedure only affects XtraDB or InnoDB-based tables. Other tables with a different storage engine, e.g. MyISAM, will be copied entirely each time an incremental backup is performed.