MySQL Multi Source Replication(MSR) 실습

MySQL Multi Source Replication(MSR) 실습

목표

- MySQL 8.0에서 기본적인 GTID 기반 Multi Source Replication을 구축한다.

 

Multi Source Replication ??

여러개의 Master DB를 1개의 Slave(Replica) 인스턴스에 연결하여 복제하는 구조로, 여러개의 Master DB의 내용을 하나의 Slave 에 모으는 기능

 

MSR 활용도 

- 여러 서버에 존재하는 각기 다른 데이터를 하나의 MySQL 서버로 통합하는 용도( ex) EDW 등 데이터 분석 용도)
- 여러 서버에 샤딩되어 있는 테이블 데이터를 하나의 데이터로 통합 (ex 기존 샤딩된 데이터베이스를 Merge할 때 사용 할 수 있음)
- 여러 서버의 데이터를 모아 하나의 MySQL에서 백업을 수행 ( 백업 서버 용도)

 

MSR 검토사항 

- 데이터베이스를 각각 구성해야 한다는 제약이 없으므로 리플리케이션 복제 시 충돌을 일으킬만한 부분이 없는지 사전에 검토 필요
- 실제 서비스 용도로 사용하기에는 많은 어려움이 있으므로 페일오버를 위해서는 다른 리플리카 서버를 둬야함

 

 

Topology

1. Slave 사전작업 

1) my.cnf 설정 변경

 

master_info_repository = 'TABLE'
relay_log_info_repository = 'TABLE'
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = sys

replicate-do-db = ch_master1:dwightdb1
replicate-do-db = ch_master1:dwightdb2
replicate-do-db = ch_master3:dwightdb3
replicate-do-db = ch_master4:dwightdb4
replicate-do-db = ch_master5:dwightdb5

 

2) 리플리케이션 계정생성

create user `replication_user`@`%` identified with mysql_native_password by 'new1234!';
grant replication slave on *.* to `replication_user`@`%`;
flush privileges;

2. Master 사전작업 

1) 각 마스터 별 디비 설정

 

2) 리플리케이션 계정생성

create user `replication_user`@`%` identified with mysql_native_password by 'new1234!';
grant replication slave on *.* to `replication_user`@`%`;
flush privileges;

3) 마스터 별 덤프 파일 생성

/mysql/Dwight/instance/bin/mysqldump -u root -pnew1234! -P 3307 --socket=/tmp/mysql_3307.sock -v --databases dwightdb1 dwightdb2 \
--quick --routines --set-gtid-purged=on \
--triggers --extended-insert --master-data=2 > /root/db_backup/master1_db.sql
 
 
 
/mysql/Dwight/instance/bin/mysqldump -u root -pnew1234! -P 3307 --socket=/tmp/mysql_3307.sock -v --databases dwightdb3 \
--quick --routines --set-gtid-purged=on \
--triggers --extended-insert --master-data=2 >/root/db_backup/master3_db.sql
 
/mysql/Dwight/instance/bin/mysqldump -u root -pnew1234! -P 3307 --socket=/tmp/mysql_3307.sock -v --databases dwightdb4 \
--quick --routines --set-gtid-purged=on \
--triggers --extended-insert --master-data=2 > /root/db_backup/master4_db.sql
 
/mysql/Dwight/instance/bin/mysqldump -u root -pnew1234! -P 3307 --socket=/tmp/mysql_3307.sock -v --databases dwightdb5 \
--quick --routines --set-gtid-purged=on \
--triggers --extended-insert --master-data=2 > /root/db_backup/master5_db.sql

4) 마스터별 GTID 추출

cat *.sql | grep GTID_PURGED | perl -p0 -e 's#/\*.*?\*/##sg' | cut -f2 -d'=' | cut -f2 -d$'\''

 

5) 기록

9f614b39-663e-11ed-b8e5-02b33e34566e:1-12
9f620898-663e-11ed-8384-0a5c9fb56988:1-12
9f60b66b-663e-11ed-b783-0268c272438a:1-10
9f620a19-663e-11ed-9928-0aba7fc3992e:1-10

 

