MySQL GTID 복제 환경에서 Reset Master 수행 시 발생 장애 케이스

글을 작성하기 전에

GTID Replication 구성 시 마스터의 Binlog 및 GTID 가 Reset Master로 초기화 되었을 때 슬레이브와의 실제 연결은 끊어졌지만 Show Slave status 상에서는 정상으로 보이는 케이스가 있어 케이스 별 내용을 정리하는 포스팅입니다.

Reset Master/ Reset Slave 명령어에 대해서는 아래 포스팅을 참고하면 좋습니다.

2022.12.27 - [MySQL & MariaDB] - MySQL GTID 복제 환경에서 Reset Master/Reset Slave 명령어

MySQL GTID 복제 환경에서 Reset Master 수행 시 발생 케이스

예외 조건

현재까지 생성된 binlog 파일이 스위치 된 상황이라면 Reset Master 시 show slave satatus 상에서는 즉시 에러가 표시 됩니다.
테스트 시 Flush binary Logs를 통해 binary File을 변경하면 마스터서버에서 Reset Master 수행시 SLAVE status에서 즉시 에러가 발생하였습니다.

마스터

Reset Master 이후 슬레이브 상태
(마스터에서 Reset Master 수행 즉시 Binlog 파일을 못찾아 IO 쓰레드 에러 발생)

insert into gtid values (1);
insert into gtid values (2);
insert into gtid values (3);
insert into gtid values (4); show master status;
insert into gtid values (5); show master status;
FLUSH BINARY LOGS;
insert into gtid values (6); show master status;
insert into gtid values (7); show master status;
insert into gtid values (8); show master status;
insert into gtid values (9); show master status;
insert into gtid values (10); show master status;
reset master;

mysql> show slave status\G
*************************** 1. row **************************
Slave_IO_State:
Master_Host: 10.213.211.170
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos:1572
Relay_Log_File: ip-10-213-212-194-relay-bin.000005
Relay_Log_Pos: 1782
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Errno: 13114
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log : 'could not find next log; the first event '' at 4, the last event read from '/data/Dwight/binlog/binlog.000002' at 1572, the last byte read from 'data/Dwight/binlog/binlog.000002' at 1572'
Last_SQL_Errno: 0
Last_SQL_Error:

발생 조건

현재까지 생성된 binlog 파일이 첫번째 파일 일때, Reset Master 를 수행하는 경우

요약

마스터 서버에서의 Reset Master 수행 시 Slave에서 리플리케이션이 정상 상태가 아니지만, show slave status에서는 정상적으로 보이는 상태가 발생
이 문제상황이 지속되면 발생하는 에러들은 Binlog 파일이 동일한 상태일때, GTID값과 Read_Master_Log_Pos( binlog 포지션) 값과 연관이 있음

* GTID 구성 시에도 IO Thread 초기화 이후에는 non-GTID 모드와 동일하게 Binlog File / Position 기반으로 동작

* 리셋마스터 수행 시 발생 시키는 트랜잭션에 따라 다양한 에러가 발생 가능함

- 에러 Case

	상세 내용	에러 메시지
1	마스터 서버 Reset Master 이후 발행된 트랜잭션이 기존의 Binlog position을 넘어 섰을 때	Last_IO_Errno: 13114 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'I/O error reading log event; the first event '' at 4, the last event read from '/data/Dwight/binlog/binlog.000001' at 2677, the last byte read from '/data/Dwight/binlog/binlog.000001' at 2677.'
2	마스터 서버 Reset Master 이후 발행된 트랜잭션이 기존의 position보다 작고, GTID도 작을 때	Last_IO_Errno: 13114 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replicated to the slave. Suggest to replicate any transactions that master has rolled back from slave to master, and/or commit empty transactions on master to account for transactions that have been'
3	마스터 서버 Reset Master 이후 발행된 트랜잭션이 기존의 posision 보다 작지만, GTID가 더 클때	GTID 및 Position 초과로 인한 에러메시지가 발생하지 않지만 데이터 정합성 이슈 발생

- 에러 Case 상세

구분을 위해 Master에서 수행한 내용은 파란색 글씨로, Slave에서 수행한 내용은 초록색 글씨로 표시하였습니다.

Case 1) 마스터 서버 Reset Master 이후 발행된 트랜잭션의 포지션이 지금까지 복제된 Binlog Position을 넘어섰을 때 (GTID 관계없음)

Master 서버에 10개 트랜잭션 삽입

