MySQL
[GTID] 복제 환경 GTID 틀어졌을 때 해결하기
erin.yoon
2022. 6. 14. 01:46
복제 환경에서 GTID가 간혹 가다 틀어지는 경우가 있다.
GTID가 틀어지는 원인
다양한 원인이 존재하나, 대부분은 sql_log_bin=ON 상태에서 DBA의 변경 작업(DML, DDL)으로 인해 발생한다.
slave의 sql_log_bin 상태가 "ON"이면 트랜잭션에도 master 서버로부터 넘어오는 GTID set과 별개로 GTID가 부여되면서 두 서버간의 GTID set이 달라지게 되는 것
GTID가 틀어졌을 때의 문제점
- failover시 문제 발생
- 달라진 데이터에 대해 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 |
+----------------------------------+------------------------------------------------------------------------------------+반응형