Last Updated on 3월 21, 2021 by 태랑(정현호)
1. MSR - Multi Source Replication
MySQL MSR - Multi Source Replication 는 1개의 Slave(Replica) 서버에 여러개의 Master DB를 연결하여 복제하는 구조로 여러개의 Master DB의 내용을 1곳의 Slave(Replica) 에 모으는 역활을 하게 됩니다.(Multi source 의 구조)
MySQL 5.7 에서 추가된 기능으로 통합,백업 , OLAP 및 배치 작업 등에서 유용하게 사용할 수 있습니다.
Multi Source Replication(MSR) 의 구성시에는 GTID 기반 복제 와 Binary Log Position 기반 복제 둘다 사용이 가능 합니다.
• 포스팅 환경
버전 : 5.7.33 , 8.0.23
복제 대상
- Master1 의 DB명 : m1testdb1, m1testdb2 / Channel 명 : ch_master1
- Master2 의 DB명 : m2testdb1, m2testdb2 / Channel 명 : ch_master2
2. 파라미터 변경
MySQL MSR 구성시 Slave(Replica) 에서는 connection metadata repository 와 applier metadata repository 에 대한 정보가 TABLE 에 저장이 되어야 합니다. 그래서 master_info_repository 와 relay_log_info_repository 파라미터를 TABLE 로 설정 해야 합니다(기본값 : FILE)
그외 Replication 에서 제외 할 Metadata 관련된 데이터베이스와 복제가 수행될 Database에 대해서 명시적으로 지정하도록 하겠습니다.
Slave(Replica) 에서 my.cnf 에 아래의 내용 입력 후 MySQL 재시작 합니다.
• MySQL 5.7 기준
[root]# vi /etc/my.cnf [mysqld] 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 # 5.7 replicate-do-db = m1testdb1 replicate-do-db = m1testdb2 replicate-do-db = m2testdb1 replicate-do-db = m2testdb2
[참고] 테이블별 복제 설정은 replicate-do-table 을 이용 하시면 됩니다.
replicate-do-table = 데이터베이스명.테이블명
* MySQL 5.7 기준 설정
• MySQL 8.0 기준
[root]# vi /etc/my.cnf [mysqld] 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 # mysql 8.0 replicate-do-db = ch_master1:m1testdb1 replicate-do-db = ch_master1:m1testdb2 replicate-do-db = ch_master2:m2testdb1 replicate-do-db = ch_master2:m2testdb2
[참고] MySQL8.0 부터 채널별 replicate-do-db 설정이 가능하고 채널명:DB명 으로 입력 하면 됩니다.
• MySQL 재시작(Slave/Replica)
[root]# systemctl restart mysqld
3. 유저 생성 및 데이터 load
3-1 복제에 사용할 유저는 아래와 같이 생성하도록 하겠습니다.
mysql> create user 'repl_user'@'%' identified by 'Repl_user1234!@#$'; mysql> grant replication slave,replication client on *.* to 'repl_user'@'%'; mysql> flush privileges;
[참조] 포스팅 환경에서 Master 서버의 복제 대상 database
## Master 1 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | m1testdb1 | | m1testdb2 | +--------------------+ mysql> SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA LIKE '%testdb%' order by 1,2; +--------------+------------+ | TABLE_SCHEMA | TABLE_NAME | +--------------+------------+ | m1testdb1 | tb_test1 | | m1testdb2 | tb_test2 | +--------------+------------+ ## Master 2 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | m2testdb1 | | m2testdb2 | | mysql | | performance_schema | | sys | +--------------------+ mysql> SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA LIKE '%testdb%' order by 1,2; +--------------+------------+ | TABLE_SCHEMA | TABLE_NAME | +--------------+------------+ | m2testdb1 | tb_test1 | | m2testdb2 | tb_test2 | +--------------+------------+
3-2 2개의 Master DB에서 각각 dump 를 수행합니다
• Master 1 에서 수행
mysql> mysqldump -u아이디 -p패스워드 -v --databases m1testdb1 m1testdb2 \ --quick --single-transaction --routines --set-gtid-purged=OFF \ --triggers --extended-insert --master-data=2 \ | gzip > /root/db_backup/master1_db.sql.gz or ## login-path 이용시 mysql> mysqldump --login-path=dba -v --databases m1testdb1 m1testdb2 \ --quick --single-transaction --routines --set-gtid-purged=OFF \ --triggers --extended-insert --master-data=2 \ | gzip > /root/db_backup/master1_db.sql.gz
• Master 2 에서 수행
mysql> mysqldump -u아이디 -p패스워드 -v --databases m2testdb1 m2testdb2 \ --quick --single-transaction --routines --set-gtid-purged=OFF \ --triggers --extended-insert --master-data=2 \ | gzip > /root/db_backup/master2_db.sql.gz or ## login-path 이용시 mysql> mysqldump --login-path=dba -v --databases m2testdb1 m2testdb2 \ --quick --single-transaction --routines --set-gtid-purged=OFF \ --triggers --extended-insert --master-data=2 \ | gzip > /root/db_backup/master2_db.sql.gz
[참고] --login-path 관련해서는 아래 포스팅을 참조하시면 됩니다.

