5.7과 8.0 multi-source replication 차이점
mysql 5.7 버전부터 multi-source replication 기능이 생기면서 유용하게 사용되고 있지만
master가 두대 이상일 때, 같은 이름의 db를 구분 지어 다른 db명으로 replication 해오는 기능은 지원되지 않았습니다.
replication filter 가 channel 별로 적용 되는 게 아닌 , global 로 적용되어
replication_rewrite_db filter를 마음대로 사용할 수 없었기 때문인데
mysql 8 버전대에서 이 부분이 아래와 같이 가능해졌습니다.
5.7
Master 1: kimdubi_db => Slave : kimdubi_db
Master 2: kimdubi_db => Slave : kimdubi_db=> replication_rewrite_db filter 가 global 로 적용되기 때문에 위와 같이 master host 가 달라도
data 충돌의 위험이 있음
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 과 같은 충돌을 피할 수 있게되었음
- 상태 확인
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
Replicate_Ignore_DB:
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: 992
Relay_Log_Space: 903
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: 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
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB: (kimdubi_db,kimdubi_db1)
Channel_Name: master_1
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
*************************** 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
Replicate_Ignore_DB:
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: 1302
Relay_Log_Space: 903
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: 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
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB: (kimdubi_db,kimdubi_db2)
Channel_Name: master_2
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
2 rows in set (0.00 sec)- replication 정상 확인
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_1
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)=> master_2
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)
