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

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

SargeSarge EntrantCurrent User Role Beginner
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 ***********************
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.