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 ***********************