바이너리 로그의 복제 데이터 포맷

바이너리 로그

MySQL에서 발생하는 모든 변경 사항을 순서대로 기록하는 로그

데이터의 변경 내역, 테이블의 구조 변경, 계정이나 권한의 변경 정보까지 모두 저장된다.

바이너리 로그에 기록된 각 변경 정보들을 이벤트라고 한다.

복제 데이터 포맷

Statement

변경 이벤트를 발생시킨 SQL문을 기록하는 방식

장점

• 로그 크기가 작다.
  • 저장 공간이 얼마 들지 않는다.
  • 레플리카 서버에서 복제할 때 빠르게 처리할 수 있다.

단점

• 비결정적인 쿼리의 경우, 복제 시 소스 서버와 레플리카 서버 간에 데이터가 달라질 수 있다.
• 데이터에 락을 더 많이 건다.
• 트랜잭션 격리 수준이 REPEATABLE-READ 이상이어야 한다.
  • 하나의 트랜잭션 내에서도 각 쿼리가 실행되는 시점마다 데이터 스냅숏이 달라질 수 있는데, 이로 인해 서버의 데이터가 일치하지 않게 될 수 있다.

비결정적 쿼리 유형

• DELETE/UPDATE 쿼리에서 ORDER BY 없이 LIMIT 절 사용
• SELECT … FOR UPDATE, SELECT … FOR SHARE 쿼리에서 NOWAIT이나 SKIP LOCKED 사용
• LOAD_FILE(), UUID(), UUID_SHORT(), USER(), FOUND_ROWS(), RAND(), VERSION() 등과 같은 함수를 사용하는 쿼리
• 동일한 파라미터 값을 입력하더라도 결과값이 달라질 수 있는 사용자 정의 함수나 스토어드 프로시저를 사용하는 쿼리

Row

변경이 발생했을 때 변경된 값 자체가 바이너리 로그에 기록되는 방식

장점

• 어떤 형태의 쿼리가 실행됐든 간에 복제 시에 데이터를 일관되게 보장할 수 있다.
• 다음과 같은 쿼리들에서 Statement보다 락이 덜 걸린다.
  • INSERT … SELECT
  • INSERT with AUTO_INCREMENT
  • 적절한 인덱스가 없어 풀스캔으로 처리되는 UDPATE/DELETE
• 모든 트랜잭션 격리 수준에서 사용 가능하다.

단점

• 로그의 크기가 크다
  • 많은 데이터가 변경되는 경우
  • BLOB 형태의 큰 값이 새로 저장되거나 변경되는 경우
• 어떤 SQL을 실행했는지 알 수 없다.
  • 릴레이 로그나 바이너리 로그를 mysqlbinlog 프로그램을 사용해 변환할 수 있다.

용량 최적화

1. ROW 이미지(binlow_row_image 시스템 변수
   1. full: 변경이 발생한 레코드의 모든 칼럼의 값을 바이너리 로그에 기록한다.

    | 이벤트 종류 | 변경 전 레코드 | 변경 후 레코드 |
    | --- | --- | --- |
    | INSERT | x | 새롭게 INSERT된 레코드의 모든 칼럼 |
    | UPDATE | 모든 칼럼 | 모든 칼럼 |
    | DELETE | 변경 전 레코드의 모든 칼럼 | x |
2. minimal: 변경 데이터에 대해 꼭 필요한 칼럼들의 값만 바이너리 로그에 기록한다.
    



3. noblob: full 옵션과 동일하지만, 레코드의 BLOB나 TEXT 칼럼에 변경이 발생하지 않은 경우는 로그에 기록하지 않는다.

2. 트랜잭션 압축

Mixed

기본적으로는 Statement 포맷을 사용하고, 실행된 쿼리와 스토리지 엔진의 종류에 따라 자동으로 Row 포맷으로 전환해서 기록한다.