MariaDB의 FederatedX 엔진을 활용한 9억 데이터 이관기

Overview

대용량 로그 테이블은 때로는 서비스에 지대한 영향을 미치기도 합니다. 게다가 이 테이블을 파티셔닝 구성을 해야하는데, 이를 서비스 운영 중인 상태에서 마스터 장비에서 Import하는 것은 사실 대단히 위험한 시도이기도 하죠.

이런 상황에서 얼마 전 FederatedX엔진을 활용하여 9억 데이터를 이관한 사례가 있는데, 이에 대해 공유하도록 하겠습니다. ^^

Goal

9억 건의 데이터를 Import하는 동안 서비스에는 어떠한 영향도 없어야 하며, 구성 후 어플리케이션 적용 전까지 데이터가 정상적으로 동기화되어야 합니다.

  1. 데이터 이동하는 동안 기존 서비스 영향 최소화 및 문제 발생 시 빠른 원복
  2. 데이터 구성 후 어플리케이션 코드 배포 전까지 데이터 동기화
  3. 데이터 보관 주기 정책에 따른 유연한 대처
    현재는 삭제 주기가 없으나, 추후 정책에 따라 변경 가능

Let me SEE..

가야할 골이 정해졌으니.. 현재 상황에 대해서 분석을 해봐야겠죠. ㅎㅎ 다음은 DB 사용 현황에 대한 내용입니다.

1) 소스 서버

  • Engine : InnoDB
  • QPS(Except SELECT) : 200qps, (MAX)500qps
  • 대상 테이블
    • download_log – 76G / 3.8억
    • open_log – 96G / 5.5억

2) 타겟 서버

  • Engine : TokuDB
  • QPS(Except SELECT) : 150qps, (MAX)300qps

서버 트래픽이 크지는 않지만, 9억 건 이상의 데이터를 타 서비스 DB로 마이그레이션을 해야하는 상황이었습니다. 그렇기에 서비스에 어떠한 영향도 없어야하며, 문제 발생 시에도 빠르게 롤백할 수 있는 방법이 되어야만 합니다.

How to Migrate?

이런 상황에서라면, 타겟 마스터 장비를 소스 마스터 혹은 슬레이브 장비와 리플리케이션을 걸어서 특정 테이블만 데이터 싱크를 맞추는 방법이 있겠습니다.

그러나, 9억 건 이상의 데이터이고, 서비스 영향없이 마스터 장비로 데이터를 넣어야 하기 때문에.. 목적에 적합한 방법은 아닌 듯 하네요. 슬레이브를 활용하고자 해도, 멀티 소스 리플리케이션을 활용할 수 있는 상황도 아니었으며, 기존 서비스 리플리케이션을 건들여야하기 때문에 깔끔해 보이지는 않았고요.

그래서 아이디어를 낸 방법이, 슬레이브 장비에 FederatedX 스토리지 엔진 전용의 MySQL 데몬을 하나 더 띄워서 데이터 이관을 하는 것이었습니다. 서비스 투입 직전 데이터 흐름을 간단하게 그림으로 표현해 보았습니다.

Migrate-With-FerderatedX-1

그렇다면, FederatedX? 뭐냐고요?

바로 이전 포스팅에서 이럴줄 알고 슬쩍 정리를 해봤습니다. ^^ 아래 링크를 쿡~!!
>> https://gywn.net/2014/12/let-me-introduce-federatedx/

자~! 그렇다면.. 데이터 이관에 대해 차근차근 단계적으로 설명하도록 하겠습니다.

  1. Data Dump Backup
  2. Table Creation
  3. Data Import & Sync
  4. Master/Slave Switching
  5. Slave Restore

1) Data Dump Backup

첫 번째 단계입니다. 옮길 대용량 테이블은 두 개이고, 조금이라도 빠르게 데이터를 이관하기 위해서 각 테이블 별로 덤프 파일을 생성합니다. 두 개의 덤프 파일이 모두 동일한 바이너리 로그 포지션을 가져야하기 때문에, UserDB 쪽 슬레이브의 SQL_Thread를 일시적으로 중지 후 포지션을 기록합니다.

>> UserDB Slave (상단 이미지 왼쪽 아래 서버)

슬레이브 시작 시 영향도를 최소화하기 위해, 슬레이브의 SQL Thread만 중지합니다. 그리고 현재 바이너리 로그 포지션을 기록해놓습니다.

mysql> stop slave sql_thread;
Query OK, 0 rows affected(0.00 sec)
 
mysql> show master status;
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.006027 | 18752723 |
+------------------+----------+

>> ContentsDB Slave (상단 이미지 오른쪽 아래 서버)

UserDB 슬레이브 장비에 3306으로 붙어서 직접 데이터를 받아옵니다. 사실 UserDB에서 로컬로 데이터를 내릴 수 있겠지만, 파일로 전송하는 단계를 생략하기 위함입니다.