3-3 데이터 load 수행
3-3-1 파일 전송
# Master 1 rsync -avzr --progress master1_db.sql.gz msr-replica:~/ # Master 2 rsync -avzr --progress master2_db.sql.gz msr-replica:~/
* 포스팅 환경에서 Slave(Replica) 서버의 hosts 는 msr-replica 입니다.
3-3-2 압축 해제 및 데이터 입력
# 압축 해제 [root]# gunzip master1_db.sql.gz [root]# gunzip master2_db.sql.gz # 데이터 입력 [root]# mysql --login-path=dba < master1_db.sql [root]# mysql --login-path=dba < master2_db.sql
3-3-3 추가된 데이터베이스 확인
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | m1testdb1 | | m1testdb2 | | m2testdb1 | | m2testdb2 | | mysql | | performance_schema | | sys | +--------------------+
4. 복제 설정
4-1 먼저 Master 정보 확인 및 binary 로그 위치를 확인 해야 합니다. 해당 정보는 이전의 mysqldump 에서 확인 할 수 있습니다.
[root]# grep MASTER_LOG_FILE master1_db.sql -- CHANGE MASTER TO MASTER_LOG_FILE='wmp-binlog.000010', MASTER_LOG_POS=1649; [root]# grep MASTER_LOG_FILE master2_db.sql -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=2702;
4-2 Replication 설정
아래와 같이 channel 구문을 사용한 복제 설정을 합니다.
4-2-1 CHANGE MASTER (MySQL 5.7)
## binlog,log pos 정보 이용시 mysql> CHANGE MASTER TO MASTER_HOST='master1', MASTER_USER='repl_user', MASTER_PASSWORD='Repl_user1234!@#$', MASTER_LOG_FILE='wmp-binlog.000010', MASTER_LOG_POS=1649 FOR CHANNEL 'ch_master1'; mysql> CHANGE MASTER TO MASTER_HOST='master2', MASTER_USER='repl_user', MASTER_PASSWORD='Repl_user1234!@#$', MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=2702 FOR CHANNEL 'ch_master2'; ## GTID 이용시 mysql> CHANGE MASTER TO MASTER_HOST='master1', MASTER_USER='repl_user',MASTER_PASSWORD='Repl_user1234!@#$', MASTER_AUTO_POSITION=1 FOR CHANNEL 'ch_master1'; mysql> CHANGE MASTER TO MASTER_HOST='master2', MASTER_USER='repl_user',MASTER_PASSWORD='Repl_user1234!@#$', MASTER_AUTO_POSITION=1 FOR CHANNEL 'ch_master2';
[참고1] GTID 모드 Replication 에서는 MASTER_AUTO_POSITION=1 를 사용 합니다.
[참고2] GTID 이용한 Replication 설정,구성은 아래 포스팅을 참조하시면 됩니다.

