MySQL MSR Replication 구성 - Multi Source Replication

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]



관련된 다른글

답글 남기기