[MySQL] MSR 복제 환경 구축하기 MySQL Multi Source Replication

hwwwa·2023년 6월 26일
mysql
0

MSR 복제 환경 구축하기

MSR (Multi-Source Replication)이란?

Multi Source Replication(MSR)은 여러개의 Master DB를 1개의 Slave 인스턴스에 연결하여 복제하는 구조로, 여러개의 Master DB(Multi source)의 내용을 하나의 Slave에 모으게 됩니다.

MySQL 5.7 에서 추가된 기능으로 통합, 백업, OLAP 및 배치 작업 등에서 유용하게 사용할 수 있으며 GTID 기반 복제와 Binary Log Position 기반 복제 모두 가능합니다.

개발 환경

  • NHN Cloud
    • CentOS 7.9
  • MySQL 8.0.33

DB 서버 정보

Master1

Instance Name: mysql-msr-source1
OS: CentOS 7.9
MySQL: 8.0.33 (13306 port)

Master2

Instance Name: mystatus-msr-source2
OS: CentOS 7.9
MySQL: 8.0.33 (13306 port)

Slave

Instance Name: mystatus-msr-replica
OS: CentOS 7.9
MySQL: 8.0.33 (13306 port)

각 서버에 MySQL 8.0.33 버전을 설치하였습니다.

설치 방법 참고 👉 https://velog.io/@inhwa1025/과제-MySQL-자동-설치-스크립트-작성하기

MySQL 설정

  • 타임존 설정

my.cnf 에 아래 내용을 추가하여 한국표준시로 변경

[mysqld]
default-time-zone="+09:00"
  • 슬로우 쿼리 로그 사용 설정, 슬로우 쿼리 기준 1초로 설정
[mysqld]
slow-query-log=1
long-query-time=1
log-output=TABLE

적용이후 mysql 서비스를 재시작

MySQL Replication 구성

Document : https://dev.mysql.com/doc/refman/8.0/en/replication-howto.html

인스턴스별로 MySQL 서버가 띄워져있음
mysql-msr-source1 👉 Source1 (Master1)
mysql-msr-source2 👉 Source2 (Master2)
mysql-msr-replica 👉 Replica (Slave)

  • 버전 : 8.0.33
  • 복제 대상
    • Master 1 Channel 명 : source_1
    • Master 2 Channel 명 : source_2

Master 파라미터 설정

  • Master 1
[mysqld]
server-id=1
  • Master 2
[mysqld]
server-id=2

my.cnf에 서버별로 다른 id를 지정

Slave 파라미터 설정

[mysqld]
server-id=12
read-only

적용을 위해 MySQL을 재시작

복제 전용 유저 생성

각 Master에 복제를 위한 권한을 설정한다. 복제를 위한 REPLICATION SLAVE 권한을 부여한다.

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'replica!@#123';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

비밀번호 체계를 이전처럼 환경설정

ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'replica!@#123';

Log 파일과 Position 확인

각 Master 서버에서 Log 파일명과 Position 정보를 확인한다.

  • Master 1
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: binlog.000002
         Position: 971
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
  • Master 2
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: binlog.000002
         Position: 973
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

mysqldump를 사용하여 데이터 스냅샷 생성

각 Master 서버에서 mysqldump를 사용하여 데이터 스냅샷 생성

$ mysqldump -u root --all-databases --source-data > dbdump1.db
$ mysqldump -u root --all-databases --source-data > dbdump2.db

slave 서버에 데이터 스냅샷 파일을 전송하고 데이터 적용

$ mysql -u root < dbdump1.db
$ mysql -u root < dbdump2.db

바이너리 로그 기반 복제 소스 추가

CHANGE REPLICATION SOURCE TO 명령어로 bin 로그 기반의 복제를 진행하기 위해 Slave에 Master에 대한 복제 채널을 구성
Host 주소, Port, 권한 정보, Log 파일 명, Log 파일 Position의 정보를 입력

mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST="***.***.***.***", SOURCE_PORT=13306, SOURCE_USER="repl", SOURCE_PASSWORD="replica!@#123", SOURCE_LOG_FILE="binlog.000007", SOURCE_LOG_POS=843 FOR CHANNEL "source_1";
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST="***.***.***.***", SOURCE_PORT=13306, SOURCE_USER="repl", SOURCE_PASSWORD="replica!@#123", SOURCE_LOG_FILE="binlog.000004", SOURCE_LOG_POS=157 FOR CHANNEL "source_2";

복제 시작

START REPLICA 명령어로 복제를 시작

mysql> START REPLICA;
mysql> START REPLICA FOR CHANNEL "source_1";
mysql> START REPLICA FOR CHANNEL "source_2";

SHOW REPLICA STATUS 명령어를 실행하여 두 채널이 모두 시작되었고 올바르게 작동하는 지 확인

*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: ***.***.***.***
                  Source_User: repl
                  Source_Port: 13306
                Connect_Retry: 60
              Source_Log_File: binlog.000002
          Read_Source_Log_Pos: 971
               Relay_Log_File: mystatus-msr-replica-relay-bin-source_1.000002
                Relay_Log_Pos: 323
        Relay_Source_Log_File: binlog.000002
           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: 971
              Relay_Log_Space: 557
              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: c71dc1f8-0906-11ee-b179-fa163e22b225
             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:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name: source_1
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
*************************** 2. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: ***.***.***.***
                  Source_User: repl
                  Source_Port: 13306
                Connect_Retry: 60
              Source_Log_File: binlog.000002
          Read_Source_Log_Pos: 973
               Relay_Log_File: mystatus-msr-replica-relay-bin-source_2.000002
                Relay_Log_Pos: 323
        Relay_Source_Log_File: binlog.000002
           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: 973
              Relay_Log_Space: 557
              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: c67aed85-0906-11ee-ad01-fa163e94f412
             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:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name: source_2
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
2 rows in set (0.00 sec)