insert into gtid values (1); show master status; select * from mysql.gtid_executed;
insert into gtid values (2); show master status;
insert into gtid values (3); show master status;
insert into gtid values (4); show master status;
insert into gtid values (5); show master status;
insert into gtid values (6); show master status;
insert into gtid values (7); show master status;
insert into gtid values (8); show master status;
insert into gtid values (9); show master status;
insert into gtid values (10); show master status;

## 수행 결과

mysql> insert into gtid values (1); show master status; select * from mysql.gtid_executed;
Query OK, 1 row affected (0.01 sec)

...
...
...

mysql> insert into gtid values (10); show master status; select * from mysql.gtid_executed;
Query OK, 1 row affected (0.00 sec)

+---------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------------------------------+
| binlog.000001 | 2907 | | | 059417b0-6f07-11ed-9907-02b33e34566e:1-10 |
+---------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

10개 트랜잭션 삽입 후 Slave 서버상태 확인

-- Master 트랜잭션 삽입 이후
show slave status\G show master status\G

## 수행 결과
mysql> show slave status\G show master status;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.213.211.170
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 2907
Relay_Log_File: ip-10-213-212-194-relay-bin.000003
Relay_Log_Pos: 3117
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 2907
Relay_Log_Space: 3519
Retrieved_Gtid_Set: 059417b0-6f07-11ed-9907-02b33e34566e:1-10
Executed_Gtid_Set: 059417b0-6f07-11ed-9907-02b33e34566e:1-10
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)

+---------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------------------------------+
| binlog.000001 | 2927 | | | 059417b0-6f07-11ed-9907-02b33e34566e:1-10 |
+---------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

Master 서버에서 Reset Master 수행

reset master; show master status;

###수행 결과###

mysql> reset master; show master status;
Query OK, 0 rows affected (0.01 sec)

Reset Master 이후 Master 서버에서 트랜잭션 삽입

insert into gtid values (11); show master status; select * from mysql.gtid_executed;
insert into gtid values (12); show master status;
insert into gtid values (13); show master status;
insert into gtid values (14); show master status;
insert into gtid values (15); show master status;
insert into gtid values (16); show master status;
insert into gtid values (17); show master status;
insert into gtid values (18); show master status;
insert into gtid values (19); show master status;
insert into gtid values (20); show master status; select * from mysql.gtid_executed;
insert into gtid values (21); show master status;

###수행결과###

mysql> insert into gtid values (11); show master status; select * from mysql.gtid_executed;
Query OK, 1 row affected (0.00 sec)

Empty set (0.00 sec)

...

mysql> insert into gtid values (20); show master status; select * from mysql.gtid_executed;
Query OK, 1 row affected (0.01 sec)

+---------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------------------------------+
| binlog.000001 | 2907 | | | 059417b0-6f07-11ed-9907-02b33e34566e:1-10 |
+---------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 059417b0-6f07-11ed-9907-02b33e34566e | 1 | 4 |
| 059417b0-6f07-11ed-9907-02b33e34566e | 5 | 5 |
| 059417b0-6f07-11ed-9907-02b33e34566e | 6 | 7 |
| 059417b0-6f07-11ed-9907-02b33e34566e | 8 | 8 |
| 059417b0-6f07-11ed-9907-02b33e34566e | 9 | 9 |
+--------------------------------------+----------------+--------------+
5 rows in set (0.00 sec)
mysql> insert into gtid values (21); show master status;
Query OK, 1 row affected (0.00 sec)

+---------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------------------------------+
| binlog.000001 | 3182 | | | 059417b0-6f07-11ed-9907-02b33e34566e:1-11 |
+---------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

Reset Master 이후 10개 트랜잭션 삽입 후 Slave 서버상태 확인

--> 마스터의 변경된 GTID 값을 Slave는 알지 못하고 있음( 정상 상태로 보임)

show slave status\G show master status\G

###수행 결과###

mysql> show slave status\G show master status;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.213.211.170
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 2907
Relay_Log_File: ip-10-213-212-194-relay-bin.000003
Relay_Log_Pos: 3117
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 2907
Relay_Log_Space: 3519
Retrieved_Gtid_Set: 059417b0-6f07-11ed-9907-02b33e34566e:1-10
Executed_Gtid_Set: 059417b0-6f07-11ed-9907-02b33e34566e:1-10
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)

+---------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------------------------------+
| binlog.000001 | 2927 | | | 059417b0-6f07-11ed-9907-02b33e34566e:1-10 |
+---------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

Reset Master 이후 11개 트랜잭션 삽입 후 Slave 서버상태 확인

