GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Innobackupex incremental backup size

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Innobackupex incremental backup size

    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

  • #2
    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-x..._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.

    Comment


    • #3
      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!


      Comment


      • #4
        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!



        Comment


        • #5
          Are all of the tables you actually use MyISAM?

          http://www.percona.com/doc/percona-x...obackupex.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.

          Comment

          Working...
          X