Innobackupex Not Backing Up All Data

MySQL & MariaDB Percona XtraBackup
1

I am new to xtrabackup and mysql for that matter and I doing some initial testing on an ubuntu 11.10 image running in KVM.

When doing a full backup it does not appear to backup all data schema etc.

Specifically I have my innodb ibdata files on various NFS mount points and all innodb ibdata files seems to be backed up fine, but in the directory where the data base was created not all data is backed up. This is a simple test DB and under the datadir I have the following sub directories:
mysql
performance_schema
sbtest

Below is the “ls -l” for what was backed up followed by the “ls -l” of the same directory in the database for each directory.

FYI - both the full backup and apply log runs “completed OK!”.

Can anyone shed light on this - what am I doing wrong?

mysql (backuo):
total 20
-rw-rw---- 1 mysql mysql 35 2012-07-28 21:58 general_log.CSM
-rw-rw---- 1 mysql mysql 8592 2012-07-28 13:46 inventory.frm
-rw-rw---- 1 mysql mysql 116 2012-07-28 13:46 inventory.MYD
-rw-rw---- 1 mysql mysql 2048 2012-07-28 18:47 inventory.MYI
-rw-rw---- 1 mysql mysql 35 2012-07-28 21:58 slow_log.CSM

mysql (in database datadir):
total 1044
-rw-rw---- 1 mysql mysql 8820 2012-07-26 13:41 columns_priv.frm
-rw-rw---- 1 mysql mysql 0 2012-07-26 13:41 columns_priv.MYD
-rw-rw---- 1 mysql mysql 4096 2012-07-26 13:41 columns_priv.MYI
-rw-rw---- 1 mysql mysql 9582 2012-07-26 13:41 db.frm
-rw-rw---- 1 mysql mysql 880 2012-07-26 14:19 db.MYD
-rw-rw---- 1 mysql mysql 5120 2012-07-26 14:19 db.MYI
-rw-rw---- 1 mysql mysql 10223 2012-07-26 13:41 event.frm
-rw-rw---- 1 mysql mysql 0 2012-07-26 13:41 event.MYD
-rw-rw---- 1 mysql mysql 2048 2012-07-26 13:41 event.MYI
-rw-rw---- 1 mysql mysql 8665 2012-07-26 13:41 func.frm
-rw-rw---- 1 mysql mysql 0 2012-07-26 13:41 func.MYD
-rw-rw---- 1 mysql mysql 1024 2012-07-26 13:41 func.MYI
-rw-rw---- 1 mysql mysql 35 2012-07-28 21:58 general_log.CSM
-rw-rw---- 1 mysql mysql 0 2012-07-26 13:41 general_log.CSV
-rw-rw---- 1 mysql mysql 8776 2012-07-26 13:41 general_log.frm
-rw-rw---- 1 mysql mysql 8700 2012-07-26 13:41 help_category.frm
-rw-rw---- 1 mysql mysql 22659 2012-07-26 13:41 help_category.MYD
-rw-rw---- 1 mysql mysql 3072 2012-07-26 13:41 help_category.MYI
-rw-rw---- 1 mysql mysql 8612 2012-07-26 13:41 help_keyword.frm
-rw-rw---- 1 mysql mysql 91408 2012-07-26 13:41 help_keyword.MYD
-rw-rw---- 1 mysql mysql 16384 2012-07-26 13:41 help_keyword.MYI
-rw-rw---- 1 mysql mysql 8630 2012-07-26 13:41 help_relation.frm
-rw-rw---- 1 mysql mysql 9252 2012-07-26 13:41 help_relation.MYD
-rw-rw---- 1 mysql mysql 19456 2012-07-26 13:41 help_relation.MYI
-rw-rw---- 1 mysql mysql 8770 2012-07-26 13:41 help_topic.frm
-rw-rw---- 1 mysql mysql 446472 2012-07-26 13:41 help_topic.MYD
-rw-rw---- 1 mysql mysql 20480 2012-07-26 13:41 help_topic.MYI
-rw-rw---- 1 mysql mysql 9510 2012-07-26 13:41 host.frm
-rw-rw---- 1 mysql mysql 0 2012-07-26 13:41 host.MYD
-rw-rw---- 1 mysql mysql 2048 2012-07-26 13:41 host.MYI
-rw-rw---- 1 mysql mysql 8592 2012-07-28 13:46 inventory.frm
-rw-rw---- 1 mysql mysql 116 2012-07-28 13:46 inventory.MYD
-rw-rw---- 1 mysql mysql 2048 2012-07-28 18:47 inventory.MYI
-rw-rw---- 1 mysql mysql 8778 2012-07-26 13:41 ndb_binlog_index.frm
-rw-rw---- 1 mysql mysql 0 2012-07-26 13:41 ndb_binlog_index.MYD
-rw-rw---- 1 mysql mysql 1024 2012-07-26 13:41 ndb_binlog_index.MYI
-rw-rw---- 1 mysql mysql 8586 2012-07-26 13:41 plugin.frm
-rw-rw---- 1 mysql mysql 0 2012-07-26 13:41 plugin.MYD
-rw-rw---- 1 mysql mysql 1024 2012-07-26 13:41 plugin.MYI
-rw-rw---- 1 mysql mysql 9996 2012-07-26 13:41 proc.frm
-rw-rw---- 1 mysql mysql 0 2012-07-26 13:41 proc.MYD
-rw-rw---- 1 mysql mysql 2048 2012-07-26 13:41 proc.MYI
-rw-rw---- 1 mysql mysql 8875 2012-07-26 13:41 procs_priv.frm
-rw-rw---- 1 mysql mysql 0 2012-07-26 13:41 procs_priv.MYD
-rw-rw---- 1 mysql mysql 4096 2012-07-26 13:41 procs_priv.MYI
-rw-rw---- 1 mysql mysql 8800 2012-07-26 13:41 proxies_priv.frm
-rw-rw---- 1 mysql mysql 1386 2012-07-26 13:41 proxies_priv.MYD
-rw-rw---- 1 mysql mysql 5120 2012-07-26 13:41 proxies_priv.MYI
-rw-rw---- 1 mysql mysql 8838 2012-07-26 13:41 servers.frm
-rw-rw---- 1 mysql mysql 0 2012-07-26 13:41 servers.MYD
-rw-rw---- 1 mysql mysql 1024 2012-07-26 13:41 servers.MYI
-rw-rw---- 1 mysql mysql 35 2012-07-28 21:58 slow_log.CSM
-rw-rw---- 1 mysql mysql 0 2012-07-26 13:41 slow_log.CSV
-rw-rw---- 1 mysql mysql 8976 2012-07-26 13:41 slow_log.frm
-rw-rw---- 1 mysql mysql 8955 2012-07-26 13:41 tables_priv.frm
-rw-rw---- 1 mysql mysql 0 2012-07-26 13:41 tables_priv.MYD
-rw-rw---- 1 mysql mysql 4096 2012-07-26 13:41 tables_priv.MYI
-rw-rw---- 1 mysql mysql 8636 2012-07-26 13:41 time_zone.frm
-rw-rw---- 1 mysql mysql 8624 2012-07-26 13:41 time_zone_leap_second.frm
-rw-rw---- 1 mysql mysql 0 2012-07-26 13:41 time_zone_leap_second.MYD
-rw-rw---- 1 mysql mysql 1024 2012-07-26 13:41 time_zone_leap_second.MYI
-rw-rw---- 1 mysql mysql 0 2012-07-26 13:41 time_zone.MYD
-rw-rw---- 1 mysql mysql 1024 2012-07-26 13:41 time_zone.MYI
-rw-rw---- 1 mysql mysql 8606 2012-07-26 13:41 time_zone_name.frm
-rw-rw---- 1 mysql mysql 0 2012-07-26 13:41 time_zone_name.MYD
-rw-rw---- 1 mysql mysql 1024 2012-07-26 13:41 time_zone_name.MYI
-rw-rw---- 1 mysql mysql 8686 2012-07-26 13:41 time_zone_transition.frm
-rw-rw---- 1 mysql mysql 0 2012-07-26 13:41 time_zone_transition.MYD
-rw-rw---- 1 mysql mysql 1024 2012-07-26 13:41 time_zone_transition.MYI
-rw-rw---- 1 mysql mysql 8748 2012-07-26 13:41 time_zone_transition_type.frm
-rw-rw---- 1 mysql mysql 0 2012-07-26 13:41 time_zone_transition_type.MYD
-rw-rw---- 1 mysql mysql 1024 2012-07-26 13:41 time_zone_transition_type.MYI
-rw-rw---- 1 mysql mysql 10630 2012-07-26 13:41 user.frm
-rw-rw---- 1 mysql mysql 760 2012-07-26 16:56 user.MYD
-rw-rw---- 1 mysql mysql 2048 2012-07-27 09:15 user.MYI
-rw-rw-rw- 1 mysql mysql 3584 2012-07-27 18:53 \usr\local\mysql\lib\plugin\cna12.dll

