Not the answer you need?
Register and ask your own question!

Innobackupex incremental backup size

leserekleserek EntrantCurrent User Role Beginner
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

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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.
  • leserekleserek Entrant Current User Role Beginner
    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 (c) 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!
  • leserekleserek Entrant Current User Role Beginner
    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 (c) 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.nemesscott.nemes MySQL Sage Current User Role Patron
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.