$ mysqldump -udumpuser -pdumppass \
  --single-transaction --no-create-info \
  --add-locks=false -h user-slave \
  --databases user_db --tables open_log \
  > /backup/open_log.sql &

$ mysqldump -udumpuser -pdumppass \
  --single-transaction --no-create-info \
  --add-locks=false -h user-slave \
  --databases user_db --tables download_log \
  > /backup/download_log.sql &

아참, 덤프하기 전에 dumpuser를 사전에 생성을 해놔야한다는 사실을 잊으면 안되겠죠. ^^

>> UserDB Slave (상단 이미지 왼쪽 아래 서버)

자~ 이제 덤프가 시작되었으니.. UserDB 쪽 슬레이브를 재계합니다.

mysql> start slave sql_thread;
Query OK, 0 rows affected(0.00 sec)

2) Table Creation

FederatedX는 원격의 테이블을 연결시켜주는 실체가 없는 브릿지 역할을 합니다. 즉, “원격 테이블”“형상 테이블” 모두 생성을 해줘야하는 것이죠.테이블 스키마 생성 작업은 모~두 서비스와는 전혀 연관이 없는 ContentsDB 슬레이브(상단 이미지 오른쪽 하단)에서 이루어집니다.

>> 원격 테이블 – 3306포트

먼저, 원격 테이블을 생성합니다. 추후 효과적인 데이터 관리를 위해 파티셔닝 설정도 이 기회에 같이 합니다. ㅎㅎ

CREATE TABLE download_log (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  user_id int(11) unsigned NOT NULL,
  pid bigint(20) unsigned NOT NULL DEFAULT '0',
  sid bigint(20) NOT NULL DEFAULT '0',
  download_dt datetime NOT NULL,
  val1 varchar(45) NOT NULL DEFAULT '',
  val2 varchar(45) NOT NULL DEFAULT '',
  PRIMARY KEY (id, download_dt),
  KEY ix_userid (user_id, download_dt)
) ENGINE=TokuDB AUTO_INCREMENT=400000000
/*!50500 PARTITION BY RANGE COLUMNS(download_dt)
(PARTITION PF_201306 VALUES LESS THAN ('2013-07-01'),
 PARTITION PF_201312 VALUES LESS THAN ('2014-01-01'),
 PARTITION PF_201406 VALUES LESS THAN ('2014-07-01'),
 PARTITION PF_201412 VALUES LESS THAN ('2015-01-01')) */;

CREATE TABLE open_log (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  user_id int(11) unsigned NOT NULL,
  pid bigint(20) unsigned NOT NULL,
  sid bigint(20) NOT NULL DEFAULT '0',
  product_type char(4) NOT NULL DEFAULT '',
  open_dt datetime NOT NULL,
  PRIMARY KEY (id, open_dt),
  KEY ix_userid_productid (user_id, open_dt)
) ENGINE=TokuDB AUTO_INCREMENT=600000000
/*!50500 PARTITION BY RANGE COLUMNS(open_dt)
(PARTITION PF_201306 VALUES LESS THAN ('2013-07-01'),
 PARTITION PF_201312 VALUES LESS THAN ('2014-01-01'),
 PARTITION PF_201406 VALUES LESS THAN ('2014-07-01'),
 PARTITION PF_201412 VALUES LESS THAN ('2015-01-01')) */;

>> 형상 테이블(FederatedX) – 13306포트

UserDB로부터 받아온 데이터를 ContentsDB로 전달하는 FederatedX 테이블 스키마입니다. 원본 서버와는 완벽하게 같을 필요는 없으나, 만약 바이너리 로그가 SQL기반으로 기록된다면, 관련 인덱스를 어느정도 맞춰놓는 것이 좋습니다. 여기서는 생략~!

CREATE TABLE download_log (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  user_id int(11) unsigned NOT NULL,
  pid bigint(20) unsigned NOT NULL DEFAULT '0',
  sid bigint(20) NOT NULL DEFAULT '0',
  download_dt datetime NOT NULL,
  val1 varchar(45) NOT NULL DEFAULT '',
  val2 varchar(45) NOT NULL DEFAULT '',
  PRIMARY KEY (id)
) ENGINE = FEDERATED connection='mysql://feduser:fedpass@127.0.0.1:3306/contents_db/download_log';
 
CREATE TABLE open_log (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  user_id int(11) unsigned NOT NULL,
  pid bigint(20) unsigned NOT NULL,
  sid bigint(20) NOT NULL DEFAULT '0',
  product_type char(4) NOT NULL DEFAULT '',
  open_dt datetime NOT NULL,
  PRIMARY KEY (id)
) ENGINE = FEDERATED connection='mysql://feduser:fedpass@127.0.0.1:3306/contents_db/open_log';

아참~! 3306 포트로 올라와있는 서버에는 feduser/fedpass로 생성된 유저가 있어야 합니다. 슬레이브가 READ_ONLY일테니 SUPER권한과 함께.. (슬레이브 READ_ONLY를 풀 수도 있겠지만.. 이보다는 전용 DB 접속 유저에 권한을 주는 것이 더 좋을 듯 하네요. ^^)