performance_schema (backed up):
directory is empty*

performance_schema (in database datadir):
total 204
-rw-rw---- 1 mysql mysql 8624 2012-07-26 13:41 cond_instances.frm
-rw-rw---- 1 mysql mysql 61 2012-07-26 13:41 db.opt
-rw-rw---- 1 mysql mysql 9220 2012-07-26 13:41 events_waits_current.frm
-rw-rw---- 1 mysql mysql 9220 2012-07-26 13:41 events_waits_history.frm
-rw-rw---- 1 mysql mysql 9220 2012-07-26 13:41 events_waits_history_long.frm
-rw-rw---- 1 mysql mysql 8878 2012-07-26 13:41 events_waits_summary_by_instance.frm
-rw-rw---- 1 mysql mysql 8854 2012-07-26 13:41 events_waits_summary_by_thread_by_event_name.frm
-rw-rw---- 1 mysql mysql 8814 2012-07-26 13:41 events_waits_summary_global_by_event_name.frm
-rw-rw---- 1 mysql mysql 8654 2012-07-26 13:41 file_instances.frm
-rw-rw---- 1 mysql mysql 8800 2012-07-26 13:41 file_summary_by_event_name.frm
-rw-rw---- 1 mysql mysql 8840 2012-07-26 13:41 file_summary_by_instance.frm
-rw-rw---- 1 mysql mysql 8684 2012-07-26 13:41 mutex_instances.frm
-rw-rw---- 1 mysql mysql 8776 2012-07-26 13:41 performance_timers.frm
-rw-rw---- 1 mysql mysql 8758 2012-07-26 13:41 rwlock_instances.frm
-rw-rw---- 1 mysql mysql 8605 2012-07-26 13:41 setup_consumers.frm
-rw-rw---- 1 mysql mysql 8637 2012-07-26 13:41 setup_instruments.frm
-rw-rw---- 1 mysql mysql 8650 2012-07-26 13:41 setup_timers.frm
-rw-rw---- 1 mysql mysql 8650 2012-07-26 13:41 threads.frm

