현재 진행하는 프로젝트는 Mysql 자동 백업 및 복구 시스템을 구현하는 것이 목적이다.
Debezium을 사용해 실시간 active DB -> standby DB 동기화가 이루어지는 상태이다.
active DB에 장애가 발생하면 standby DB로 failover가 진행되고 이후 업데이트는 standby DB에서 수행된다.
문제는 다시 active DB가 복구 되었을 때 active와 standby의 동기화가 다시 진행되어야 하는데, 어떤 시점부터 반영해야하는지를 확인하고, 해당 시점부터 복구를 진행할 수 있어야 한다.
이러한 문제를 해결하기 위해 GTID (Global Transaction Identifier) 를 도입에 대한 의견이 있었고, 이를 테스트 해보기로 했다.
GTID란
Global Transaction Identifier의 약자로 Transaction 마다 고유한 GTID를 가지게 된다.
GTID의 구조는 [sever uuid]:[transaction sequence Number] 의 형태로 uuid가 고유하기 때문에 모든 mysql server에서 transaction이 고유한 아이디를 가지고, 이를 사용하여 식별할 수 있게 됨을 의미한다.
기존 binlog 복제 방식은 로그 파일명 + offset으로 각 이벤트를 식별했다.
이 방식의 한계점은 Origin 서버에서만 유효하게 식별된다는 점이다. (복제된 서버는 같은 이벤트에 대해 다른 식별값을 가진다.)
GTID를 도입하면 소스 서버와 복제 서버 모두 동일한 이벤트에 대해 동일한 식별값을 가진다.
mysql DB에 GTID를 도입하고 해당 값을 기반으로 백업 및 복구를 간단히 수행해본다.
초기 설정
Docker compose를 사용해 Master, Replica DB 서버를 실행한다.
GTID를 사용하기 위해서 주의할 점은 server_id 값을 기준으로 uuid가 생성되기 때문에 server id가 고유해야한다.
# ./master/my.cnf
[mysqld]
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=mysql-bin
binlog_format=ROW
#./replica/my.cnf
[mysqld]
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=mysql-bin
binlog_format=ROW
gtid_mode=ON : gtid모드를 킨다.
enforce_gtid_consistency=ON : gtid 일관성을 해칠 수 있는 쿼리의 실행을 막는다.
ex ) 실행시점, 환경에 따라 결과가 다른 쿼리
CREATE TABLE new_table SELECT * FROM old_table;
DELETE FROM test_table LIMIT 10;- Docker compose
version: '3.8'
services:
mysql-master:
image: mysql:8.0
container_name: mysql-master
restart: always
ports:
- "3307:3306"
environment:
MYSQL_ROOT_PASSWORD:
volumes:
- ./master/my.cnf:/etc/mysql/conf.d/my.cnf
- master-data:/var/lib/mysql
- ./backup:/backup
mysql-replica:
image: mysql:8.0
container_name: mysql-replica
restart: always
ports:
- "3308:3306"
environment:
MYSQL_ROOT_PASSWORD:
volumes:
- ./replica/my.cnf:/etc/mysql/conf.d/my.cnf
- replica-data:/var/lib/mysql
- ./backup:/backup
volumes:
master-data:
replica-data:
목표 1 : GTID 적용 후 full dump 복구 (binlog 명령 사용)
docker compose container volume 초기화 후 빌드
docker compose down -v
docker compose up -d --build- master gtid status
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 | 197 | | | 6b2f4e09-7a6f-11f0-92d6-fa9c63d122e3:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+- test_db 생성 / Executed_Gtid_Set
5→6
mysql> CREATE DATABASE test_db;
#Query OK, 1 row affected (0.03 sec)
mysql> SHOW MASTER STATUS;
#Executed_Gtid_Set: 6b2f4e09-7a6f-11f0-92d6-fa9c63d122e3:1-6- table 생성 및 더미 데이터 추가
ysql> create table test_table (id bigint PRIMARY KEY, name varchar(40));
#Query OK, 0 rows affected (0.07 sec)
mysql> SHOW MASTER STATUS;
#Executed_Gtid_Set: 6b2f4e09-7a6f-11f0-92d6-fa9c63d122e3:1-7
mysql> INSERT INTO test_table VALUE (1, 'TEST_1');
#Query OK, 1 row affected (0.03 sec)
mysql> SHOW MASTER STATUS;
#Executed_Gtid_Set: 6b2f4e09-7a6f-11f0-92d6-fa9c63d122e3:1-8
mysqlbinlog사용.sql생성
mysqlbinlog --read-from-remote-source=BINLOG-DUMP-GTIDS -hlocalhost -P3307 -uroot -p --include-gtids='6b2f4e09-7a6f-11f0-92d6-fa9c63d122e3:6-10' mysql-bin.000001 --to-last-log --base64-output=DECODE-ROWS -vv --result-file="C:\var\backups\mysql\full.sql"--include-gtids,--exclude-gtids옵션으로 gtid 범위를 제한할 수 있다.- gtid:1-5는 기존 mysql 이 생성한 로그 (timezone관련) → gtid 범위를 6이후로 제한
include-gtid옵션 설정 시 존재하지 않는 범위인 경우 무시된다.
--base64-output=DECODE-ROWS -vv: 사람이 읽을 수 있는 형태로 디코딩 + 더 자세히
생성된 dump 파일 내 더미 데이터 관련 내용
SET @@SESSION.GTID_NEXT= '6b2f4e09-7a6f-11f0-92d6-fa9c63d122e3:6'/*!*/;
# at 274
#250816 16:08:51 server id 1 end_log_pos 391 CRC32 0xad250258 Query thread_id=8 exec_time=0 error_code=0 Xid = 8
SET TIMESTAMP=1755328131/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
CREATE DATABASE test_db
/*!*/;
# at 391
#250816 16:11:14 server id 1 end_log_pos 468 CRC32 0xaf724af0 GTID last_committed=1 sequence_number=2 rbr_only=no original_committed_timestamp=1755328274440004 immediate_commit_timestamp=1755328274440004 transaction_length=236
# original_commit_timestamp=1755328274440004 (2025-08-16 16:11:14.440004 ���ѹα� ǥ�ؽ�)
# immediate_commit_timestamp=1755328274440004 (2025-08-16 16:11:14.440004 ���ѹα� ǥ�ؽ�)
/*!80001 SET @@session.original_commit_timestamp=1755328274440004*//*!*/;
/*!80014 SET @@session.original_server_version=80043*//*!*/;
/*!80014 SET @@session.immediate_server_version=80043*//*!*/;
SET @@SESSION.GTID_NEXT= '6b2f4e09-7a6f-11f0-92d6-fa9c63d122e3:7'/*!*/;
# at 468
#250816 16:11:14 server id 1 end_log_pos 627 CRC32 0xb71ec17b Query thread_id=8 exec_time=0 error_code=0 Xid = 16
use `test_db`/*!*/;
SET TIMESTAMP=1755328274/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table test_table (id bigint PRIMARY KEY, name varchar(40))
/*!*/;
# at 627
#250816 16:12:34 server id 1 end_log_pos 706 CRC32 0xd8cfa062 GTID last_committed=2 sequence_number=3 rbr_only=yes original_committed_timestamp=1755328354180548 immediate_commit_timestamp=1755328354180548 transaction_length=306
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1755328354180548 (2025-08-16 16:12:34.180548 ���ѹα� ǥ�ؽ�)
# immediate_commit_timestamp=1755328354180548 (2025-08-16 16:12:34.180548 ���ѹα� ǥ�ؽ�)
/*!80001 SET @@session.original_commit_timestamp=1755328354180548*//*!*/;
/*!80014 SET @@session.original_server_version=80043*//*!*/;
/*!80014 SET @@session.immediate_server_version=80043*//*!*/;
SET @@SESSION.GTID_NEXT= '6b2f4e09-7a6f-11f0-92d6-fa9c63d122e3:8'/*!*/;
# at 706
#250816 16:12:34 server id 1 end_log_pos 784 CRC32 0x7d22b302 Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1755328354/*!*/;
BEGIN
/*!*/;
# at 784
#250816 16:12:34 server id 1 end_log_pos 851 CRC32 0x42d42e97 Table_map: `test_db`.`test_table` mapped to number 90
# has_generated_invisible_primary_key=0
# at 851
#250816 16:12:34 server id 1 end_log_pos 902 CRC32 0x55c98586 Write_rows: table id 90 flags: STMT_END_F
### INSERT INTO `test_db`.`test_table`
### SET
### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='TEST_1' /* VARSTRING(160) meta=160 nullable=1 is_null=0 */
# at 902
#250816 16:12:34 server id 1 end_log_pos 933 CRC32 0x87d88116 Xid = 19
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
dump file 기반 복구
cmd /c mysql -h localhost -P 3308 -u root -p --default-character-set=utf8mb4 < "C:/var/backups\mysql/full.sql"
- replica master status
mysql> show master status
-> ;
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| mysql-bin.000003 | 882 | | | 6b2c3358-7a6f-11f0-b3b8-aa787f3e7e70:1-5,
6b2f4e09-7a6f-11f0-92d6-fa9c63d122e3:6-8 |
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+mysql> use test_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test_table |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from test_table;
Empty set (0.00 sec)❗Trouble: row insert 이벤트는 반영이 안되는 문제
원인
dump 파일 내 Row Insert 정보는 주석처리 되어있음.
--base64-output=DECODE-ROWS -vv를 쓰면, row-based 이벤트를 사람이 읽게 디코딩해서 주석으로만 보여준다.
이 부분은 사람이 읽으라고 붙여준 주석이기 때문에 반영되지 않는다.지금 만든 `full.sql` 안에서 ``` ### INSERT INTO ... ### SET @1=... ```
해결 방법
파일을 만들 때도 디코딩 옵션(--base64-output=DECODE-ROWS -vv ) 빼고 생성 → 그 다음 적용.
# 1) 리플레이 가능 파일 생성 (BINLOG 블록 포함)
mysqlbinlog --read-from-remote-source=BINLOG-DUMP-GTIDS `
-h localhost -P 3307 -u root -p `
mysql-bin.000001 --to-last-log `
--result-file="C:\\var\\backups\\mysql\\replay.sql"
# 2) 적용
mysql -h localhost -P 3308 -u root -p < "C:\\var\\backups\\mysql\\replay.sql"목표 2 : 초기 복구 이후 데이터 업데이트 반영 (GTID 범위 지정)
현재 test_table 의 (1,'TEST_1') 까지 동기화 된 상태
MASTER 에 데이터 추가 후 해당 GTID에 대한 내용을 반영한다.
MASTER
mysql> INSERT INTO test_table VALUE (2, 'TEST_2');
Query OK, 1 row affected (0.02 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 | 1239 | | | 6b2f4e09-7a6f-11f0-92d6-fa9c63d122e3:1-9 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)GTID가 9로 증가
mysqlbinlog --read-from-remote-source=BINLOG-DUMP-GTIDS -hlocalhost -P3307 -uroot -p --include-gtids='6b2f4e09-7a6f-11f0-92d6-fa9c63d122e3:8-10' mysql-bin.000001 --to-last-log --result-file="C:\var\backups\mysql\full.sql"
cmd /c 'mysql -h localhost -P 3308 -u root -p < "C:\var\backups\mysql\full.sql"'GTID 범위를 8-10 로 설정하여 dump 생성 후 반영
Replica
mysql> show master status ;
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| mysql-bin.000001 | 1417 | | | 6b2c3358-7a6f-11f0-b3b8-aa787f3e7e70:1,
6b2f4e09-7a6f-11f0-92d6-fa9c63d122e3:6-9 |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from test_table;
+----+--------+
| id | name |
+----+--------+
| 1 | TEST_1 |
| 2 | TEST_2 |
+----+--------+gtid값이 업데이트 되면서 (2, 'TEST_2') 데이터도 동기화 되었음을 확인했다.
또 Replica 자체의 gtid가 생성된 것을 확인했다. dump 내용을 반영하는 이벤트 자체를 replica에서도 binlog에 기록한다.
만약 Master 와 Replica에서 둘 다 데이터를 삽입, 수정하는 경우
#Replica
mysql> INSERT INTO test_table VALUE(6, 'test_6');
#Master
mysql> INSERT INTO test_table VALUE (6, 'TEST_6');ERROR 1062 (23000) at line 134: Duplicate entry '6' for key 'test_table.PRIMARY'Duplicate key error 가 발생한다.
그럼 수정의 경우는
#Master
mysql> UPDATE test_table SET name = 'TEST_5_UPDATE' WHERE id = 5;
#Replica
mysql> UPDATE test_table SET name = 'test_5_update_replica' WHERE id = 5;
#dump 생성 후 Replica에 반영
#Replica
mysql> SELECT * FROM test_table;
+----+---------------+
| id | name |
+----+---------------+
| 1 | TEST_1 |
| 2 | TEST_2 |
| 3 | TEST_3 |
| 4 | TEST_4 |
| 5 | TEST_5_UPDATE |
| 6 | test_6 |
+----+---------------+Dump 파일에 있는 update문이 데이터를 덮어쓴다.
즉 Insert문의 경우 duplicate key error가 발생하지만 update는 이후에 실행된 정보로 수정된다.
GTID를 사용하면 여러대의 mysql 서버에서 각 트랜잭션 이벤트를 ID값으로 구분할 수 있다.
양쪽 DB 모두 데이터가 업데이트 되는 상황에서 정확히 동기화하기 위해서는 duplicate key와 같이 발생 가능한 문제들을 사전에 예방하거나, 발생 시 대처하는 로직이 필요할 것 같다.