지금까지 단계가 완료되면, ContentsDB에는 다음과 같이 서버가 구성이 되어 있겠네요.

Migrate-With-FerderatedX-2

3) Data Import & Sync

옮겨야할 데이터도 있고 옮길 그릇도 있으니, 이제 실제 데이터 이관 작업 후 데이터 동기화를 하는 작업만 남았겠네요. ^^

mysql -ufeduser -pfedpass contents_db < /backup/open_log.sql &
mysql -ufeduser -pfedpass contents_db < /backup/download_log.sql &

시간이 꽤 걸리는 이 작업이 완료가 되면, 이제 FederatedX가 떠있는 DB에 접속하여 슬레이브 구성을 합니다.

## replicate_do_table 설정
mysql> set global replicate_do_table = 'user_db.download_log,user_db.open_log ';
 
## 슬레이브 설정
mysql> CHANGE MASTER TO
MASTER_HOST='use-slave',
MASTER_PORT= 3306,
MASTER_USER='repl',
MASTER_PASSWORD='xxxxxx',
MASTER_LOG_FILE='mysql-bin.006027',
MASTER_LOG_POS=18752723;
 
## 슬레이브 시작
start slave;

이 과정까지 되면, 제일 처음 표현했던 이미지대로 데이터가 흐르게 됩니다.

4) Master/Slave Switching

가장 트래픽이 적은 시점에 ContentsDB 쪽 마스터/슬레이브 장비를 스위칭합니다.

MHA같은 솔루션을 사용하고 있다면, 더욱 쉽게 마스터/슬레이브 스위칭이 이루어질 것이고, 스위칭 직후에는 아래와 같은 형태로 데이터가 흐르게 되겠죠. 아참, 스위칭 전에 반드시 다음 단계에서 진행할 슬레이브 복구를 위해, 백업을 해주는 것이 좋겠네요.

Migrate-With-FerderatedX-3

당연한 이야기겠지만, 로그 테이블들이 신규 슬레이브(구 마스터)에는 없기 때문에 ContentsDB 쪽 마스터/슬레이브는 끊어지겠죠.

5) Slave Restore

스위칭 직전 백업 데이터로, 끊어진 슬레이브를 연결합니다. 그리고, 어플리케이션에서 UserDB에 위치한 로그 테이블을 더이상 참조하지 않는 시점에 로그 테이블을 제거하도록 합니다. 이 시점에서는 더이상 FederatedX 테이블도 필요 없기 때문에, 13306포트로 구동 중인 DB서버를 셧다운하도록 합니다.

최종적으로는 대형 로그 테이블이 타 DB로 이관이된 아래와 같은 모습이 됩니다. (휴~! 끝)

Migrate-With-FerderatedX-4

 

마스터/슬레이브 스위칭 시 순단 현상은 있었겠지만, 적어도 9억 데이터를 이관하는 동안 어떠한 영향도 없었습니다.

Result

InnoDB를 TokuDB로 이관을 하면서 디스크 사용률이 30% 이하로 줄었습니다. TokuDB에 대한 설명은 굳이 여기에서 하지 않아도 될 것 같네요. ^^ 만약 궁금하시다면, 아래 포스팅 내용을 참고하세요.
https://gywn.net/2014/05/fractal-index-in-tokudb/

이관 후 데이터 사이즈는 다음과 같습니다.

  • download_log
    – 76G -> 21G
  • open_log
    – 96G -> 23G

파티셔닝 구조로 변경하면서, 데이터 보관 주기에 정책에 따라 유연하게 데이터를 유지할 수 있게 되었습니다.

참고로, FederatedX에서 슬레이브를 연결한 직후 동기화 되는 최대 속도는 다음과 같습니다. TokuDB Small 포멧임에도 단일 쓰레드로 3000 query 이상을 발휘합니다.

| Com_insert     | 3268    |
| Com_insert     | 3255    |
| Com_insert     | 3223    |
| Com_insert     | 3200    |
| Com_insert     | 3233    |

Conclusion

지금까지 FederatedX를 사용하여 9억건 데이터를 타 DB로 이관한 사례를 정리하였습니다.

물론, 굳이 이 방법이 아니라고 하더라도 대형 테이블을 타 DB로 이관할 수 있는 방법은 있겠죠. Tungsten Replicator와 같은 솔루션을 활용하거나, 혹은 개발 부서의 적극적인 지원을 받거나..^^

잘 활용되지 않는 FederatedX 엔진이라고 할 지라도, 이러한 용도로 활용을 한다면 꽤 난감한 상황(이를테면 DB 혹은 테이블 명이 변경되는)에서도 유연하게 대처할 수 있겠습니다. 때마침 좋은 사례가 있어서 공유 드립니다.

