■ MSR (Multi Source Replication)이란?
간단하게 설명해서 여러개의 마스터 서버 발생하는 데이터를 하나의 슬레이브 서버에서 받아내는 방식입니다.
그림 1. MSR 구성도
이 방식을 이용하면 여러 마스터 서버의 특정 DB만을 슬레이브에서 받아서 한번에 조회가 가능하다는 장점이 있습니다. 예를 들어 MASTER 1번의 특정 DB, MASTER 2번의 특정 DB, MASTER 3은 전체 DB를 복제하여 이 DB들을 한곳에 모아서 조회를 할수가 있습니다. 이것을 응용한다면 아래와 같은 그림으로도 구성할 수 있습니다.
그림 2. MSR 다중 구성도
여러개의 마스터-슬레이브 복제 클러스터들이 있을때 이중에서 특정 마스터의 특정 DB들만을 한곳에 모을 수 있습니다. 또한 이 데이터를 통계데이터로 이용하거나 다른곳에 데이터를 제공하는 소스로 이용할 수도 있습니다. 또한 전체 백업, 로그 통합, 배치작업, DW등등의 용도로 무궁무진하게 응용해서 사용할 수 있는것이 바로 MSR입니다. 또한 배치성 프로그램들이 있다면 이것들을 하나로 모아서 MSR에 보내면 한곳에서 조회가 가능하다는 장점이 있습니다.
그러나 이 시스템도 단점이 좀 있습니다. 여러곳의 마스터 데이터를 받다보니 복제가 밀릴수도 있는 점입니다. 부하가 많은 서버들의 데이터를 받아낸다면 많이 밀릴수도 있습니다. 그래서 MSR서버는 기존 마스터, 슬레이브 서버보다 스펙이 좋아야 하며 실시간 데이터 조회용으로는 적합하지 않다는 점입니다. 주로 통계데이터 용도로 이용하면 좋을것 같습니다.
■ 서버 환경 설정
아래 설정방법은 신규로 MSR 서버를 구축한다는 가정하에 설정하는 방법입니다.
▶ 테스트 복제 환경
| 서버명 | IP | ROLE |
| M1 | 192.168.0.11 | Master 1번 |
| M2 | 192.168.0.12 | Master 2번 |
| M3 | 192.168.0.13 | Master 3번 |
| SLAVE | 192.168.0.14 | 슬레이버 서버 |
▶ 파라미터 설정방법
슬레이브 서버에 먼저 다음과 같은 파라미터를 설정합니다. MySQL 5.7과 8.0 2가지 버전으로 설명합니다. MSR복제에서는 채널이라는 옵션을 가지게 되는데 이때 MySQL 5.7은 채널의 옵션이 복제설정, 중지, 시작시에만 채널이 개념을 가지지만 MySQL 8.0에서는 5.7의 옵션외에도 복제 단위에 대해 채널의 옵션을 가지게 됩니다. 아래 환경설정을 보시게 되면 좀더 자세히 알수 있습니다.
• 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
replicate-do-db = mdb_1
replicate-do-db = mdb_2
replicate-do-db = mdb_3
replicate-ignore-db = mysql, information_schema, performance_schema, sys,
-- replicate-do-db, replicate-ignore-db 파라미터 설정은 채널별이 아닌 서버 단위 설정임. 어차피 GTID로 식별되어 오기 때문에 서버 혹은 DB끼리 중복될 이유는 없다.
• 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
replicate-do-db = chnl_master_1:mdb_1
replicate-do-db = chnl_master_2:mdb_2
replicate-do-db = chnl_master_3:mdb_3
-- 채널별로 복제할 DB, 무시할 DB를 선책할 수 있음.
-- 채널 옵션 없는 replicate-ignore-db는 전체 대상으로 무시하는 방법.
-- 채널별로 복제할 DB를 선택하는 예.
replicate-do-db = chnl_master_1:m1db1
replicate-do-db = chnl_master_1:m1db2
replicate-do-db = chnl_master_2:m2db1
replicate-do-db = chnl_master_2:m2db2
-- 채널별로 복제를 무시하는 옵션을 선택할 시
replicate-do-db = chnl_master_1:m1db1
replicate-do-db = chnl_master_1:m1db1
replicate-do-db = chnl_master_2:m2db1
replicate-do-db = chnl_master_2:m2db1
• 계정 생성
MSR 환경에 참여할 모든 마스터 서버에서 실행합니다.
create user 'repl'@'%' identified by 'admin1234';
grant replication slave,replication client on *.* to 'repl'@'%';
flush privileges;
■ MSR 설정
▶ MySQL 5.7 설정 방법
• 서버 덤프 백업
- 마스터 서버 1번
shell> mysqldump -uroot -p'admin1234' -v --databases mdb_1 \
--single-transaction --routines --set-gtid-purged=ON \
--triggers --extended-insert --master-data=2 > mdb_1.sql
- 마스터 서버 2번
shell> mysqldump -uroot -p'admin1234' -v --databases mdb_2 \
--single-transaction --routines --set-gtid-purged=ON \
--triggers --extended-insert --master-data=2 > mdb_2.sql
- 마스터 서버 3번
shell> mysqldump -uroot -p'admin1234' -v --databases mdb_3 \
--single-transaction --routines --set-gtid-purged=ON \
--triggers --extended-insert --master-data=2 > mdb_3.sql
• 덤프내의 gtid 출력 및 메모
위에서 받은 덤프 파일에서 SET GTID로 시작하는 내용을 찾아서 메모합니다. GTID위치는 플랫폼마다 틀릴수도 있는데 리눅스 같은 경우는 맨 아래 있지만 MAC에서 받으면 맨위에 있기도 합니다. 만약 덤프 파일이 크다면 맨위나 맨아래를 뒤지면 아래와 비슷한 문구를 발견할 수 있습니다. head 명령이나 tail 명령을 이용하여 확인해 봅니다.
shell> cat mdb_1.sql | grep -A 10 -i GTID;
...
SET @@GLOBAL.GTID_PURGED='79def70a-facf-11ec-bbf5-000c2912a3fd:1-13';
...
shell> cat mdb_2.sql | grep -A 10 -i GTID;
...
SET @@GLOBAL.GTID_PURGED='8e0628c6-facf-11ec-bb2b-0050563d3a8e:1-13';
...
shell> cat mdb_3.sql | grep -A 10 -i GTID;
...
SET @@GLOBAL.GTID_PURGED='92d6eee1-facf-11ec-bc00-0050563f25db:1-13';
...
• 데이터 임포트
위의 덤프파일을 슬레이브 서버에 모두 IMPORT시킵니다. 임포트할때 GTID 관련 에러때문에 멈출수도 있습니다. 이걸 예방하기 위해 -f 옵션으로 GTID에러가 나와도 밀어넣는 옵션으로 밀어 넣습니다. 만약 덤프파일의 크기가 작거나 몇기가 바이트이고 덤프파일을 편집기(보통 VI)로 열어도 될만큼 서버에 메모리 여유가 있다면 편집기로 편집하여 위의 GLOBAL.GTID_PURGED 항목을 삭제하고 저장한 후 임포트합니다.
shell> mysql -uroot -p'admin1234' -f < mdb_1.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
shell> mysql -uroot -p'admin1234' -f < mdb_2.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1840 (HY000) at line 65: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
shell> mysql -uroot -p'admin1234' -f < mdb_3.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1840 (HY000) at line 65: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
• gtid 설정
반드시 GTID 정보를 날리고 작업합니다. 기존 GTID정보가 남아있다면 복제시 반드시 실패합니다. 꼭 기존 정보를 삭제하고 진행해야 올바로 MSR복제가 수행됩니다.
mysql> reset master;
mysql> reset slave all;
mysql> select @@global.gtid_executed, @@global.gtid_purged, @@gtid_executed, @@gtid_purged;
+------------------------+----------------------+-----------------+---------------+
| @@global.gtid_executed | @@global.gtid_purged | @@gtid_executed | @@gtid_purged |
+------------------------+----------------------+-----------------+---------------+
| | | | |
+------------------------+----------------------+-----------------+---------------+
GTID를 설정합니다 .위에서 메모해 두었던 GTID를 아래 형식처럼 쭈욱 나열합니다. 위의 마스터가 3대였으므로 3대의 GTID정보를 차례대로 입력합니다. 만약 GTID정보가 한 서버에 2-3개의 열로 있다면 그 3개의 열대로 차례대로 입력해 주면 됩니다.
mysql> SET @@GLOBAL.gtid_purged = "79def70a-facf-11ec-bbf5-000c2912a3fd:1-13, 8e0628c6-facf-11ec-bb2b-0050563d3a8e:1-13, 92d6eee1-facf-11ec-bc00-0050563f25db:1-13";
• Replication 설정
복제를 설정하는 방법입니다. 채널이라는 개념을 이용하여 복제하기 때문에 채널별로 설정 및 확인을 하면서 설정을 진행합니다.
- 채널 1번 복제 설정
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.11',
MASTER_USER='repl',MASTER_PASSWORD='admin1234',
MASTER_AUTO_POSITION=1 FOR CHANNEL 'chnl_master_1';
- 채널 1번 복제 설정 상태 확인.
mysql> SHOW SLAVE STATUS FOR CHANNEL "chnl_master_1"\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.11
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: relay_log-chnl_master_1.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB: mdb_1,mdb_2,mdb_3
Replicate_Ignore_DB: 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: 0
Relay_Log_Space: 154
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: NULL
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: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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: 79def70a-facf-11ec-bbf5-000c2912a3fd:1-13,
8e0628c6-facf-11ec-bb2b-0050563d3a8e:1-13,
92d6eee1-facf-11ec-bc00-0050563f25db:1-13
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: chnl_master_1
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
- 채널 1번 복제 수행
mysql> START SLAVE FOR CHANNEL "chnl_master_1";
- 복제 상태 확인
아래 내용중 Slave_IO_Running: Yes, Slave_SQL_Running: Yes 인지 확인하고 Last_IO_Error, Last_SQL_Error를 확인하여 이상이 없음을 확인합니다.
mysql> SHOW SLAVE STATUS FOR CHANNEL "chnl_master_1"\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.11
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binary_log.000005
Read_Master_Log_Pos: 4203
Relay_Log_File: relay_log-chnl_master_1.000002
Relay_Log_Pos: 1039
Relay_Master_Log_File: binary_log.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mdb_1,mdb_2,mdb_3
Replicate_Ignore_DB: 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: 4203
Relay_Log_Space: 1254
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: 79def70a-facf-11ec-bbf5-000c2912a3fd
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: 79def70a-facf-11ec-bbf5-000c2912a3fd:14-16
Executed_Gtid_Set: 79def70a-facf-11ec-bbf5-000c2912a3fd:1-16,
8e0628c6-facf-11ec-bb2b-0050563d3a8e:1-16,
92d6eee1-facf-11ec-bc00-0050563f25db:1-16
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: chnl_master_1
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
- 복제 채널 2,3번 실행 및 확인
위와 같은 방법으로 복제 채널 2번, 3번을 설정하면서 각 단계마다 이상이 없는지 확인합니다.
> 채널 2번 복제 설정
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.12',
MASTER_USER='repl',MASTER_PASSWORD='admin1234',
MASTER_AUTO_POSITION=1 FOR CHANNEL 'chnl_master_2';
mysql> SHOW SLAVE STATUS FOR CHANNEL "chnl_master_2"\G;
mysql> START SLAVE FOR CHANNEL "chnl_master_2";
mysql> SHOW SLAVE STATUS FOR CHANNEL "chnl_master_2"\G;
> 채널 3번 복제 설정
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.13',
MASTER_USER='repl',MASTER_PASSWORD='admin1234',
MASTER_AUTO_POSITION=1 FOR CHANNEL 'chnl_master_3';
mysql> SHOW SLAVE STATUS FOR CHANNEL "chnl_master_3"\G;
mysql> START SLAVE FOR CHANNEL "chnl_master_3";
mysql> SHOW SLAVE STATUS FOR CHANNEL "chnl_master_3"\G;
- 전체 채널 확인.
mysql> show slave status\G;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.31
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binary_log.000005
Read_Master_Log_Pos: 4203
Relay_Log_File: relay_log-chnl_master_1.000002
Relay_Log_Pos: 1039
Relay_Master_Log_File: binary_log.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mdb_1,mdb_2,mdb_3
Replicate_Ignore_DB: 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: 4203
Relay_Log_Space: 1254
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: 79def70a-facf-11ec-bbf5-000c2912a3fd
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: 79def70a-facf-11ec-bbf5-000c2912a3fd:14-16
Executed_Gtid_Set: 79def70a-facf-11ec-bbf5-000c2912a3fd:1-16,
8e0628c6-facf-11ec-bb2b-0050563d3a8e:1-16,
92d6eee1-facf-11ec-bc00-0050563f25db:1-16
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: chnl_master_1
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.32
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binary_log.000005
Read_Master_Log_Pos: 4203
Relay_Log_File: relay_log-chnl_master_2.000002
Relay_Log_Pos: 1039
Relay_Master_Log_File: binary_log.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mdb_1,mdb_2,mdb_3
Replicate_Ignore_DB: 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: 4203
Relay_Log_Space: 1254
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: 8e0628c6-facf-11ec-bb2b-0050563d3a8e
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: 8e0628c6-facf-11ec-bb2b-0050563d3a8e:14-16
Executed_Gtid_Set: 79def70a-facf-11ec-bbf5-000c2912a3fd:1-16,
8e0628c6-facf-11ec-bb2b-0050563d3a8e:1-16,
92d6eee1-facf-11ec-bc00-0050563f25db:1-16
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: chnl_master_2
Master_TLS_Version:
*************************** 3. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.33
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binary_log.000004
Read_Master_Log_Pos: 4203
Relay_Log_File: relay_log-chnl_master_3.000002
Relay_Log_Pos: 1039
Relay_Master_Log_File: binary_log.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mdb_1,mdb_2,mdb_3
Replicate_Ignore_DB: 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: 4203
Relay_Log_Space: 1254
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: 3
Master_UUID: 92d6eee1-facf-11ec-bc00-0050563f25db
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: 92d6eee1-facf-11ec-bc00-0050563f25db:14-16
Executed_Gtid_Set: 79def70a-facf-11ec-bbf5-000c2912a3fd:1-16,
8e0628c6-facf-11ec-bb2b-0050563d3a8e:1-16,
92d6eee1-facf-11ec-bc00-0050563f25db:1-16
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: chnl_master_3
Master_TLS_Version:
3 rows in set (0.00 sec)
ERROR:
No query specified
▶ MySQL 8.0 설정 방법
• 서버 덤프 백업
- 마스터 서버 1번
shell> mysqldump -uroot -p'admin1234' -v --databases mdb_1 \
--single-transaction --routines --set-gtid-purged=ON \
--triggers --extended-insert --source-data=2 > mdb_1.sql
- 마스터 서버 2번
shell> mysqldump -uroot -p'admin1234' -v --databases mdb_2 \
--single-transaction --routines --set-gtid-purged=ON \
--triggers --extended-insert --source-data=2 > mdb_2.sql
- 마스터 서버 3번
shell> mysqldump -uroot -p'admin1234' -v --databases mdb_3 \
--single-transaction --routines --set-gtid-purged=ON \
--triggers --extended-insert --source-data=2 > mdb_3.sql
• 덤프내의 gtid 출력 및 메모
위에서 받은 덤프 파일에서 SET GTID로 시작하는 내용을 찾아서 메모합니다. GTID위치는 플랫폼마다 틀릴수도 있는데 리눅스 같은 경우는 맨 아래 있지만 MAC에서 받으면 맨위에 있기도 합니다. 만약 덤프 파일이 크다면 맨위나 맨아래를 뒤지면 아래와 비슷한 문구를 발견할 수 있습니다. head 명령이나 tail 명령을 이용하여 확인해 봅니다.
메모할 데이터는 uuid로 시작하는 e7b4fe5d-fec0-11ec-ad13-0050563ff209:1-12 형식의 내용만 메모합니다.
shell> cat mdb_1.sql | grep -A 10 -i GTID;
...
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'e7b4fe5d-fec0-11ec-ad13-0050563ff209:1-12';
...
shell> cat mdb_2.sql | grep -A 10 -i GTID;
...
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'ffc71a1b-fec0-11ec-aaba-005056304afc:1-12';
...
shell> cat mdb_3.sql | grep -A 10 -i GTID;
...
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '407944bf-fec1-11ec-900d-0050562fd4cf:1-12';
...
• 데이터 임포트
위의 덤프파일을 슬레이브 서버에 모두 IMPORT시킵니다. 임포트할때 GTID 관련 에러때문에 멈출수도 있습니다. 이걸 예방하기 위해 -f 옵션으로 GTID에러가 나와도 밀어넣는 옵션으로 밀어 넣습니다. 만약 덤프파일의 크기가 작거나 몇기가 바이트이고 덤프파일을 편집기(보통 VI)로 열어도 될만큼 서버에 메모리 여유가 있다면 편집기로 편집하여 위의 GLOBAL.GTID_PURGED 항목을 삭제하고 저장한 후 임포트합니다.
mysql> mysql -uroot -p'admin1234' -f < mdb_1.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> mysql -uroot -p'admin1234' -f < mdb_2.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1840 (HY000) at line 65: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
mysql> mysql -uroot -p'admin1234' -f < mdb_3.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1840 (HY000) at line 65: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
• gtid 설정
반드시 GTID 정보를 날리고 작업합니다. 기존 GTID정보가 남아있다면 복제시 반드시 실패합니다. 꼭 기존 정보를 삭제하고 진행해야 올바로 MSR복제가 수행됩니다.
mysql> reset master;
mysql> reset slave all;
mysql> select @@global.gtid_executed, @@global.gtid_purged, @@gtid_executed, @@gtid_purged;
+------------------------+----------------------+-----------------+---------------+
| @@global.gtid_executed | @@global.gtid_purged | @@gtid_executed | @@gtid_purged |
+------------------------+----------------------+-----------------+---------------+
| | | | |
+------------------------+----------------------+-----------------+---------------+
GTID를 설정합니다 .위에서 메모해 두었던 GTID를 아래 형식처럼 쭈욱 나열합니다. 위의 마스터가 3대였으므로 3대의 GTID정보를 차례대로 입력합니다. 만약 GTID정보가 한 서버에 2-3개의 열로 있다면 그 3개의 열대로 차례대로 입력해 주면 됩니다.
mysql> SET @@GLOBAL.gtid_purged = "79def70a-facf-11ec-bbf5-000c2912a3fd:1-13, 8e0628c6-facf-11ec-bb2b-0050563d3a8e:1-13, 92d6eee1-facf-11ec-bc00-0050563f25db:1-13";
• Replication 설정
복제를 설정하는 방법입니다. 채널이라는 개념을 이용하여 복제하기 때문에 채널별로 설정 및 확인을 하면서 설정을 진행합니다. MySQL 5.7에서의 명령과는 좀 상이하기 때문에 정확히 입력합니다.
- 채널 1번 복제 설정
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST="192.168.0.11",
SOURCE_USER="repl", SOURCE_PASSWORD="admin1234",
MASTER_AUTO_POSITION=1 FOR CHANNEL 'chnl_master_1';
- 채널 1번 복제 설정 상태 확인.
*************************** 1. row ***************************
Replica_IO_State: Connecting to source
Source_Host: 192.168.0.11
Source_User: repl
Source_Port: 3306
Connect_Retry: 60
Source_Log_File:
Read_Source_Log_Pos: 4
Relay_Log_File: relay_log-chnl_master_1.000001
Relay_Log_Pos: 4
Relay_Source_Log_File:
Replica_IO_Running: Connecting
Replica_SQL_Running: Yes
Replicate_Do_DB:
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_Source_Log_Pos: 0
Relay_Log_Space: 157
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2061
Last_IO_Error: error connecting to master 'repl@192.168.0.36:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 0
Source_UUID:
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp: 220708 10:47:57
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 407944bf-fec1-11ec-900d-0050562fd4cf:1-12,
e7b4fe5d-fec0-11ec-ad13-0050563ff209:1-12,
ffc71a1b-fec0-11ec-aaba-005056304afc:1-12
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: chnl_master_1
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
> 에러 내용
error connecting to master 'repl@192.168.0.36:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
MySQL 5.7과 비슷하게 복제 명령어를 실행하면 위와 같은 에러가 발생하게 됩니다, 위의 에러는 caching_sha2_password 관련하여 에러가 발생하게 되는데 이 에러를 방지하기 위해서는 옵션 하나를 추가해주어야 합니다. GET_SOURCE_PUBLIC_KEY=1라는 옵션을 이용하면 위의 에러가 사라지게 됩니다.
• 복제 재수행
- 복제 중지
mysql> STOP REPLICA FOR CHANNEL "chnl_master_1";
- 복제 설정
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST="192.168.0.11",
SOURCE_USER="repl", SOURCE_PASSWORD="admin1234",
GET_SOURCE_PUBLIC_KEY=1,
MASTER_AUTO_POSITION=1 FOR CHANNEL 'chnl_master_1';
- 복제 수행
mysql> START REPLICA FOR CHANNEL "chnl_master_1";
- 복제 상태 확인
mysql> show replica status for channel "chnl_master_1"\G;;
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.0.11
Source_User: repl
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binary_log.000001
Read_Source_Log_Pos: 3431
Relay_Log_File: relay_log-chnl_master_1.000002
Relay_Log_Pos: 423
Relay_Source_Log_File: binary_log.000001
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
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_Source_Log_Pos: 3431
Relay_Log_Space: 641
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1
Source_UUID: e7b4fe5d-fec0-11ec-ad13-0050563ff209
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 407944bf-fec1-11ec-900d-0050562fd4cf:1-12,
e7b4fe5d-fec0-11ec-ad13-0050563ff209:1-12,
ffc71a1b-fec0-11ec-aaba-005056304afc:1-12
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: chnl_master_1
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 1
Network_Namespace:
1 row in set (0.00 sec)
- 복제 채널 2,3번 실행 및 확인
위와 같은 방법으로 복제 채널 2번, 3번을 설정하면서 각 단계마다 이상이 없는지 확인합니다.
> 채널 2번 복제 설정
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST="192.168.0.12",
SOURCE_USER="repl", SOURCE_PASSWORD="admin1234",
GET_SOURCE_PUBLIC_KEY=1,
MASTER_AUTO_POSITION=1 FOR CHANNEL 'chnl_master_2';
mysql> SHOW REPLICA STATUS FOR CHANNEL "chnl_master_2"\G;
mysql> START REPLICA FOR CHANNEL "chnl_master_2";
mysql> SHOW REPLICA STATUS FOR CHANNEL "chnl_master_2"\G;
> 채널 3번 복제 설정
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST="192.168.0.13",
SOURCE_USER="repl", SOURCE_PASSWORD="admin1234",
GET_SOURCE_PUBLIC_KEY=1,
MASTER_AUTO_POSITION=1 FOR CHANNEL 'chnl_master_3';
mysql> SHOW REPLICA STATUS FOR CHANNEL "chnl_master_3"\G;
mysql> START REPLICA FOR CHANNEL "chnl_master_3";
mysql> SHOW REPLICA STATUS FOR CHANNEL "chnl_master_3"\G;
- 전체 채널 확인.
mysql> show slave status\G;
mysql> show replica status\G;
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.0.11
Source_User: repl
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binary_log.000001
Read_Source_Log_Pos: 3431
Relay_Log_File: relay_log-chnl_master_1.000002
Relay_Log_Pos: 423
Relay_Source_Log_File: binary_log.000001
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
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_Source_Log_Pos: 3431
Relay_Log_Space: 641
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1
Source_UUID: e7b4fe5d-fec0-11ec-ad13-0050563ff209
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 407944bf-fec1-11ec-900d-0050562fd4cf:1-12,
e7b4fe5d-fec0-11ec-ad13-0050563ff209:1-12,
ffc71a1b-fec0-11ec-aaba-005056304afc:1-12
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: chnl_master_1
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 1
Network_Namespace:
*************************** 2. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.0.12
Source_User: repl
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binary_log.000001
Read_Source_Log_Pos: 3431
Relay_Log_File: relay_log-chnl_master_2.000002
Relay_Log_Pos: 423
Relay_Source_Log_File: binary_log.000001
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
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_Source_Log_Pos: 3431
Relay_Log_Space: 641
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 2
Source_UUID: ffc71a1b-fec0-11ec-aaba-005056304afc
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 407944bf-fec1-11ec-900d-0050562fd4cf:1-12,
e7b4fe5d-fec0-11ec-ad13-0050563ff209:1-12,
ffc71a1b-fec0-11ec-aaba-005056304afc:1-12
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: chnl_master_2
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 1
Network_Namespace:
*************************** 3. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.0.13
Source_User: repl
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binary_log.000001
Read_Source_Log_Pos: 3431
Relay_Log_File: relay_log-chnl_master_3.000002
Relay_Log_Pos: 423
Relay_Source_Log_File: binary_log.000001
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
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_Source_Log_Pos: 3431
Relay_Log_Space: 641
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 3
Source_UUID: 407944bf-fec1-11ec-900d-0050562fd4cf
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 407944bf-fec1-11ec-900d-0050562fd4cf:1-12,
e7b4fe5d-fec0-11ec-ad13-0050563ff209:1-12,
ffc71a1b-fec0-11ec-aaba-005056304afc:1-12
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: chnl_master_3
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 1
Network_Namespace:
3 rows in set (0.00 sec)
ERROR:
No query specified
■ 버전별 명령어 비교 정리
| MySQL 5.7 | MySQL 8/0 | |
| 복제 명령어 채널별 설정 | CHANGE MASTER TO MASTER_HOST='192.168.0.11', MASTER_USER='repl',MASTER_PASSWORD='admin1234', MASTER_AUTO_POSITION=1 FOR CHANNEL 'chnl_master_1'; |
CHANGE REPLICATION SOURCE TO SOURCE_HOST="192.168.0.11", SOURCE_USER="repl", SOURCE_PASSWORD="admin1234", GET_SOURCE_PUBLIC_KEY=1, MASTER_AUTO_POSITION=1 FOR CHANNEL 'chnl_master_1'; |
| 복제 명령어 채널별 시작 | START SLAVE FOR CHANNEL "채널이름"; | START REPLICA FOR CHANNEL "채널이름"; |
| 복제명령어 채널별 중지 | STOP SLAVE FOR CHANNEL "채널이름"; | STOP REPLICA FOR CHANNEL "채널이름"; |
| 채널별 상태 확인 | SHOW SLAVE STATUS FOR CHANNEL "ch_master1"\G; | SHOW REPLICA STATUS FOR CHANNEL "ch_master1"\G; |
| 슬레이브의 모든 정보 날리기 | RESET SLAVE ALL FOR CHANNEL "ch_master1"; | RESET REPLICA ALL FOR CHANNEL "ch_master1"; |
■ 모니터링 방법
Performance Schema를 이용해서 복제 상태를 조회해볼 수 있습니다.
replication_connection_status 테이블을 이용하면 모니터링이 가능합니다.
채널 별로 조회가 가능하며 전체 또는 채널별로 조회가 가능합니다.
▶ 채널별 전체 조회
mysql> SELECT *
FROM performance_schema.replication_connection_status\G;
▶ 특정 채널별 조회
mysql> SELECT *
FROM performance_schema.replication_connection_status
WHERE CHANNEL_NAME='chnl_master_1'\G
■ 참고 URL
https://hoing.io/archives/18540
https://dev.mysql.com/doc/refman/5.7/en/replication-multi-source.html
※ 도움이 되셨다면 광고클릭 한번 부탁드립니다. ※
'Databases > MySQL' 카테고리의 다른 글
| [MySQL] [MHA] 01. MHA 이론 (0) | 2022.10.02 |
|---|---|
| [MySQL][Replication] Semi Sync 기능과 설정방법 (0) | 2022.08.07 |
| [MySQL][Admin] timestamp 컬럼 속성과 timzone과의 관계 (0) | 2022.06.28 |
| [MySQL][Master-Slave] GTID 복제 에러 건너뛰기 (0) | 2022.06.12 |
| [MySQL][Admin] InndoDB - Undo Log (0) | 2022.05.13 |