대용량 트래픽을 견디는 서버를 만들기 위한 DB Replication 구축 - Microsoft Azure Cloud 서버의 MySQL 8.0.xx을 사용한다면 꼭 주의해야 하는 부분은? (a.k.a DB 구축 삽질기)

INTRO

안녕하세요 Soo 입니다. 오늘은 프로젝트를 진행하면서 직면했던 문제점과 이에 대한 해결책을 공유해보고자 합니다. 이번 주제는 데이터베이스 솔루션인 MySQL과 관련이 있습니다. 항상 어떻게하면 더 성능이 좋은 프로그램을 만들까 신경쓰면서 프로젝트를 해나가던 도중에 DB를 마스터와 슬레이브의 구조로 분리하여 성능 향상을 도모할 수 있다는 사실을 알게되었는데요. 이번 글에서는 Replication 구축이 왜 필요한지와 이를 구축하면서 겪었던 작은 에피소드를 다뤄보고자 합니다. 

---

Master-Slave Replication 구축의 필요성

Master-Slave Replication 구조

애플리케이션을 개발할 때 성능과 관련해서 가장 많은 문제가 생기는 부분 중 하나가 바로 데이터베이스 부분입니다. DB 커넥션 연결, 디스크 IO 발생, 인덱싱의 부재 등 성능 저하를 일으킬 수 있는 요인들이 정말 다양한데다가 각각 소요되는 비용도 어마어마하기 때문인데요. 예시로 하나의 쿼리를 수행하는데 있어서 더 많은 비용이 드는 부분은 실제 쿼리가 실행되는 시점이 아니라 DB 연결, 쿼리 파싱 등의 준비 과정이며, 쿼리 실행을 마치고 실행 내용을 반환하거나 DB에 반영하는 사후처리 시에도 쿼리 실행자체보다 더 많은 비용이 듭니다. 때문에 들어오는 요청이 많아질수록 RDBMS 서버에 가해지는 부하 또한 급증하게 됩니다. 

이러한 문제점을 개선하기 위해서 RDBMS의 서버 성능 자체를 업그레이드 하는 방법도 고려할 수 있겠지만 RDBMS의 경우는 서버의 확장이 굉장히 제한적입니다. DB의 일차적인 존재 이유는 데이터를 변함 없이 완벽하게 보존하기 위함인데 Scale out을 통한 서버 확장 시 데이터의 정합성이 깨지는 문제가 발생할 수 있기 때문입니다. 그래서 Scale up을 통한 서버 스케일링이 강요되는데 문제는 하드웨어 성능을 업그레이드 하는데는 한계가 있고, 일정 수준 이상으로 올라가면 업그레이드 비용의 증가 폭은 막대한 반면 실제 성능 증가는 미미해진다는 점입니다. 그래서 서버 스케일링 말고도 메인 서버에 가해지는 부하를 최대한 줄여주는 것이 정말 중요하다고 할 수 있습니다. 이를 통해 CPU의 사용률을 낮춰 꼭 필요한 트랜잭션에 대한 처리 성능을 높여줌으로써 전체 서비스에 대한 성능 향상을 가져올 수 있게 됩니다. 

(서버 스케일링에 대한 자세한 내용은 Scale up vs Scale out에 대해서 작성해 놓은 이전 글 soolow-and-sooteady.tistory.com/2 을 참고해 주세요.)

