[MySQL] 바이너리 로그의 포맷은 왜 ROW를 권장하는가?

바이너리 로그의 포맷에 대해 이야기하기에 앞서서 바이너리 로그에 대해 정의해야 한다.

 

바이너리 로그(Binary Log)

---

바이너리 로그(binary log)는 MySQL에서 사용하는 기능 중 하나로, 데이터베이스에서 수행된 모든 데이터 변경 작업(INSERT, UPDATE, DELETE 등)을 기록하는 데 사용된다.

바이너리 로그에는 다음과 같은 중요정보들이 포함된다.

• SQL 쿼리문(또는 변화의 결과물 - ROW)
• 쿼리의 실행시각 및 쿼리 대상 데이터베이스에 대한 정보

그렇다면 왜 바이너리 로그를 사용할까?

• 복제(Replication): 바이너리 로그는 MySQL Replication의 핵심 요소이다. Master 서버의 바이너리 로그에 쓰인 내용이 Slave 서버에 반영되어 복제 프로세스가 이루어진다.
• 데이터 복구(Point-In-Time Recovery): 예기치 못한 데이터 손실이 발생했을 경우, Binary Log에 기록된 작업들을 재실행 함으로써 특정 시점으로 데이터베이스 상태를 되돌릴 수 있다.

바이너리 로그를 사용함으로써 데이터 복구와 복제라는 두 가지 중요한 일을 수행할 수 있지만, 디스크 공간을 소비하고 I/O 성능에 약간의 부하를 준다는 점을 유의해야한다.

 

바이너리 로그 포맷(Binary Log Format)

---

MySQL의 바이너리 로그(Binary Log)는 세 가지 주요 로깅 포맷을 제공한다.

Statement-based (--binlog-format=STATEMENT)

• 데이터 변경 작업을 수행하는 SQL 쿼리문을 바이너리 로그에 저장한다.
• Replication시 복제본 서버는 원본 서버의 바이너리 로그를 읽어들여 그 안에 기록된 SQL 문장들을 순서대로 재실행하여 Replication을 수행한다.
• SQL 쿼리문을 바이너리 로그에 저장하므로 감사에 용이하다.
• 트랜잭션 격리수준이 Repeatable Read 이상이어야 한다.

Row-based (--binlog-format=ROW (기본값))

• 바이너리 로그에서 개별 테이블 행이 어떻게 영향을 받는지를 로그에 기록한다.
• Replication시 SQL 문장을 해석하거나 재실행하는 것이 아니라, 단순히 특정 레코드의 변경 사항을 적용하여 Replication을 수행한다.

바이너리 로그의 내용은 일반 텍스트 형태로 기록되지 않고 바이너리 형식으로 보관되기 때문에, 직접적으로 내용을 읽을 수는 없지만, mysqlbinlog 도구를 사용하면 바이너리 로그 파일의 내용을 텍스트 형태로 변환하여 확인할 수 있다.

Statement-based

# at 141
#191024 15:58:08 server id 1  end_log_pos 236   Execute    load data local infile '/tmp/sbtest1.txt' REPLACE into table sbtest1 fields terminated by '\t'​

위와 같이, Statement-based 로깅의 경우 실행된 SQL 쿼리 문장(load data local infile '/tmp/sbtest1.txt' REPLACE into table sbtest1 fields terminated by '\t')이 직접 로그에 기록된다.

Row-based

### UPDATE `test`.`sbtest1`
### WHERE
###   @1=7292
###   @2='33381779362-68792378251-25015017183-69472446453-71422697075'
### SET
###   @1=7292
###   @2='33381779362-68792378251-97250707473-96610169832-15182108387'

Row-based 로깅의 경우 SQL 쿼리의 변화된 결과가 바이너리 로그에 기록된다.

위 예시에서는 test.sbtest1 테이블의 한 레코드가 UPDATE 되었음을 표시하고 있다. WHERE 절 이후에 원래 값(@1=7292, @2='33381779362-68792378251-25015017183-69472446453-71422697075')이 나타나며, SET 절 이후에는 변화된 값(@1=7292, @2='33381779362-68792378251-97250707473-96610169832-15182108387')이 나타난다.

