Ncloud에서 MySQL Multi Source Replication 구성하는 방법에 대한 상세 가이드입니다

개요

n대의 마스터 DB와 1대의 슬레이브 DB를 연결하여 마스터 DB들의 데이터를 슬레이브 DB 한곳에 모아 조회할 수 있는 MySQL Multi-Source-Replication(MSR)을 구성하는 방법을 정리해보겠습니다.

테스트 준비

  • 마스터 서버 2대, 슬레이브 서버 1대 준비
  • 각 서버에 MySQL 5.7 이상 설치
  • MySQL 리플리케이션작업 진행 시 마스터 서버의 데이터베이스에 쓰기 작업 금지
Ncloud에서 MySQL Multi Source Replication 구성하는 방법

ACG 설정

서버 준비가 끝났으면 우선 마스터 서버 슬레이브 서버로 디비 백업 파일 복사과 복제 구성에 필요한 22, 3306 포트를 오픈해야 합니다.
오픈 할 때 규칙은 마스터 서버와 슬레이브 서버의 공통 ACG에 사설 IP 대역 전체를 지정할 수도 있고, 슬레이브 서버 전용 ACG에 마스터 서버 IP만 등록하는 방법도 있습니다.

Ncloud에서 MySQL Multi Source Replication 구성하는 방법

Slave 장비에 백업 디렉토리 생성

먼저 Slave 장비에 Master 장비들로 부터 DB 복원용 덤프 파일을 전송 받을 백업 디렉토리를 생성하는 것 부터 시작하겠습니다.

~# mkdir /data
~# cd /data
Ncloud에서 MySQL Multi Source Replication 구성하는 방법

Master1 장비 구성

master1 설정 추가

  • /etc/my.cnf에 Master1 설정을 추가합니다.
~# vi /etc/my.cnf

server-id = 1
log-bin = mysql-bin
binlog_format = mixed
character-set-server = utf8

#MySQL을 재시작해 변경사항 적용.
~# systemctl restart mysqld
Ncloud에서 MySQL Multi Source Replication 구성하는 방법
Ncloud에서 MySQL Multi Source Replication 구성하는 방법

리플리케이션 계정 생성

MySQL 접속 후 리플리케이션을 진행할 계정 생성

#master1 계정 생성.
mysql> create user '리플리케이션 계정명'@'%' identified by '패스워드';
mysql> grant replication slave,replication client on *.* to '리플리케이션 계정명'@'%';
mysql> flush privileges;
Ncloud에서 MySQL Multi Source Replication 구성하는 방법

테스트 DB 생성

다음으로 테스트에 사용할 DB를 생성하겠습니다. 이미 생성되어 있는 다른 DB를 사용할 경우에는 별도로 테스트용 DB를 생성할 필요는 없습니다.

mysql> CREATE DATABASE testdb1 default CHARACTER SET UTF8;
Ncloud에서 MySQL Multi Source Replication 구성하는 방법

Master1 정보확인

이제 리플리케이션 설정에 필요한 Master 정보를 확인합니다.

mysql> show variables like 'server_id';
mysql> show master status;
[show master status] 명령으로 확인한 [File], [Position] 정보는 아래쪽에서 Slave DB를 설정할 때 입력해야 하므로 별도로 기록해두어야 합니다.
Ncloud에서 MySQL Multi Source Replication 구성하는 방법

DB 백업 파일 슬레이브 장비로 전송

testdb1 DB의 백업파일을 생성하고, 슬레이브 서버로 전송합니다.

~# mkdir /data
~# cd /data
~# mysqldump -u root -p --databases testdb1 > /data/test1.sql 

#슬레이브 서버에 전송.
~# scp test1.sql root@10.0.0.8:/data
Ncloud에서 MySQL Multi Source Replication 구성하는 방법

Master2 장비 구성

Master2 설정 추가

  • /etc/my.cnf에 Master2 설정을 추가합니다.
~# vi /etc/my.cnf

server-id = 2
log-bin = mysql-bin
binlog_format = mixed
character-set-server = utf8

#DB를 재시작하여 변경사항 적용.
~# systemctl restart mysqld
Ncloud에서 MySQL Multi Source Replication 구성하는 방법
Ncloud에서 MySQL Multi Source Replication 구성하는 방법