--> 마스터에서 보낸 binlog position이 지금까지 반영한 position을 넘어서자 마자 에러 발생

show slave status\G show master status\G

###수행결과###

mysql> show slave status\G show master status;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.213.211.170
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 2907
Relay_Log_File: ip-10-213-212-194-relay-bin.000003
Relay_Log_Pos: 3117
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: No
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 2907
Relay_Log_Space: 3519
Last_IO_Errno: 13114
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'I/O error reading log event; the first event '' at 4, the last event read from '/data/Dwight/binlog/binlog.000001' at 2907, the last byte read from '/data/Dwight/binlog/binlog.000001' at 2907.'
Master_UUID: 059417b0-6f07-11ed-9907-02b33e34566e
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
Retrieved_Gtid_Set: 059417b0-6f07-11ed-9907-02b33e34566e:1-10
Executed_Gtid_Set: 059417b0-6f07-11ed-9907-02b33e34566e:1-10

1 row in set, 1 warning (0.01 sec)

+---------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------------------------------+
| binlog.000001 | 2927 | | | 059417b0-6f07-11ed-9907-02b33e34566e:1-10 |
+---------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

Case 2) 마스터 서버 Reset Master 이후 발행된 트랜잭션의 포지션이 지금까지 복제된 Binlog Position보다 작고, GTID도 작을 때

초기 테이블 셋팅

사용 프로시저 ( 1000개의 데이터 Insert)

Master 서버에 4개 트랜잭션 삽입

-- Reset Master 전 다량의 트랜잭션 발생을 위한 프로시저 수행
call kk();
insert into gtid values (1); show master status; select * from mysql.gtid_executed;
insert into gtid values (2); show master status;
insert into gtid values (3); show master status;

## 수행 결과

mysql> call kk();
Query OK, 1 row affected (4.42 sec)

mysql> insert into gtid values (1); show master status; select * from mysql.gtid_executed;
Query OK, 1 row affected (0.01 sec)

+---------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+---------------------------------------------+
| binlog.000001 | 288432 | | | 059417b0-6f07-11ed-9907-02b33e34566e:1-1001 |
+---------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

...

mysql> insert into gtid values (3); show master status;
Query OK, 1 row affected (0.01 sec)

+---------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+---------------------------------------------+
| binlog.000001 | 288982 | | | 059417b0-6f07-11ed-9907-02b33e34566e:1-1003 |
+---------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

4개 트랜잭션 삽입 후 Slave 서버상태 확인

-- Master 트랜잭션 삽입 이후
show slave status\G show master status\G

###트랜잭션 삽입 이후 의 결과###

mysql> show slave status\G show master status;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.213.211.170
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 288982
Relay_Log_File: ip-10-213-212-194-relay-bin.000003
Relay_Log_Pos: 289192
Relay_Master_Log_File: binlog.000001
    Slave_IO_Running: Yes
Slave_SQL_Running: Yes
  Exec_Master_Log_Pos: 288982
Relay_Log_Space: 289594
    Retrieved_Gtid_Set: 059417b0-6f07-11ed-9907-02b33e34566e:1-1003
Executed_Gtid_Set: 059417b0-6f07-11ed-9907-02b33e34566e:1-1003
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)

+---------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+---------------------------------------------+
| binlog.000001 | 290988 | | | 059417b0-6f07-11ed-9907-02b33e34566e:1-1003 |
+---------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

Master 서버에서 Reset Master 수행

reset master; show master status;

###수행 결과###

mysql> reset master; show master status;
Query OK, 0 rows affected (0.01 sec)

Reset Master 이후 Master 서버에서 트랜잭션 삽입

insert into gtid values (1); show master status; 
insert into gtid values (1); show master status;
insert into gtid values (1); show master status;
insert into gtid values (1); show master status;
insert into gtid values (1); show master status;
insert into gtid values (1); show master status;
insert into gtid values (1); show master status;
insert into gtid values (1); show master status; 
...
...
--지속적으로 삽입

###수행결과###

...

mysql> insert into gtid values (1); show master status;
Query OK, 1 row affected (0.00 sec)

+---------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------------------------------+
| binlog.000001 | 17757 | | | 059417b0-6f07-11ed-9907-02b33e34566e:1-64 |
+---------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into gtid values (1); show master status;
Query OK, 1 row affected (0.01 sec)

+---------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------------------------------+
| binlog.000001 | 18582 | | | 059417b0-6f07-11ed-9907-02b33e34566e:1-67 |
+---------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

