Percona Data Recovery Tool for InnoDB 복구

InnoDB 데이터 파일을 복구하는 중에 아래와 같이 incorrect key 문제로 복구가 안되는 경우에 percona data recovery tool 을 이용해서 데이터를 복원할 수 있습니다.

mysql> alter table innovation import tablespace;
ERROR 1034 (HY000): Incorrect key file for table 'innovation'; tr
mysql> repair table innovation;

 

아래 percona data recovery tool for innodb 웹페이지를 참고하시기 바랍니다.

https://www.percona.com/docs/wiki/innodb-data-recovery-tool_start.html

 

간략하게 절차는 아래와 같습니다.

  • percona data recovery tool for innodb 설치
  • create_defs.pl 실행 : table schema 정의
  • make : 소스 컴파일
  • constraint__parser 실행 : data 추출

 

[root@ohnew percona]# ./create_defs.pl --host=127.0.0.1 --port=13306 --user=root --db=ohnew --table=innovation > include/table_defs.h
[root@ohnew percona]# cat include/table_defs.h
#ifndef table_defs_h
#define table_defs_h

// Table definitions
table_def_t table_definitions[] = {
 {
 name: "innovation",
 {
 { /* varchar(255) */
 name: "series",
 type: FT_CHAR,
 min_length: 0,
 max_length: 765,

has_limits: FALSE,
 limits: {
 can_be_null: FALSE,
 char_min_len: 0,
 char_max_len: 765,
 char_ascii_only: TRUE
 },

can_be_null: FALSE
 },
 { /* */
 name: "DB_TRX_ID",
 type: FT_INTERNAL,
 fixed_length: 6,

can_be_null: FALSE
 },
 { /* */
 name: "DB_ROLL_PTR",
 type: FT_INTERNAL,
 fixed_length: 7,

can_be_null: FALSE
 },
 { /* bigint(20) */
 name: "user_id",
 type: FT_INT,
 fixed_length: 8,

has_limits: FALSE,
 limits: {
 can_be_null: TRUE,
 int_min_val: -9223372036854775806LL,
 int_max_val: 9223372036854775807LL
 },

can_be_null: TRUE
 },
 { type: FT_NONE }
 }
 },
};

#endif
[root@ohnew percona]# make
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -c tables_dict.c -o lib/tables_dict.o
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -c check_data.c -o lib/check_data.o
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -o constraints_parser constraints_parser.c lib/tables_dict.o lib/print_data.o lib/check_data.o lib/libut.a lib/libmystrings.a
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -static -lrt -o page_parser page_parser.c lib/tables_dict.o lib/libut.a
[root@ohnew percona]# ./constraints_parser -5 -f innovation.ibd > ./innovation.tsv &
[1] 4604
[root@ohnew percona]# LOAD DATA INFILE '/home/percona/innovation.tsv' REPLACE INTO TABLE `innovation` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'innovation\t' (series, user_id);

[1]+ Done ./constraints_parser -5 -f innovation.ibd > ./innovation.tsv

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s