MySQL_5.7의 n-gram 전문 검색을 이상하지 않게 써보아요.

Overview

MySQL5.6부터는 InnoDB에서도 전문검색이 가능하기는 하였습니다만.. 아쉽게도 여전히 공백 기준으로 단어들이 파싱이 되는 “MeCab Full-Text Parser Plugin” 방식으로 동작합니다. 즉, 한국말처럼 공백만으로 단어를 파싱할 수 없는 언어의 경우에는 크게 매력적이지는 않습니다. InnoDB에서 전문검색 인덱싱이 가능하다는 것은 Transaction이 전제로 이루어지는 것이라고 볼 수 있기에.. 리플리케이션 및 시점 백업/복구 측면에서는 혁신으로 볼 수 있습니다.

반드시 Limit로 끊어서 가져오고자 한다면, “Order By”로 정렬을 하세요~ 이 관련해 버그가 있고 조만간 픽스될 예정이기는 합니다. (n-gram 처리 시  스토리지 엔진에서  limit이 영향을 미쳐 제대로된 결과 도출 혹은 최악의 경우 크래시까지 발생할 수 있어요.)

그런데 MySQL 5.7부터는 InnoDB에서도 n-gram 방식의 전문 검색 인덱스를 지원하면서, 한국어/중국어/일본어에서도 효율적인 전문 검색이 가능하게 되었습니다. 물론 mroonga와 같은 Third party 스토리지 엔진을 설치해서 사용을 할 수 있겠지만.. 백업/복구가 늘 이슈가 늘 따라다니고는 했습니다.

참고로, 전문 검색이란.. LIKE ‘%검색어%’ 과 동일한 역할을 한다고 보시면 되겠습니다. (조금 의미는 다르지만.. ㅋ)

N-GRAM?

자세한 내용은 하단 링크를 쭉 읽오보시면 되겠습니다만..

https://dev.mysql.com/doc/refman/5.7/en/fulltext-search-ngram.html

간단하게 저 메뉴얼 내용을 인용하자면, 컨텐츠의 인덱스를 아래와 같이 첫글자/두글자..(1그램,2그램,3그램..) 등등으로 나누어서 파싱하여 전문검색 인덱스를 만드는 것이라고 생각하면 되겠습니다.

n=1: 'a', 'b', 'c', 'd'
n=2: 'ab', 'bc', 'cd'
n=3: 'abc', 'bcd'
n=4: 'abcd'

그리고, 기본적으로 InnoDB에서 제공해주는 ngram의 최소 토큰 사이즈(ngram_token_size)는 2이고, 위에서 “n=2″ 부터 토큰을 만들게 됩니다. 당연한 이야기겠지만, n-수치가 낮을수록 토큰 수가 많아질 것이기에, 모든 검색어들이 3글자부터 시작된다면 이 수치를 3으로 상향 조정하는 것도 인덱싱 관리 및 사이즈 안정성에 도움이 되겠습니다.

Simple Test

mysql> CREATE TABLE `articles` (
    ->  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->  `body` text,
    ->  PRIMARY KEY (`id`),
    ->  FULLTEXT KEY `ftx` (`body`) WITH PARSER ngram
    ->) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
mysql> insert into articles (body) values ('east');
mysql> insert into articles (body) values ('east area');
mysql> insert into articles (body) values ('east job');
mysql> insert into articles (body) values ('eastnation');
mysql> insert into articles (body) values ('eastway, try try');
mysql> insert into articles (body) values ('try try');

“WITH PARSER ngram” 부분을 명시적으로 적지 않으면, 기존 전문검색 인덱스로 구성되니 유의하시기 바랍니다. 자, 이 상태에서 ‘st’가 들어있는 데이터를 검색해볼까요?

mysql> SELECT * FROM articles WHERE MATCH(body) AGAINST('st' IN BOOLEAN MODE); 
+----+------------------+
| id | body             |
+----+------------------+
|  7 | east area        |
|  6 | east             |
|  8 | east job         |
|  9 | eastnation       |
| 10 | eastway, try try |
+----+------------------+