Reset Master 이후 트랜잭션 삽입하면서 Slave 서버상태 확인

--> 슬레이브 서버의 GTID 값이 마스터보다 커졌다는 IO Thread 에러 발생, 여러번 테스트 시에도 해당 메시지가 언제 발생하는지는 일정하지 않음. (60번대, 80번대 , 40번대) 일정 주기 없음

###수행결과###

...

mysql> show slave status\G show master status;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.213.211.170
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 288982
Relay_Log_File: ip-10-213-212-194-relay-bin.000003
Relay_Log_Pos: 289192
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replicated to the slave. Suggest to replicate any transactions that master has rolled back from slave to master, and/or commit empty transactions on master to account for transactions that have been'
Retrieved_Gtid_Set: 059417b0-6f07-11ed-9907-02b33e34566e:1-1003
Executed_Gtid_Set: 059417b0-6f07-11ed-9907-02b33e34566e:1-1003
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)

+---------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+---------------------------------------------+
| binlog.000001 | 290988 | | | 059417b0-6f07-11ed-9907-02b33e34566e:1-1003 |
+---------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

Case 3) 마스터 서버 Reset Master 이후 발행된 트랜잭션의 포지션이 지금까지 복제된 Binlog Position보다 작고, GTID가 더 클 때

초기 테이블 셋팅

Master 서버에 4개 트랜잭션 삽입

-- 한개의 트랜잭션에 많은 Binlog Position를 변경시키기 위한 CTAS
create table dwight as select * from aaa; show master status; select * from mysql.gtid_executed;
insert into gtid values (1); show master status; select * from mysql.gtid_executed;
insert into gtid values (2); show master status;
insert into gtid values (3); show master status;

## 수행 결과

mysql> create table dwight as select * from aaa; show master status; select * from mysql.gtid_executed;
Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0

Empty set (0.00 sec)

mysql>
mysql> insert into gtid values (1); show master status; select * from mysql.gtid_executed;
Query OK, 1 row affected (0.00 sec)

+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 | 1968 | | | 059417b0-6f07-11ed-9907-02b33e34566e:1-2 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

Empty set (0.00 sec)

mysql> insert into gtid values (2); show master status;
Query OK, 1 row affected (0.01 sec)

+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 | 2243 | | | 059417b0-6f07-11ed-9907-02b33e34566e:1-3 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into gtid values (3); show master status;
Query OK, 1 row affected (0.01 sec)

-- Master 트랜잭션 삽입 이후
show slave status\G show master status\G

###트랜잭션 4개 삽입 이후 의 결과###

*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.213.211.170
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
      Read_Master_Log_Pos: 2518
Relay_Log_File: ip-10-213-212-194-relay-bin.000003
Relay_Log_Pos: 2728
Relay_Master_Log_File: binlog.000001
    Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 2518
Relay_Log_Space: 3130
   Retrieved_Gtid_Set: 059417b0-6f07-11ed-9907-02b33e34566e:1-4
Executed_Gtid_Set: 059417b0-6f07-11ed-9907-02b33e34566e:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)

*************************** 1. row ***************************
File: binlog.000001
Position: 2545
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 059417b0-6f07-11ed-9907-02b33e34566e:1-4
1 row in set (0.00 sec)

Master 서버에서 Reset Master 수행

reset master; show master status;

###수행 결과###

mysql> reset master; show master status;
Query OK, 0 rows affected (0.01 sec)

Reset Master 이후 Master 서버에서 트랜잭션 삽입

-- 기존에 삽입한 트랜잭션 4개 보다 많은 8개의 트랜잭션 삽입
insert into gtid values (11); show master status; select * from mysql.gtid_executed;
insert into gtid values (12); show master status;
insert into gtid values (13); show master status;
insert into gtid values (14); show master status;
insert into gtid values (15); show master status;
insert into gtid values (16); show master status;
insert into gtid values (17); show master status;
insert into gtid values (18); show master status; select * from mysql.gtid_executed;

###수행결과###

mysql> insert into gtid values (11); show master status; select * from mysql.gtid_executed;
Query OK, 1 row affected (0.01 sec)

Empty set (0.00 sec)

...

mysql> insert into gtid values (18); show master status;
Query OK, 1 row affected (0.00 sec)

+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 | 2357 | | | 059417b0-6f07-11ed-9907-02b33e34566e:1-8 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

Reset Master 이후 8개 트랜잭션 삽입 후 Slave 서버상태 확인

