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

MyISAM and InnoDB tables backup using innobackupex

kannan2410kannan2410 EntrantInactive User Role Beginner
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..

Comments

  • psongpsong Contributor Inactive User Role Beginner
    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
  • kannan2410kannan2410 Entrant Inactive User Role Beginner
    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

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



    3) # 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..
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.