MSR 중지

STOP REPLICA 명령어로 MSR 구성 중지
기본적으로 MSR의 모든 채널이 중지되며, FOR CHANNEL절을 사용해 특정 채널만 중지 가능

mysql> STOP REPLICA;
mysql> STOP REPLICA FOR CHANNEL "source_1";
mysql> STOP REPLICA FOR CHANNEL "source_2";

MSR 재설정

RESET REPLICA 명령어로 MSR 구성을 재설정
기본적으로 MSR의 모든 채널이 재설정되며, FOR CHANNEL절을 사용해 특정 채널만 재설정 가능

mysql> RESET REPLICA ALL;
mysql> RESET REPLICA FOR CHANNEL "source_1";
mysql> RESET REPLICA FOR CHANNEL "source_2";

MSR 복제 오류로 인한 재구성하기

Master 서버와 Slave 서버간 동기화 오류로 MSR을 재구성해야하는 경우 재구성 방법입니다.

1. MSR 중지

STOP REPLICA 명령어로 MSR 구성을 중지합니다.

mysql> STOP REPLICA FOR CHANNEL "source_1";
mysql> STOP REPLICA FOR CHANNEL "source_2";
mysql> STOP REPLICA;

2. MSR 설정 초기화

RESET REPLICA 명령어로 MSR 구성을 초기화합니다.

mysql> RESET REPLICA FOR CHANNEL "source_1";
mysql> RESET REPLICA FOR CHANNEL "source_2";
mysql> RESET REPLICA ALL;

RESET REPLICA ALL 명령어를 실행 해주지 않으면 이후 데이터 스냅샷 적용 시 ERROR 3079 (HY000) at line 24: Multiple channels exist on the replica. Please provide channel name as an argument. 오류가 발생합니다.

3. mysqldump를 사용하여 데이터 스냅샷 생성 및 적용

각 Master 서버에서 mysqldump를 사용하여 데이터 스냅샷을 생성합니다.

$ mysqldump -u root --all-databases --source-data > dbdump1.db
$ mysqldump -u root --all-databases --source-data > dbdump2.db

생성한 데이터 스냅샷을 Slave 서버에 전송하고 Slave 서버에 데이터 스냅샷을 적용합니다.

$ mysql -u root < dbdump1.db
$ mysql -u root < dbdump2.db

4. MSR 복제 구성 정보 입력

각 Master의 정보 확인

  • Master1
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: binlog.000007
         Position: 96622105
     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: binlog.000004
         Position: 157
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

Slave 서버에서 MSR 구성 정보 변경

CHANGE REPLICATION SOURCE TO 명령어로 bin 로그 기반의 복제를 진행하기 위해 Slave에 Master에 대한 복제 채널을 구성합니다.
앞서 확인한 Host 주소, Port, 권한 정보, Log 파일 명, Log 파일 Position의 정보를 입력합니다.

mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST="***.***.***.***", SOURCE_PORT=13306, SOURCE_USER="repl", SOURCE_PASSWORD="replica!@#123", SOURCE_LOG_FILE="binlog.000007", SOURCE_LOG_POS=96622105 FOR CHANNEL "source_1";

mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST="***.***.***.***", SOURCE_PORT=13306, SOURCE_USER="repl", SOURCE_PASSWORD="replica!@#123", SOURCE_LOG_FILE="binlog.000004", SOURCE_LOG_POS=157 FOR CHANNEL "source_2";

MSR 복제 시작

START REPLICA 명령어로 복제를 시작합니다.

mysql> START REPLICA;
mysql> START REPLICA FOR CHANNEL "source_1";
mysql> START REPLICA FOR CHANNEL "source_2";

SHOW REPLICA STATUS 명령어를 실행하여 두 채널이 모두 시작되었고 올바르게 작동하는 지 확인합니다.

mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: ***.***.***.***
                  Source_User: repl
                  Source_Port: 13306
                Connect_Retry: 60
              Source_Log_File: binlog.000007
          Read_Source_Log_Pos: 96622105
               Relay_Log_File: mystatus-msr-replica-relay-bin-source_1.000002
                Relay_Log_Pos: 323
        Relay_Source_Log_File: binlog.000007
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
                   Last_Errno: 0
                   Last_Error:
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
                             .
                             .
                             .
*************************** 2. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: ***.***.***.***
                  Source_User: repl
                  Source_Port: 13306
                Connect_Retry: 60
              Source_Log_File: binlog.000004
          Read_Source_Log_Pos: 157
               Relay_Log_File: mystatus-msr-replica-relay-bin-source_2.000002
                Relay_Log_Pos: 323
        Relay_Source_Log_File: binlog.000004
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
                   Last_Errno: 0
                   Last_Error:
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
                             .
                             .
                             .
2 rows in set (0.00 sec)
profile
hwwwa
이전 포스트

[MySQL] 복제 환경 구축하기 MySQL Replication

다음 포스트

[RealMySQL8.0] 4장 - MySQL 엔진 아키텍처

0개의 댓글