간만의 포스팅이라 내용이 매끄럽지 않네요. 게다가 하나하나 모두 설명하기에는 무리가 있어서.. 많은 부분을 생략하기도 했고요. ㅠㅠ 기회가 된다면, 이에 대해 조금 더 자세하게 정리할 수 있는 자리가 있으면 좋겠습니다.ㅎㅎ

MySQL에서 테이블 스키마를 “무중단”으로 변경해보자!!

Overview

MySQL은 단순 쿼리 처리 능력은 탁월하나 테이블 스키마 변경 시에는 상당히 불편합니다. 일단 테이블 스키마 변경 구문을 실행하면 임시 테이블 생성 후 데이터를 복사하고, 데이터를 복사하는 동안에는 테이블에 READ Lock이 발생하여 데이터 변경 작업을 수행하지 못합니다. (Table Lock이 걸리죠.)

이 같은 현상은 인덱스, 칼럼 추가/삭제 뿐만 아니라 캐릭터셋 변경 시에도 동일하게 발생합니다. (최근 5.5 버전에서는 인덱스 추가/삭제에서는 임시 테이블을 생성하지 않습니다.)

얼마 전 서비스 요구 사항 중 테이블 칼럼을 무중단으로 변경하는 것이 있었는데, 이에 관해 정리 드리겠습니다.^^

요구사항

서비스 요구사항과 개인적인 요구 사항은 다음과 같습니다.

  1. 최대한 서비스 중단 없이 가능해야 함
  2. 테이블에 Lock이 발생하지 말아야 함
  3. 빠르게 구현해야 함(개인 요구 사항)
  4. 재사용 가능해야 함(개인 요구 사항)
  5. 문제 발생 시 복구가 쉬워야 함

개인적으로 빠르게 적용하는 것과 이와 같은 이슈가 추후 재 발생 시 재사용할 수 있도록 모듈화하자는 것이 목표였습니다.

대상 테이블 분석

대상 테이블은 다음과 같은 특징이 있었습니다.

  1. Auto_increment 옵션이 적용되었으며, Primary Key가 존재함
  2. 데이터 변경에는 Insert와 Delete만 발생(Update 없음)
  3. 연관된 프로시저 및 트리거는 없음

무엇보다 Update가 없기 때문에 조금 더 생각을 심플하게 가져갈 수 있었습니다.

작업 시나리오

How to migrate data to different table

트리거로 기존 테이블에 Insert및 Delete 시 변경 분을 별도 테이블에 저장을 합니다. 그리고 임시 테이블을 만들고 해당 테이블에 기존 테이블 데이터를 이관합니다. 물론 임시 테이블에는 원하는 스키마로 변경된 상태겠죠. Import가 마무리되면 트리거에 쌓인 데이터로 변경 분을 적용하고 최종적으로 테이블을 Rename함으로써 프로세스가 마무리됩니다.

조금더 상세하게 풀자면 다음과 같습니다.

  1. 임시 테이블 생성
    • Insert/Delete 시 변경 사항을 저장할 테이블
      (TAB01_INSERT, TAB01_DELETE)
    • 데이터를 임시로 저장할 테이블
  2. 트리거 생성
    • 원본 테이블에 Delete 발생 시 해당 ROW를 임시 테이블에 저장
    • 원본 테이블에 Insert 발생 시 해당 ROW를 임시 테이블에 저장
  3. 원본 테이블 export/import
    • export -> 테이블명 변경 -> import
    • SED로 테이블 명을 임시 테이블 명으로 변경
  4. 원본 테이블과 임시 테이블 RENAME
    • 원본 테이블 : TAB01 -> TAB01_OLD
    • 임시 테이블 : TAB01_TMP -> TAB01
  5. 테이블 변경 분 저장
    • TAB01_INSERT에 저장된 데이터 Insert
    • TAB01_DELETE에 저장된 데이터 Delete
  6. 트리거, 임시테이블 제거
    • 트리거 : Insert트리거, Delete 트리거
    • 테이블 : TAB01_INSERT, TAB01_DELETE

모든 작업을 어느정도 자동화 구현하기 위해 “작업 스크립트”를 만드는 프로시저와 데이터 이관을 위한 Shell 스크립트를 작성하였습니다.

Shell 스크립트는 Export와 동시에 SED 명령을 통해 자동으로 테이블 이름을 변경하여 Import하도록 구현하였습니다.

DB명, 테이블명,  스키마 변경 내용을 다음과 같이 인자 값으로 넘겨서 프로시저를 호출합니다.
(프로시저 소스는 가장 하단 참조)

call print_rb_query('dbatest', 'TAB01', 'MODIFY ACT_DESC VARCHAR(100) CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI DEFAULT NULL;');

그러면 아래와 같이 결과가 나오는데 Step1, 2, 3을 순차적으로 실행하면 됩니다. 보기 쉽게 주석을 추가하겠습니다.^^

