Signal9

MySQL은 여러 종류의 RDB 중에서 개발자가 접근성도 높고, 레퍼런스도 많은 DB이다. 글쓴이도 가벼운 게시판류 개발부터 데이터 분석 플랫폼까지 다양한 유형의 소프트웨어 개발에 MySQL을 사용해 왔다. 요즘은 클라우드 플랫폼에서 PAAS 형태로 서비스하고 있어 DB 인프라 지식이 많이 필요하지 않는 시점이지만 혹시나 나중에 글쓴이가 필요할까 싶어 정리 차원에서 이 글을 작성한다.

; 차후 관련 TIP을 생각나는데로 계속 보강해 나갈 예정이다.

MySQL Replica

MySQL Replica를 구성하는 방법은 인터넷에 많은 정보가 존재하니 굳이 여기서 설명하지 않겠다.
대신 Replica 구성 후 글쓴이가 사용하면서 가장 자주격은 크리티컬한 아래 3가지 이슈를 처리하는 방법을 정리하고자 한다.

오류로 인한 복제 중단
복제 지연
복제 깨져서 복구 필요한 경우

복제 상태 확인

아래 코드는 실재 MySQL Replica Client에서 “show replica status” 명령을 입력을때 나오는 내용이다. 현재 Master 서버에서 Replica 서버로 데이터가 복제되고 있는 상태를 보여준다. 많은 정보를 제공하고 다 알면 좋겠지만 필요한 부분만 알고 넘어가자.

mysql> SHOW REPLICA STATUS\G;
*************************** 1. row ***************************
            Replica_IO_State: Waiting for source to send event
                 Source_Host: 1.2.3.4
                 Source_User: replication
                 Source_Port: 53386
               Connect_Retry: 60
             Source_Log_File: mysql-bin.000133
         Read_Source_Log_Pos: 11044081
              Relay_Log_File: relay-bin.000054
               Relay_Log_Pos: 1825364
       Relay_Source_Log_File: mysql-bin.000133
          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: 11044081
             Relay_Log_Space: 11044633
             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: 0Source_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: 534fbfda-03f7-11ed-b0e0-000d3a8eff2c
            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_TLS_Version:
      Source_public_key_path:
       Get_Source_public_key: 0
         Network_Namespacea :
1 row in set (0.00 sec)

복제 오류

MySQL 복제는 Binlog를 사용해서 이루어진다(더 복잡하고 세부적인 내용이 있지만 이 정도만 알면 된다. 이 정도만 알아도 문제 해결에 문제없다.). DB 저장된 RAW 데이터를 동기화하는 것이 아니라 실행 명령을 복기함으로 복제가 이루어진다. 복사기로 A4용지를 몽땅 이미지로 복사하는 것이 아니라 한 자 한 자 누가 읽어 주면 다시 수기로 복사본을 만드는 것이다. 당연히 복제 도중 Replica 서버에서 Master 노드에서 실행되던 쿼리가 오류가 발생하면 복제 진행이 되지 않는다. 이런 경우 ‘show replica status’로 상태를 조회할 수 있고, ‘Last_Error’ 항목에서 오류 내용을 확인할 수 있다.
아래 예제는 자주 발생하는 오류 몇 가지 내용이다.

primary key 가 중복시 발생 오류
중복되는 키를 삭제하면 문제가 해결된다. 그래서 Replica 서버는 키 중복을 방지하기 위해서 읽기 전용 모드로 설정해서 사용하는 것이 장애 발생을 미연에 방지할 수 있다.
1
Last_Error: Error 'Duplicate entry '123' for key 'PRIMARY'' on query. Default database: 'testdb'. Query: 'INSERT INTO test_table (id, value) VALUES (123, 'data')'

외래키 제약 오류 발생
외래키가 존재하지 않거나 관련해서 발생하는 오류로 동기화 진행이 안되는 문제로, 외래키 제약을 삭제하거나 외래키 문제를 해결하면 오류가 해결된다.
1
Last_Error: Cannot add or update a child row: a foreign key constraint fails (`testdb`.`order_details`, CONSTRAINT `fk_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`))
네트워크 오류
Master와 Replica 간의 통신 연결이 끊어졌거나 기타 원인으로 원활하지 못할 때 발생한다. 네트워크 상태나 MySQL 서버의 접속 계정 권한 상태를 확인해 보자.
1
Last\_Error: Lost connection to MySQL server during query
BinLog file 오류
BinLog 파일을 찾을 수 없어서 발생한 문제로 binlog 이름이나 경로 확인해서 해결 가능하다. MySQL 설정을 변경했거나 기타 이유로 binlog 파일이 사라졌을 경우 발생한다.
1
Last\_Error: Could not find log file 'mysql-bin.000123'

이런 오류가 발생 시 복제를 아래 명령어를 사용해서 잠시 중단시키고 문제를 해결한 다음 다시 복제를 시작하면 된다.
; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 는 문제가 발생하고 있는 항목을 건너뛰게 해준다. 문제의 근본적인 해결이 아니지만 회피할 수 있는 방법이다.

mysql> STOP REPLICA;
...
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
...
mysql> START REPLICA;

복제 지연

MySQL을 서비스 중 Master 노드에서 장시간 Lock이 걸리는 트랜잭션을 다수가 발생시키는 작업을 하거나 대용량 혹은 대량의 Row가 변경되는 트랜잭션을 발생시키면 Replica 노드에서 복제 지연이 발생한다. 이런 경우 두 서버의 데이터를 조회해서 데이터의 차이로 복제 지연을 확인할 수 있지만 MySQL 쿼리 명령 결과로 지연 내역을 확인 가능하다.