--> 마스터 서버에서 수행된 GTID 값이 슬레이브보다 커졌지만, 리플리케이션 에러가 발생하지 않고 트랜잭션도 적용되지 않음

show slave status\G show master status\G

###수행 결과###

mysql> show slave status\G show master status;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.213.211.170
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 2518
Relay_Log_File: ip-10-213-212-194-relay-bin.000003
Relay_Log_Pos: 2728
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Retrieved_Gtid_Set: 059417b0-6f07-11ed-9907-02b33e34566e:1-4
Executed_Gtid_Set: 059417b0-6f07-11ed-9907-02b33e34566e:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)

+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 | 2545 | | | 059417b0-6f07-11ed-9907-02b33e34566e:1-4 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

Slave 서버 Slave Restart 수행

-> 슬레이브를 재기동하게 되면 마스터의 GTID에서 반영되지 않은 값을 적용하기 때문에 1-8번까지 트랜잭션 적용

stop slave; start slave;
show slave status\G show master status;

###수행결과###

mysql> stop slave; start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show slave status\G show master status;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.213.211.170
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 2357
Relay_Log_File: ip-10-213-212-194-relay-bin.000004
Relay_Log_Pos: 1551
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Retrieved_Gtid_Set: 059417b0-6f07-11ed-9907-02b33e34566e:1-8
Executed_Gtid_Set: 059417b0-6f07-11ed-9907-02b33e34566e:1-8
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)

+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 | 3653 | | | 059417b0-6f07-11ed-9907-02b33e34566e:1-8 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

데이터 정합성 확인

-> Reset Master 이후 발행한 1-4번까지의 GTID가 반영되지 않았으며 데이터 불일치 발생

Master 서버

show master status; select * from gtid;

###수행 결과###

mysql> show master status; select * from gtid;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 | 2357 | | | 059417b0-6f07-11ed-9907-02b33e34566e:1-8 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
+------+
11 rows in set (0.00 sec)

Slave 서버

show master status; select * from gtid;

###수행 결과###

mysql> show master status; select * from gtid;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 | 3653 | | | 059417b0-6f07-11ed-9907-02b33e34566e:1-8 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 15 |
| 16 |
| 17 |
| 18 |
+------+
7 rows in set (0.00 sec)

'MySQL & MariaDB' 카테고리의 다른 글

[Aurora MySQL] 쿼리 수행중 the table tmp is full Error 발생 (0)	2023.08.15
MySQL Major/Minor Upgrade (0)	2022.12.27
MySQL GTID 복제 환경에서 Reset Master/Reset Slave 명령어 (0)	2022.12.27
MySQL Multi Source Replication(MSR) 실습 (0)	2022.12.26
MySQL GTID Replication 실습 (0)	2022.12.22

기록 저장소

MySQL GTID 복제 환경에서 Reset Master 수행 시 발생 장애 케이스

글을 작성하기 전에

MySQL GTID 복제 환경에서 Reset Master 수행 시 발생 케이스

예외 조건

발생 조건

요약

Case 1) 마스터 서버 Reset Master 이후 발행된 트랜잭션의 포지션이 지금까지 복제된 Binlog Position을 넘어섰을 때 (GTID 관계없음)

Case 2) 마스터 서버 Reset Master 이후 발행된 트랜잭션의 포지션이 지금까지 복제된 Binlog Position보다 작고, GTID도 작을 때

Case 3) 마스터 서버 Reset Master 이후 발행된 트랜잭션의 포지션이 지금까지 복제된 Binlog Position보다 작고, GTID가 더 클 때

'MySQL & MariaDB' 카테고리의 다른 글

티스토리툴바

MySQL GTID 복제 환경에서 Reset Master 수행 시 발생 장애 케이스

글을 작성하기 전에

MySQL GTID 복제 환경에서 Reset Master 수행 시 발생 케이스

예외 조건

발생 조건

요약

Case 1) 마스터 서버 Reset Master 이후 발행된 트랜잭션의 포지션이 지금까지 복제된 Binlog Position을 넘어섰을 때 (GTID 관계없음)

Case 2) 마스터 서버 Reset Master 이후 발행된 트랜잭션의 포지션이 지금까지 복제된 Binlog Position보다 작고, GTID도 작을 때

Case 3) 마스터 서버 Reset Master 이후 발행된 트랜잭션의 포지션이 지금까지 복제된 Binlog Position보다 작고, GTID가 더 클 때

'MySQL & MariaDB' 카테고리의 다른 글

'MySQL & MariaDB' Related Articles

티스토리툴바