>> Step 1>  Prepare : SQL <<
## 임시 테이블 생성
DROP TABLE IF EXISTS dbatest.TAB01_INSERT;
CREATE TABLE dbatest.TAB01_INSERT LIKE TAB01;
DROP TABLE IF EXISTS dbatest.TAB01_DELETE;
CREATE TABLE dbatest.TAB01_DELETE LIKE TAB01;
DROP TABLE IF EXISTS dbatest.TAB01_TMP;
CREATE TABLE dbatest.TAB01_TMP LIKE TAB01;

## 임시 테이블 스키마 변경
ALTER TABLE dbatest.TAB01_TMP AUTO_INCREMENT = 10660382;
ALTER TABLE dbatest.TAB01_TMP MODIFY ACT_DESC VARCHAR(100) CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI DEFAULT NULL;
DELIMITER $$

## Delete 트리거 생성
DROP TRIGGER IF EXISTS dbatest.TRG_TAB01_DELETE$$
CREATE TRIGGER dbatest.TRG_TAB01_DELETE
AFTER DELETE ON dbatest.TAB01
FOR EACH ROW
BEGIN
INSERT INTO dbatest.TAB01_DELETE VALUES(
    OLD.ACT_ID,
    OLD.ACT_UID,
    OLD.ACT_USER_NAME,
    OLD.ACT_TIME,
    OLD.TO_UID,
    OLD.TO_USER_NAME,
    OLD.ACT_TYPE,
    OLD.POSTID,
    OLD.TAB01,
    OLD.ACT_DESC,
    OLD.BEFORE_USER_NAME,
    OLD.PHOTO_LINK,
    OLD.THUMB_URL,
    OLD.FROM_SERVICE);
END$$

## Insert 트리거 생성
DROP TRIGGER IF EXISTS dbatest.TRG_TAB01_INSERT$$
CREATE TRIGGER dbatest.TRG_TAB01_INSERT
AFTER INSERT ON dbatest.TAB01
FOR EACH ROW
BEGIN
INSERT INTO dbatest.TAB01_INSERT VALUES(
    NEW.ACT_ID,
    NEW.ACT_UID,
    NEW.ACT_USER_NAME,
    NEW.ACT_TIME,
    NEW.TO_UID,
    NEW.TO_USER_NAME,
    NEW.ACT_TYPE,
    NEW.POSTID,
    NEW.TAB01,
    NEW.ACT_DESC,
    NEW.BEFORE_USER_NAME,
    NEW.PHOTO_LINK,
    NEW.THUMB_URL,
    NEW.FROM_SERVICE);
END$$
DELIMITER ;

>> Step 2>  Data Copy : Shell Script <<
## 데이터 마이그레이션
mig_dif_tab.sh dbatest TAB01 TAB01_TMP

>> Step 3>  Final Job : SQL <<
## 변경분 적용
INSERT INTO dbatest.TAB01_TMP SELECT * FROM dbatest.TAB01_INSERT;
DELETE A FROM dbatest.TAB01_TMP A INNER JOIN dbatest.TAB01_DELETE B ON A.ACT_ID = B.ACT_ID;

## 테이블 Rename(Swap)
RENAME TABLE dbatest.TAB01 TO dbatest.TAB01_OLD, dbatest.TAB01_TMP TO dbatest.TAB01;

## 트리거 제거
DROP TRIGGER IF EXISTS dbatest.TRG_TAB01_DELETE;
DROP TRIGGER IF EXISTS dbatest.TRG_TAB01_INSERT;

##변경분 재 적용 (확인 사살)
INSERT INTO dbatest.TAB01 SELECT * FROM dbatest.TAB01_INSERT;
DELETE A FROM dbatest.TAB01 A INNER JOIN dbatest.TAB01_DELETE B ON A.ACT_ID = B.ACT_ID;

## 임시 테이블 제거
DROP TABLE IF EXISTS dbatest.TAB01_INSERT;
DROP TABLE IF EXISTS dbatest.TAB01_DELETE;

임시 테이블에는 Auto_increment 값을 기존보다 1% 높게 설정하여 테이블명 Swap 후 Primary Key 충돌이 없도록 하였습니다. 그리고 Insert ignore문을 사용하여 Primary Key 중복으로 발생하는 오류는 무시하도록 하였고, 테이블 Rename 후 변경분을 재 적용하는 확인사살도 하였습니다. ^^;;

Shell 스크립트

위에서 Step2에서 사용하는 Shell 스크립트는 다음과 같습니다.

