2011.09.26 11:23

MySQL 파티션 테이블 백업 및 복구

ADWorks 파티셔닝 테이블 백업 정책

1. 파티셔닝 테이블
 - ADWORKS_TS

2. 사용하지 않는 테이블 ( adws2v1, adws2v2 Miner 정지 )
 - ADWORKS_NB
 - ADWORKS_SPM
 - ADWORKS_SPM_TS
 - ADWORKS_STAT_BOUNCE
 - ADWORKS_STAT_SPM

3. ADWORKS_TS 데이터는 2년 유지
 ex) 2011년 01월 01일 2008년 데이터 백업 후 삭제 (추후 복구가능 - 하지만 시간이 다소 걸릴 수 있음)

=====================================================================================================

ADWORKS_TS 파티셔닝 테이블 백업

]# mysqldump --user=username --password=password --no-create-info --where="TS_ID<2008030000000000000" adworksv2 ADWORKS_TS > /P0802.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008030000000000000 and TS_ID<2008040000000000000" adworksv2 ADWORKS_TS > /P0803.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008040000000000000 and TS_ID<2008050000000000000" adworksv2 ADWORKS_TS > /P0804.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008050000000000000 and TS_ID<2008060000000000000" adworksv2 ADWORKS_TS > /P0805.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008060000000000000 and TS_ID<2008070000000000000" adworksv2 ADWORKS_TS > /P0806.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008070000000000000 and TS_ID<2008080000000000000" adworksv2 ADWORKS_TS > /P0807.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008080000000000000 and TS_ID<2008090000000000000" adworksv2 ADWORKS_TS > /P0808.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008090000000000000 and TS_ID<2008100000000000000" adworksv2 ADWORKS_TS > /P0809.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008100000000000000 and TS_ID<2008110000000000000" adworksv2 ADWORKS_TS > /P0810.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008110000000000000 and TS_ID<2008120000000000000" adworksv2 ADWORKS_TS > /P0811.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008120000000000000 and TS_ID<2009010000000000000" adworksv2 ADWORKS_TS > /P0812.sql


=====================================================================================================

ADWORKS_TS 파티셔닝 테이블 해당하는 년 복구

1. 해당하는 년의 파티셔닝 테이블을 생성한 후에 백업파일 넣음

 - 해당하는 년의 테이블 생성 (장점 : 운영중인 ADWORKS_TS 테이블에 LOCK 걸리지 않는다. 단점 :  랜딩상세,전환상세 보고서 다운로드시 소스를 변경해야한다.)