4-2-2 CHANGE MASTER (MySQL 8.0 ~ 8.0.22)
8.0.22 까지는 아래 명령을 통해서 수행을 합니다.
## binlog,log pos 정보 이용시 mysql> CHANGE MASTER TO MASTER_HOST="master1", MASTER_USER="repl_user", MASTER_PASSWORD="Repl_user1234!@#$", GET_MASTER_PUBLIC_KEY=1, MASTER_LOG_FILE='wmp-binlog.000005', MASTER_LOG_POS=196 FOR CHANNEL "ch_master1"; mysql> CHANGE MASTER TO MASTER_HOST="master2", MASTER_USER="repl_user", MASTER_PASSWORD="Repl_user1234!@#$", GET_MASTER_PUBLIC_KEY=1, MASTER_LOG_FILE='binlog.000006', MASTER_LOG_POS=1990 FOR CHANNEL "ch_master2"; mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (m1testdb1,m1testdb2) FOR CHANNEL 'ch_master1'; mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (m2testdb1,m2testdb2) FOR CHANNEL 'ch_master2';
* GTID 사용은 위의 내용을 참조하시면 됩니다.
4-2-3 CHANGE MASTER (MySQL 8.0.23)
8.0.23 부터는 아래 명령을 통해서 수행을 합니다.
CHANGE REPLICATION SOURCE TO .. 로 구문이 변경되었습니다.
## binlog,log pos 정보 이용시 mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST="master1", SOURCE_USER="repl_user", SOURCE_PASSWORD="pasRepl_user1234!@#$sword", GET_SOURCE_PUBLIC_KEY=1, MASTER_LOG_FILE='wmp-binlog.000005', MASTER_LOG_POS=196 FOR CHANNEL "ch_master1"; mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST="master2", SOURCE_USER="repl_user", SOURCE_PASSWORD="Repl_user1234!@#$", GET_SOURCE_PUBLIC_KEY=1, SOURCE_LOG_FILE='binlog.000006', SOURCE_LOG_POS=1990 FOR CHANNEL "ch_master2"; mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (m1testdb1,m1testdb2) FOR CHANNEL 'ch_master1'; mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (m2testdb1,m2testdb2) FOR CHANNEL 'ch_master2';
[참조] REPLICATION FILTER는 Dynamic 적용을 위하여 실행하는 것이며 my.cnf 에서 설정한 replicate-do-db 는 MySQL 재시작을 고려하여 설정하였습니다.
기존의 CHANGE MASTER 구문을 사용하면 가능하지만 아래처럼 Warning 메세지가 발생 합니다.
Warning (Code 1287): 'CHANGE MASTER' is deprecated and will be removed in a future release. Please use CHANGE REPLICATION SOURCE instead Warning (Code 1287): 'MASTER_HOST' is deprecated and will be removed in a future release. Please use SOURCE_HOST instead Warning (Code 1287): 'MASTER_USER' is deprecated and will be removed in a future release. Please use SOURCE_USER instead Warning (Code 1287): 'MASTER_PASSWORD' is deprecated and will be removed in a future release. Please use SOURCE_PASSWORD instead Warning (Code 1287): 'MASTER_LOG_FILE' is deprecated and will be removed in a future release. Please use SOURCE_LOG_FILE instead Warning (Code 1287): 'MASTER_LOG_POS' is deprecated and will be removed in a future release. Please use SOURCE_LOG_POS instead
GET_SOURCE_PUBLIC_KEY 와 GET_MASTER_PUBLIC_KEY 는 caching_sha2_password 관련하여 에러 발생 방지를 위해서 추가한 옵션 입니다. 미사용시 아래와 같이 에러가 발생합니다.
Last_IO_Error: error connecting to master 'repl_user@master1:3306' - retry-time: 60 retries: 4 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
4-2-4 Replication 시작
• MySQL 5.7
# 각 채널별로 시작 mysql> START SLAVE FOR CHANNEL "ch_master1"; mysql> START SLAVE FOR CHANNEL "ch_master2"; or # 모든 채널에 대해서 모두 시작 mysql> START SLAVE;
• MySQL 8.0
# 각 채널별로 시작 mysql> START REPLICA FOR CHANNEL "ch_master1"; mysql> START REPLICA FOR CHANNEL "ch_master2"; or # 모든 채널에 대해서 모두 시작 mysql> START REPLICA;
MySQL 8.0 에서 부터는 SLAVE 용어에서 REPLICA 로 변경되었으며 명령어도 slave 대신 replica 로 변경하여 사용하면 대부분 실행 됩니다.
4-2-3 Replication 상태 확인
# 채널별 Slave 상태 조회 mysql> SHOW SLAVE STATUS FOR CHANNEL "ch_master1"\G mysql> SHOW SLAVE STATUS FOR CHANNEL "ch_master2"\G or # 전체 조회 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: master1 Master_User: repl_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: wmp-binlog.000008 Read_Master_Log_Pos: 194 Relay_Log_File: relay_log-ch_master1.000005 Relay_Log_Pos: 409 Relay_Master_Log_File: wmp-binlog.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: testdb1,testdb2,testdb1,testdb2 Replicate_Ignore_DB: mysql,test,information_schema,performance_schema,sys,mysql,test,information_schema,performance_schema,sys Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 194 Relay_Log_Space: 4344 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 1f3b100f-8275-11eb-b444-08002761d7ed Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 1f3b100f-8275-11eb-b444-08002761d7ed:1412-1417 Executed_Gtid_Set: 1f3b100f-8275-11eb-b444-08002761d7ed:1-1417, 706aaace-11d5-11eb-8127-0800279c8b61:1-678 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: ch_master1 Master_TLS_Version: *************************** 2. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: master2 Master_User: repl_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000076 Read_Master_Log_Pos: 154 Relay_Log_File: relay_log-ch_master2.000002 Relay_Log_Pos: 361 Relay_Master_Log_File: binlog.000076 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: testdb1,testdb2,testdb1,testdb2 Replicate_Ignore_DB: mysql,test,information_schema,performance_schema,sys,mysql,test,information_schema,performance_schema,sys Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 573 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_UUID: 43828b8f-65cc-11eb-b96d-080027bf7f98 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 1f3b100f-8275-11eb-b444-08002761d7ed:1-1417, 706aaace-11d5-11eb-8127-0800279c8b61:1-678 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: ch_master2 Master_TLS_Version:
5. 관련된 추가 정보
먼저 문제 발생시 트랜잭션의 Skip은 아래 포스팅에서 3번 항목을 보시면 됩니다.