리플리케이션 계정 생성

MySQL 접속 후 리플리케이션을 진행할 계정 생성합니다.

#master2 계정 생성.
mysql> create user '리플리케이션 계정명'@'%' identified by '패스워드';
mysql> grant replication slave,replication client on *.* to '리플리케이션 계정명'@'%';
mysql> flush privileges;
Ncloud에서 MySQL Multi Source Replication 구성하는 방법

테스트 DB 생성

다음으로 테스트에 사용할 DB를 생성하겠습니다. 이미 생성되어 있는 다른 DB를 사용할 경우에는 별도로 테스트용 DB를 생성할 필요는 없습니다.

mysql> CREATE DATABASE testdb2 default CHARACTER SET UTF8;
Ncloud에서 MySQL Multi Source Replication 구성하는 방법

Master2 정보확인

마찬가지로 리플리케이션 설정에 필요한 Master 정보를 확인합니다.

mysql> show variables like 'server_id';
mysql> show master status;
[show master status] 명령으로 확인한 [File], [Position] 정보는 아래쪽에서 Slave DB를 설정할 때 입력해야 하므로 별도로 기록해두어야 합니다.
Ncloud에서 MySQL Multi Source Replication 구성하는 방법

DB 백업 파일 슬레이브 장비로 전송

testdb2 DB의 백업파일 생성하고, 슬레이브 서버로 전송합니다.

~# mkdir /data
~# cd /data
~# mysqldump -u root -p --databases testdb2 > /data/test2.sql

~# scp test2.sql root@10.0.0.8:/data
Ncloud에서 MySQL Multi Source Replication 구성하는 방법

Slave 장비 구성

Slave 설정 추가

  • /etc/my.cnf에 Slave 설정을 추가합니다.
    MySQL 8.0 버전 부터는 replicate-do-db 설정에 채널 정보도 추가할 수 있게 업데이트 되었습니다.
  • ~# vi /etc/my.cnf

### MySQL5.7 기준 ###

server-id = 3 

#리플리케이션 대상 디비 설정.
replicate-do-db = testdb1
replicate-do-db = testdb2

#리플리케이션 제외 디비 설정.
replicate-ignore-db = information_schema
replicate-ignore-db = mysql
replicate-ignore-db = performance_schema
replicate-ignore-db = sys

master_info_repository = 'TABLE'
relay_log_info_repository = 'TABLE'
slave-skip-errors = all

#DB를 재시작하여 변경사항 적용.
~# systemctl restart mysqld

  • ~# vi /etc/my.cnf

### MySQL 8 기준 ###

server-id = 3 

# 리플리케이션 대상 디비 설정 (replicate-do-db = 채널명:DB명)
replicate-do-db = ch_testdb1:testdb1
replicate-do-db = ch_testdb1:testdb2

#리플리케이션 제외 디비 설정.
replicate-ignore-db = information_schema
replicate-ignore-db = mysql
replicate-ignore-db = performance_schema
replicate-ignore-db = sys

slave-skip-errors = all

#DB를 재시작하여 변경사항 적용.
~# systemctl restart mysqld
Ncloud에서 MySQL Multi Source Replication 구성하는 방법
Ncloud에서 MySQL Multi Source Replication 구성하는 방법

DB 복구 진행

먼저 복구할 DB를 미리 생성하고 Master1, Master2 장비에서 전송 받은 백업 파일을 사용해 DB를 복구합니다.

#복구할 DB 생성.
mysql> create database testdb1;
mysql> create database testdb2;
mysql> quit

#DB 복구
~# mysql -u root -p testdb1 < /data/test1.sql
~# mysql -u root -p testdb2 < /data/test2.sql
Ncloud에서 MySQL Multi Source Replication 구성하는 방법
  • 백업 파일을 전송 받은 디렉토리로 이동해 백업 파일이 정상적으로 전송되었는지 확인합니다.
Ncloud에서 MySQL Multi Source Replication 구성하는 방법
Ncloud에서 MySQL Multi Source Replication 구성하는 방법

리플리케이션 채널 설정

리플리케이션 채널을 설정하는 쿼리문은 다음과 같습니다.
채널 설정 구문은 MySQL 버전별로 다르고, 특히 8.0.23 버전부터는 완전히 달라지므로 버전에 맞게 사용하시면 됩니다.

  • # MySQL 5.7