Master Node Status

먼저 Master 노드에서 ‘SHOW MASTER STATUS’ 쿼리를 사용해서 상태롤 조회하자.
- File : mysql-bin.000001
- Position : 11044081
위 두개의 값이 현재 Master Node의 데이터 기록 위치이다.

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 11044081 | testdb       |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

Replica Node Status

다음으로 Replica 노드에서 ‘SHOW REPLICA STATUS’ 로 상태를 확인하자.

아래 3개의 항목만 확인하면 된다.

mysql> SHOW REPLICA STATUS\G;
     ...
         Source_Log_File: mysql-bin.000001
     Read_Source_Log_Pos: 11044081
     ...
     Exec_Source_Log_Pos: 11044081

File == Source_Log_File 가 일치해야 하고,
Position == Read_Source_Log_Pos == Exec_Source_Log_Pos , 3개 값이 일치해야 한다.

서버 To 서버로 데이터 전송이 느려지며 Position == Read_Source_Log_Pos 값이 차이가 나고, 복사 후 반영이 느려지면 Read_Source_Log_Pos == Exec_Source_Log_Pos가 차이 난다.

여기까지 같으면 Master에서 Replica서버로 데이터 자체는 복사되었다고 보면 된다. 하지만 Read_Source_Log_Pos과 Exec_Source_Log_Pos 값이 동일하지 않으면 복제된 데이터가 Replica에 반영되지 않았다는 상태를 보여준다.

데이터 복제 지연의 원인은 다양해서 그때그때 상태를 보고 파악해야 한다.
아래 쿼리로 마스트 노드에서 복제가 지연된 작업 목록을 확인 가능하다.
; 솔직히 글쓴이는 아래 쿼리로 내용을 파악한다고 해도 해결 할 수 가 없었다..

1	mysql> show binlog events in '<BinLog File, mysql-bin.000133>' from <Log_Pos, 11044081> limit 3;

위 쿼리로 문제 소지가 되는 작업을 무시하고 넘어갈수도 있다. 앞에서 사용했던 방법처럼 잠시 복제 진행을 멈추고 쿼리 실행을 몇 단계 뛰어 넘게하고 복제기능을 다시 동작시키면 된다.

1
2
3

mysql> STOP REPLICA;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START REPLICA;

하지만 경험상 일정 시간 경과 후에도 동기화 포지션이 동일해지지 않으면 복제가 깨졌다고 보아야한다. 복제를 멈추고 Master Node데이터를 수동으로 복사해서 적용 후 복제를 재가동시키면 된다.

Replica 복제 중지, Master Node DB 사용을 중지한다.
Master Node에서 복제가 걸려있는 DB를 모두 Dump 한다.
Replica Node에서 기존 데이터 모두 삭제하고 Master Node에서 복사해온 dump data로 DB 복구한다.

; 복구전에 binlog 파일을 모두 삭제하는 것도 혹시나 모를 문제를 방지할 수 있는 좋은 방법이다.

Master Node의 Binlog 파일과 포지션 정보를 바탕으로 Replica 복재를 재시작하면 된다.

1	mysql> CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE = '<BinLog File, mysql-bin.000133>', SOURCE_LOG_POS = <Log_Pos, 11044081>;

DB 용량 관리

DB를 오래 사용하다가 보면 용량이 늘어나게 된다. 일반 물리서버에 설치했을 때는 크게 문제 되지 않지만 PAAS 서비스에서 DB 스토리지는 비싼 자원이기에 요금 폭탄을 맞을 수 있다. 실시간으로 조회할 필요 없는 데이터는 아카이빙하던데 삭제하는 것이 좋다.
아래 쿼리는 디비 용량을 조회하는 쿼리이다.

mysql> SELECT
 table_schema,
 SUM(data_length + index_length + data_free)/1024/1024 AS total_mb,
 SUM(data_length)/1024/1024 AS data_mb,
 SUM(index_length)/1024/1024 AS index_mb,
 SUM(data_free)/1024/1024 AS free_mb,
 COUNT(*) AS tables,
 CURDATE() AS today
FROM
 information_schema.tables
 GROUP BY table_schema
 ORDER BY 2 DESC
;

그리고 Binlog를 사용할 경우 binlog를 삭제하는 것도 용량을 줄이는 한 방법이다. MySQL 설정에서 binlog 유효기간을 지정하여 일정 이상 용량이 늘어나지는 않도록 설정할 수 있지만 필요시 바로 삭제도 가능하다.

mysql> show binary logs;
...
<binlog 목록>
...
mysql> purge binary logs to 'binlog.001006'

위 쿼리를 사용해서 binlog을 조회하고 삭제 가능하다.

참고는 글쓴이는 AWS Aurora MySQL를 사용 도중 실제 사용량으로 잡히지 않는 DB 스토리지 용량으로 요금 폭탄을 맞은 적이 있다. PAAS 사용 시 스토리지 사용 용량은 항상 모니터링하고 적정 용량을 인지 체크할 필요가 있다.

마무리

앞에서 말한 것과 같이 RDS PAAS 서비스가 요즘 대세인 현실에서 DB 메니징 관련 스킬이 필요할까 싶다. 하지만 사람일은 모르는 일이고 DB 오류는 서비스 장래로 이어짐으로 한 번쯤을 알아두면 유사한 경우에 빠르게 대처할 수 있을 것이다.

MySQL TIP

MySQL Replica

복제 상태 확인

복제 오류

복제 지연

DB 용량 관리

마무리