xtrabackup single table

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

Добавить комментарий

Ваш адрес email не будет опубликован.

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.