mysql> CHANGE MASTER TO MASTER_HOST='마스터IP', MASTER_PORT=포트번호, MASTER_USER='생성한 리플리케이션 계정명', MASTER_PASSWORD='패스워드', MASTER_LOG_FILE='위에서 확인된 File명', MASTER_LOG_POS=위에서 확인된 Position 번호 FOR CHANNEL '채널 이름';

  • # MySQL 8.0 ~ 8.0.22
mysql> CHANGE MASTER TO MASTER_HOST='마스터IP', MASTER_PORT=포트번호, MASTER_USER='생성한 리플리케이션 계정명', MASTER_PASSWORD='패스워드', GET_MASTER_PUBLIC_KEY=1, MASTER_LOG_FILE='위에서 확인된 File명', MASTER_LOG_POS=위에서 확인된 Position 번호 FOR CHANNEL '채널 이름';

  • # MySQL 8.0.23 이후 버전
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='마스터IP', SOURCE_PORT=포트번호, SOURCE_USER='생성한 리플리케이션 계정명', SOURCE_PASSWORD='패스워드', GET_SOURCE_PUBLIC_KEY=1, SOURCE_LOG_FILE='위에서 확인된 File명', SOURCE_LOG_POS=위에서 확인된 Position 번호 FOR CHANNEL 'ch_testdb1';

여기에 필요한 정보 중에서 [MASTER_LOG_FILE(SOURCE_LOG_FILE)]과 [MASTER_LOG_POS(SOURCE_LOG_POS)]은 위쪽에서 확인한 Master DB들의 정보에 나타났던 것으로 정리하면 다음과 같습니다.

DB File Position
Master1mysql-bin.0000011321
Master2mysql-bin.000002970

Master 서버 정보 입력

Slave 서버에 Master 서버 정보를 하나씩 입력합니다.

  • #Master1
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.6', MASTER_USER='testuser', MASTER_PORT=3306, MASTER_PASSWORD='Test123$', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1321 FOR CHANNEL 'ch_testdb1';

#Master2
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.7', MASTER_USER='testuser', MASTER_PORT=3306, MASTER_PASSWORD='Test123$', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=970 FOR CHANNEL 'ch_testdb2';

mysql> FLUSH PRIVILEGES;

  • #Master1
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.6', MASTER_USER='testuser', MASTER_PORT=3306, MASTER_PASSWORD='Test123$', GET_MASTER_PUBLIC_KEY=1, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1321 FOR CHANNEL 'ch_testdb1';

#Master2
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.7', MASTER_USER='testuser', MASTER_PORT=3306, MASTER_PASSWORD='Test123$', GET_MASTER_PUBLIC_KEY=1, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=970 FOR CHANNEL 'ch_testdb2';

mysql> FLUSH PRIVILEGES;

  • #Master1
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.0.0.6', SOURCE_PORT=3306, SOURCE_USER='testuser', SOURCE_PASSWORD='Test!@123', GET_SOURCE_PUBLIC_KEY=1, SOURCE_LOG_FILE='mysql-bin.000001', SOURCE_LOG_POS=1321 FOR CHANNEL 'ch_testdb1';

#Master2
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.0.0.7', SOURCE_PORT=3306, SOURCE_USER='testuser', SOURCE_PASSWORD='Test!@123', GET_SOURCE_PUBLIC_KEY=1, SOURCE_LOG_FILE='mysql-bin.000001', SOURCE_LOG_POS=970 FOR CHANNEL 'ch_testdb2';

mysql> FLUSH PRIVILEGES;
Ncloud에서 MySQL Multi Source Replication 구성하는 방법

리플리케이션 채널 시작

리플리케이션 시작 후 에러가 없는지 확인합니다.

  • #MySQL 5.7 기준
mysql> start slave for channel 'ch_testdb1'; 
mysql> start slave for channel 'ch_testdb2';

mysql> show slave status for channel 'ch_testdb1'\G 
mysql> show slave status for channel 'ch_testdb2'\G

  • #MySQL 8 기준
mysql> start replica for channel 'ch_testdb1'; 
mysql> start replica for channel 'ch_testdb2';