[참조] MySQL 8.0 에서는 SLAVE 대신 REPLICA 사용하면 됩니다.
5-1) Replication 시작
# 채널별 시작 mysql> START SLAVE FOR CHANNEL "ch_master1"; mysql> START SLAVE FOR CHANNEL "ch_master2"; or # 모든 채널에 대해서 모두 시작 mysql> START SLAVE;
5-2) Replication 중지
# 채널별 중지 mysql> STOP SLAVE FOR CHANNEL "ch_master1"; mysql> STOP SLAVE FOR CHANNEL "ch_master2"; or # 모든 채널에 대해서 모두 시작 mysql> START SLAVE;
5-3) Slave 상태 조회
mysql> SHOW SLAVE STATUS FOR CHANNEL "ch_master1"\G mysql> SHOW SLAVE STATUS FOR CHANNEL "ch_master2"\G or # 전체 조회 mysql> show slave status\G
5-4) Resetting Multi-Source Replicas
# 채널별 Reset mysql> RESET SLAVE FOR CHANNEL "ch_master1"; mysql> RESET SLAVE FOR CHANNEL "ch_master2"; or # 모든 채널 Reset mysql> RESET SLAVE ALL;
5-5) 모니터링 채널 - Performance Schema
Replication_connection_status 테이블을 사용하여 채널을 모니터링 할 수 있으며 전체 또는 채널별로 조회할 수 있습니다.
mysql> SELECT * FROM performance_schema.replication_connection_status\G; or mysql> SELECT * FROM performance_schema.replication_connection_status WHERE CHANNEL_NAME='ch_master1'\G
Ref link
dev.mysql.com/5.7/replication-multi-source [Link]
dev.mysql.com/8.0/replication-multi-source [Link]
dev.mysql.com/5.7/change-replication-filter [Link]
dev.mysql.com/8.0/change-replication-filter [Link]
관련된 다른글





Senior DBA(MySQL, Oracle) - 현재 위메프에서 많은 새로움을 경험중입니다
At WeMakePrice / Previous - Oracle Korea ACS Support / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io