sbtest (from backup):
*** the directory is empty ****

sbtest (from database datadir):
total 12
-rw-rw---- 1 mysql mysql 61 2012-07-26 14:57 db.opt
-rw-rw---- 1 mysql mysql 8632 2012-07-26 15:02 sbtest.frm

2

I added the --defaults-file=‘location of my cnf’ to the innobackupex --copy-back command and it now appears to backup all data.

However, why would I need to do that? I also noticied on the output of the backup where the files where not appropriately backed up the output said they where.

Below is the saved output of showing it said it copied the files (but indeed it did not). So something seems a tad weird…

…

120728 21:58:46 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in
innobackupex: subdirectories of ‘/nfsvol1/largecluster2/mysql/data’
innobackupex: Backing up files ‘/nfsvol1/largecluster2/mysql/data/mysql/.{frm,MYD,MYI,MRG, TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (75 files)
innobackupex: Backing up files '/nfsvol1/largecluster2/mysql/data/performance_schema/.{frm ,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (18 files)
innobackupex: Backing up file ‘/nfsvol1/largecluster2/mysql/data/sbtest/db.opt’
innobackupex: Backing up file ‘/nfsvol1/largecluster2/mysql/data/sbtest/sbtest.frm’
120728 21:58:47 innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files

…

3

Small typo in prior response:
I added --defaults-file option to the innobackupex on full back up.

The wording --copy-back was a “typo” and should have not been there.