MyISAM and InnoDB tables backup using innobackupex

Hi,

Can we take backup MyISAM and InnoDB engines tables using innobackupex…?

In my DB having tables with MyISAM and InnoDB engines.

While taking backup I can restore successful only tables which are using in MyISAM engine.

<table.ibd> is missing in my backup for InnoDB tables.

This is my db data-dir…

ls -l /data/mysql/data/testdb/
total 4429856
-rw-rw---- 1 mysql mysql 38460 Jul 14 09:31 ad.frm
-rw-rw---- 1 mysql mysql 15008044 Jul 14 09:31 ad.MYD
-rw-rw---- 1 mysql mysql 5434368 Jul 14 09:31 ad.MYI
-rw-rw---- 1 mysql mysql 8716 Jul 14 09:31 ad_logs.frm
-rw-rw---- 1 mysql mysql 19922944 Jul 14 09:31 ad_logs.ibd
-rw-rw---- 1 mysql mysql 43796 Jul 14 09:31 admin.frm
-rw-rw---- 1 mysql mysql 1017716528 Jul 14 09:32 admin.MYD
-rw-rw---- 1 mysql mysql 811008 Jul 14 09:32 admin.MYI
-rw-rw---- 1 mysql mysql 17036 Jul 14 09:32 audit.frm
-rw-rw---- 1 mysql mysql 3149922304 Jul 14 10:01 audit.ibd
-rw-rw---- 1 mysql mysql 8772 Jul 14 10:00 ca_bids.frm
-rw-rw---- 1 mysql mysql 54525952 Jul 14 10:01 ca_bids.ibd
-rw-rw---- 1 mysql mysql 45494 Jul 14 10:01 cam.frm
-rw-rw---- 1 mysql mysql 272629760 Jul 14 10:04 cam.ibd
-rw-rw---- 1 mysql mysql 65 Jul 14 09:30 db.opt

This is my backup path…

ls -l /data/backup/innobackupex/Full/2014-07-15_09-07-38/testdb/
total 1014808
-rw-r----- 1 root root 38460 Jul 15 09:07 ad.frm
-rw-r----- 1 root root 15008044 Jul 15 09:07 ad.MYD
-rw-r----- 1 root root 5434368 Jul 15 09:07 ad.MYI
-rw-r----- 1 root root 8716 Jul 15 09:07 ad_logs.frm
-rw-r----- 1 root root 43796 Jul 15 09:07 admin.frm
-rw-r----- 1 root root 1017716528 Jul 15 09:07 admin.MYD
-rw-r----- 1 root root 811008 Jul 15 09:07 admin.MYI
-rw-r----- 1 root root 17036 Jul 15 09:07 audit.frm
-rw-r----- 1 root root 8772 Jul 15 09:07 ca_bids.frm
-rw-r----- 1 root root 45494 Jul 15 09:07 cam.frm
-rw-r----- 1 root root 65 Jul 15 09:07 db.opt

Please help me here. Thanks in advance…

Kannan…

Hi Kannan,

I tried innobackupex on a database with mixed MyISAM and InnoDB tables and didn’t have the problem as you had. Please provide:

  1. innobackupex --version
  2. xtrabackupex --version
  3. innobackupex full command and full output

Thanks,
Peiran

Hi Peiran,

Thanks for your reply. The details which you are asked is below. Please check and correct me if anything wrong…

  1. innobackupex --version

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.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

  1. xtrabackup --version

xtrabackup version 2.2.3 based on MySQL server 5.6.17 Linux (x86_64) (revision id: )

  1. innobackupex --user=xxxx --password=xxxx /data/backup/innobackupex/Full/

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.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

140718 10:50:13 innobackupex: Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_group=xtrabackup’ as ‘root’ (using password: YES).
140718 10:50:13 innobackupex: Connected to MySQL server
140718 10:50:13 innobackupex: Executing a version check against the server…
140718 10:50:14 innobackupex: Done.
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints “completed OK!”.

innobackupex: Using mysql server version 5.6.12-56-log

innobackupex: Created backup directory /data/backup/innobackupex/Full/2014-07-18_10-50-14

140718 10:50:14 innobackupex: Starting ibbackup with command: xtrabackup --defaults-group=“mysqld” --backup --suspend-at-end --target-dir=/data/backup/innobackupex/Full/2014-07-18_10-50-14 --tmpdir=/tmp --extra-lsndir=’/tmp’
innobackupex: Waiting for ibbackup (pid=27429) to suspend
innobackupex: Suspend file ‘/data/backup/innobackupex/Full/2014-07-18_10-50-14/xtrabackup_suspended_2’

