새소식

인기 검색어

MySQL & Maria

[MySQL] MSR ( Multi Source Replication )

  • -
반응형

■ Multi Source Replication이란?

MySQL MSR은 기본 Replication구조에서 확장된 개념으로 Binary Log를 기본으로 하여 각각의 마스터를 CHANNEL로 연결하여 Replicate 하는 구조입니다. 각각의 CHANNEL을 통해 Master / Slave 간 연결을 하고, 이 CHANNEL은 Replication 연결에 사용되는 Thread(IO, SQL, Worker 및 Coordinator 등)들을 관리합니다.

 

■ 사용 용도?

예를 들어서 어떤 시스템이 공통DB, 메인DB, 로그DB로 구성되어 있고 로그DB는 여러개로 샤딩(Sharding)되어 물리적으로 다른 장비에서 서비스를 하고있다고 생각해 볼 수 있습니다.
그런데 어떤 지표를 보기 위해서 모든 DB에서 한방에 쿼리를 해서 join 을 걸어야 한다면, 예전에는 batch 작업으로 특정시점에 데이터를 모두 백업해서, 커다란 장비에 복원해야 했습니다. 엄청 오래걸리고 까다롭습니다.
MySQL의 복원은 특히 오래 걸립니다. 그런데, MSR이 사용된다면? 그냥 큰 디스크가 존재하는 장비하나에 모든 DB의 Slave 로 Channel로 구성 할 수 있습니다. 예전처럼 여러 벤더사의 Third Party Tool을 사용하지 않고, MySQL의 자체 기능을 사용하여 관리하기 용이한 시스템을 구축 할 수 있습니다.

 

■ 구성 방법

1) 서버 정보

Hostname IP DB Version
mysql1 10.0.3.245  MySQL 8.0
mysql2 10.0.3.175 MySQL 8.0
slave 10.0.3.239  MySQL 8.0

 

2) MySQL Repository Variables 확인 및 변경

- MSR 설정을 위해서는 Repository를 FILE이 아닌 TABLE에 저장해야 합니다.

mysql> show variables like '%repository%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| master_info_repository    | TABLE |
| relay_log_info_repository | TABLE |
+---------------------------+-------+
2 rows in set (0.00 sec)

 

3) 서버 ID 변경

- 변경후 /etc/my.cnf에도 추가해야 됨

- Replication을 맺기 위해서는 각 DB의 Server_id가 달라야 한다.

1.Master1
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+

2.Master2
mysql> set global server_id=2;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.00 sec)

3.Slave
mysql> set global server_id=3;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 3     |
+---------------+-------+
1 row in set (0.00 sec)

 

4) Slave DB my.cnf 수정

- 사용자 DB 이외의 SYSTEM DB는 Master Node들의 정보가 섞이면 안 되기 때문에 ignore 설정한다 

server_id=3
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = sys

 

5) BINARY & RELAY LOG 환경변수 확인

- Binary와 Relay Log가 켜져 있는지 확인해보았다. ( Replication을 맺기 위해서 )

mysql> show variables like '%log_bin%';
+---------------------------------+-------------------------+
| Variable_name                   | Value                   |
+---------------------------------+-------------------------+
| log_bin                         | ON                      |
| log_bin_basename                | /data/data/binlog       |
| log_bin_index                   | /data/data/binlog.index |
| log_bin_trust_function_creators | OFF                     |
| log_bin_use_v1_row_events       | OFF                     |
| sql_log_bin                     | ON                      |
+---------------------------------+-------------------------+


mysql> show variables like '%relay%';
+---------------------------+----------------------------------+
| Variable_name             | Value                            |
+---------------------------+----------------------------------+
| max_relay_log_size        | 0                                |
| relay_log                 | slave-relay-bin                  |
| relay_log_basename        | /data/data/slave-relay-bin       |
| relay_log_index           | /data/data/slave-relay-bin.index |
| relay_log_info_file       | relay-log.info                   |
| relay_log_info_repository | TABLE                            |
| relay_log_purge           | ON                               |
| relay_log_recovery        | OFF                              |
| relay_log_space_limit     | 0                                |
| sync_relay_log            | 10000                            |
| sync_relay_log_info       | 10000                            |
+---------------------------+----------------------------------+

 

6) Replication User 추가 ( 모든 서버 )

- native_password로 안 해주면 Error 발생 ( message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. 

mysql>  create user 'repl'@'10.0.3.%' identified WITH mysql_native_password by 'repl';

mysql>  grant all on *.* to 'repl'@'10.0.3.%';

7) replication 설정

- Replication 구성시 'for channel' 명령 구분을 이용하여 Channel 명을 적어준다.

- Slave 시작 시 Start Slave / Start Slave for channel 'master_1' 이렇게 2개를 이용하여 전체 Slave를 시작하거나 특정 Channel의 Slave를 시작할 수 있다.

1.Master1번
mysql> show master status\G
*************************** 1. row ***************************
             File: binlog.000001
         Position: 1889
     Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