참고로, 위 정렬 순서는 단어 매칭 수가 많은 데이터 순서로 자동 정렬이 됩니다.

STOPWORD Problem

우연찮게 동료가 자신의 이름을 검색을 하다 발견했던 이슈인데.. 위 상태에서 “ea”를 포함하는 데이터를 검색해봅니다. 분명히 데이터가 존재함에도, 아래와 같이 한 건도 나오지 않는 점을 확인할 수 있습니다.

mysql> SELECT * FROM articles WHERE MATCH(body) AGAINST('ea' IN BOOLEAN MODE); 
Empty set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH(body) AGAINST('eas' IN BOOLEAN MODE); 
Empty set (0.01 sec)

아무래도 5.7에서 새롭게 들어온 n-gram방식이라서 그런지요. INNODB_FT_DEFAULT_STOPWORD에 의존적으로 n-gram 도 동작을 하게 됩니다. 기존 전문검색인덱스에서는 공백 기준으로 토큰화되기 때문에, 영문에서 자주 쓰이는 단어들은 인덱스로 분류되지 않도록 지정을 합니다. 이 단어들이 모여있는 테이블이 바로 information_schema 에 위치한 INNODB_FT_DEFAULT_STOPWORD 테이블이죠.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a     |
| about |
| an    |
| are   |
| as    |
| at    |
..중략..
| who   |
| will  |
| with  |
| und   |
| the   |
| www   |
+-------+

그런데 참 재미있는 것이.. 단어 파싱의 기준은 n-gram으로 하면서, 실제 룰 일부는 기존의 “MeCab” 기준으로 적용한다는 사실이죠. 위 예제에서는 위에 포함이 되어 있는 “a” 단어로 인하여 “east”문자는 “e”, “st” 이렇게 파싱이 되고, 결과적으로 “ea” 검색 결과에서 누락이 되는 상황이 되어버리고 만 것입니다.

INNODB_FT_DEFAULT_STOPWORD에 대한 조금더 상세한 얘기는 하단 매뉴얼을 참고하시면 되겠습니다.

https://dev.mysql.com/doc/refman/5.6/en/fulltext-stopwords.html

Workaround

현재로서는 “information_schema” 하단에 위치한 이 테이블의 데이터를 조작하거나, 변경할 수 있는 방안은 없습니다.

오라클/Percona에서 어떻게 대응해줄지는 모르겠지만, 일단 당장 이 이슈가 해결될 것 같지는 않기에, 조금 트릭으로 풀어야겠습니다.  그래서 아래와 같이 데이터가 전혀 없는 ngram_stopwords 테이블을 만들고, 이 테이블을 INNODB_FT_DEFAULT_STOPWORD 대용으로 사용하겠다고 파라메터 지정하는 것인데요.

mysql> CREATE TABLE mysql.ngram_stopwords(value VARCHAR(18)) ENGINE = INNODB;
mysql> SET GLOBAL innodb_ft_server_stopword_table='mysql/ngram_stopwords';

기존에 이미 n-gram 전문검색인덱스가 존재한다면, 반드시 테이블 재구성을 하시기 바랍니다. (모든 데이터를 다시 파싱을 해야하기 때문이죠.)

mysql> alter table articles engine = innodb;

이제 아까처럼 다시 “ea”로 검색을하면, 아래와 같이 예~쁜 결과가 나오게 됩니다. ^____^

mysql> SELECT * FROM articles WHERE MATCH(body) AGAINST('ea' IN BOOLEAN MODE); 
+----+------------------+
| id | body             |
+----+------------------+
|  7 | east area        |
|  6 | east             |
|  8 | east job         |
|  9 | eastnation       |
| 10 | eastway, try try |
+----+------------------+

