서로 다른 binlog_format 간 복제

2024/08 에 작성한 글을 옮깁니다.

what is binlog_format ?

MySQL의 바이너리 로그(binlog)를 어떤 형식으로 기록할지 결정하는 설정입니다. 주로 Replication과 복구(Point-in-time recovery)에 사용됩니다.

총 3가지 포맷이 있습니다.

1. STATEMENT

-- SQL 문장 자체를 그대로 기록
UPDATE OrganizationUsages SET usage_bytes=162859274423 WHERE usage_id=355...

장점: binlog 크기 작음
단점: NOW(), RAND() 같은 비결정적 함수는 master/slave 결과가 달라질 수 있음

2. ROW (기본값, MySQL 8.0+)

-- 변경된 행의 before/after 값을 기록
# before: usage_bytes=100, last_updated_at='2026–03–31 10:00:00'
# after: usage_bytes=162859274423, last_updated_at='2026–03–31 10:06:49'

장점: 정확함, 비결정적 함수도 안전
단점: 변경 행이 많을수록 binlog 크기가 커짐

3. MIXED

평소엔 STATEMENT, 비결정적 함수 감지 시 자동으로 ROW로 전환
두 방식의 절충안

why this test shoud be go on ?

운영중, 기존 장비에 신규장비 복제를 붙이는 작업이 진행되어야했습니다.
그래서 기존 장비와 신규장비의 binlog_format 이 달라도 복제가 가능한지 확인 필요했습니다.

그에 따라 장비간 서로 다른 binlog_format 에 따른 복제 테스트를 진행했습니다.

how test is work ?

1. binlog_format 에 따라 binlog 가 어떻게 기록되는지 확인

21:20:35)[binlog_format_test]TEST]insert into test (id, name) values (111,'aaa');
Query OK, 1 row affected (0.00 sec)

# mysqlbinlog 유틸리티로 변환
mysqlbinlog --verbose --database=binlog_format_test --start-datetime="2024-07-21 21:15" --stop-datetime="2024-07-21 21:25" "/home1/irteam/dba/lhw/mysql-bin.000048" > mysqlbinlog.sql


# binlog 발췌 (STATEMENT 로 기록)
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=45/*!*/;
BEGIN
/*!*/;
# at 759744564
#240721 21:20:41 server id 7855  end_log_pos 759744715 CRC32 0xa89831b2   Query thread_id=10167115  exec_time=0 error_code=0
SET TIMESTAMP=1721564441/*!*/;
insert into test (id, name) values (111,'aaa')
/*!*/;
# at 759744715

2. binlog_format 변경법

21:41:41)[binlog_format_test]TEST]select @@global.binlog_format;
+------------------------+
| @@global.binlog_format |
+------------------------+
| MIXED                  |
+------------------------+
1 row in set (0.00 sec)



21:41:19)[binlog_format_test]TEST]stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

21:41:22)[binlog_format_test]TEST]SET GLOBAL binlog_format = 'ROW';
Query OK, 0 rows affected (0.00 sec)

21:41:25)[binlog_format_test]TEST]start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)



21:41:41)[binlog_format_test]TEST]select @@global.binlog_format;
+------------------------+
| @@global.binlog_format |
+------------------------+
| ROW                    |
+------------------------+
1 row in set (0.00 sec)

the result is …

• MIXED : safe 쿼리는 Statement로 기록, unsafe 쿼리는 Row로 기록
• STATEMENT : safe 쿼리, unsafe 쿼리 모두 Statement 로 기록
• ROW : safe 쿼리, unsafe 쿼리 모두 Row 로 기록

Slave의 binlog_format은 Slave 자체 기록 방식에만 영향을 미치며, 복제 오류는 “Master ROW + Slave Statement + Binlog 활성화” 조합에서만 발생한다.

잠깐 ! 🤚 마지막으로 “설정 변경이 즉각적으로, 전역적으로 반영될 것”이라는 오해를 바로잡습니다.

1. SET GLOBAL binlog_format = 'ROW' 로 변경해도, 이미 열려있는 커넥션(session)에는 즉시 적용되지 않습니다. 새로 연결되는 세션부터 ROW가 적용됩니다. 즉, GLOBAL 변경의 효과는 새 세션부터 라는 뜻입니다.

2. Slave는 Master의 binlog를 받아 relay log에 그대로 복사합니다. 이때 Slave 자신의 binlog_format 설정과 무관하게, 받은 형식 그대로 relay log에 씁니다. Slave의 binlog_format이 영향을 미치는 건, Slave 자신이 직접 실행한 쿼리를 자신의 binlog에 기록할 때뿐입니다.

Master binlog: ROW 형식으로 기록
       ↓ 그대로 전달
Slave relay log: ROW 형식 그대로 저장  ← Slave의 binlog_format 설정 무관
       ↓ 적용(SQL thread)
Slave DB 반영