MySQL_5.7의 n-gram? 버그앤런!

Overview

바로 얼마전 포스팅에서 n-gram에 대한 간단한 소개를 했었는데.. 아무래도 5.7에 처음으로 소개된 기능인만큼 현재 이슈 사항에 대해서 공유를 해볼 필요가 있어보입니다. (이전 포스팅: http://gywn.net/2017/04/mysql_57-ngram-ft-se/)

제가 겪은 상황과 우회할 수 있는 방안.. 그리고 현재 진행 상황에 대한 내용이예요. ^^

1. Performance Problem

일단 InnoDB의 n-gram 인덱싱은 두 글자로만 나뉘어서 토큰으로 만들어집니다. 그리고 이 토큰들은 도큐멘트 아이디를 각각 가짐으로써, 빠르게 “두 글자”가 포함된 문서를 바로 찾아낼 수 있는 것이지요.

예를들어, 실제 n-gram 인덱싱이 관리되는 모양새를 바라보면.. 아래처럼 인덱싱 모습을 볼 수 있답니다요. 공백을 제외한 모든 데이터를 두 글자(ngram_token_size가 2인 경우)로 나눠서 구조화하는 것이죠.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+------+--------------+-------------+-----------+--------+----------+ 
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | 
+------+--------------+-------------+-----------+--------+----------+ 
|   ef |            2 |          11 |        10 |     10 |        4 | 
|   ef |            2 |          11 |        10 |     11 |        4 | 
|   fg |            2 |          11 |        10 |      2 |        5 | 
|   fg |            2 |          11 |        10 |      3 |        5 | 
|   fg |            2 |          11 |        10 |      4 |        5 | 
+------+--------------+-------------+-----------+--------+----------+

그리고 검색어는 두 글자 씩 나눠지면서 아래와 같이 쿼리가 변환됩니다. (boolean모드에서입니다.)

## 원래 쿼리
mysql> SELECT * FROM test WHERE match(j) against ('나는나야' in boolean mode);

## 변환된 쿼리
mysql> SELECT * FROM test WHERE match(j) against ('"나는 는나 나야"' in boolean mode);

두 글자 씩 내부적으로 “순서까지 일치”하도록 2글자 단어 검색을 3회 한다는 것을 의미하지요. 이것은 곧.. 검색어가 길어질수록.. 특히나 검색어 내부에 분포도 최악인 단어가 포함된다면.. 흠.. 쿼리 성능에도 영향을 줄 수 있습니다. 하단 간단한 테스트를 보시지요. :-)

“은행나무” 라는 데이터 30만 건을 생성한 후 “는나무”와 “나무는” 두 검색(0건인 결과)을 해본 결과입니다. “은행” 단어의 위치에 따라 상이한 응답 시간을 보여주지요. 아직 옵티마이저의 한계로 결론을..

## '"는나 나무"'
mysql> select * from test where match(j) against ('는나무' in boolean mode) ;
Empty set (0.00 sec)

## '"나무 무는"'
mysql> select * from test where match(j) against ('나무는' in boolean mode) ;
Empty set (0.21 sec)

검색어 위치에 따라 쿼리 성능이 달라질 수 있습니다.

일반적으로는 이렇게 특정 단어에 쏠리는 경우는 희박하기에.. S급 이슈는 아닐 것으로 보여지지만.. 적어도 사용자 패턴에 따른 성능 차가 발생할 수 있는 만큼, 인지는 하고 있어야겠습니다.

2. Wrong Result Problem

