[MySQL] binlog_format

목차

---

1. binlog_format

 

MySQL의 bin log 형식은 --binlog-format 명령을 이용해 변경할 수 있습니다.

MySQL binlog 포맷은 STATEMENT, ROW, MIXED 세 가지가 있으며, 각 포맷은 변경된 데이터를 기록하는 방식이 다르고 성능과 데이터의 정확성에 영향을 줍니다.

 

• STATEMENT
• ROW
• MIXED

 

포맷	특징	장점	단점
STATEMENT	SQL 문 자체를 기록	속도가 빠르고 로그 크기가 작음	쿼리에 AUTO_INCREMENT나 비결정적 함수가 포함된 경우 복제DB에서 데이터가 달라질 수 있음
ROW	변경된 행의 실제 값을 기록	정확한 데이터 복제를 보장	로그 크기 크고 성능 저하가 발생할 수 있음
MIXED	보통 STATEMENT로 기록 비결정적 함수 포함 시 ROW로 기록	STATEMENT/ROW 모두 사용	내부 작동 방식이 명확하지 않을 수 있음

 

binlog-format은  서버에서 바이너리 로깅이 활성화될 때 적용되며, log_bin 시스템 변수가 ON으로 설정된 경우에 해당합니다.

 

MySQL 8.4에서는 기본적으로 바이너리 로깅이 활성화되어 있으며, 기본적으로 ROW 기반 형식을 사용합니다.

MySQL 8.0.34 버전 이후로 binlog_format 변수는 deprecated되어 향후 MySQL 버전에서 제거될 예정입니다.

새로운 MySQL 에서는 다른 로깅방식이 제거되고 ROW 기반 로깅만 지원될 것으로 보입니다.

 

binlog_format 변수는 MySQL 서버 시작 시 또는 실행 중에 설정할 수 있지만, 특정 조건에서는 실행 중 변경이 불가능하거나 변경 시 복제가 실패할 수 있습니다.

기본값은 ROW이나, NDB Cluster에서는 기본값이 MIXED로 설정됩니다. NDB Cluster에서는 STATEMENT 기반 복제를 지원하지 않습니다.

 

MIXED가 설정된 경우 기본적으로 STATEMENT 기반 복제를 사용하지만, ROW 기반 복제가 정확한 결과를 보장하는 경우에는 자동으로 ROW 기반 복제를 사용합니다. 예를 들어, 로드 가능한 함수 또는 UUID() 함수가 포함된 경우 ROW 기반 복제가 사용됩니다.

 

다음과 같은 상황에서는 동적으로 binlog-format 값을 변경할 수 없습니다.

• 저장 함수 또는 트리거 내에서 binlog-format 변경 불가
• 세션에서 임시 테이블이 열려 있는 경우, 해당 세션에서 binlog-format 변경 불가
  (SET @@SESSION.binlog_format)
• 하나 이상의 복제 채널에서 임시 테이블이 열려 있는 경우, 전역 binlog-format 변경 불가
  (SET @@GLOBAL.binlog_format, SET @@PERSIST.binlog_format)
• 하나 이상의 복제 채널 적용 스레드가 실행 중인 경우, 전역 binlog-format 변경 불가
  (SET @@GLOBAL.binlog_format 또는 SET @@PERSIST.binlog_format)

위 경우에 복제 형식을 변경하려 하면 오류가 발생합니다.

하지만 SET @@PERSIST_ONLY.binlog_format을 사용하면 언제든지 복제 형식을 변경할 수 있습니다.

이 설정은 실행 중인 글로벌 시스템 변수 값을 수정하지 않으며, 서버를 재시작한 후에만 적용됩니다.

 

임시 테이블은 binlog-format  = STATEMENT 인 경우에만 blnlog에 로깅되며, ROW 및 MIXED 기반 복제에서는 로깅되지 않으므로 임시 테이블이 생성되어있는 상태라면 binlog-format 을 변경하지 않아야 합니다.

 

마스터DB에서 binlog-format을 변경해도 슬레이브DB의  binlog-format이 자동으로 변경되지는 않습니다.

리플리케이션 도중 슬레이브의 binlog가 활성화된 상태에서 마스터와 다른 로그포맷을 사용하면 문제가 발생할 수 있습니다.

예를 들어 마스터가 ROW 또는 MIXED 형식을 사용하지만 슬레이브가 STATEMENT 형식을 사용하면 복제가 실패할 수 있습니다. 복제본 서버는 ROW 형식의 바이너리 로그 항목을 STATEMENT 형식으로 변환할 수 없기 때문입니다.

 

 

다음 서버 옵션의 동작은 바이너리 로그 형식에 따라 달라질 수 있습니다.

• --replicate-do-db
• --replicate-ignore-db
• --binlog-do-db
• --binlog-ignore-db

 

 

1) STATEMENT

STATEMENT는 실행된 SQL쿼리 자체를 로그에 기록하는 방식입니다.

binlog_format이 STATEMNET 인 경우 SQL문 자체가 binlog에 저장됩니다.

따라서 비결정적 함수(NOW, RAND, UUID)나 AUTO_INCREMENT가 포함된 경우 실행할 때마다 값이 달라져 마스터와 슬레이브간의 데이터 불일치가 발생할 수 있습니다.

-- 세션 binlog_format 을 STATEMENT 로 설정
SET SESSION binlog_format = 'STATEMENT';

-- 실제로 실행된 쿼리
UPDATE users SET last_login = NOW() WHERE id = 1;

-- 저장된 binlog 를 mysqlbinlog로 출력
# at 150
# 2025-02-24 10:00:00 server id 1  end_log_pos 300 CRC32 0x12345678
UPDATE users SET last_login = NOW() WHERE id = 1;

 

 

2) ROW

ROW는 변경된 레코드의 데이터 자체를 기록하는 방식입니다.

모든 변경사항을 행 단위로 binlog 에 기록하기 때문에 데이터 정합성이 높습니다.

그러나 변경된 모든 데이터를 binlog 에 기록하므로 binlog의 크기가 급격히 증가할 수 있고, 다른 모드에 비해 속도가 느립니다.

-- 세션 binlog_format 을 ROW 로 설정
SET SESSION binlog_format = 'ROW';

-- 실제로 실행된 쿼리
UPDATE users SET last_login = NOW() WHERE id = 1;

-- 저장된 binlog 를 mysqlbinlog로 출력
# at 150
 2025-02-24 10:00:00 server id 1  end_log_pos 300 CRC32 0x12345678
### UPDATE users
### WHERE
###   @1=1
### SET
###   @2='2025-02-24 10:00:00'

 

 

3) MIXED

MIXED는 STATEMENT와 ROW모드를 혼합하여 사용합니다.
기본적으로 STATEMENT 모드처럼 동작하지만, 비결정적 함수가 포함된 경우 자동으로 ROW 모드로 전환하여 기록합니다.

일반적인 환경에서 가장 많이 설정하는 포맷으로, ROW와 STATEMENT 모드의 이점을 모두 사용할 수 있습니다.

-- 세션 binlog_format 을 MIXED 로 설정
SET SESSION binlog_format = 'MIXED';