xtrabackup version 2.2.3 based on MySQL server 5.6.17 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql/data
xtrabackup: open files limit requested 0, set to 999999
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:50M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
xtrabackup: using O_DIRECT

log scanned up to (2728341893)
InnoDB: Attempted to open a previously opened tablespace. Previous tablespace mysql/innodb_index_stats uses space ID: 2 at filepath: ./mysql/innodb_index_stats.ibd. Cannot open tablespace testdb/ad_logs which uses space ID: 2 at filepath: ./testdb/ad_logs.ibd
InnoDB: Attempted to open a previously opened tablespace. Previous tablespace mysql/slave_master_info uses space ID: 4 at filepath: ./mysql/slave_master_info.ibd. Cannot open tablespace testdb/cam which uses space ID: 4 at filepath: ./testdb/cam.ibd
InnoDB: Attempted to open a previously opened tablespace. Previous tablespace mysql/slave_relay_log_info uses space ID: 3 at filepath: ./mysql/slave_relay_log_info.ibd. Cannot open tablespace testdb/ca_bids which uses space ID: 3 at filepath: ./testdb/ca_bids.ibd
InnoDB: Attempted to open a previously opened tablespace. Previous tablespace mysql/innodb_table_stats uses space ID: 1 at filepath: ./mysql/innodb_table_stats.ibd. Cannot open tablespace testdb/admin which uses space ID: 1 at filepath: ./testdb/admin.ibd
[01] Copying ./ibdata1 to /data/backup/innobackupex/Full/2014-07-18_10-50-14/ibdata1

log scanned up to (2728341893)
log scanned up to (2728341893)
[01] …done
[01] Copying ./mysql/innodb_index_stats.ibd to /data/backup/innobackupex/Full/2014-07-18_10-50-14/mysql/innodb_index_stats.ibd
[01] …done

log scanned up to (2728341893)
[01] Copying ./mysql/slave_relay_log_info.ibd to /data/backup/innobackupex/Full/2014-07-18_10-50-14/mysql/slave_relay_log_info.ibd
[01] …done
[01] Copying ./mysql/innodb_table_stats.ibd to /data/backup/innobackupex/Full/2014-07-18_10-50-14/mysql/innodb_table_stats.ibd
[01] …done
[01] Copying ./mysql/slave_worker_info.ibd to /data/backup/innobackupex/Full/2014-07-18_10-50-14/mysql/slave_worker_info.ibd
[01] …done
[01] Copying ./mysql/slave_master_info.ibd to /data/backup/innobackupex/Full/2014-07-18_10-50-14/mysql/slave_master_info.ibd
[01] …done
xtrabackup: Creating suspend file ‘/data/backup/innobackupex/Full/2014-07-18_10-50-14/xtrabackup_suspended_2’ with pid ‘27429’

log scanned up to (2728341893)

140718 10:50:20 innobackupex: Continuing after ibbackup has suspended
140718 10:50:20 innobackupex: Executing FLUSH TABLES WITH READ LOCK…
140718 10:50:20 innobackupex: All tables locked and flushed to disk

140718 10:50:20 innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of ‘/data/mysql/data’
innobackupex: Backing up files ‘/data/mysql/data/performance_schema/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (53 files)
innobackupex: Backing up files '/data/mysql/data/mysql/
.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (74 files)
innobackupex: Backing up files ‘/data/mysql/data/testdb/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}’ (11 files)

log scanned up to (2728341893)
log scanned up to (2728341893)
140718 10:50:23 innobackupex: Finished backing up non-InnoDB tables and files

140718 10:50:23 innobackupex: Executing FLUSH ENGINE LOGS…
140718 10:50:23 innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): ‘2728341893’
xtrabackup: Stopping log copying thread.
.>> log scanned up to (2728341893)

xtrabackup: Creating suspend file ‘/data/backup/innobackupex/Full/2014-07-18_10-50-14/xtrabackup_log_copied’ with pid ‘27429’
xtrabackup: Transaction log of lsn (2728341893) to (2728341893) was copied.
140718 10:50:24 innobackupex: All tables unlocked

innobackupex: Backup created in directory ‘/data/backup/innobackupex/Full/2014-07-18_10-50-14’
innobackupex: MySQL binlog position: filename ‘mysql-bin06.000042’, position 120
140718 10:50:24 innobackupex: Connection to database server closed
140718 10:50:24 innobackupex: completed OK!

Thanks,
Kannan…