단, 이렇게 트릭을 사용한 이상.. 사이드이펙트가 무엇일지 생각을 해봐야겠지요? stopword와 관련된 테이블을 여러개를 가질 수 없다는 점입니다. 즉, MeCab 파서에도 영향을 줄 것이므로, 반드시 이 점 인지시기 바랍니다. ^^ (가장 좋은 해결책은 n-gram 파서 경우에는 INNODB_FT_DEFAULT_STOPWORD를 적용하지 않는 것일텐데.. ㅎㅎ)

이 부분은 조만간 Percona 에서는 파라메터로 제어할 수 있도록 해준대요~ 기다리세요. ㅋㅋ

Conclusion

역시. 신기능은 조금 기다려야했나.. 버그 이슈로 마음고생 많이 했습니다.

그렇지만.. 어찌어찌 해결이 되었고. 아마도 다음 릴리즈에서는 Percona에서는 픽스해서 GA로 오픈할 듯 하네요. 안정화를 더 해봐야하고, 성능 튜닝도 이루어져야할 것이겠지만.. 점차 좋은 기능들이 InnoDB로 들어오게 되면서, 리플 안정성 및 데이터 백업/복구 측면에서 대단한 발전이 있는 것은 사실입니다.

물론. Elastic Search와 같은 솔루션으로 해결해볼 수 있겠으나, 적절한 데이터 사이즈에서는 충분히 MySQL의 쿼리캐시(Result Cache)를 같이 조합해보면 대단한 효과를 낼 수 있다고 판단합니다.

데이터는 데이터일뿐.. ㅋㅋ 좋은밤 되세요.^^

세상만사 귀찮은 MySQL DBA를 위한 자동 복구 시나리오

Overview

안녕하세요. 요새 창고 대방출! 그동안 미뤄 두었던 얘기들을 연달아 공유합니다. (마스터 스크립트를 만들어야하는 수고를 덜기 위해.. 해당 스크립트 제거 및 스크립트 수정하였습니다.)

MySQL을 사용하는 이상, 리플리케이션 활용에서 벗어나기 쉽지 않은데요. 그 말은 곧 다수의 동일한 데이터를 가진 여러개의 서버를 운영관리 해야한다는 말과 같고.. 장비가 많아진다는 것은 그만큼 데이터 복구가 많다는 이야기이기도 하지요. 특히나 샤딩 환경으로 데이터 폭증을 대비해두었다면 더욱 그렇습니다.

게다가 복구 시 새벽 백업을 사용한다는 말은 곧 새벽 이후로 저장이된 변경 이력을 일괄 적용을 해야하고.. 이 내용이 많으면 데이터 동기화 시간도 적지않게 소모되고.. (횡설수설~)

이런 환경 속에서 세상 만사가 귀찮은 DBA를 위해.. 복구 자동화 방안에 대해 간단하게 얘기를 해봅니다.

Prepare

아.. 크게 중요한 것은 아니고.. 제가 구성을 한 환경입니다. 뭐.. “Linux+MySQL+Xtrabackup”이 되는 환경이면 큰 이슈는 없다는 생각이.. ^^ (단, Xtrabackup은 MySQL5.7 데이터를 백업 가능해야합니다.)

  • CentOS 6.8
  • MySQL 5.7.14
  • Xtrabackup-2.4.3
  • pv

그리고 일단 저는 소심하기 때문에..  GTID가 아닌 기존 Binlog Position 기반의 리플리케이션 기준으로 데이터 복제를 하도록 구성하였습니다. :-)

그리고 문제없이 데이터를 땡겨오는 수단으로.. SCP를 활용할 것인데.. 이 말은 곧 인증 관련하여 패스워드 없이 데이터를 땡겨올 수 있도록 미리미리 id_rsa.pub 키를 리플리케이션 노드끼리 페어링해놓도록 합시다.  즉.. 패스워드 없이 아래와 같이 서버 접근이 되도록 준비해야한다는 것이죠. (이 부분은 패스~)

server1$ ssh mysql@server2
server2$ ssh mysql@server1

Restore Scenario