2.Master2번
mysql> show master status\G
*************************** 1. row ***************************
             File: binlog.000001
         Position: 1889
     Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

3.SLAVE 서버
mysql> CHANGE MASTER TO MASTER_HOST='10.0.3.245', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=1889 FOR CHANNEL 'master_1';

mysql> CHANGE MASTER TO MASTER_HOST='10.0.3.175', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=1889 FOR CHANNEL 'master_2';

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

 

8) Slave 상태 조회

- 전체 Slave를 조회하거나 특정 Channel의 Slave 조회 가능

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.3.245
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 2382
               Relay_Log_File: slave-relay-bin-master_1.000006
                Relay_Log_Pos: 321
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          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: 2382
              Relay_Log_Space: 704
              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: 041a94de-4c26-11ec-9620-020017027810
             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:
                 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: 10.0.3.175
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 2434
               Relay_Log_File: slave-relay-bin-master_2.000009
                Relay_Log_Pos: 321
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          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: 2434
              Relay_Log_Space: 704
              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: 0a822572-4c26-11ec-80fe-0200170013f1
             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:
                 Channel_Name: master_2
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
2 rows in set, 1 warning (0.01 sec)



mysql> show slave status for channel 'master_1'\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.3.245
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 1889
               Relay_Log_File: slave-relay-bin-master_1.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_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_Master_Log_Pos: 1889
              Relay_Log_Space: 539
              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: 041a94de-4c26-11ec-9620-020017027810
             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:
                 Channel_Name: master_1
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

 

9) 모니터링

mysql>  select * From performance_schema.replication_connection_status\G;
*************************** 1. row ***************************
                                      CHANNEL_NAME: master_1
                                        GROUP_NAME:
                                       SOURCE_UUID: 041a94de-4c26-11ec-9620-020017027810
                                         THREAD_ID: 61
                                     SERVICE_STATE: ON
                         COUNT_RECEIVED_HEARTBEATS: 8
                          LAST_HEARTBEAT_TIMESTAMP: 2021-11-23 06:57:17.322559
                          RECEIVED_TRANSACTION_SET:
                                 LAST_ERROR_NUMBER: 0
                                LAST_ERROR_MESSAGE:
                              LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                           LAST_QUEUED_TRANSACTION:
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
     LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
       LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
                              QUEUEING_TRANSACTION:
    QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
   QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
        QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
*************************** 2. row ***************************
                                      CHANNEL_NAME: master_2
                                        GROUP_NAME:
                                       SOURCE_UUID: 0a822572-4c26-11ec-80fe-0200170013f1
                                         THREAD_ID: 63
                                     SERVICE_STATE: ON
                         COUNT_RECEIVED_HEARTBEATS: 8
                          LAST_HEARTBEAT_TIMESTAMP: 2021-11-23 06:57:17.326453
                          RECEIVED_TRANSACTION_SET:
                                 LAST_ERROR_NUMBER: 0
                                LAST_ERROR_MESSAGE:
                              LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                           LAST_QUEUED_TRANSACTION:
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
     LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
       LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
                              QUEUEING_TRANSACTION:
    QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
   QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
        QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
2 rows in set (0.00 sec)

 

* 써보고 느낀 점은 Sharding 되어있는 Server에 Join이나 배치성 프로그램이 돌 때 보다 이렇게 하나로 모아서 해당 MSR 서버에서 돌리는 것이 좋아 보인다. 하지만 여러 개의 Master 서버가 있을 시 여러 서버에서 정보 들어오다 보니  Seconds_Behind_Master가 일반 Replication에 비해 많이 밀리는 것이 보였다.

 

 

■ 참고 문서

https://saramin.github.io/2020-11-09-MSR/

 

MySQL MSR(Multi Source Replication) 도입

MySQL Multi Source Replication 도입 사례

saramin.github.io

https://dev.mysql.com/doc/refman/5.7/en/replication-multi-source.html

 

MySQL :: MySQL 5.7 Reference Manual :: 16.1.5 MySQL Multi-Source Replication

16.1.5 MySQL Multi-Source Replication MySQL multi-source replication enables a replica to receive transactions from multiple immediate sources in parallel. In a multi-source replication topology, a replica creates a replication channel for each source tha

dev.mysql.com

 

반응형
저작자표시

'MySQL & Maria' 카테고리의 다른 글

[Maria] Galera Cluster 성능 튜닝  (0) 2021.12.06
[ MARIA ] Galera Cluster 구성  (0) 2021.12.03
[MySQL] MySQL_8.0 Binary Install  (0) 2021.11.10
[MySQL / Maria] Innodb_flush_log_at_trx_commit  (0) 2021.11.10
[Maria] max_binlog_size 이슈  (0) 2021.10.26
Contents

당신이 좋아할만한 콘텐츠

댓글 0

포스팅 주소를 복사했습니다

이 글이 도움이 되었다면 공감 부탁드립니다.

SHARE

DARK

MENU

티스토리툴바