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