서비스의 특성 혹은 개개인의 선호도에 따라 다르겠지만.. 제가 가장 중요시한 가치는 일순위는 역시 복구 효율성입니다. 물론, 효율성 이전에 서비스 영향도는 거의 없어야 하겠죠. ㅜㅜ 그렇다면, 당연한 이야기겠지만.. 데이터 전송 트래픽에 대한 조절 방안도 필요하겠죠.

일반적으로 복구를 하는 케이스라면, 두 가지 경우를 생각해볼 수 있습니다.

  1. restore from master data
    마스터의 데이터로 슬레이브를 추가하는 케이스로.. 받아온 서버의 Binlog 포지션이 슬레이브 구성의 정보가 됩니다.
  2. restore from slave data
    슬레이브의 데이터로 슬레이브를 구성하는 케이스로.. 현재 슬레이브와 동일한 슬레이브를 하나 더 추가하는 케이스입니다. 데이터를 받아오는 슬레이브의 Slave Status 정보가 복구 슬레이브의 구성 정보가 됩니다.

진행하기에 앞서서 양해의 말씀을 드리자면.. 이 자리는 어디까지나 자동 복구 시나리오를 설명하는 공유의 장이지.. 현재 날코딩으로 제가 꾸역꾸역 구현한 스크립트를 공유하는 자리는 아니랍니다. ^^ 뭐. 너저분한 코드 정리를 하게 되고.. 이것저것 엮인 것들을 심플하게 풀어놓을 수 있다면 얘기가 달라지겠지만.. 전 귀찮아하는 DBA이기에.. Validation Check 혹은 패스워드 암호화 같은 설명은 모두 스킵하였습니다. ㅋㅋ

그리고 데이터 복구 사전 지식으로.. Xtrabackup의 하단 매뉴얼을 먼저 읽어보심 큰 도움이 되리라 확신합니다.
https://www.percona.com/doc/percona-xtrabackup/2.4/howtos/setting_up_replication.html

자. 그럼 이야기를 풀어볼까요?

case 1) restore from master data

앞서 얘기한대로.. 마스터로부터 데이터를 받아서, 바로 하단에 슬레이브를 구성하는 케이스로.. 하단 이미지와 같은 모습으로 데이터를 구성하는 케이스입니다.

Restore_From_Master_Data

Restore_From_Master_Data

1) 데이터 끌어오기

Xtabackup의 마지막 과정 중.. InnoDB가 아닌 데이터를 카피하는 과정이 있는데.. “–no-lock” 옵션을 주지 않으면, 데이터 정합성 보장을 위해 데이터 카피하는 동안 글로벌 Lock을 걸고 수행하게 됩니다. 즉.. 서비스에 영향을 준다는 말이겠지요.

innobackupex 부분을 바로 쉘에 넣어서 한방에 수행하도록 아래와 같이 작성합니다.(로컬 백업 습관으로.. 대충 변경했더니.. 생각이 없었네요. ㅋㅋ) 스트리밍으로 전송되는 백업 데이터를 pv로 제어(초당 50메가, 바쁜 서버는 더욱 약하게)하고..

ssh -o StrictHostKeyChecking=no mysql@${TARGET_HOST} \
 "innobackupex \
 --host="127.0.0.1" \
 --user=backupuser \
 --password="backuppass" \
 --no-lock \
 --stream=xbstream \
 /data/backup | pv --rate-limit 50000000" 2> innobackupex.log \
 | xbstream -x 2> xbstream.log

innobackupex.log와 xbstream.log 두 곳의 로그를 보고, 받아온 백업 데이터 유효성을 검수할 수 있습니다. innobackupex.log에는 “completed OK!” 로 끝이 나야 정상적으로 데이터를 받아온 것이고, xbstream.log결과에는 아~무런 내용도 없어야 제대로 스트리밍을 풀어냈다고 볼 수 있어요.

2) 리두로그 적용하기

앞선 과정이 정상적으로 수행되었다는 가정 하에 진행하는 것으로..  데이터가 백업 및 전송되는 동안 변경된 데이터를 적용하는 과정입니다. 이 역시 innobackupex.log의 마지막이 “completed OK!” 로 끝이 나야 정상적으로 로그가 적용된 것으로 볼 수 있습니다.

innobackupex --apply-log . 2>> innobackupex.log

3) 슬레이브 구성하기

마지막으로.. 실제 슬레이브로 구성할 마스터의 포지션을 추출하는 과정으로.. apply log 이후 생성이 되는 “xtrabackup_binlog_info” 에서 위치 정보를 아래와 같이 추출합니다. 보통은 아래와 같이 탭으로 구분하여 마스터의 바이너리 로그 포지션이 기록되어 있는데..

mysql-bin.000001     481

저는 아래와 같이 sed 명령을 통해서 조금 있어보이게(?) 로그 포지션을 정의하였습니다. awk든 뭐든.. 편한 방법으로.. ^^

MASTER_LOG_FILE=`sed -r "s/^(.*)\s+([0-9]+)/\1/g" xtrabackup_binlog_info`
MASTER_LOG_POS=`sed -r "s/^(.*)\s+([0-9]+)/\2/g" xtrabackup_binlog_info`

자.  이제, 마스터 서버도 정해졌고, 리플리케이션 구성을 위한 바이너리 로그 포지션도 정해졌으니.. 아래와 같이 슬레이브를 구성해보도록 해봅시다.

echo "change master to
 master_host='${TARGET_HOST}',
 master_user='repl',
 master_password='replpass',
 master_log_file='${MASTER_LOG_FILE}',
 master_log_pos=${MASTER_LOG_POS}; 
 start slave;"\
| mysql -uroot -pxxxxxx

이렇게 하면 큰 무리 없이 아래와 같이 마스터 데이터를 활용하여 신규 슬레이브를 구성 완료입니다.

Restore_From_Master_Data_Final

Restore_From_Master_Data_Final

case 2) restore from slave data

두번째 케이스.. 현재 위치한 슬레이브 서버의 데이터를 활용하여 신규 슬레이브 서버를 추가하는 케이스인데.. 아래와 같은 데이터 흐름을 가집니다. 정상적으로 구동 중인 슬레이브 서버로부터 데이터를 가져와서, 해당 슬레이브의 슬레이브 정보를 바탕으로 동일한 위상의 추가 슬레이브를 구성하는 시나리오죠. (말이 참 어렵죠? ㅋ)

Restore_From_Slave_Data

Restore_From_Slave_Data

1) 데이터 끌어오기

이번에는 슬레이브로부터 받아온 백업 스트리밍 데이터를 바로 xbstream으로 풀어버리는 과정으로, 마찬가지로 pv로 전송량 제어를 하였습니다. 어차피 슬레이브이니.. 서비스 투입된 장비가 아니라면.. 기가비트로 땡겨와도 문제 없으니.. 굳이 안써도 될 것 같지만..;;

앞 게이스와 다르게 슬레이브 포지션을 받아올 목적으로 아래와 같이 ” –slave-info” 옵션을 지정해줍니다. (이 옵션을 줘야, 슬레이브 상태를 기록합니다.)

ssh -o StrictHostKeyChecking=no mysql@${TARGET_HOST} \
 "innobackupex \
 --host="127.0.0.1" \
 --user=backupuser \
 --password="backuppass" \
 --slave-info \
 --stream=xbstream \
 /data/backup | pv --rate-limit 50000000" 2> innobackupex.log \
 | xbstream -x 2> xbstream.log

마찬가지로 innobackupex.log와 xbstream.log 두 곳의 로그를 보고, 받아온 백업 데이터 유효성을 검수할 수 있습니다. innobackupex.log에는 “completed OK!” 로 끝이 나야 정상적으로 데이터를 받아온 것이고, xbstream.log결과에는 아~무런 내용도 없어야 제대로 스트리밍을 풀어낸 것이죠.

2) 리두로그 적용하기

데이터가 백업 및 전송되는 동안 변경된 데이터를 적용을 이전과 마찬가지로 동일하게 수행을 합니다. 이 역시 innobackupex.log의 마지막이 “completed OK!” 로 끝이 나야 정상적으로 처리된 것입니다.

innobackupex --apply-log . 2>> innobackupex.log

3) 슬레이브 구성하기

슬레이브 포지션은 이제.. 앞서 마스터에서 추출한 것과는 조금 다르게 정의를 해야하는데.. 먼저 바이너리 로그 포지션은 “xtrabackup_slave_info”에서 찾아내야 합니다.

마지막으로.. 실제 슬레이브로 구성할 마스터의 포지션을 추출하는 과정으로.. apply log 이후 생성이 되는 “xtrabackup_binlog_info” 에서 위치 정보를 아래와 같이 추출합니다. 보통은 아래와 같이 탭으로 구분하여 마스터의 바이너리 로그 포지션이 기록되어 있는데.. 실제 아래와 같은 형태로 저장이 되어 있지요.

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=481

아래와 같이 정규식으로 로그 파일과 포지션을 가져와 보겠습니다.

MASTER_LOG_FILE=`sed -r "s/(.*)MASTER_LOG_FILE='(.*)', MASTER_LOG_POS=([0-9]+)/\2/g" xtrabackup_slave_info`
MASTER_LOG_POS=`sed -r "s/(.*)MASTER_LOG_FILE='(.*)', MASTER_LOG_POS=([0-9]+)/\3/g" xtrabackup_slave_info`

여기까지 바이너리 로그 포지션을 가져오는 것은 큰 무리 없이 진행을 하였는데.. 문제는 실제로 리플리케이션을 맺어야하는 서버에 대한 정보를 어디서 가져와야하냐는 것에 있습니다.  물론 이것을 별도의 모니터링 툴에서 추출을 하거나.. 혹은 주기적으로 어딘가에 마스터 정보를 밀어넣거나 할 수 있겠지만.. 제가 택한 방식은 데이터를 땡겨온 타겟 슬레이브 장비에 들어가서 직접 Slave Status를 조회해서, 현재 바라보고 있는 마스터 서버 정보를 가져오는 방식이었습니다.

MASTER_HOST=`mysql -urepl -preplpass -h ${TARGET_HOST} -e 'show slave status\G' | grep 'Master_Host: ' | sed -r 's/\s*(Master_Host: )//g'|sed -r 's/\s*//g'`

한가지 불만은.. Xtrabackup에서 xtrabackup_binlog_info 파일을 생성을 할 때, 왜 슬레이브에서 바라보고 있는 마스터 정보를 기록해주지 않는가..이지만.. 이건 뭐 솔루션이고.. 그들만의 철학이 있다고 생각으로~ 제가 조금 더 귀찮아짐을 택하기로 했습니다.

자.  이제, 마스터 서버도 알아냈고, 리플리케이션 구성을 위한 바이너리 로그 포지션도 정해졌으니.. 아래와 같이 슬레이브를 구성해보도록 해봅시다. 실제 마스터의 호스트가 타겟 호스트가 아닌, 마스터 호스트(앞에서 알아낸) 정도가 변경되었습니다.

echo "change master to
 master_host='${MASTER_HOST}',
 master_user='repl',
 master_password='replpass',
 master_log_file='${MASTER_LOG_FILE}',
 master_log_pos=${MASTER_LOG_POS}; 
 start slave;"\
| mysql -uroot -pxxxxxx

자~ 여기까지 되면.. 아래와 같이 슬레이브로부터 데이터를 받아와서~ 동일한 레벨의 슬레이브 구성 성공입니다.

Restore_From_Slave_Data_Final

Restore_From_Slave_Data_Final

Conclusion

지금까지, 현재 돌고 있는 데이터를 활용하여 슬레이브를 구성하는 방법에 대해서 알아보았습니다. 시작하기 전에 양해의 말씀을 드린 것처럼, 스크립트를 모두 풀어서 보여드릴 수는 없었습니다. 물론 몇백라인정도밖에 안되는 간단하다면 간단한 스크립트이기는 하나.. 이것이 정답일 수 없기 때문이지요. (세트로 묶어서.. 범용적으로 조금 사용할 수준이 되면.. 공개는 그때 해보도록 할께요. ^^)

SCP로 데이터를 땡겨오지 않고, nc 유틸을 통해 받아올 수도 있는 것이고.. 고전적인 Binlog 포지션이 아닌.. GTID를 통해 더욱 심플하게 해결할 수도 있겠죠.

제 방안은 한 사례일 뿐.. 그 이상도 이하도 아닙니다. 그래서 편하게 제가 생각하는 이 데이터 흐름을 다른 세상만사 귀찮은 DBA분들이 더욱 완성도 있는 그림을 그려주면 좋겠다는 바램으로.. 공유하였습니다. 쿨럭~
(근데.. 이것 시스템화 해놓고 보니.. 정말 시간 많이 잡아먹고.. 손 많이 가는 노가다 작업이 줄어서.. 일도 많이 줄어버렸네요~ 크항~!)

긴~글, 늦은밤 이만 마무리 하겠습니다. ^^

MySQL에서 파티션 일부를 다른 파티션 테이블로 옮겨보기

Overview

한동안 운영에 치여, 문서를 못봤더니, 재미난 사례를 많이 놓친듯.
그래서 여기저기 떠도는 문서 중 재미난 사례 하나를 내 입맛에 맞게 샘플을 변경해서 공유해봅니다.
(영혼없이 붙여넣기만 해도 알아보기 쉽게 ㅋㅋ)

Preview

파티셔닝 특정 부분을 다른 테이블 혹은 파티셔닝 일부로 넘기는 방안에 대한 것인데..

move-partition-data-file

move-partition-data-file

하단 포스팅 내용 중 미흡한 부분을 보완해서 정리해본 것입니다

https://dzone.com/articles/how-to-move-a-mysql-partition-from-one-table-to-an?utm_medium=feed&utm_source=feedpress.me&utm_campaign=Feed:%20dzone

Generate Test Data

먼저 테스트 데이터를 생성해야할테니..

mysql> create table f_tb (
      seq bigint(20) not null default '0',
      regdate date not null,
      cont text not null,
      primary key (seq,regdate)
  ) engine=innodb collate=utf8_unicode_ci
  /*!50500 partition by range columns(regdate)
  (partition p09 values less than ('2016-10-01'),
   partition p10 values less than ('2016-11-01'),
   partition p11 values less than ('2016-12-01'),
   partition p12 values less than ('2017-01-01')) */;

아래처럼 테스트로 사용할 데이터를 간단하게 생성해봅니다. 2017-01-01 기점으로 랜덤하게 120일 사이 일을 빼서 마치 파티셔닝 테이블이 관리된 것처럼 데이터를 밀어넣는 것이죠.

## 1건 데이터 생성
mysql> insert ignore into f_tb values 
    (rand()*1000000000, date_sub('2017-01-01', interval rand()*120 day), repeat(uuid(),5));

## 원하는 만큼 반복 수행
mysql> insert ignore into f_tb 
  select rand()*1000000000, 
      date_sub('2017-01-01', 
      interval rand()*120 day), repeat(uuid(),5) 
  from f_tb;

위에서 만든 이후 실제 테이블 데이터 건 수를 보면.. (전 대략 8000건만 만들었어요. 귀찮아서. ㅋ)

mysql> select count(*) from f_tb;
+----------+
| count(*) |
+----------+
|     8064 |
+----------+

Move Datafile to Temporay Table

자~ 이제 특정 파티셔닝 파일을 다릍 파티셔닝의 일부로 옮기는 작업을 해볼까요.

1) 동일한 테이블을 만들고, 파티셔닝이 없는 일반 테이블로 구성한다.

mysql> create table f_tb_p09 like f_tb;
mysql> alter table f_tb_p09 remove partitioning;

2) 임시 테이블 discard처리

f_tb에서 9월 데이터를 가져오기 위해, 테이블스페이스에서 discard 하도록 수행합니다. 임시 깡통 데이터가 사라지겠지요.

mysql> alter table f_tb_p09 discard tablespace;

3) 테이블 데이터 복사

파일 복사 이전에, 원본 테이블에 export를 위한 락을 걸고, 데이터 카피 후 락을 풀어줍니다.
(두개 세션 열기 귀찮으니.. MySQL 콘솔 클라이언트에서 바로 파일을 복사하도록.. ㅋㅋ)

mysql> flush table f_tb for export;
mysql> \! cp /data/mysql/test_db/f_tb#p#p09.ibd /data/mysql/test_db/f_tb_p09.ibd
mysql> unlock tables;

4) 임시 테이블에 데이터파일 Import

옮겨온 데이터파일을 실제 임시로 생성한 테이블에서 읽을 수 있도록 import 처리 해줍니다.

mysql> alter table f_tb_p09 import tablespace;

자~ 이제 정상적으로 데이터가 잘 옮겨졌는지 카운트를 해볼까요?

mysql> select count(*) from f_tb_p09;
+----------+
| count(*) |
+----------+
|     1860 |
+----------+

굳~ 잘 되었네요.

Import to archive

아카이브 테이블이 없다는 가정하에.. 아래와 같이 타겟 파티셔닝 테이블을 생성합니다. 당연한 이야기겠지만, 파티셔닝 정의를 제외한 테이블 구조는 동일해야하겠지요?

mysql> create table t_tb (
      seq bigint(20) not null default '0',
      regdate date not null,
      cont text not null,
      primary key (seq,regdate)
  ) engine=innodb collate=utf8_unicode_ci
  /*!50500 partition by range columns(regdate)
  (partition p09 values less than ('2016-10-01'),
   partition p10 values less than ('2016-11-01')) */;

mysql> select count(*) from t_tb;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

자~ 끝판왕.. 마지막으로.. 타겟 파티셔닝 파일에 데이터파일을 변경해준다는 하단 alter 구문만 날려주면 끝~

mysql> alter table t_tb exchange partition p09 with table f_tb_p09;

mysql> select count(*) from t_tb;
+----------+
| count(*) |
+----------+
|     1860 |
+----------+

중요치는 않지만.. 임시 테이블 데이터는 깡통으로 남지요.
(COPY가 아닙니다. 임시 테이블에 데이터는 사라져요. ㅋ)

mysql> select count(*) from f_tb_p09;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

Conclusion

신기하기는 했지만.. 이걸 어디에 쓸 수 있을까 잠깐 생각을 해봤는데.. 대고객 온라인 서비스에서는 큰 의미는 없다고 생각되는데요.

MySQL의 가장 큰 강점은 개인적으로 “데이터 복제”라고 생각합니다. Replication.. 그러나.. 위와 같이 데이터파일을 옮긴다면.. 아무래도 OS 상에서 동작하기에.. 데이터 복제 개념과는 동떨어진 처리입니다. 즉.. 설혹 마스터에서 특정 테이블 일부 데이터를 다른 테이블의 일부로 옮겨놓아도, 실제 슬레이브에서는 이 명령이 그대로 적용되지 않음을 의미하죠.

그러나!

만약 “특정 테이블”에 대한 데이터 분석 혹은 보관과 같은 이슈가 있을 시.. 덤프 없이, 놀고 있는 슬레이브, 혹은 스탠바이, 장비에서 일시적으로 잠금 처리를 한 후 파일 단위로 빠르게 옮겨봄으로써 의외로 쉽게 대응이 가능합니다. 물로 반드시 파티셔닝 테이블에 포함해야할 필요는 없지만.. ^^ 어플리케이션 복잡도를 줄이기 위해서라면 위와 같이 파티셔닝 테이블의 일부로 적용하는 것도 좋은 케이스이겠고요.

감사합니다. ^^