Backing up and restoring a single table

MySQL & MariaDB Percona XtraBackup
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

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

  2. 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=‘ユーザテーブル’;

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

  2. 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
ALTER TABLE TEST_RESTORE.trn_users IMPORT TABLESPACE;

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

  2. 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: [url]http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html[/url]
    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!!!