Print
카테고리: [ MariaDB ]
조회수: 33768

multi source replication 이란?

하나의 slave db서버가 여러개의 master와 연결하여 replication 을 구성하는 구조
mysql 5.7.x 버전대 부터 사용가능
백업 용도, 로그 통합 DB, 배치작업 등을 위해 데이터를 한곳에 모을 필요가 있을 때 매우 유용한 기능

slave는 여러 개의 master 를 channel name 으로 구분함

구성방법

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

=> MSR 설정을 위해서는 repository 를 TABLE 에 저장해야함
master_info_repository는 slave server 가 master status와 connection inforamtion 를 어디에 저장하겠냐는 파라미터로
TABLE 로 설정 시 mysql.slave_master_info 에서 조회 가능

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

=> M/S 모든 서버들은 서로 server id 가 달라야함

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

mysql> CREATE USER 'replUser'@'172.17.0.5' IDENTIFIED BY '!dlatl00';
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replUser'@'172.17.0.5';
Query OK, 0 rows affected (0.02 sec)
mysql>  CHANGE MASTER TO
    ->  MASTER_HOST='172.17.0.6',
    ->  MASTER_USER='replUser',
    ->  MASTER_PASSWORD='!dlatl00',
    ->  MASTER_LOG_FILE='mysql-bin.000010',
    ->  MASTER_LOG_POS=716
    ->  FOR CHANNEL 'master_1';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql>  CHANGE MASTER TO
    ->  MASTER_HOST='172.17.0.7',
    ->  MASTER_USER='replUser',
    ->  MASTER_PASSWORD='!dlatl00',
    ->  MASTER_LOG_FILE='mysql-bin.000010',
    ->  MASTER_LOG_POS=714
    ->  FOR CHANNEL 'master_2';
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql>
mysql>  CHANGE MASTER TO
    ->  MASTER_HOST='172.17.0.8',
    ->  MASTER_USER='replUser',
    ->  MASTER_PASSWORD='!dlatl00',
    ->  MASTER_LOG_FILE='mysql-bin.000011',
    ->  MASTER_LOG_POS=388
    ->  FOR CHANNEL 'master_3';
Query OK, 0 rows affected, 2 warnings (0.04 sec)



START SLAVE FOR CHANNEL 'master_1';
START SLAVE FOR CHANNEL 'master_2';
START SLAVE FOR CHANNEL 'master_3';


mysql> show slave status for channel 'master_1'\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.000010
          Read_Master_Log_Pos: 716
               Relay_Log_File: relay-bin-master_1.000003
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin.000010
             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: 716
              Relay_Log_Space: 533
              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: 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:
                 Channel_Name: master_1
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)
[root@da25e57166b5 data]# ls -ltr relay-bin*
-rw-r----- 1 root root 155 Nov  3 09:52 relay-bin-master_3.000002
-rw-r----- 1 root root 211 Nov  3 09:52 relay-bin-master_1.000002
-rw-r----- 1 root root  56 Nov  3 09:52 relay-bin-master_1.index
-rw-r----- 1 root root 211 Nov  3 09:52 relay-bin-master_2.000002
-rw-r----- 1 root root  28 Nov  3 09:52 relay-bin-master_3.index
-rw-r----- 1 root root  56 Nov  3 09:52 relay-bin-master_2.index
-rw-r----- 1 root root 510 Nov  3 09:58 relay-bin-master_1.000003
-rw-r----- 1 root root 510 Nov  3 09:58 relay-bin-master_2.000003

=> master 마다 각각 다른 relay-log file 떨굼

모니터링


mysql> select * From performance_schema.replication_connection_status\G:
*************************** 1. row ***************************
                                      CHANNEL_NAME: master_1
                                        GROUP_NAME:
                                       SOURCE_UUID: ba315763-e7e8-11e9-9c29-0242ac110002
                                         THREAD_ID: 37
                                     SERVICE_STATE: ON
                         COUNT_RECEIVED_HEARTBEATS: 17
                          LAST_HEARTBEAT_TIMESTAMP: 2019-11-03 10:01:00.967195
                          RECEIVED_TRANSACTION_SET:
                                 LAST_ERROR_NUMBER: 0
                                LAST_ERROR_MESSAGE:
                              LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                           LAST_QUEUED_TRANSACTION: ANONYMOUS
 LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2019-11-03 09:58:00.954012
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2019-11-03 09:58:00.954012
     LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2019-11-03 09:58:00.955845
       LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2019-11-03 09:58:00.955890
                              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: ba315763-e7e8-11e9-9c29-0242ac110002
                                         THREAD_ID: 39
                                     SERVICE_STATE: ON
                         COUNT_RECEIVED_HEARTBEATS: 17
                          LAST_HEARTBEAT_TIMESTAMP: 2019-11-03 10:00:38.758086
                          RECEIVED_TRANSACTION_SET:
                                 LAST_ERROR_NUMBER: 0
                                LAST_ERROR_MESSAGE:
                              LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                           LAST_QUEUED_TRANSACTION: ANONYMOUS
 LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2019-11-03 09:58:08.748663
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2019-11-03 09:58:08.748663
     LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2019-11-03 09:58:08.750592
       LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2019-11-03 09:58:08.750634
                              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 ***************************
               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: 902
               Relay_Log_File: relay-bin-master_2.000003
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1007
                   Last_Error: Error 'Can't create database 'test1'; database exists' on query. Default database: 'test1'. Query: 'create database test1'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 714
              Relay_Log_Space: 721
              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: 1007
               Last_SQL_Error: Error 'Can't create database 'test1'; database exists' on query. Default database: 'test1'. Query: 'create database test1'
  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:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 191103 09:58:08
               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:

=> master_2 replication 에러 발생

mysql> stop slave for channel 'master_2';
Query OK, 0 rows affected (0.01 sec)

mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.01 sec)

*************************** 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: 902
               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:
          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: 902
              Relay_Log_Space: 888
              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:
                 Channel_Name: master_2
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace: