MyISAM and InnoDB tables backup using innobackupex

MySQL & MariaDB Percona XtraBackup
1

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…

2

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

3

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…