I copied data from Mysql source version to Mysql destination version and insert to database Test_Database_Backup.<\/p>\n
Steps executed:
\n1. Backup one table
\ninnobackupex —no-lock —include=’^test_database_backup[.]trn_users’ —no-timestamp \/tmp\/test_single_back<\/p>\n
2. Confirmed directory \/tmp\/test_single_back<\/p>\n
3.Execute prepare step before restoring
\ninnobackupex —apply-log —export \/tmp\/test_single_back<\/p>\n
4.Confirm \/tm\/test_single\/back\/test_database_backup had file
\ntrn_users.frm
\ntrn_users.ibd
\ntrn_users.exp
\ntrn_users.cfg<\/p>\n
5. Log in to Mysql server ( the same server with TEST_DATABASE_BACKUP)<\/p>\n
6. Create a database to restore with name : TEST_RESTORE<\/p>\n
7.create table trn_users with the same schema of Test_Database_Backup
\nCREATE TABLE `trn_users` (
\n`id` bigint(20) NOT NULL COMMENT ‘\u30e6\u30fc\u30b6ID’,
\n`as_id` varchar(255) DEFAULT NULL,
\n`user_name` varchar(100) NOT NULL COMMENT ‘\u30e6\u30fc\u30b6\u540d’,
\n`user_status` tinyint(4) NOT NULL DEFAULT ‘2’ COMMENT ‘\u30e6\u30fc\u30b6\u72b6\u614b : 1\uff1a\u30c1\u30e5\u30fc\u30c8\u30ea\u30a2\u30eb\u5b8c\u4e86\\n2\uff1a\u30c1\u30e5\u30fc\u30c8\u30ea\u30a2\u30eb\u672a\u5b8c\u4e86\\n3\uff1a\u4e0d\u6b63\u30a2\u30af\u30bb\u30b9’,
\n`user_category` tinyint(4) DEFAULT NULL COMMENT ‘\u30e6\u30fc\u30b6\u5c5e\u6027’,
\n`tutorial_num` varchar(50) DEFAULT NULL COMMENT ‘\u30c1\u30e5\u30fc\u30c8\u30ea\u30a2\u30eb\u6e08\u756a\u53f7’,
\n`tutorial_finish_datetime` datetime DEFAULT NULL COMMENT ‘\u30c1\u30e5\u30fc\u30c8\u30ea\u30a2\u30eb\u5b8c\u4e86\u65e5\u6642’,
\n`last_login_datetime` datetime DEFAULT NULL COMMENT ‘\u6700\u7d42\u30ed\u30b0\u30a4\u30f3\u65e5\u6642’,
\n`continue_login_day` smallint(6) DEFAULT NULL COMMENT ‘\u9023\u7d9a\u65e5\u6570’,
\n`af` varchar(255) DEFAULT NULL,
\n`create_datetime` datetime DEFAULT NULL COMMENT ‘\u4f5c\u6210\u65e5\u6642’,
\nPRIMARY KEY (`id`),
\nKEY `condition1` (`last_login_datetime`)
\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’\u30e6\u30fc\u30b6\u30c6\u30fc\u30d6\u30eb’;<\/p>\n
8. Discard tablespace of trn_users;
\nALTER TABLE TEST_RESTORE.trn_users DISCARD TABLESPACE;<\/p>\n
9. Copy trn_users.ibd and trn_users.exp FROM \/tm\/test_single\/back\/test_database_backup to \/var\/lib\/mysql\/test_restore
\nand change owner to mysql user<\/p>\n
10.Import Tablespace
\nALTER TABLE TEST_RESTORE.trn_users IMPORT TABLESPACE;<\/p>\n
11. [ Got error -1 from storage engine] occured<\/p>\n
12. Confirm error log file
\n140121 11:38:22 InnoDB: Error: tablespace id and flags in file ‘.\/test_restore\/trn_users.ibd’ are 321 and 0, but in the InnoDB
\nInnoDB: data dictionary they are 329 and 0.
\nInnoDB: Have you moved InnoDB .ibd files around without using the
\nInnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
\nInnoDB: Please refer to
\nInnoDB: http:\/\/dev.mysql.com\/doc\/refman\/5.5\/…-datadict.html
\nInnoDB: for how to resolve the issue.
\n140121 11:38:22 InnoDB: cannot find or open in the database directory the .ibd file of
\nInnoDB: table `test_restore`.`trn_users`
\nInnoDB: in ALTER TABLE … IMPORT TABLESPACE<\/p>\n","protected":false},"excerpt":{"rendered":"
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… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15,6],"tags":[21],"class_list":["post-833","post","type-post","status-publish","format-standard","hentry","category-database","category-linux","tag-percona"],"yoast_head":"\n