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:
-
Backup one table
innobackupex --no-lock --include=’^test_database_backup[.]trn_users’ --no-timestamp /tmp/test_single_back
-
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
-
Log in to Mysql server ( the same server with TEST_DATABASE_BACKUP)
-
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=‘ユーザテーブル’;
-
Discard tablespace of trn_users;
ALTER TABLE TEST_RESTORE.trn_users DISCARD TABLESPACE;
-
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;
-
[ Got error -1 from storage engine] occured
-
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/en/innodb-troubleshooting-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
Thanks so much!!!
