[GTID] 복제 환경 GTID 틀어졌을 때 해결하기

복제 환경에서 GTID가 간혹 가다 틀어지는 경우가 있다.

 

GTID가 틀어지는 원인

다양한 원인이 존재하나, 대부분은 sql_log_bin=ON 상태에서 DBA의 변경 작업(DML, DDL)으로 인해 발생한다.

slave의 sql_log_bin 상태가 "ON"이면 트랜잭션에도 master 서버로부터 넘어오는 GTID set과 별개로 GTID가 부여되면서 두 서버간의 GTID set이 달라지게 되는 것

 

 

GTID가 틀어졌을 때의 문제점

1. failover시 문제 발생
2. 달라진 데이터에 대해 master로부터 DML이 넘어오는 경우 replication 자체가 깨질 위험 있음

 

해결 방법

기본적으로 GTID 맞추는 작업은 1. 데이터 정합성 맞추기 → 2. GTID 맞추기의 과정이며,

1. 데이터 정합성 맞추기 단계에서 아래 경우에 따라 수행 작업이 달라진다.

• binary log에 존재하고 수동 작업이 가능한 경우: 수동으로 맞춰준다.
• binary log에 없거나 수동 작업하기에는 양이 너무 많을 경우: 어느 작업으로 인한 것이고, 어느 데이터를 기준으로 맞출지 정한 후 전체 복구 수행(aurora라면 snapshot 이용)

 

1) binlog에 존재하고 수동 작업이 가능한 경우의 해결 방법

GTID 맞추는 작업을 예시와 함께 설명하기 위해 상황을 재연해보았다.

아래는 데이터 정합성과 GTID가 틀어진 상황이다.

 

# master
mysql> show global variables like '%gtid%';
+----------------------------------+-------------------------------------------+
| Variable_name                    | Value                                     |
+----------------------------------+-------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                        |
| enforce_gtid_consistency         | ON                                        |
| gtid_executed                    | 889a1efa-d722-11ec-b66c-0242ac130003:1-31 |
| gtid_executed_compression_period | 1000                                      |
| gtid_mode                        | ON                                        |
| gtid_owned                       |                                           |
| gtid_purged                      |                                           |
| session_track_gtids              | OFF                                       |
+----------------------------------+-------------------------------------------+
 
mysql> select * from t2;
+----+-------+
| id | value |
+----+-------+
|  1 | a     |
|  2 | b     |
|  3 | c     |
|  4 | d     |
+----+-------+

# slave
mysql> show global variables like '%gtid%';
+----------------------------------+------------------------------------------------------------------------------------+
| Variable_name                    | Value                                                                              |
+----------------------------------+------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                                                                 |
| enforce_gtid_consistency         | ON                                                                                 |
| gtid_executed                    | 87b71e7d-d722-11ec-82b8-0242ac130002:1,889a1efa-d722-11ec-b66c-0242ac130003:1-31   |
| gtid_executed_compression_period | 1000                                                                               |
| gtid_mode                        | ON                                                                                 |
| gtid_owned                       |                                                                                    |
| gtid_purged                      |                                                                                    |
| session_track_gtids              | OFF                                                                                |
+----------------------------------+------------------------------------------------------------------------------------+
 
mysql> select * from t2;
+----+-------+
| id | value |
+----+-------+
|  1 | a     |
|  2 | b     |
|  3 | c     |
|  4 | d     |
|  5 | test  |
+----+-------+

 

 

1. [Slave] binlog에서 slave에 있는 GTID set에 해당하는 쿼리문 확인

-- binlog 파일 일부 내용
SET @@SESSION.GTID_NEXT= '87b71e7d-d722-11ec-82b8-0242ac130002:1'/*!*/;
# at 1422
#220524  9:30:20 server id 11  end_log_pos 1494 CRC32 0xa8698448    Query   thread_id=75    exec_time=0 error_code=0
...
### INSERT INTO `test`.`t2`
### SET
###   @1=5
###   @2='test'
# at 1587
...

 

2. [Slave] 해당 쿼리를 롤백해도 이상 없을지 확인 후 롤백

