GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MyISAM and InnoDB tables backup using innobackupex

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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..
    Last edited by kannan2410; 07-17-2014, 01:55 AM.

  • #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

    Comment


    • #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

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

      Comment

      Working...
      X