이와 같이 바이너리 로그의 형태는 설정된 모드에 따라 크게 달라진다.

Mixed (--binlog-format=MIXED)

• 기본적으로 Statement-based 로깅을 사용하지만, Statement-based로 안전하게 복제되지 않을 수 있을 때에는 자동으로 Row-based 방식으로 전환하여 로깅을 진행한다.
• 안전하게 복제되지 않을수 있을 때란 비확정적 쿼리(non-deterministic query)가 발생했을 때가 대표적이다. 비확정적 쿼리는 항상 같은 결과를 반환하지 않는 쿼리를 말하는데, 예를 들어 UUID()나 RAND()와 같은 함수를 사용하는 쿼리, 혹은 데이터베이스의 특정 상태(예: AUTO_INCREMENT 값, 시스템 변수 등)에 의존하는 쿼리 등이 해당한다.

MySQL의 Mixed 방식에서의 로깅 방식 결정

다음의 경우 Statement-based 바이너리 로깅이 아닌 Row-based 바이너리 로깅을 사용한다.

• 함수에 UUID()가 포함되어 있을 때.
• AUTO_INCREMENT 열이 있는 하나 이상의 테이블이 업데이트되고 트리거나 저장된 함수가 호출될 때.
• 뷰의 본문이 row-based 복제를 필요로 할 때, 뷰를 생성하는 문장도 그것을 사용합니다. 예를 들어, 뷰를 생성하는 문장이 UUID() 함수를 사용할 때 이런 경우가 발생합니다.
• 로드 가능한 함수 호출이 포함되어 있을 때.
• FOUND_ROWS() 또는 ROW_COUNT()가 사용될 때.
• USER(), CURRENT_USER(), 또는 CURRENT_USER가 사용될 때. (참조: Bug #28086)
• 관련된 테이블 중 하나가 mysql 데이터베이스의 로그 테이블일 때.
• LOAD_FILE() 함수가 사용될 때.
• 명령문이 하나 이상의 시스템 변수를 참조할 때. 

https://dev.mysql.com/doc/refman/8.0/en/binary-log-mixed.html

비확정적 쿼리가 문제가 되는경우 row-based나, mixed-based를 사용하는것이 좋다는것은 알겠다. 그럼 mixed-based를 사용하면 되지 왜 row-based를 사용해야 할까?

핵심은 STATEMENT 포맷은 SQL 쿼리 문장 그대로를 기록하는 반면, ROW 포맷은 쿼리의 결과(변경된 레코드)를 기록한다는 점에 있다. 

복제(Replication)

---

앞서 바이너리 로그는 MySQL Replication의 핵심요소라고 설명했다. MySQL에서 복제는 어떻게 수행되길래 바이너리 로그를 필요로 하는걸까?

데이터베이스 복제작업은 다음과 같은 과정을 거친다.

1. 소스 MySQL서버의 전체 데이터 스냅샷을 레플리카 서버에 이관한다. (mysqldump와 같은 툴 활용)
2. 스냅샷이 생성되는 동안 소스 서버에서 발생하는 모든 데이터 변경 작업은 바이너리 로그에 기록된다.
3. 스냅샷 이관이 완료되면 레플리카 서버는 소스 서버의 바이너리 로그를 읽어 릴레이 로그에 저장하고, 릴레이로그에서 읽어 DB에 반영한다.

복제과정에서의 바이너리 로그 포맷

---

Statement-based 바이너리 로그 포맷의 경우 복제과정에서 소스서버에서 수행된 쿼리를 그대로 실행하게 된다. 때문에 소스서버에서 불필요하게 많은 락을 유발한 쿼리들이 그대로 동일하게 수행될 수 있다.

반면 Row-based 바이너리 로그포맷은 변경된 데이터 자체가 전달되어 복제가 수행되기 때문에 소스서버에서 발생한 락을 재생산하지 않는다.