[MySQL - MSR(Multi Source Replication)구성]

안녕하세요 이번글에서는 MSR(Multi Source Replication)을 구성해보았습니다. 보통 log성으로 많이 사용한답니다. 여러 db의 log를 분석하기 힘드니 한 slave에 모아 한번에 분석하는 느낌(?)이랍니다.

 

 

 

MSR 이란

기본 Replication구조에서 확장된 개념으로 Binary Log를 기본으로 하여 각각의 마스터를 CHANNEL로 연결하여 Replicate 하는 구조입니다.

 

각각의 CHANNEL을 통해 Master / Slave 간 연결을 하고, 이 CHANNEL은 Replication 연결에 사용되는 Thread(IO, SQL, Worker 및 Coordinator 등)들을 관리합니다.

 

전체적인 틀로 여러개의 Master DB를 1개의 Slave 인스턴스에 연결하여 복제하는 구조로, 여러개의 Master DB의 내용을 하나의 Slave에 모으는 역활을 하게 됩니다.

 

 

 

사용 목적

예를 들어서 어떤 시스템이 공통DB, 메인DB, 로그DB로 구성되어 있고 로그DB는 여러개로 샤딩(Sharding)되어 물리적으로 다른 장비에서 서비스를 하고있다고 생각해 볼 수 있습니다.

 

그런데 어떤 지표를 보기 위해서 모든 DB에서 한방에 쿼리를 해서 join 을 걸어야 한다면, 예전에는 batch 작업으로 특정시점에 데이터를 모두 백업해서, 커다란 장비에 복원해야 했습니다. 엄청 오래걸리고 까다롭습니다.

 

MySQL의 복원은 특히 오래 걸립니다. 그런데, MSR이 사용된다면? 그냥 큰 디스크가 존재하는 장비하나에 모든 DB의 Slave 로 Channel로 구성 할 수 있습니다. 예전처럼 여러 벤더사의 Third Party Tool을 사용하지 않고, MySQL의 자체 기능을 사용하여 관리하기 용이한 시스템을 구축 할 수 있습니다.

 

 

 

 

 

테스트 환경

Hostname	IP	Version
master1	10.70.101.78	mysql 8.0
master2	10.70.101.79
slave	10.70.101.80

 

 

 

 

사전 작업

 

■ DB install (모든 서버)

download URL : https://downloads.mysql.com/archives/community/

[root@master1 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.27-el7-x86_64.tar.gz
--2022-03-07 15:07:22--  https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.27-el7-x86_64.tar.gz
Resolving downloads.mysql.com (downloads.mysql.com)... 137.254.60.14
Connecting to downloads.mysql.com (downloads.mysql.com)|137.254.60.14|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.27-el7-x86_64.tar.gz [following]
--2022-03-07 15:07:23--  https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.27-el7-x86_64.tar.gz
Resolving cdn.mysql.com (cdn.mysql.com)... 203.235.97.158
Connecting to cdn.mysql.com (cdn.mysql.com)|203.235.97.158|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 827238016 (789M) [application/x-tar-gz]
Saving to: ‘mysql-8.0.27-el7-x86_64.tar.gz’

100%[==================================================>] 827,238,016 2.38MB/s   in 4m 25s

2022-03-07 15:11:49 (2.97 MB/s) - ‘mysql-8.0.27-el7-x86_64.tar.gz’ saved [827238016/827238016]



mysql> select @@version
    -> ;
+-----------+
| @@version |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.00 sec)

 

 

 

■ 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)

 

 

 

■ server id 설정

replication 맺기 위해 DB server의 id가 각자 달라야합니다.

## server_id설정 & /etc/my.cnf에 추가
mysql> set global server_id=<번호>;

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

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

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

 

 

■ Slave서버 my.cnf수정

사용자가 사용하는 database이외에 default인 System DB는 Master Node 정보가 섞이면 안되기 때문에 ignore설정

