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)