GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Backing up and restoring a single table

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

  • Backing up and restoring a single table

    Hi,

    Sorry for my bad English.

    I have a database system with one master and slave , about 1TB data ><
    System not yet backup before.

    Now , I want to use xtrabackup to backup single table .
    I read Partial Backup but I have 4 problem as the following:

    1. Is it Ok if I run fullbackup with data 1TB?
    2. Is it nesscessary to run fullbackup or full database before backup a single table ( I guest no need but..)
    3. ALL of my table is innodb but when run backup , the message on screen alway show information ALL TABLE IS LOCKED AND FLUSHED TO DISK.
    it is ok?

    4. When I run as the introduction at "partial backup " with single table , on step IMPORT TABLESPACE, it alway occur error like that

    140120 19:25:33 InnoDB: Error: tablespace id and flags in file './test_restore/trn_users.ibd' are 321 and 0, but in the InnoDB
    InnoDB: data dictionary they are 327 and 0.

    whats happening?

    Please help me!!!

    Thanks in advance.
    Last edited by binhminh07; 01-20-2014, 06:01 AM.

  • #2
    1) yes, it's absolutely fine, i saw xtrabackup running without any problems over terabytes of dataset.
    2) No.
    3) Xtrabackup requires FLUSH TABLES WITH READ LOCK (FTWRL) to copy non-innodb tables and table structure files (.frm files). If all your tables are innodb and you don't issue any DDL during course of problem you can avoid FTWRL by using --no-lock option to avoid tables being locked. As --no-lock describes use this option if you don't care backup binary log position so backup doesn't contains xtrabackup_binlog_info file if backup is created with --no-lock option because --no-lock prevents FTWRL which is required to get consistent positions for binary log. However, FTWRL is normally for short period of times if all your tables are InnoDB. You can read more about it here http://www.percona.com/doc/percona-x...ved_ftwrl.html
    3) It's probably when copying non-innodb and tables structure files (.frm files) so it should be ok.
    4) What is source MySQL version from where you back up ? Can you please post your steps for partial backup and attach backup log too.

    Comment


    • #3
      Thanks for your reply!! ^^

      1> Could you estimate How long time neccessary to backup with 1tb ?
      And I want excute with hot backup, my service can't maintenance or something like that
      I see that althought backup one table but the ibdata and ib_logfile data also copy to backup folder.

      2>
      Mysql source version
      5.5.8-log
      Mysql destination vesion
      5.5.34-32.0-log

      I copied data from Mysql source version to Mysql destination version and insert to database Test_Database_Backup.

      Steps executed:
      1. Backup one table
      innobackupex --no-lock --include='^test_database_backup[.]trn_users' --no-timestamp /tmp/test_single_back

      2. Confirmed directory /tmp/test_single_back

      3.Execute prepare step before restoring
      innobackupex --apply-log --export /tmp/test_single_back

      4.Confirm /tm/test_single/back/test_database_backup had file
      trn_users.frm
      trn_users.ibd
      trn_users.exp
      trn_users.cfg

      5. Log in to Mysql server ( the same server with TEST_DATABASE_BACKUP)

      6. Create a database to restore with name : TEST_RESTORE

      7.create table trn_users with the same schema of Test_Database_Backup
      CREATE TABLE `trn_users` (
      `id` bigint(20) NOT NULL COMMENT 'ユーザID',
      `as_id` varchar(255) DEFAULT NULL,
      `user_name` varchar(100) NOT NULL COMMENT 'ユーザ名',
      `user_status` tinyint(4) NOT NULL DEFAULT '2' COMMENT 'ユーザ状態 : 1:チュートリアル完了\n2:チュートリアル未完了\n3:不正アクセス',
      `user_category` tinyint(4) DEFAULT NULL COMMENT 'ユーザ属性',
      `tutorial_num` varchar(50) DEFAULT NULL COMMENT 'チュートリアル済番号',
      `tutorial_finish_datetime` datetime DEFAULT NULL COMMENT 'チュートリアル完了日時',
      `last_login_datetime` datetime DEFAULT NULL COMMENT '最終ログイン日時',
      `continue_login_day` smallint(6) DEFAULT NULL COMMENT '連続日数',
      `af` varchar(255) DEFAULT NULL,
      `create_datetime` datetime DEFAULT NULL COMMENT '作成日時',
      PRIMARY KEY (`id`),
      KEY `condition1` (`last_login_datetime`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ユーザテーブル';

      8. Discard tablespace of trn_users;
      ALTER TABLE TEST_RESTORE.trn_users DISCARD TABLESPACE;

      9. Copy trn_users.ibd and trn_users.exp FROM /tm/test_single/back/test_database_backup to /var/lib/mysql/test_restore
      and change owner to mysql user

      10.Import Tablespace
      ALTER TABLE TEST_RESTORE.trn_users IMPORT TABLESPACE;

      11. [ Got error -1 from storage engine] occured

      12. Confirm error log file
      140121 11:38:22 InnoDB: Error: tablespace id and flags in file './test_restore/trn_users.ibd' are 321 and 0, but in the InnoDB
      InnoDB: data dictionary they are 329 and 0.
      InnoDB: Have you moved InnoDB .ibd files around without using the
      InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
      InnoDB: Please refer to
      InnoDB: http://dev.mysql.com/doc/refman/5.5/...-datadict.html
      InnoDB: for how to resolve the issue.
      140121 11:38:22 InnoDB: cannot find or open in the database directory the .ibd file of
      InnoDB: table `test_restore`.`trn_users`
      InnoDB: in ALTER TABLE ... IMPORT TABLESPACE


      Thanks so much!!!!
      Last edited by binhminh07; 01-20-2014, 09:39 PM.

      Comment


      • #4
        I so urgent , please help me.

        I edit ibd file with hex editor, but It also error occured...
        When I excute Alter table, below error :
        ERROR 2006 (HY000): MySQL server has gone away
        No connection. Trying to reconnect...

        Comment


        • #5
          ibdata1 is shared tablespace and it contains data dictionary information, undo information etc and it should be copied during backup regardless it's full backup or partial backup. For partial backup the destination server should be percona server or at minimum it works for Oracle MySQL 5.6 and i can see your destination server is 5.5.34-32.0-log is that percona server or stock oracle mysql ?
          Further on destination server (percona server) innodb_import_table_from_xtrabackup should be enabled prior to table import. Please check here for details http://www.percona.com/doc/percona-x...obackupex.html and http://www.percona.com/doc/percona-x...ables_ibk.html

          Also, you may want to check this thread about it http://www.percona.com/forums/questi...-s-full-backup

          Comment


          • #6
            This is my destination server information
            Server version: 5.5.34-32.0-log Percona Server (GPL),

            Thank you very much Mirfan. After add innodb_import_table_from_xtrabackup = 1 to my.cnf , restore worked!!!

            Thanks for your support!!!

            Comment


            • #7
              Glad to hear that

              Comment

              Working...
              X