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
Rss Feed