MySQL의 binary log
MySQL의 binary log는 데이터 변경을 기록하는 방식에 따라 세 가지 형식(ROW, STATEMENT, MIXED)을 지원한다. 각 형식의 특징과 차이점은 다음과 같다.
- ROW
- 각 테이블 행(Row) 단위로 변경 내용을 binary log에 기록한다.
- 어떤 SQL 문이 실행되었는지가 아닌, 어떤 행이 어떻게 변경되었는지를 기록한다.
- 데이터 변경의 정확성이 높아 replication 시 데이터 불일치 문제를 방지할 수 있다.
- 대량의 데이터 변경 시 binary log의 크기가 커질 수 있다.
- MysQL 5.7.7 버전부터 기본 값이다.
- STATEMENT
- 실행된 SQL 문 자체를 binary log에 기록한다.
- DB Replication 시에 SQL 문이 replica에서 그대로 실행된다.
- 로그 크기가 작고, 단순한 쿼리일 때 효율적이다.
- 비결정적(Nondeterministic) 쿼리나 환경에 따라 달라질 수 있는 쿼리의 경우, replication 시 데이터 불일치가 발생할 수 있다.
- Nondeterministic SQL: 동일한 입력 매개변수를 사용하더라도 여러 번 실행하면 다른 결과를 생성할 수 있는 SQL 문이다. 이는 주로 데이터베이스 외부 요인에 의존하거나 시간이 지남에 따라 변경되는 함수가 존재하기 때문이다. Nondeterministic SQL은 replication 시에 데이터 불일치 문제가 발생하며 쿼리 성능에 영향을 줄 수 있다.
- MIXED
- 기본적으로 STATEMENT 방식을 사용하지만, 특정 경우(ex. 비결정적 쿼리)에는 ROW 방식으로 변환하여 binary log에 기록한다.
- STATEMENT와 ROW 방식의 장점을 결합하여 일반적인 쿼리는 효율적으로 기록하고, replication에 문제가 될 수 있는 쿼리는 안전하게 기록한다.
문제 정의
- 목표: RDS 클러스터 파라미터 그룹의 binlog_format 값을 ROW -> OFF로 변경한다.
- 수행: RDS 클러스터 파라미터 그룹 수정 후 Reader, Writer 인스턴스 재부팅
- pending-reboot -> in-sync
- 문제: MySQL 세션에 접속하여 binlog_format 값을 조회하면 여전히 ROW로 출력된다.
표준 MySQL은 binlog_format 값으로 OFF를 지원하지 않는다.
참고: 7.4.4.1 바이너리 로깅 형식
- MySQL에서 지원하는 binlog_format 값
- STATEMENT (MySQL 5.7.7 이전까지 default)
- ROW (MySQL 5.7.7 이후로 default)
- MIXED
MySQL 공식 문서에 따르면 SHOW VARIABLES LIKE 'binlog_format' 결과로 OFF를 반환하지 않는다. 즉, 파라미터 그룹의 설정 값으로 OFF가 존재하나, 서버 변수 값으로는 OFF를 반영할 수 없다.
AWS 공식문서에 따르면 클러스터 파라미터 그룹의 binlog_format=OFF로 설정하면 log_bin 값이 OFF가 되고, MySQL에서 binlog_format 값은 DB의 기본 값인 ROW가 된다고 한다.
When binary logging is disabled in Aurora MySQL (binlog_format = OFF), you can’t use features like AWS Database Migration Service (DMS) that depend on binary logs. However, SHOW VARIABLES LIKE 'binlog_format' will still return ROW.
Aurora MySQL에서는 클러스터 파라미터 그룹에서 binlog_format을 OFF로 설정할 수 있지만, 이는 binary logging 자체를 비활성화하는 동작이다.
반면에 MysQL에서는 binlog_format에 OFF 옵션이 존재하지 않고, binary logging을 사용하지 않기 위해서는 log_bin을 OFF로 설정해야 한다.
SHOW VARIABLES LIKE 'binlog_format'; -- ROW 출력
SHOW VARIABLES LIKE 'log_bin'; -- OFF 출력
권장 사항에 따르면
- Aurora MySQL: binlog_format=ROW 권장 (replication 불일치 방지)
- 표준 MySQL: MySQL 5.7.7 버전부터 binlog_format=ROW가 default 값이 되었다. 그리고 MySQL 8.0.34 버전부터 binlog_format 변수가 deprecated가 되었다.
log_bin과 binlog_format의 차이점
- log_bin
- 의미: binary log 기능 활성화 여부
- 타입: Boolean (ON, OFF)
- 역할: binary log 자체 활성화 여부
- Aurora MySQL에서 binlog_format=OFF 설정 시 MySQL의 log_bin 값이 OFF로 설정된다.
- binlog_format
- 의미: 활성화된 binary log를 어떤 형식으로 기록할지 여부
- 타입: ENUM (STATEMENT, ROW, MIXED)
- 역할: 로그 기록 방식 선택
- 비고: MySQL 8.0.34 기준으로 deprecated 되었다.
참고문서
- How do I turn on binary logging for my Aurora MySQL-Compatible cluster?
- Configuring Aurora MySQL binary logging for Single-AZ databases
- 7.4.4.1 Binary Logging Formats
