MySQL8 Multi Source Replication 개선점

5.7과 8.0 multi-source replication 차이점

mysql 5.7 버전부터 multi-source replication 기능이 생기면서 유용하게 사용되고 있지만
두대 이상의 Master 에서 같은 이름의 database를 구분 지어 다른 db명으로 변경해서
replication 해오는 기능은 지원되지 않았습니다.
replication filter 가 channel 별로 적용 되는 게 아닌 , global 로 적용되어
replication_rewrite_db filter를 마음대로 사용할 수 없었기 때문인데
mysql 8 버전대에서 이 부분이 아래와 같이 가능해졌습니다.

MySQL 5.7

Master 1: kimdubi_db => Slave : kimdubi_db
Master 2: kimdubi_db => Slave : kimdubi_db

=> replication_rewrite_db filter 가 global 로 적용되기 때문에 위와 같이 master host 가 달라도 data 충돌의 위험이 있음

MySQL 8.0

Master 1: kimdubi_db => Slave : kimdubi_db1
Master 2: kimdubi_db => Slave : kimdubi_db2

=> 같은 db 명이지만 slave 로 replication 해올 때 구분 짓기 가능

적용 방법

  • multi source replication 설정
mysql> change master to
    -> master_host='172.17.0.6',
    -> master_port=3306,
    -> master_user='replUser',
    -> master_password='!dlatl00',
    -> master_log_file='mysql-bin.000011',
    -> master_log_pos=789
    -> for channel 'master_1';
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> change master to
    -> master_host='172.17.0.7',
    -> master_port=3306,
    -> master_user='replUser',
    -> master_password='!dlatl00',
    -> master_log_file='mysql-bin.000010',
    -> master_log_pos=1099
    -> for channel 'master_2';
Query OK, 0 rows affected, 2 warnings (0.04 sec)
  • 각각 master 로 부터 replication 받아올 db 생성
mysql> create database kimdubi_db1;
Query OK, 1 row affected (0.00 sec)

mysql> create database kimdubi_db2;
Query OK, 1 row affected (0.02 sec)
  • replication filter 설정
mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (kimdubi_db1), REPLICATE_REWRITE_DB = (kimdubi_db, kimdubi_db1)) FOR CHANNEL 'master_1';
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (kimdubi_db2), REPLICATE_REWRITE_DB = (kimdubi_db, kimdubi_db2)) FOR CHANNEL 'master_2';
Query OK, 0 rows affected (0.01 sec)

=> 5.7 버전에서는 replication filter 가 global 로 적용되었다면
8.0 버전 부터는 channel 별로 적용되기 때문에 5.7 과 같은 데이터 중복 충돌을 피할 수 있게 되었음
slave 는 relay log 로 부터 REPLICATE_REWRITE_DB 를 통해 kimdubi_db 를 kimdubi_db1 으로 치환하고
치환 된 kimdubi_db1 을 복제하게 됨

  • replication status 확인
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.6
                  Master_User: replUser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000011
          Read_Master_Log_Pos: 992
               Relay_Log_File: relay-bin-master_1.000003
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin.000011
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

              Replicate_Do_DB: kimdubi_db1
 
          Exec_Master_Log_Pos: 992
              Relay_Log_Space: 903
              Until_Condition: None

             Master_Server_Id: 64
                  Master_UUID: ba315763-e7e8-11e9-9c29-0242ac110002
             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
  
         Replicate_Rewrite_DB: (kimdubi_db,kimdubi_db1)
                 Channel_Name: master_1

*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.7
                  Master_User: replUser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000010
          Read_Master_Log_Pos: 1302
               Relay_Log_File: relay-bin-master_2.000004
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: kimdubi_db2
 
          Exec_Master_Log_Pos: 1302
              Relay_Log_Space: 903

             Master_Server_Id: 2
                  Master_UUID: ba315763-e7e8-11e9-9c29-0242ac110002
             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

         Replicate_Rewrite_DB: (kimdubi_db,kimdubi_db2)
                 Channel_Name: master_2
 
2 rows in set (0.00 sec)
  • replication 정상 확인
### MASTER_1 에서 수행
mysql> use kimdubi_db;
Database changed

mysql> create table test (t int);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into test values(1);
Query OK, 1 row affected (0.05 sec)


### MASTER_2 에서 수행
mysql> use kimdubi_db;
Database changed
mysql> create table test (t int);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into test values(2);
Query OK, 1 row affected (0.03 sec)


### SLAVE에서 데이터 확인
mysql> select * from kimdubi_db1.test;
+------+
| t    |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql> select * from kimdubi_db2.test;
+------+
| t    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)