서버의 가용량을 늘리는 방법은 어떤 것이 있고 무엇을 선택하면 좋을까요? (Feat. 서비스가 급속

 

이때, Master-Slave 형태의 Replication 구조를 구축하는 방법을 활용할 수 있습니다. 많은 서비스의 경우 쓰기 요청보다도 DB로부터 데이터를 읽어오는 요청이 대다수를 차지하기 때문에 DB를 마스터 서버와 슬레이브 서버로 분리하고, 마스터 서버에서는 쓰기 요청에 대한 처리를 전담하고 슬레이브 서버에서는 읽기 요청에 대한 처리를 전담하는 식으로 만들어 줌으로써 마스터 서버에 가해지는 부하를 줄여주는 것입니다.

주의할 점은 마스터-슬레이브간의 동기화는 마스터 서버에서 쓰기 요청을 처리한 후 해당 내용을 담은 로그를 기록하고 이를 슬레이브 서버에서 가져와 읽음으로써 업데이트 내용이 슬레이브 서버에도 반영된다는 점입니다. 그래서 마스터-슬레이브 간의 커넥션에서 문제가 생기는 경우 업데이트 내용이 제대로 반영되지 않을 수 있습니다. 그런데 양 서버간의 커넥션 문제는 생각보다 자주 발생하기 때문에 혹시나 정합성에 민감한 데이터를 다루고 있는 경우 선택적으로 마스터 서버에서 해당 읽기 요청을 처리하도록 설정해주어야 데이터 불일치에 대한 문제점을 해결할 수 있겠습니다.

---

간단하게 Master-Slave Replication 을 구축해 봅시다.

MySQL을 활용해 Master-Slave DB 구조를 구축하기 위해서는 MySQL 서버 두개가 필요합니다. 다양한 방법을 활용할 수 있으며 저의 경우에는 Microsoft Azure 클라우드 서버를 활용하였습니다. MySQL 서버를 발급받기 까지의 과정은 해당 링크로 대체하겠습니다. 문서화 및 한글화가 매우 잘 되어있어서 클라우드 서버를 처음 활용해본 저의 경우에도 손쉽게 서버를 하나 더 생성할 수 있었습니다.

docs.microsoft.com/ko-kr/azure/mysql/quickstart-create-mysql-server-database-using-azure-portal

빠른 시작: 서버 만들기 - Azure Portal - Azure Database for MySQL

 

아이디 발급이 완료되면 옆에 개요를 클릭한 후 서버이름(호스트)과 관리자 로그인 이름(아이디)을 활용해 로그인 해줄 수 있습니다. 

 

SSL connection을 통한 연결이 강제되어 있으므로 위와 같이 SSL connection이 필요하다는 에러 메시지가 뜨며 연결에 실패합니다. 본 포스팅에서는 마스터 서버와 슬레이브 서버를 연결해보는 것이 일차적인 목표이기 때문에 SSL connection을 끄고 진행하겠으며, 이와 관련한 내용은 차후에 따로 포스트를 하도록 하겠습니다. 

 

옆의 연결보안을 누르면 다음과 같은 화면이 나옵니다. 여기서 현재 클라이언트 IP를 추가해준뒤 Azure 서비스 방문 허용을 예로 바꿔줍니다. 이후 SSL 연결 적용을 사용 안함으로 변경한 뒤 위의 저장버튼을 눌러주면 됩니다.

 

그리고 다시 터미널로 돌아가서 아까 입력했던 정보를 그대로 입력한 뒤 패스워드를 알맞게 입력하면 위와 같은 메시지가 나오면서 MySQL 서버로 접속이 됩니다. 그러면 이제 해당 서버에 마스터로 활용할 DB를 먼저 만들고 마스터용 계정을 만들어서 권한 부여를 해줍니다. 

 

위와 같이 DB와 계정 생성을 모두 마치면 해당 DB를 마스터 DB로 활용할 수 있도록 권한을 모두 부여해주고 권한을 FLUSH해주면 됩니다.

root 계정을 사용하면 접근할 수 있는 권한이 너무 많아져 의도치 않은 실수가 발생하거나 보안상 문제를 야기할 수도 있습니다. 따라서 root를 사용한 복제는 MySQL 공식 홈페이지에서도 권장하고 있지 않기 때문에 root 계정에서 로그아웃 한 뒤 방금 만들고 권한을 부여한 계정에 다시 로그인 하도록 하겠습니다.

 

로그인 해서 DB를 조회해보면 DB에 대한 기본 메타정보 등을 담고 있는 information_schema 외에 아까 생성해서 해당 계정에 권한 부여했던 DB만이 존재하는 것을 볼 수 있습니다.

슬레이브 DB를 설정하기 전에 복제를 위해 필요한 마스터의 Binary Log와 Pos를 파악하기 위해서 SHOW MASTER STATUS 명령어를 활용해 두 정보를 조회해 보았습니다. 

 

첫 번째 문제 발생 및 해결

해당 에러메시지를 해결하기 위해서 여러 자료들을 보면서 열심히 따라했지만 여전히 마스터 STATUS를 볼 권한이 없다고 나왔습니다. 제가 봤던 자료들은 모두 'GRANT ALL PRIVILEGES'에 대해서만 언급하고 있었습니다. 몇 시간을 무의미하게 소모한 끝에 포기하는 심정으로 에러 메세지에 나온 권한을 부여해보기로 했습니다. 다시 root 계정으로 돌아가 해당 계정에 REPLICATION CLINET 권한을 넘겨주었습니다. 그리고 다시 새로운 계정으로 돌아와서 SHOW MASTER STATUS를 입력해보니 해당 정보가 잘 출력되는 것을 알 수 있었습니다.

 

주의할 점은 REPLICATION CLIENT 권한은 글로벌 권한이기 때문에 특정 DB만을 대상으로 설정해줄 수 없습니다. 그래서 'ON *.*' 으로만 부여가 가능합니다. 다시 권한을 부여해준 다음 FLUSH PRIVILEGES 명령어를 통해 권한 플러시를 해주고 아까 만들어 둔 계정으로 돌아갑니다.

 

다시 SHOW MASTER STATUS 명령어를 통해 조회를 해보면 이번에는 마스터 DB로 활용할 DB의 바이너리 로그와 로그의 현재 위치가 잘 나오는 것을 알 수 있습니다. 두 정보 모두 슬레이브 DB를 마스터 DB에 연동해줄 때 꼭 필요한 정보이므로 미리 다른데 복사해두면 더 편하게 작업을 마칠 수 있습니다.

 

마스터 DB에 대한 설정이 모두 끝났으면 이제 슬레이브 DB 설정을 해줄 차례입니다. 위에서는 새롭게 마스터-슬레이브 복제 구조를 구축했기 때문에 기존 마스터 DB에 존재하는 데이터들을 덤프해서 가져올 필요가 없었습니다. 그러나 기본적으로는 마스터 DB가 데이터를 갖고 있는 경우에는 양 DB간의 완전한 동기화를 위해서 마스터 DB에 존재하는 데이터를 덤프한 뒤 슬레이브에 추가할 필요가 있습니다. 관련된 내용은 이 문서에 잘 정리되어 있으므로 링크를 걸어두도록 하겠습니다.

docs.microsoft.com/ko-kr/azure/mysql/concepts-migrate-dump-restore

덤프 및 복원을 사용하여 마이그레이션 - Azure Database for MySQL

슬레이브의 경우 Azure에서 DB서버를 하나 더 발급받아서 이용할 수도 있지만 편의상 로컬 데이터 베이스를 활용하였습니다. 기본적으로 클라우드, 로컬 상관없이 DB 서버를 설정하는 내용은 같으므로 큰 문제는 없습니다.

 

그럼 슬레이브 서버에서도 아까처럼 먼저 root 계정으로 접속해줍니다. 그 뒤 슬레이브 서버를 사용할 때 접속할 계정과 함께 슬레이브 DB도 아래처럼 새로 만들어 줍니다. 이미 계정을 미리 만들어 둔 터라 계정 생성에 실패했다고 나왔는데, 처음으로 계정을 만드는 경우라면 문제없이 만들어집니다.

계정과 DB가 모두 만들어졌다면 이제 슬레이브 복제를 위해 권한을 부여할 차례입니다. 아래의 문서를 보면 복제 권한을 부여하기 위해 다음과 같은 명령어를 입력하라고 나와있습니다.

GRANT REPLICATION SLAVE ON *.* TO '<username>'@'%';

docs.microsoft.com/ko-kr/azure/mysql/howto-data-in-replication

데이터에서 복제 구성-Azure Database for MySQL

 

위의 명령어를 이용해 슬레이브 권한을 부여해줍니다. OK가 나오면 잊지말고 슬레이브 계정에 대한 슬레이브 DB 권한도 부여해주면 됩니다. 주의할 점은 지금 만드는 마스터-슬레이브 구조에서 슬레이브 DB는 읽기 작업을 전담하는 역할을 하므로 읽기 작업 SELECT에 대한 권한만을 부여해주는 것입니다. 물론 권한 부여를 반영하기 위해서 권한 플러시도 해줍니다.

이제 슬레이브 서버를 위한 계정과 DB 설정이 모두 끝났으니 root 계정에서 로그아웃 한 후 아까 만들었던 슬레이브 전용계정으로 다시 로그인 해줍니다. 

 

이렇게 DB를 조회했을 때 아까 권한을 부여해줬던 DB가 존재하는지 확인해주면 됩니다. SELECT 권한만 부여해주었기 때문에 해당 서버에서 CUD 작업을 할 경우 'command_denied'에러가 나며 명령이 수행되지 않는다는 것을 알 수 있습니다. 

이제 마지막으로 해당 슬레이브 서버가 마스터 서버를 바라볼 수 있도록 CHANGE MASTER TO 명령어를 이용해 값을 설정해주는 것입니다. 

(참고: 이 명령어를 입력하기 전에 슬레이브 복제가 진행중이라면 STOP SLAVE를 통해서 멈춘 뒤 입력해야 합니다.)

CHANGE MASTER TO
MASTER_HOST='<MASTER 서버 호스트 주소>', -> (Azure에서는 서버 이름에 해당하는 부분)
MASTER_USER='<MASTER 서버 접속 계정>', -> (관리자이름 앞부분은 새 마스터 계정, 뒷부분은 그대로)
MASTER_USER='<MASTER 서버 접속 계정의 패스워드'>,
MASTER_LOG_FILE='<바이너리 로그 파일 이름>', -> (SHOW MASTER STATUS에 표시된 파일 이름)
MASTER_LOG_POS='<바이너리 로그 파일 위치>' -> (SHOW MASTER STATUS에 표시된 POSITION 값) 

 

두 번째 문제 발생

해당 쿼리를 알맞게 입력했지만 권한이 없다고 나옵니다. 아래 REPLICATION CLIENT에 대한 부분은 슬레이브 계정에는 부여를 해주지 않았기 때문에 오류가 발생하는 것이라 생각해서 다시 root 계정으로 돌아가 부여해주고 다시 시도해 보았습니다. SHOW SLAVE STATUS\G; 명령어는 이제 잘 작동하지만 여전히 CHANGE MASTER TO 명령어는 입력해보면 권한이 없다는 말만 반복합니다. 아까 분명히 REPLICATION SLAVE 권한을 부여했는데 말이죠.

 

해결책은?

오류 메세지를 꼼꼼히 다시 읽어본 것이 문제 해결에 도움이 되었습니다. 제가 부여했던 권한은 REPLICATION SLAVE 였으나  현재 필요한 권한은 'REPLICATION_SLAVE_ADMIN' 혹은 'SUPER' 권한이라고 나와있었습니다. 문제의 원인을 찾았으니 다시 root 계정으로 돌아가서 제대로 된 권한 부여를 해줍니다.

다시 권한부여된 계정으로 돌아와서 아까와 같은 명령어를 그대로 쳐줍니다. 그럼 이번에는 'ACCESS_DENIED'가 뜨지 않고 성공적으로 명령어가 수행되는 것을 알 수 있습니다.

여기까지 완료되었다면 START SLAVE 명령어를 먼저 실행해주고 SHOW SLAVE STATUS\G 명령어를 입력해서 슬레이브 복제가 제대로 진행되고 있는지 확인해 줍니다.

위와 같이 SLAVE_IO 스레드와 SLAVE_SQL 스레드가 둘 다 YES로 잘 동작하고 있고 아래 문구에도 슬레이브가 지금까지 모든 로그를 읽었고, 마스터로부터 업데이트를 기다리고 있다(SLAVE has read all relay log; waiting for more update)는 문구가 떠있으면 복제가 제대로 완료된 것입니다. 이제 마스터 서버로 가서 DB에 Replication을 구축한 마스터 DB에 접속한 다음 내용을 추가/삭제 해보면서 슬레이브 DB에 제대로 반영되었는지 확인해주면 되겠습니다.

 

참고

혹시나 아래와 같이 에러 메세지에 접근 거부라고 되어있는 경우에는 마스터 서버의 root 계정으로 돌아가서 마스터 서버용으로 생성했던 계정에 GRANT REPLICATION SLAVE 를 다시 한 번 부여해주시면 문제가 해결됩니다.

 

추가내용

슬레이브 연결이 완료된 서버가 잘 작동하는지 확인하기 위해서 복제를 진행하고 마스터 서버에서 데이터를 추가해주었지만 슬레이브 서버에는 전달이 되지 않는 문제가 있었습니다. 그래서 SHOW SLAVE STATUS\G; 명령어를 통해서 다음과 같은 오류가 발생했다는 것을 발견할 수 있었습니다. 의심되는 부분은 마스터 서버에서 복제하려는 데이터베이스의 이름과 슬레이브 서버에서 복제 받으려는 데이터베이스의 이름이 다르다는 부분이었습니다. (pr vs pr_repl)

그래서 혹시나 하고 기존 슬레이브의 DB를 삭제한 뒤 동일한 이름인 pr로 DB를 생성한 후 위에서 부여했던 대로 SELECT 권한만 넘겨주고 다시 슬레이브 계정으로 돌아와서 MASTER_LOG_FILE과 MASTER_LOG_POS에 해당하는 인자 값을 최신 값으로 업데이트 시켜주었습니다. 이후 마스터 DB에서 테이블을 추가했을 때 작동에 이상이 없는 것으로 보아 마스터와 슬레이브 서버에서 복제에 활용할 DB의 이름을 맞춰주어야 하는 듯 합니다.

 

마스터에서 생성한 테이블과 레코드

 

슬레이브에서 조회한 값

 

슬레이브 상태 조회

SLAVE STATUS를 조회해보면 이제는 복제후에도 오류 없이 SLAVE_IO와 SLAVE_SQL 스레드가 작동중인 것을 볼 수 있습니다. 

아랫 부분을 살펴봐도 SLAVE_SQL 스레드가 모든 로그를 읽었으며, 업데이트를 기다리고 있다는 문구가 출력되었다는 것은 복제가 잘 진행되었음을 반증한다고 할 수 있겠습니다.

---

왜 부여할 권한이 많은 자료들에서 언급하는 내용과 다를까?

마이크로 소프트에서 문서화 한 내용이나 MySQL 마스터-슬레이브 구축 등의 키워드를 통해 검색해서 나온 자료들을 살펴보면 모두 마스터 DB를 위해서는 GRANT ALL PRIVILEGES, 그리고 슬레이브 DB를 위해서는 GRANT REPLICATION 만 언급하고 있습니다. 그런데 해당 권한을 부여한 뒤에도 발생하는 에러 메시지들을 살펴보면 GRANT REPLICATION CLIENT 혹은 GRANT REPLICATION_SLAVE_ADMIN 처럼 비슷하지면서도 다른 권한들이 필요하다는 내용이 등장합니다.

위의 두 권한은 추가적인 보안을 위해서 8.0.xx 버전에서 새롭게 등장한 권한으로 보입니다. 그런데 실무에서는 아직도 MySQL 5.x 버전이 많이 사용된다고 하므로 추측을 해보자면 검색을 통해 나오는 많은 자료들이 5.x 버전을 기준으로 작성되어 있기에 이러한 변화가 다뤄지지 않은 듯 합니다. 때문에 저처럼 개발환경에서 8.0.xx을 사용하는 경우라면 MySQL에서 제공하는 에러 메시지나 아래의 링크를 참고해 해당 버전에서 부여 가능한 권한 목록 그리고 이 권한들이 주는 기능들을 좀 더 주의 깊게 살펴볼 필요가 있겠습니다.

dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave-admin 

MySQL :: MySQL 8.0 Reference Manual :: 6.2.2 Privileges Provided by MySQL

---

Azure MySQL 8.0.xx 서버에서는 마스터 DB 설정이 불가능하다?

슬레이브 DB가 마스터 DB를 바라보게 할 때 사용되는 핵심 명령어가 바로 CHANGE MASTER TO 입니다. 그런데 해당 명령어를 사용해보면 다음과 같은 에러문구가 등장합니다.

ERROR 1227 (42000): Access denied; you need (at least one of) the GRANT OPTION privilege(s) for this operation

어떠한 계정에서 CHANGE MASTER TO 명령어를 사용할 수 있도록 하기 위해서 필요한 권한은 SUPER 혹은 REPLICATION_SLAVE_ADMIN 입니다. 그리고 REPLICATION_SLAVE_ADMIN 를 다른 계정에 부여하도록 권한을 주는 것이 바로 SUPER 권한입니다. 로컬 DB의 경우 ROOT 계정이 말 그대로 모든 권한을 다 가지고 있기 때문에 위의 RSA 권한을 부여하는데 큰 지장이 없었습니다. 그러나 이와는 달리 Azure 서버의 경우는 DB 서버를 발급 받을 때 생성했던 root 계정마저도 SUPER 권한을 가지고 있지 않다고 합니다. 실제로 root 계정을 활용해 CHANGE MASTER TO 를 수행했어도 접근 거부 에러가 발생했습니다. 

결국 다른 계정에 RSA 권한을 부여해서 CHANGE MASTER TO를 활용해 슬레이브로 만드는 작업이나 Azure의 root 계정을 가지고 슬레이브로 만드는 작업이 모두 가능하지 않았습니다. 그래서 Azure에 MySQL 8.0.xx 서버를 발급해서 슬레이브 DB로 만드는 것이 불가능하다는 결론에 이르게 되었습니다. 

---

OUTRO

Azure MySQL 5.x 버전에서도 동일한 현상이 나타나는지에 대해서는 확실하지 않습니다. 다만 GRANT REPLICATION CLIENT 혹은 GRANT REPLICATION_SLAVE_ADMIN 등의 권한이 8.0 이상의 버전에서 보안상의 이유로 추가로 만들어졌다는 점과 CHANGE MASTER TO 를 사용할 수 없는 문제를 해결하기 위해서 많은 내용을 검색했음에도 Azure MySQL 8.0.xx은 슬레이브 DB로 사용이 불가능 할 수도 있다는 사실을 다루는 내용이 없었다는 점을 미루어 볼 때, 해당 문제는 8.0 이상의 MySQL을 사용할 때만 나타나는 문제라고 추측해볼 수 있겠습니다. 이 부분은 차후 MySQL 5.x 서버를 직접 발급받아서 실험 진행해보도록 하겠습니다.

---

Reference

gywn.net/2017/06/mysql-slave-addition-effect/

docs.microsoft.com/ko-kr/azure/mysql/howto-data-in-replication

docs.microsoft.com/ko-kr/azure/mysql/howto-data-in-replication

docs.microsoft.com/ko-kr/azure/mysql/howto-data-in-replication

dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave-admin 

---

Project

github.com/f-lab-edu/Hello-World

f-lab-edu/Hello-World