개요
n대의 마스터 DB와 1대의 슬레이브 DB를 연결하여 마스터 DB들의 데이터를 슬레이브 DB 한곳에 모아 조회할 수 있는 MySQL Multi-Source-Replication(MSR)을 구성하는 방법을 정리해보겠습니다.
테스트 준비
- 마스터 서버 2대, 슬레이브 서버 1대 준비
- 각 서버에 MySQL 5.7 이상 설치
- MySQL 리플리케이션작업 진행 시 마스터 서버의 데이터베이스에 쓰기 작업 금지
![Ncloud에서 MySQL Multi Source Replication 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-01.png)
ACG 설정
서버 준비가 끝났으면 우선 마스터 서버 슬레이브 서버로 디비 백업 파일 복사과 복제 구성에 필요한 22, 3306 포트를 오픈해야 합니다.
오픈 할 때 규칙은 마스터 서버와 슬레이브 서버의 공통 ACG에 사설 IP 대역 전체를 지정할 수도 있고, 슬레이브 서버 전용 ACG에 마스터 서버 IP만 등록하는 방법도 있습니다.
![Ncloud에서 MySQL Multi Source Replication 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-02.png)
Slave 장비에 백업 디렉토리 생성
먼저 Slave 장비에 Master 장비들로 부터 DB 복원용 덤프 파일을 전송 받을 백업 디렉토리를 생성하는 것 부터 시작하겠습니다.
~# mkdir /data
~# cd /data
![Ncloud에서 MySQL Multi Source Replication 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-15.png)
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 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-03.png)
![Ncloud에서 MySQL Multi Source Replication 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-04.png)
리플리케이션 계정 생성
MySQL 접속 후 리플리케이션을 진행할 계정 생성
#master1 계정 생성.
mysql> create user '리플리케이션 계정명'@'%' identified by '패스워드';
mysql> grant replication slave,replication client on *.* to '리플리케이션 계정명'@'%';
mysql> flush privileges;
![Ncloud에서 MySQL Multi Source Replication 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-05.png)
테스트 DB 생성
다음으로 테스트에 사용할 DB를 생성하겠습니다. 이미 생성되어 있는 다른 DB를 사용할 경우에는 별도로 테스트용 DB를 생성할 필요는 없습니다.
mysql> CREATE DATABASE testdb1 default CHARACTER SET UTF8;
![Ncloud에서 MySQL Multi Source Replication 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-07.png)
Master1 정보확인
이제 리플리케이션 설정에 필요한 Master 정보를 확인합니다.
mysql> show variables like 'server_id';
mysql> show master status;
Slave DB를 설정할 때 입력해야 하므로 별도로 기록해두어야
합니다.
![Ncloud에서 MySQL Multi Source Replication 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-06.png)
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 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-08.png)
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 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-09.png)
![Ncloud에서 MySQL Multi Source Replication 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-10.png)
리플리케이션 계정 생성
MySQL 접속 후 리플리케이션을 진행할 계정 생성합니다.
#master2 계정 생성.
mysql> create user '리플리케이션 계정명'@'%' identified by '패스워드';
mysql> grant replication slave,replication client on *.* to '리플리케이션 계정명'@'%';
mysql> flush privileges;
![Ncloud에서 MySQL Multi Source Replication 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-11.png)
테스트 DB 생성
다음으로 테스트에 사용할 DB를 생성하겠습니다. 이미 생성되어 있는 다른 DB를 사용할 경우에는 별도로 테스트용 DB를 생성할 필요는 없습니다.
mysql> CREATE DATABASE testdb2 default CHARACTER SET UTF8;
![Ncloud에서 MySQL Multi Source Replication 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-13.png)
Master2 정보확인
마찬가지로 리플리케이션 설정에 필요한 Master 정보를 확인합니다.
mysql> show variables like 'server_id';
mysql> show master status;
Slave DB를 설정할 때 입력해야 하므로 별도로 기록해두어야
합니다.
![Ncloud에서 MySQL Multi Source Replication 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-12.png)
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 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-14.png)
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 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-16.png)
![Ncloud에서 MySQL Multi Source Replication 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-17.png)
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 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-18.png)
- 백업 파일을 전송 받은 디렉토리로 이동해 백업 파일이 정상적으로 전송되었는지 확인합니다.
![Ncloud에서 MySQL Multi Source Replication 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-19.png)
![Ncloud에서 MySQL Multi Source Replication 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-20.png)
리플리케이션 채널 설정
리플리케이션 채널을 설정하는 쿼리문은 다음과 같습니다.
채널 설정 구문은 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 |
---|---|---|
Master1 | mysql-bin.000001 | 1321 |
Master2 | mysql-bin.000002 | 970 |
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 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-21.png)
리플리케이션 채널 시작
리플리케이션 시작 후 에러가 없는지 확인합니다.
-
#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 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-22.png)
- Master2
상태 확인에서는 [Slave_IO_State], [Slave_IO_Running], [Slave_SQL_Running] 항목들이 아래와 같이 되어 있으면 정상입니다.
![Ncloud에서 MySQL Multi Source Replication 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-23.png)
리플리케이션 테스트
리플리케이션 설정을 모두 마친 후에 Master 서버들에서 DB 작업을 진행하고, Slave 서버에 해당 내용이 복제되는지 확인해보겠습니다.
- Master1 서버 작업
Master1 서버에 테스트를 위한 [sampletable] Table을 생성합니다.
![Ncloud에서 MySQL Multi Source Replication 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-24.png)
- Slave 서버 확인
Slave 서버에서 testdb1 DB에 sampletable Table이 복제되었는지 확인합니다.
![Ncloud에서 MySQL Multi Source Replication 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-25.png)
- Master2 서버 작업
Master2 서버에 테스트를 위한 [sampletable] Table을 생성합니다.
![Ncloud에서 MySQL Multi Source Replication 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-26.png)
- Slave 서버 확인
Slave 서버에서 testdb2 DB에 sampletable Table이 복제되었는지 확인합니다.
![Ncloud에서 MySQL Multi Source Replication 구성하는 방법](/images/database/ncloud-database-mysql-multi-source-replication-guide-27.png)
버전별 명령어 비교
-
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
- MySQL 5.7 Multi-Source Replication 가이드
- MySQL 8.0 Multi-Source Replication 가이드