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

Backing up and restoring a single table

binhminh07binhminh07 EntrantCurrent User Role Beginner

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.


  • mirfanmirfan Database Administrator Inactive User Role Beginner
    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
    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.
  • binhminh07binhminh07 Entrant Current User Role Beginner
    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.

    Mysql source version
    Mysql destination vesion

    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

    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`)

    8. Discard tablespace of trn_users;

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

    10.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: Please refer to
    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`

    Thanks so much!!!!
  • binhminh07binhminh07 Entrant Current User Role Beginner
    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...
  • mirfanmirfan Database Administrator Inactive User Role Beginner
    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 and

    Also, you may want to check this thread about it
  • binhminh07binhminh07 Entrant Current User Role Beginner
    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!!!
  • mirfanmirfan Database Administrator Inactive User Role Beginner
    Glad to hear that :)
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.