[root@localhost bin]# vi /etc/my.cnf
[mysqld]
datadir=/data
socket=/tmp/mysql.sock
log-error=/log/mariadb.log
pid-file=/log/mariadb.pid
symbolic-links=0
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
relay_log=mysql-relay-bin
log_slave_updates=1
read_only=1
relay_log_purge=1
[mysqld_safe]
log-error=/log/mariadb.log
pid-file=/log/mariadb.pid
!includedir /etc/my.cnf.d

 

 

 

■ log_bin & relay_log 활성화

replicaion을 맺기 위해 log_bin과 relay_log 파라미터를 활성화 시킵니다.

mysql> show variables like '%log_bin%';
+---------------------------------+-------------------+
| Variable_name                   | Value             |
+---------------------------------+-------------------+
| log_bin                         | ON                |
| log_bin_basename                | /data/mysql       |
| log_bin_index                   | /data/mysql.index |
| log_bin_trust_function_creators | OFF               |
| log_bin_use_v1_row_events       | OFF               |
| sql_log_bin                     | ON                |
+---------------------------------+-------------------+
6 rows in set (0.01 sec)


mysql> show variables like '%relay%';
+---------------------------+-------------------------------+
| Variable_name             | Value                         |
+---------------------------+-------------------------------+
| max_relay_log_size        | 0                             |
| relay_log                 | master1-relay-bin             |
| relay_log_basename        | /data/master1-relay-bin       |
| relay_log_index           | /data/master1-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                         |
+---------------------------+-------------------------------+
11 rows in set (0.00 sec)

 

 

■ replication user 생성 (모든 서버)

※  message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. 에러 발생하기 때문에 아래와 같이 native_password 옵션 사용

mysql> create user 'repl'@'10.70.101.%' identified WITH mysql_native_password by 'repl';
Query OK, 0 rows affected (0.02 sec)

mysql> grant all on *.* to repl@'10.70.101.%';
Query OK, 0 rows affected (0.00 sec)

 

 

 

■ Replication 설정

'for channel' 명령 구분을 이용하여 Channel명을 명시해줍니다.

<Master1>
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql.000003
         Position: 705
     Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

<Master2>
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql.000001
         Position: 156
     Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)


<Slave>
mysql> CHANGE MASTER TO MASTER_HOST='10.70.101.78', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysql.000003', MASTER_LOG_POS=705 FOR CHANNEL 'master_1';
Query OK, 0 rows affected, 8 warnings (0.01 sec)


mysql> CHANGE MASTER TO MASTER_HOST='10.70.101.79', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysql.000001', MASTER_LOG_POS=156 FOR CHANNEL 'master_2';
Query OK, 0 rows affected, 8 warnings (0.01 sec)

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


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

 

 

■ slave 상태 조회

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.70.101.78
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql.000003
          Read_Master_Log_Pos: 1413
               Relay_Log_File: mysql-relay-bin-master_1.000002
                Relay_Log_Pos: 1028
        Relay_Master_Log_File: mysql.000003
             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: 1413
              Relay_Log_Space: 1246
              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: 78d9c631-9dde-11ec-9e50-005056a816be
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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 source to send event
                  Master_Host: 10.70.101.79
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql.000001
          Read_Master_Log_Pos: 864
               Relay_Log_File: mysql-relay-bin-master_2.000002
                Relay_Log_Pos: 1028
        Relay_Master_Log_File: mysql.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: 864
              Relay_Log_Space: 1246
              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: b5350d6c-9dde-11ec-9172-005056a8cd66
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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.00 sec)

 

 

 

 

■ Replication 테스트

<Master1>
mysql> create database kim;
Query OK, 1 row affected (0.00 sec)
<Master2>
mysql> create database kim2;
Query OK, 1 row affected (0.00 sec)

<Slave>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kim                |
| kim2               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

 

 

 

참고

 

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

MySQL MSR(Multi Source Replication) 도입

https://yunhyeonglee.tistory.com/44?category=1220694 

[MySQL] MSR ( Multi Source Replication )

https://hoing.io/archives/18540