MySQL 기본 Collation이 대소문자를 구분하지 않기 때문에.. 기본 설정으로는 이슈가 없습니다. 그러나, 만약 대소문자 구분하도록 서버 설정을 구성하였다면, 의도치않게 원하는 결과가 나타나지 않는 이슈가 있습니다.
(참고 : https://bugs.mysql.com/bug.php?id=78048)

n-gram 인덱싱은 대소문자 구분을 하지 않습니다.

이것은 단순히 ngram의 이슈라기 보다는 InnoDB Fulltext Index 자체적인 문제로 보이는데.. 꽤 오래전부터 이 부분에 대한 개선은 진행 중인것으로 보이네요. 간단하게 우회할 수 있는 방안으로는 추가로 “LIKE” 검색 조건을 주는 것입니다.

## 변경 전 ##
SELECT * FROM test
WHERE MATCH (j) against ('aA' IN boolean MODE);

## 변경 후 ##
SELECT * FROM test
WHERE MATCH (j) against ('aA' IN boolean MODE)
  AND j LIKE '%aA%';

3. Symbol Character Searching Problem

최근들어 Percona와 가장 활발하게 논쟁 중인 이슈입니다. 논쟁하면서 오라클 쪽으로도 하단 리포트가 올려서 상황을 지켜보고 있는 사항이기도 하죠. (참고 : https://bugs.mysql.com/bug.php?id=86164)

특수문자 검색이 되지 않습니다.

이슈가 되는 것은.. 실제  ngram 파싱에는 정상적으로 인덱싱이 잘 토큰화 되었는데.. 실제 검색에서는 특수문자가 포함된 경우 전혀 검색이 안되는 케이스입니다. 처음에는 이 이슈의 원인이, n-gram에서 특수 문자 자체를 공백과 같이 무시하기 때문에, 토크나이징이 안된 것이 아닐까 의심을 했었지만.. 실제 내부 인덱스 현황을 살펴봤을 때 토크나이징 자체까지는 이슈가 없음을 확인했습니다.

mysql>SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;                                                                                                                                                
+------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------+--------------+-------------+-----------+--------+----------+
| !e   |            2 |          12 |         2 |      2 |        3 |
| !e   |            2 |          12 |         2 |     12 |        3 |
| #e   |            4 |           4 |         1 |      4 |        3 |
| $e   |            5 |           5 |         1 |      5 |        3 |
| %e   |            6 |           6 |         1 |      6 |        3 |
| &e   |            8 |           8 |         1 |      8 |        3 |
+------+--------------+-------------+-----------+--------+----------+

현재로써는 특수문자 관련된 내용을 정제해서 풀텍스트 인덱스로 만들든지.. 혹은 아래와 같이 풀텍스트 검색 시 패턴 검색 식으로 우선 데이터 스캔량을 줄이고, LIKE 검색을 추가하는 방안 밖에 없어 보입니다.(현재로써는..)

## 변경 전 ##
SELECT * FROM test
WHERE MATCH (j) against ('A&T' IN boolean MODE);

## 변경 후 ##
SELECT * FROM test
WHERE MATCH (j) against ('A*T' IN boolean MODE)
  AND j LIKE '%A&T%';

단, 위와 같이 우회하는 경우, A와 T가 포함되는 모든 단어들이 n-gram으로 필터링 되기 때문에 의도치 않게 스캔량이 많아질 리스크는 있습니다. 이런 상황을 인지하고 “특수문자 검색”을 허용할지.. 혹은 일단 특수문자 검색이 안되는 사항을 인지하고 다른 방안을 찾아볼지는 서비스 요구사항에 따라 다르겠습니다.

Mroonga에서 사용하는 Groonga 경우에는 이러한 특수문자 처리 여부도 파서 선택을 통해 수행할 수 있는데.. 아직 InnoDB의 n-gram에는 아쉬운 부분입니다.

Performance Test

무작위로 데이터를 밀어넣어 놓고, 간단하게 두 가지 상황에 대해서 트래픽을 날려본 결과를 간단하게 공표(?)해봅니다. 단, 현재 MySQL 5.7에서는 소팅없이 limit을 수행하는 경우 결과가 정상적으로 나오지 않거나, 최악의 경우에는 크래시 되는 심각한 버그가 존재합니다. (핫픽스로 우리는 이슈 해결을 하였으나, GA 적용까지는 조금 시기가 더 걸릴 듯 합니다요. 참고만. ^^ )

당연한 이야기겠지만, 소팅없이 limit으로 자르는 경우에는 전체 데이터를 살펴볼 필요가 없기에, 적절한 응답 시간을 보여줍니다. 만건 이상 데이터에서도 33ms 정도로 나쁘지 않은 속도이죠.

초당수행건수(평균수행마이크로초)
1 : 1~9건
10 : 10~99건
100 : 100~999건
1000 : 1000~9999건
10000 : 10000 이상

#############################
## SORTING
#############################
1365qps, 1:709(409),  10:496(747), 100:140(2958), 1000:17(19266), 10000:3(170724)
1402qps, 1:761(398),  10:476(713), 100:148(2858), 1000:16(18395), 10000:1(188640)

#############################
## REMOVE SORTING
#############################
2807qps, 1:1517(363), 10:966(465), 100:279(807), 1000:41(4153), 10000:4(33704)
2818qps, 1:1512(352), 10:938(473), 100:333(781), 1000:34(3774), 10000:1(32702)

Conclusion

Mroonga로 n-gram 풀텍스트 엔진을 써번 경험으로 무작정 시도해본, InnoDB n-gram 풀텍스트 엔진.. 아직까지는 대량의 데이터 처리에는 미흡한 모습을 보입니다. 옵티마이저의 쿼리 파싱부터 버그 이슈까지.. 차츰 좋아지겠지만.. 현재는 이러한 이슈로 정리해 보겠습니다.

  • 검색어 위치에 따라 쿼리 성능이 달라질 수 있습니다.
  • n-gram 인덱싱은 대소문자 구분을 하지 않습니다.
  • 특수문자 검색이 되지 않습니다.

 

그렇다고 해도 전문 검색을 기존의 Like ‘%검색%’ 방식과 같이 모든 데이터를 매번 풀스캔 하는 것보다는 훨씬 나아요.

만약.. 전문 검색 엔진을 도입하기에는 볼륨이 크지 않고, Like검색을 하기에는 풀스캔 부담이 있으신 분들은.. 그래도 도입을 고려해봐도 나쁘지는 않을 것 같네요. 단, GA버전으로 n-gram 버그가 픽스되기 전까지는 반드시! 소팅(order by)을 붙여서 사용하시기를 극히 추천합니다.

(하아.. 몇 달 사이 많은 것을 배웠네요.)

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분들이 더욱 완성도 있는 그림을 그려주면 좋겠다는 바램으로.. 공유하였습니다. 쿨럭~
(근데.. 이것 시스템화 해놓고 보니.. 정말 시간 많이 잡아먹고.. 손 많이 가는 노가다 작업이 줄어서.. 일도 많이 줄어버렸네요~ 크항~!)

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