6) GTID를 제외한 덤프 스크립트 생성

sed '/GTID_PURGED/d' master1_db.sql > master1_db_nopurge.sql
sed '/GTID_PURGED/d' master3_db.sql > master3_db_nopurge.sql
sed '/GTID_PURGED/d' master4_db.sql > master4_db_nopurge.sql
sed '/GTID_PURGED/d' master5_db.sql > master5_db_nopurge.sql

6) Slave 서버로 전송

rsync -avzr --progress master1_db_nopurge.sql root@10.213.213.165:/tmp
rsync -avzr --progress master3_db_nopurge.sql root@10.213.213.165:/tmp
rsync -avzr --progress master4_db_nopurge.sql root@10.213.213.165:/tmp
rsync -avzr --progress master5_db_nopurge.sql root@10.213.213.165:/tmp

3. Master 사전작업 

1) dump 파일 리스토어

mysql -u root -P 3307 --socket=/tmp/mysql_3307.sock -pnew1234! < master1_db_nopurge.sql
mysql -u root -P 3307 --socket=/tmp/mysql_3307.sock -pnew1234! < master3_db_nopurge.sql
mysql -u root -P 3307 --socket=/tmp/mysql_3307.sock -pnew1234! < master4_db_nopurge.sql
mysql -u root -P 3307 --socket=/tmp/mysql_3307.sock -pnew1234! < master5_db_nopurge.sql

 

2) SLAVE 서버 접속 후 gtid 셋팅

mysql> RESET MASTER;
mysql> SET @@GLOBAL.gtid_purged = "9f614b39-663e-11ed-b8e5-02b33e34566e:1-12, 9f620898-663e-11ed-8384-0a5c9fb56988:1-12, 9f60b66b-663e-11ed-b783-0268c272438a:1-10, 9f620a19-663e-11ed-9928-0aba7fc3992e:1-10";

3) 리플리케이션 설정

 

CHANGE MASTER TO MASTER_HOST="10.213.211.170", MASTER_USER="replication_user", MASTER_PORT=3307,\
MASTER_PASSWORD="new1234!", MASTER_AUTO_POSITION=1 FOR CHANNEL "ch_master1";
 
 
CHANGE MASTER TO MASTER_HOST="10.213.213.53", MASTER_USER="replication_user", MASTER_PORT=3307,\
MASTER_PASSWORD="new1234!", MASTER_AUTO_POSITION=1 FOR CHANNEL "ch_master3";
 
CHANGE MASTER TO MASTER_HOST="10.213.211.8", MASTER_USER="replication_user", MASTER_PORT=3307,\
MASTER_PASSWORD="new1234!", MASTER_AUTO_POSITION=1 FOR CHANNEL "ch_master4";
 
CHANGE MASTER TO MASTER_HOST="10.213.212.194", MASTER_USER="replication_user", MASTER_PORT=3307,\
MASTER_PASSWORD="new1234!", MASTER_AUTO_POSITION=1 FOR CHANNEL "ch_master5";
 
 
 
 
 
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (dwightdb1,dwightdb2) FOR CHANNEL "ch_master1";
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (dwightdb3) FOR CHANNEL "ch_master3";
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (dwightdb4) FOR CHANNEL "ch_master4";
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (dwightdb5) FOR CHANNEL "ch_master5";

 

 4)리플리케이션 시작 및 상태 확인

START SLAVE; SHOW SLAVE STATUS;

 

 

질문

1. 테스트 시에는 각각의 다른 마스터 서버에서 동일한 Logical Database를 Replication 하는 것은 가능한가요??

 

구성은 가능하나 duplication 등 기타 사항에 대한 고려사항이 증가합니다.

PK중복 등 Replication 을 중지 시킬만한 충돌이 발생하면 리플리케이션이 종료됩니다.

-> GTID Replication으로 구성하였기 때문에 skip_counter를 셋팅하는 것이 아니라 GTID_NEXT를 통해 스킵할 수 있음(정합성 확인 후 스킵 필요)