Mysql Multi source replication 로 백업 데이터 중앙화

Mysql DB Multi source replication

지난 글에선 데이터 백업, 쿼리 분산을 위한 Mysql replication 을 소개했다. DB 서버 하나에 데이터를 넣어두는 것이 위험하다고 생각해 복제용 DB를 만들어 데이터를 백업했고, 이를 읽기 전용 서버로 생각하여 백업과 부하 분산 두 가지를 잡을 수 있었다.

 

이번엔 새로운 니즈가 생겼다. 지금 프로젝트 배포를 개인 홈 서버에 하고 있는데 물리적인 문제가 생겨 데이터가 날아가면 어쩌지 하는 생각이다. (혹시 불이라도 나거나 SSD가 고장나면 어째..)

 

원하는 구조는 아래와 같다. Api 서버별로 다른 DB 서버로부터 Cloud server 에 하나의 Mysql DB 를 띄워 단순 복제하는 것이다. 여러 소스로부터 복제한다고 해서 이를 Multi source replication / MSR 으로 부른다.

 

 

 

Hands on

1. Source 서버 Log 파일 확인

 

source 로 사용될 mysql server 의 '/etc/mysql/conf.d/my.cnf' 에 아래와 같이 log 파일의 prefix 와 server id 를 지정해 준다.

 

아래 예시에 log-bin 은 replica 가 읽을 바이너리 파일의 prefix 를 지정하는 것이고, server-id는 각 db server 의 키가 되니 고유하게 지정한다. 

 

[mysqld]

log-bin=mysql-member-bin

server-id=1

[mysqld]

log-bin=mysql-album-slave-bin

server-id=2

# log slave history also
log_slave_updates = 1

 

 

Source 가 될 각 mysql 을 재실행해주고 'SHOW MASTER STATUS\G' 을 입력하여 Master 설정을 확인한다. 이때 File 에 앞서 설정한 prefix 가 제대로 붙었는지 확인하고, 구체적인 file 명과 position 을 따로 기록해둔다.

 

mysql> SHOW MASTER STATUS\G

*************************** 1. row ***************************
             File: mysql-album-slave-bin.000001
         Position: 3883
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

 

 

2. Replica 서버 Log 파일 확인

 

replica 서버로 사용될 서버에도  마찬가지로 '/etc/mysql/conf.d/my.cnf' 에 log 파일 prefix 와 server id를 기입해 준다.

 

[mysqld]

# prefix of binary log
log-bin=mysql-backup-bin

server-id=3

 

 

3. Replication 유저 생성

 

source 서버로 사용될 각 서버에 백업용 유저를 생성할 것이다. 이때 이는 소스 서버 별로 다를 필요는 없는데, Master - Replica 서버에서 겹쳐선 안된다. Replica 서버의 유저 정보들을 확인해서 이를 피한 Replication 유저를 생성한다.

 

CREATE USER 'bakup'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'bakup'@'%';

 

 

4. Replication source channel 설정

 

Replica 서버에 아래처럼 각 source 별 replication channel 을 등록한다.

 

앞서 1번에서 소스 별로 따로 기록해 둔 Log file 명과 position 이 여기에 사용된다. Channel 이름은 편하게 작성해도 된다. 

 

CHANGE REPLICATION SOURCE TO SOURCE_HOST="${SERVER_URL}", \
                             SOURCE_PORT=${SERVER_PORT}, \
                             SOURCE_USER="${REPLICA_USER_NAME}", \
                             SOURCE_PASSWORD="${REPLICA_USER_PASSWORD}", \
                             SOURCE_LOG_FILE="${BIN_LOG_FILE_NAME}", \
                             SOURCE_LOG_POS=${BIN_FILE_POSITION} \
                             FOR CHANNEL "${CHANNEL_NAME}";

 

 

5. Replica 서버 실행, 확인, 종료

 

아래 명령어로 앞서 생성한 Channel 별 replica 실행 또는 전부 실행할 수 있다.

 

START SLAVE FOR CHANNEL "${CHANNEL_NAME}";

START SLAVE;

 

 

아래 명령어로 Slave 상태를 확인할 수 있다.

 

SHOW SLAVE STATUS\G;

 

 

혹 에러가 생기거나 복제를 중지하고 싶다면 아래 명령어를 쓸 수 있다. 에러가 생겨 재시도하는 경우 stop -> reset -> channel 재등록 -> start 순으로 진행한다.

 

STOP SLAVE FOR CHANNEL "${CHANNEL_NAME}";

STOP SLAVE;

RESET SLAVE ALL;

 

 

6. 예시 

 

정상적으로 설정을 마친 복제 서버의 상태는 다음과 같다. 위 예시에선 이해를 쉽게 하기 위해 Log 파일 이름이나 server key 값을 바꿔 표시해 그 부분이 아래 출력에선 다를 수 있다.

 

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.18.0.2
                  Master_User: bakup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-album-slave-bin.000003
          Read_Master_Log_Pos: 3883
               Relay_Log_File: 68d8dd971232-relay-bin-album@002dslave.000002
                Relay_Log_Pos: 772
        Relay_Master_Log_File: mysql-album-slave-bin.000003
             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: 3883
              Relay_Log_Space: 1005
              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: 3ad1fa46-858d-11ee-91c3-0242ac120002
             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: album-slave
           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: 172.18.0.8
                  Master_User: bakup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-member-bin.000003
          Read_Master_Log_Pos: 2541
               Relay_Log_File: 68d8dd971232-relay-bin-member.000002
                Relay_Log_Pos: 968
        Relay_Master_Log_File: mysql-member-bin.000003
             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: 2541
              Relay_Log_Space: 1192
              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: 3850a457-858d-11ee-90ca-0242ac120008
             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: member
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
2 rows in set, 1 warning (0.00 sec)