#!/bin/sh
if [ $# -ne 3 ]; then
echo "Usage: ${0} <Database_Name> <Orignal_Table> <Target_Table>"
echo "<Example>"
echo "Database Name : snsdb"
echo "Orignal Table : tab01"
echo "Target Table  : tab01_tmp"
echo "==> ${0} snsdb tab01 tab01_tmp"
exit 1
fi
## Declare Connection Info
export DB_CONNECT_INFO="-u계정 -p패스워드"
export REMOTE_DB_HOST="DB호스트URL"
export REMOTE_DB_PORT="3306"
## Exec profile for mysql user
. ~/.bash_profile
## Dump and Insert Data
mysqldump ${DB_CONNECT_INFO}                           \
  --single-transaction                                 \
  --no-create-db                                       \
  --no-create-info                                     \
  --triggers=false                                     \
  --comments=false                                     \
  --add-locks=false                                    \
  --disable-keys=false                                 \
  --host=${REMOTE_DB_HOST}                             \
  --port=${REMOTE_DB_PORT}                             \
  --databases ${1}                                     \
  --tables ${2}                                        \
| sed -r 's/^INSERT INTO `'${2}'`/INSERT INTO `'${3}'`/gi' \
| mysql ${DB_CONNECT_INFO} -h ${REMOTE_DB_HOST} -P ${REMOTE_DB_PORT} ${1}

Export 값을 파이프(|)로 sed로 넘기고, sed에서는 테이블명만 정규식으로 바꿔서 최종적으로 Import하는 구문입니다.

Create Table As Select 혹은 Insert into Select 구문을 사용하지 않은 이유는 Redo 로그가 비대해짐에 따라 시스템 부하가 따르기 때문입니다. tx_isolation 값을 READ-COMMITTED로 설정하여도 데이터가 전체 들어간 시점에 일시적인 테이블 Lock이 발생합니다. (트랜잭션을 마무리하는 과정에서 발생하는 Lock입니다.) “MySQL 트랜잭션 Isolation Level로 인한 장애 사전 예방 법” 을 참고하세요.^^

프로시저

단일 테이블이라면 위에 있는 스크립트를 약간만 수정하면 되겠지만, 제 경우는 변경할 테이블이 꽤 많아서 스크립트를 생성하는 프로시저를 아래와 같이 구현하였습니다.

사실 모든 과정을 Perl 혹은 Java로 구현하면 Step없이 간단했겠지만, DB 접속 서버에 구성하기 위해서는 절차 상 복잡한 부분이 있어서 프로시저로 작성하였습니다. ㅎㅎ

DELIMITER $$
DROP PROCEDURE print_rb_query $$
CREATE PROCEDURE print_rb_query(IN P_DB VARCHAR(255), IN P_TAB VARCHAR(255), IN P_ALTER_STR VARCHAR(1024))
BEGIN
    ## Declare Variables
    SET @qry = '\n';
    SET @ai_num = 0;
    SET @ai_name = '';
    SET @col_list = '';

    ## Get AUTO_INCREMENT Number for Temporary Table
    SELECT CAST(AUTO_INCREMENT*1.01 AS UNSIGNED) INTO @ai_num
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = P_DB
    AND TABLE_NAME = P_TAB;

    ## Get auto_increment Column Name
    SELECT COLUMN_NAME INTO @ai_name
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = P_DB
    AND TABLE_NAME = P_TAB
    AND EXTRA = 'auto_increment';

    SET @qry = CONCAT(@qry, '>> Step 1>  Prepare : SQL <<\n');
    SET @qry = CONCAT(@qry, 'DROP TABLE IF EXISTS ',P_DB,'.',P_TAB,'_INSERT;\n');
    SET @qry = CONCAT(@qry, 'CREATE TABLE ',P_DB,'.',P_TAB,'_INSERT LIKE ',P_TAB,';\n');
    SET @qry = CONCAT(@qry, 'DROP TABLE IF EXISTS ',P_DB,'.',P_TAB,'_DELETE;\n');
    SET @qry = CONCAT(@qry, 'CREATE TABLE ',P_DB,'.',P_TAB,'_DELETE LIKE ',P_TAB,';\n');
    SET @qry = CONCAT(@qry, 'DROP TABLE IF EXISTS ',P_DB,'.',P_TAB,'_TMP;\n');
    SET @qry = CONCAT(@qry, 'CREATE TABLE ',P_DB,'.',P_TAB,'_TMP LIKE ',P_TAB,';\n');
    SET @qry = CONCAT(@qry, 'ALTER TABLE ',P_DB,'.',P_TAB,'_TMP AUTO_INCREMENT = ',@ai_num,';\n');
    SET @qry = CONCAT(@qry, 'ALTER TABLE ',P_DB,'.',P_TAB,'_TMP ',P_ALTER_STR,'\n');

    ## Change Delimiter
    SET @qry = CONCAT(@qry, 'DELIMITER $$\n');

    ## Get Column List for Delete Trigger
    select GROUP_CONCAT('\n    OLD.',COLUMN_NAME) into @col_list
    from INFORMATION_SCHEMA.COLUMNS
    where TABLE_SCHEMA = P_DB
    and table_name = P_TAB
    order by ORDINAL_POSITION;

    SET @qry = CONCAT(@qry, 'DROP TRIGGER IF EXISTS ',P_DB,'.TRG_',P_TAB,'_DELETE$$\n');
    SET @qry = CONCAT(@qry, 'CREATE TRIGGER ',P_DB,'.TRG_',P_TAB,'_DELETE\n');
    SET @qry = CONCAT(@qry, 'AFTER DELETE ON ',P_DB,'.',P_TAB,'\n');
    SET @qry = CONCAT(@qry, 'FOR EACH ROW\n');
    SET @qry = CONCAT(@qry, 'BEGIN\n');
    SET @qry = CONCAT(@qry, 'INSERT INTO ',P_DB,'.',P_TAB,'_DELETE VALUES(');
    SET @qry = CONCAT(@qry, @col_list);
    SET @qry = CONCAT(@qry, ');\n');
    SET @qry = CONCAT(@qry, 'END$$\n');

    ## Get Column List for Insert Trigger
    select GROUP_CONCAT('\n    NEW.',COLUMN_NAME) into @col_list
    from INFORMATION_SCHEMA.COLUMNS
    where TABLE_SCHEMA = P_DB
    and table_name = P_TAB
    order by ORDINAL_POSITION;
    SET @qry = CONCAT(@qry, 'DROP TRIGGER IF EXISTS ',P_DB,'.TRG_',P_TAB,'_INSERT$$\n');
    SET @qry = CONCAT(@qry, 'CREATE TRIGGER ',P_DB,'.TRG_',P_TAB,'_INSERT\n');
    SET @qry = CONCAT(@qry, 'AFTER INSERT ON ',P_DB,'.',P_TAB,'\n');
    SET @qry = CONCAT(@qry, 'FOR EACH ROW\n');
    SET @qry = CONCAT(@qry, 'BEGIN\n');
    SET @qry = CONCAT(@qry, 'INSERT INTO ',P_DB,'.',P_TAB,'_INSERT VALUES(');
    SET @qry = CONCAT(@qry, @col_list);
    SET @qry = CONCAT(@qry, ');\n');
    SET @qry = CONCAT(@qry, 'END$$\n');

    ## Change Delimiter
    SET @qry = CONCAT(@qry, 'DELIMITER ;\n');
    SET @qry = CONCAT(@qry, '\n\n');

    ## Insert Data
    SET @qry = CONCAT(@qry, '>> Step 2>  Data Copy : Shell Script <<\n');
    SET @qry = CONCAT(@qry, 'mig_dif_tab.sh ',P_DB,' ',P_TAB,' ',P_TAB,'_TMP\n');
    SET @qry = CONCAT(@qry, '\n\n');
    SET @qry = CONCAT(@qry, '>> Step 3>  Final Job : SQL <<\n');

    ## Insert Data into Temporary Table
    SET @qry = CONCAT(@qry, 'INSERT IGNORE INTO ',P_DB,'.',P_TAB,'_TMP SELECT * FROM ',P_DB,'.',P_TAB,'_INSERT;\n');

    ## Delete Data from Temporary Table
    SET @qry = CONCAT(@qry, 'DELETE A FROM ',P_DB,'.',P_TAB,'_TMP A INNER JOIN ',P_DB,'.',P_TAB,'_DELETE B ON A.',@ai_name,' = B.',@ai_name,';\n');

    ## Swap table names
    SET @qry = CONCAT(@qry, 'RENAME TABLE ',P_DB,'.',P_TAB,' TO ',P_DB,'.',P_TAB,'_OLD, ',P_DB,'.',P_TAB,'_TMP TO ',P_DB,'.',P_TAB,';\n');

    ## Drop Triggers
    SET @qry = CONCAT(@qry, 'DROP TRIGGER IF EXISTS ',P_DB,'.TRG_',P_TAB,'_DELETE;\n');
    SET @qry = CONCAT(@qry, 'DROP TRIGGER IF EXISTS ',P_DB,'.TRG_',P_TAB,'_INSERT;\n');

    ## Insert Data
    SET @qry = CONCAT(@qry, 'INSERT IGNORE INTO ',P_DB,'.',P_TAB,' SELECT * FROM ',P_DB,'.',P_TAB,'_INSERT;\n');

    ## Delete Data
    SET @qry = CONCAT(@qry, 'DELETE A FROM ',P_DB,'.',P_TAB,' A INNER JOIN ',P_DB,'.',P_TAB,'_DELETE B ON A.',@ai_name,' = B.',@ai_name,';\n');

    ## Drop Temporary Tables
    SET @qry = CONCAT(@qry, 'DROP TABLE IF EXISTS ',P_DB,'.',P_TAB,'_INSERT;\n');
    SET @qry = CONCAT(@qry, 'DROP TABLE IF EXISTS ',P_DB,'.',P_TAB,'_DELETE;\n');
    SET @qry = CONCAT(@qry, '\n\n');
    SELECT @qry;
END$$
DELIMITER ;

Conclusion

결과적으로 서비스 영향없이 무중단으로 테이블 스키마를 변경하였습니다. 만약 Update가 있는 경우라면 “INSERT INTO .. ON DUPLICATE KEY UPDATE..” 문을 사용하면 해결 가능하다는 생각이 드네요.^^ 물론 Update 트리거에도 Insert와 동일한 액션이 취해져야 하겠죠.

서비스 중단없이 DB 스키마를 변경했다는 점에서 큰 의의가 있었네요.^^

MySQL DB 데이터 이관 자동화 구현하기

Overview

DB를 운영하다 보면, 한 개의 MySQL 인스턴스에 여러 개의 데이터베이스를 모아서 보관하는 경우가 있습니다. 그러면 가끔 DB명이 충돌나는 경우도 발생하죠. 오늘은 Dump/Rename/Import 등 모든 프로세스를 자동화할 수 있는 방안을 제시해 봅니다.

요구사항

무조건 자동으로 동작해야 하고, 기억력이 나쁜 제가 나중에 사용하기 쉽게 재사용성도 좋아야한다는 것입니다. 그리고 사용 방법을 잊어도 쉽게 상기할 수 있는 방안도 있어야겠죠.ㅋ (제가 정한 요구사항입니다. ㅋ)

  1. 모든 프로세스는 자동화되어야 한다.
  2. 스크립트 수정 없이 재사용이 가능해야 한다.
  3. 사용 매뉴얼이 있어야 한다.

자동화 구현

프로세스 순서는 다음과 같고 2단계부터는 파이프( “|” )로 한번에 처리합니다.

  1. 인자 수 체크
  2. 로컬 데이터베이스 Drop & Create (Rename)
  3. 원격 데이터 Dump
  4. Dump 파일에서 DB명 Rename
  5. 로컬 데이터 베이스로 Import

SHELL 스크립트

작성한 SHELL 스크립트 원본이고, 이관 작업은 데이터베이스 레벨로 이루어집니다. 테이블 레벨로의 확장은 하단 스크립트를 약간만 변경하면 될 것 같네요. (따로 구현은 안했습니다. ㅎ)

참고로 예전 포스팅 “리눅스에 MySQL 설치하기(CentOS 5.6)“대로 서버를 설치를 했으면, .bash_profile 에 mysql root 패스워드가 명시되어 있을 것입니다. 아래 스크립트는 .bash_profile 이 필요합니다.

#!/bin/sh

if [ $# -ne 5 ]; then
echo "Usage: ${0} <REMOTE_HOST> <REMOTE_DB> <REMOTE_DB_ID> <REMOTE_DB_PW> <LOCAL_TARGET_DB>"
echo "<Example>"
echo "REMOTE_HOST : 192.168.100.10"
echo "REMOTE_DB : snsdb"
echo "REMOTE_DB_ID : sns"
echo "REMOTE_DB_PW : sns12#"
echo "LOCAL_TARGET_DB : kth_snsdb"
echo "==> ${0} targetdb01 snsdb sns sns12# kth_snsdb"
exit 1
fi

## Exec profile for mysql user
. ~/.bash_profile

## Declare Valiables
REMOTE_HOST=${1}
REMOTE_DB=${2}
REMOTE_DB_ID=${3}
REMOTE_DB_PW=${4}
LOCAL_TARGET_DB=${5}

echo ">> Start Migration $db database"

echo ">> Drop and Create Database at Local Host"
mysql -uroot -p${ADMIN_PWD} -e"DROP DATABASE IF EXISTS ${LOCAL_TARGET_DB};CREATE DATABASE ${LOCAL_TARGET_DB};"

echo ">> Dump, Rename DB name, Insert Data"
mysqldump -u${REMOTE_DB_ID} -p${REMOTE_DB_PW} --host=${REMOTE_HOST} --single-transaction --no-create-db --databases ${REMOTE_DB} | sed -r 's/^USE `(.*)`;$/USE `'${LOCAL_TARGET_DB}'`/g' | mysql -uroot -p${ADMIN_PWD} ${LOCAL_TARGET_DB}

echo ">> Finish Migration $db database"

실제 사용은 다음과 같습니다.

$ ./mig_service_data 192.168.10.111 dbname dbuser dbpass dbname_renamed
>> Start Migration database
>> Drop and Create Database at Local Host
>> Dump, Rename DB name, Insert Data
>> Finish Migration database

모를 때는 아래와 같이 Script만 실행하면 매뉴얼이 나와요~

$ ./mig_service_data 
Usage: ./mig_service_data <REMOTE_HOST> <REMOTE_DB> <REMOTE_DB_ID> <REMOTE_DB_PW> <LOCAL_TARGET_DB>
<Example>
REMOTE_HOST : 192.168.100.10
REMOTE_DB : snsdb
REMOTE_DB_ID : sns
REMOTE_DB_PW : sns12#
LOCAL_TARGET_DB : kth_snsdb
==> ./mig_service_data targetdb01 snsdb sns sns12# kth_snsdb

Conclusion

위 프로세스는 Dump파일을 직접 열고, 데이터베이스 명을 원하는 이름으로 변경하여 진행해도 상관없습니다. 그러나 작은 운영이라도 최대한 자동화하여 운영 이슈를 최소화 하는 것을 저는 지향합니다. 설혹, 구현한 스크립트가 단 한번만 사용될 지언정^^;;