📌 해당 쿼리 수행자를 확인하고 그 수행자에게 확인하거나, 쿼리 수행자를 찾지 못하는 경우에는 개발팀과 협의 하에 진행한다.

mysql> set sql_log_bin=off;
mysql> delete from t2 where id=5;

 

3. [Slave] 데이터 정합성이 맞춰진 상태이므로, GTID 틀어진 것 맞춰주기

⚠️ 주의: stop slave를 하기 전, 반드시 slave db의 서비스 사용 여부를 확인하고 개발팀과 협의 후 진행한다. slave가 항상 모든 서비스에 reader용으로만 사용되지는 않는다는 점 유의하자.

-- slave 중지
mysql> stop slave;

-- slave의 gtid set 확인
mysql> show global variables like '%gtid%';
+----------------------------------+------------------------------------------------------------------------------------+
| Variable_name                    | Value                                                                              |
+----------------------------------+------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                                                                 |
| enforce_gtid_consistency         | ON                                                                                 |
| gtid_executed                    | 87b71e7d-d722-11ec-82b8-0242ac130002:1,889a1efa-d722-11ec-b66c-0242ac130003:1-31   |
| gtid_executed_compression_period | 1000                                                                               |
| gtid_mode                        | ON                                                                                 |
| gtid_owned                       |                                                                                    |
| gtid_purged                      |                                                                                    |
| session_track_gtids              | OFF                                                                                |
+----------------------------------+------------------------------------------------------------------------------------+

-- 복제 정보 초기화
mysql> reset master; reset slave all;

-- sql_log_bin 비활성화
mysql> set sql_log_bin = off;

-- GTID 맞추는 작업 진행
mysql> set global gtid_purged= '{두 번째 작업에서 확인한 gtid_executed value 중 master 서버의 GTID set}';

-- sql_log_bin 활성화
mysql> set sql_log_bin = on;

-- change master to
mysql> CHANGE MASTER TO
MASTER_HOST=‘{master host}',
MASTER_PORT={master port},
MASTER_USER=‘repl’,
MASTER_PASSWORD=‘{replication용 계정 비밀번호}’,
MASTER_AUTO_POSITION =1;

-- slave 재개
mysql> start slave;

-- 복제가 잘 되고 있는지 slave 상태 확인
-- slave_IO_Running, slave_SQL_Running 모두 Yes여야 함
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.19.0.2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 8183
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 414
        Relay_Master_Log_File: mysql-bin.000001
             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: 8183
              Relay_Log_Space: 621
              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: 10
                  Master_UUID: 889a1efa-d722-11ec-b66c-0242ac130003
             Master_Info_File: /var/lib/mysql/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: 889a1efa-d722-11ec-b66c-0242ac130003:1-31
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.01 sec)

 

 

4. source-replica간 GTID가 올바르게 맞춰졌는지 확인

# master
mysql> show global variables like '%gtid%';
+----------------------------------+------------------------------------------------------------------------------------+
| Variable_name                    | Value                                                                              |
+----------------------------------+------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                                                                 |
| enforce_gtid_consistency         | ON                                                                                 |
| gtid_executed                    | 889a1efa-d722-11ec-b66c-0242ac130003:1-31                                          |
| gtid_executed_compression_period | 1000                                                                               |
| gtid_mode                        | ON                                                                                 |
| gtid_owned                       |                                                                                    |
| gtid_purged                      |                                                                                    |
| session_track_gtids              | OFF                                                                                |
+----------------------------------+------------------------------------------------------------------------------------+

# slave
mysql> show global variables like '%gtid%';
+----------------------------------+------------------------------------------------------------------------------------+
| Variable_name                    | Value                                                                              |
+----------------------------------+------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                                                                 |
| enforce_gtid_consistency         | ON                                                                                 |
| gtid_executed                    | 889a1efa-d722-11ec-b66c-0242ac130003:1-31                                          |
| gtid_executed_compression_period | 1000                                                                               |
| gtid_mode                        | ON                                                                                 |
| gtid_owned                       |                                                                                    |
| gtid_purged                      | 889a1efa-d722-11ec-b66c-0242ac130003:1-31                                          |
| session_track_gtids              | OFF                                                                                |
+----------------------------------+------------------------------------------------------------------------------------+