CREATE TABLE `ADWORKS_TS` (
  `TS_ID` bigint(19) unsigned NOT NULL COMMENT '트랜잭션아이디',
  `LANDING_YN` char(1) DEFAULT '' COMMENT '랜딩Y/N',
  `TARGET_YN` char(1) DEFAULT '' COMMENT '타겟Y/N',
  `OLD_YN` char(1) DEFAULT '' COMMENT '구버전Y/N',
  `VERSION` varchar(4) DEFAULT NULL COMMENT '스크립트버전',
  `SPONSOR_NO` int(8) DEFAULT NULL COMMENT '광고주번호',
  `CMP_KWD` varchar(100) DEFAULT NULL COMMENT '구매키워드',
  `CMP_KWD_NO` varchar(20) DEFAULT NULL COMMENT '구매키워드일련번호',
  `VHC_CD` char(4) DEFAULT NULL COMMENT '광고상품아이디',
  `FST_TS_ID` bigint(19) DEFAULT NULL COMMENT '최초트랜잭션아이디',
  `LST_TS_ID` bigint(19) DEFAULT NULL COMMENT '마지막트랜잭션아이디',
  `VST_URL` varchar(3000) DEFAULT NULL COMMENT '방분URL',
  `REF_URL` varchar(3000) DEFAULT NULL COMMENT '참조URL',
  `TG_PG_NM` varchar(100) DEFAULT NULL COMMENT '타겟페이지 명',
  `TG_PRD_AMT` varchar(22) DEFAULT '0' COMMENT '타겟페이지 구매금액',
  `TG_E1` varchar(100) DEFAULT NULL COMMENT '타겟페이지 구매기타1',
  `TG_E2` varchar(100) DEFAULT NULL COMMENT '타겟페이지 구매기타2',
  `TG_E3` varchar(100) DEFAULT NULL COMMENT '타겟페이지 구매기타3',
  `TG_E4` varchar(100) DEFAULT NULL COMMENT '타겟페이지 구매기타4',
  `TG_E5` varchar(100) DEFAULT NULL COMMENT '타겟페이지 구매기타5',
  `TG_AC` char(4) DEFAULT NULL COMMENT '전환유형',
  `TG_AN` varchar(10) DEFAULT NULL COMMENT '광고주별 전환추젹 설정 인련번호',
  `IP_ADDR` varchar(32) DEFAULT NULL COMMENT '클라이언트아이피',
  `VST_YYYY` char(4) DEFAULT NULL COMMENT '방문 년',
  `VST_MM` char(2) DEFAULT NULL COMMENT '방문 월',
  `VST_DD` char(2) DEFAULT NULL COMMENT '방문 일',
  `VST_HH` char(2) DEFAULT NULL COMMENT '방문 시',
  `VST_MMSS` char(4) DEFAULT NULL COMMENT '방문 분초',
  `VST_WEEK_OF_YEAR` int(10) unsigned DEFAULT NULL COMMENT '방문 주',
  `VST_DAY_OF_WEEK` int(10) unsigned DEFAULT NULL COMMENT '방문 요일',
  `VST_DT` char(10) DEFAULT NULL COMMENT '방문일[2008.12.25]',
  `VST_FST_DT` char(10) DEFAULT NULL COMMENT '최초방문일[2008.12.25]',
  `VST_FST_HH` char(2) DEFAULT NULL,
  `VST_LST_DT` char(10) DEFAULT NULL COMMENT '마지막랜딩방문일[2008.12.25]',
  `VST_LST_HH` char(2) DEFAULT NULL,
  `REF_URL_HOST` varchar(80) DEFAULT NULL COMMENT '참조 URL 호스트',
  `REF_URL_DOMAIN` varchar(80) DEFAULT NULL COMMENT '참조 URL 도메인',
  `QUERY_KWD` varchar(200) DEFAULT NULL COMMENT '쿼리 키워드',
  `CRT_DT` datetime DEFAULT NULL,
  `USER_AGENT` varchar(1024) DEFAULT NULL COMMENT 'user-agent',
  `DEVICE_NM` varchar(50) DEFAULT NULL COMMENT '디바이스명',
  PRIMARY KEY (`TS_ID`),
  KEY `NewIndex1` (`SPONSOR_NO`,`VST_DT`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 
/*!50100 PARTITION BY RANGE (TS_ID) (PARTITION P0802 VALUES LESS THAN (2008030000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0803 VALUES LESS THAN (2008040000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0804 VALUES LESS THAN (2008050000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0805 VALUES LESS THAN (2008060000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0806 VALUES LESS THAN (2008070000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0807 VALUES LESS THAN (2008080000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0808 VALUES LESS THAN (2008090000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0809 VALUES LESS THAN (2008100000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0810 VALUES LESS THAN (2008110000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0811 VALUES LESS THAN (2008120000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0812 VALUES LESS THAN (2009010000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM) */

 - 백업파일 넣음
]# mysql -u username -p adworksv2 < /P0803.sql
]# mysql -u username -p adworksv2 < /P0804.sql
]# mysql -u username -p adworksv2 < /P0805.sql
]# mysql -u username -p adworksv2 < /P0806.sql
]# mysql -u username -p adworksv2 < /P0807.sql
]# mysql -u username -p adworksv2 < /P0808.sql
]# mysql -u username -p adworksv2 < /P0809.sql
]# mysql -u username -p adworksv2 < /P0810.sql
]# mysql -u username -p adworksv2 < /P0811.sql
]# mysql -u username -p adworksv2 < /P0812.sql


2. ADWORKS_TS 테이블에 파티션 재정의 후에 백업파일 넣음

 - 테이블 파이션 재정의 (장점 :  랜딩상세,전환상세 보고서 다운로드시 소스를 변경하지 않아도 된다. 단점 : 운영중인 ADWORKS_TS 테이블에 LOCK 걸린다.)
ALTER TABLE ADWORKS_TS REORGANIZE PARTITION p0 INTO (
PARTITION S0802 VALUES LESS THAN (2008030000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0803 VALUES LESS THAN (2008040000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0804 VALUES LESS THAN (2008050000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0805 VALUES LESS THAN (2008060000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0806 VALUES LESS THAN (2008070000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0807 VALUES LESS THAN (2008080000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0808 VALUES LESS THAN (2008090000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0809 VALUES LESS THAN (2008100000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0810 VALUES LESS THAN (2008110000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0811 VALUES LESS THAN (2008120000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0812 VALUES LESS THAN (2009010000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM
);
 
 - 백업파일 넣음
]# mysql -u username -p adworksv2 < /P0803.sql
]# mysql -u username -p adworksv2 < /P0804.sql
]# mysql -u username -p adworksv2 < /P0805.sql
]# mysql -u username -p adworksv2 < /P0806.sql
]# mysql -u username -p adworksv2 < /P0807.sql
]# mysql -u username -p adworksv2 < /P0808.sql
]# mysql -u username -p adworksv2 < /P0809.sql
]# mysql -u username -p adworksv2 < /P0810.sql
]# mysql -u username -p adworksv2 < /P0811.sql
]# mysql -u username -p adworksv2 < /P0812.sql

 
* 참조 사이트 
1. http://dev-world.springnote.com/pages/6907325

Trackback 0 Comment 0