mysql> show replica status for channel 'ch_testdb1'\G 
mysql> show replica status for channel 'ch_testdb2'\G
  • Master1
    상태 확인에서는 [Slave_IO_State], [Slave_IO_Running], [Slave_SQL_Running] 항목들이 아래와 같이 되어 있으면 정상입니다.
Ncloud에서 MySQL Multi Source Replication 구성하는 방법
  • Master2
    상태 확인에서는 [Slave_IO_State], [Slave_IO_Running], [Slave_SQL_Running] 항목들이 아래와 같이 되어 있으면 정상입니다.
Ncloud에서 MySQL Multi Source Replication 구성하는 방법

리플리케이션 테스트

리플리케이션 설정을 모두 마친 후에 Master 서버들에서 DB 작업을 진행하고, Slave 서버에 해당 내용이 복제되는지 확인해보겠습니다.

  • Master1 서버 작업
    Master1 서버에 테스트를 위한 [sampletable] Table을 생성합니다.
Ncloud에서 MySQL Multi Source Replication 구성하는 방법
  • Slave 서버 확인
    Slave 서버에서 testdb1 DB에 sampletable Table이 복제되었는지 확인합니다.
Ncloud에서 MySQL Multi Source Replication 구성하는 방법
  • Master2 서버 작업
    Master2 서버에 테스트를 위한 [sampletable] Table을 생성합니다.
Ncloud에서 MySQL Multi Source Replication 구성하는 방법
  • Slave 서버 확인
    Slave 서버에서 testdb2 DB에 sampletable Table이 복제되었는지 확인합니다.
Ncloud에서 MySQL Multi Source Replication 구성하는 방법

버전별 명령어 비교

  • MySQL 5.7 리플리케이션 명령어
    채널 설정 CHANGE MASTER TO MASTER_HOST='마스터IP', MASTER_PORT=포트번호, MASTER_USER='생성한 리플리케이션 계정명', MASTER_PASSWORD='패스워드', MASTER_LOG_FILE='위에서 확인된 File명', MASTER_LOG_POS=위에서 확인된 Position 번호 FOR CHANNEL '채널 이름';
    채널 시작START SLAVE FOR CHANNEL "채널이름";
    채널 중지STOP SLAVE FOR CHANNEL "채널이름";
    채널 상태 확인SHOW SLAVE STATUS FOR CHANNEL "채널이름";
    채널 정보 삭제RESET SLAVE ALL FOR CHANNEL "채널이름";
  • MySQL 5.7 리플리케이션 명령어
    채널 설정 CHANGE MASTER TO MASTER_HOST='마스터IP', MASTER_PORT=포트번호, MASTER_USER='생성한 리플리케이션 계정명', MASTER_PASSWORD='패스워드', GET_Master_PUBLIC_KEY=1, MASTER_LOG_FILE='위에서 확인된 File명', MASTER_LOG_POS=위에서 확인된 Position 번호 FOR CHANNEL '채널 이름';
    채널 시작START SLAVE FOR CHANNEL "채널이름";
    채널 중지STOP SLAVE FOR CHANNEL "채널이름";
    채널 상태 확인SHOW SLAVE STATUS FOR CHANNEL "채널이름";
    채널 정보 삭제RESET SLAVE ALL FOR CHANNEL "채널이름";
  • MySQL 8.0 리플리케이션 명령어
    채널 설정 CHANGE REPLICATION SOURCE TO SOURCE_HOST='마스터IP', SOURCE_PORT=포트번호, SOURCE_USER='생성한 리플리케이션 계정명', SOURCE_PASSWORD='패스워드', GET_SOURCE_PUBLIC_KEY=1, SOURCE_LOG_FILE='위에서 확인된 File명', SOURCE_LOG_POS=위에서 확인된 Position 번호 FOR CHANNEL 'ch_testdb1';
    채널 시작START REPLICA FOR CHANNEL "채널이름";
    채널 중지STOP REPLICA FOR CHANNEL "채널이름";
    채널 상태 확인SHOW REPLICA STATUS FOR CHANNEL "채널이름";
    채널 정보 삭제RESET REPLICA ALL FOR CHANNEL "채널이름";

참고 URL

  1. MySQL 5.7 Multi-Source Replication 가이드
  2. MySQL 8.0 Multi-Source Replication 가이드
Tags: database backup mysql