Announcement

Announcement Module
Collapse
No announcement yet.

XtraBackup ignores --databases directive and use extra (not listed) databases

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

  • XtraBackup ignores --databases directive and use extra (not listed) databases

    Hello to everybody !

    I use the following command to make XtraBackup under Percona 5.5.38-35.2

    innobackupex-1.5.1 --user=root --password=XXXXXXXX --slave-info \
    --defaults-file=/etc/my.cnf --databases="w3i mysql performance_schema phpmyadmin" --stream=tar ./ \
    | gzip -c -1 > /backup/daily/`date '+%F'`_XTRABACKUP_MAIN.tar.gz ;

    So it must backup only
    w3i mysql performance_schema phpmyadmin
    right ?

    QUESTION 1 - why backup applies to the databases what ARE NOT LISTED in
    --databases="w3i mysql performance_schema phpmyadmin"
    ?
    Please, see output below

    QUESTION 2 - real total size of
    --databases="w3i mysql performance_schema phpmyadmin"
    is no more than 3MB only.

    But the size of resulting backup file
    1.5 GB (gzipped) !!!
    It means that XtraBackup REALLY include to backup the big databases "joomla333" and "w2big" and "pt" - although they are NOT INCLUDED in directive
    --databases="w3i mysql performance_schema phpmyadmin"

    WHY XtraBackup "catches" extra (not listed) databases ?!

    Thanks in advance for any hint !

    The output is
    140820 20:36:39 innobackupex-1.5.1: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
    140820 20:36:39 innobackupex-1.5.1: Connected to MySQL server
    140820 20:36:39 innobackupex-1.5.1: Executing a version check against the server...
    140820 20:36:39 innobackupex-1.5.1: Done.
    IMPORTANT: Please check that the backup run completes successfully.
    At the end of a successful backup run innobackupex-1.5.1
    prints "completed OK!".

    innobackupex-1.5.1: Using mysql server version 5.5.38-35.2

    innobackupex-1.5.1: Created backup directory /backup

    140820 20:36:39 innobackupex-1.5.1: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/tmp/mysql --tmpdir=/tmp/mysql --stream=tar
    innobackupex-1.5.1: Waiting for ibbackup (pid=17658) to suspend
    innobackupex-1.5.1: Suspend file '/tmp/mysql/xtrabackup_suspended_2'

    xtrabackup_55 version 2.1.9 for Percona Server 5.5.35 Linux (x86_64) (revision id: 746)
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /var/lib/mysql
    xtrabackup: open files limit requested 65535, set to 65535
    xtrabackup: using the following InnoDB configuration:
    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 = 268435456
    xtrabackup: using O_DIRECT
    >> log scanned up to (6052682682)
    [01] Streaming ./ibdata1
    >> log scanned up to (6052682682)
    [01] ...done
    [01] Streaming ./joomla333/j333_user_usergroup_map.ibd
    [01] ...done
    [01] Streaming ./joomla333/j333_session.ibd
    [01] ...done
    .................................................. .................................................. ..LONG LIST of tables....continue.............
    [01] Streaming ./cb2/j333cb2_finder_links_terms3.ibd
    [01] ...done
    [01] Streaming ./w2big/documents.ibd
    >> log scanned up to (6052682682)
    >> log scanned up to (6052682682)
    .................................................. .................................................. ..LONG LIST of tables....continue.............
    >> log scanned up to (6052682682)
    >> log scanned up to (6052682682)
    [01] ...done
    [01] Streaming ./pt/productsmap.ibd
    [01] ...done
    [01] Streaming ./pt/categories.ibd
    .................................................. .................................................. ..LONG LIST of tables....continue.............
    [01] Streaming ./pt/jobs.ibd
    [01] ...done
    [01] Streaming ./pt/feeds.ibd
    [01] ...done
    xtrabackup: Creating suspend file '/tmp/mysql/xtrabackup_suspended_2' with pid '17658'

    140820 20:39:14 innobackupex-1.5.1: Continuing after ibbackup has suspended
    140820 20:39:14 innobackupex-1.5.1: Starting to lock all tables...
    140820 20:39:14 innobackupex-1.5.1: All tables locked and flushed to disk
    140820 20:39:14 innobackupex-1.5.1: Failed to get master binlog coordinates from SHOW SLAVE STATUS
    140820 20:39:14 innobackupex-1.5.1: This means that the server is not a replication slave. Ignoring the --slave-info option

    140820 20:39:14 innobackupex-1.5.1: Starting to backup non-InnoDB tables and files
    innobackupex-1.5.1: in subdirectories of '/var/lib/mysql'
    innobackupex-1.5.1: Backing up files '/var/lib/mysql/w3i/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM ,CSV,opt,par}' (31 files)
    >> log scanned up to (6052682682)
    innobackupex-1.5.1: Backing up files '/var/lib/mysql/phpmyadmin/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM ,CSV,opt,par}' (18 files)
    innobackupex-1.5.1: Backing up files '/var/lib/mysql/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM ,CSV,opt,par}' (18 files)
    innobackupex-1.5.1: Backing up files '/var/lib/mysql/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM ,CSV,opt,par}' (72 files)
    140820 20:39:15 innobackupex-1.5.1: Finished backing up non-InnoDB tables and files

    140820 20:39:15 innobackupex-1.5.1: Waiting for log copying to finish

    xtrabackup: The latest check point (for incremental): '6052682682'
    xtrabackup: Stopping log copying thread.
    .>> log scanned up to (6052682682)

    xtrabackup: Creating suspend file '/tmp/mysql/xtrabackup_log_copied' with pid '17658'
    xtrabackup: Transaction log of lsn (6052682682) to (6052682682) was copied.
    140820 20:39:16 innobackupex-1.5.1: All tables unlocked

    innobackupex-1.5.1: Backup created in directory '/backup'
    140820 20:39:16 innobackupex-1.5.1: Connection to database server closed
    innobackupex-1.5.1: You must use -i (--ignore-zeros) option for extraction of the tar stream.
    140820 20:39:16 innobackupex-1.5.1: completed OK!
    ********************************FINISH ***********************







Working...
X