[MySQL] 복제 구성을 위한 바이너리 로깅 형식

개요

MySQL의 복제 데이터 포맷에는 어떤 방식이 있는지 알아보고 간단한 테스트를 통해 유의점을 확인해보겠습니다.

 

복제 포맷

MySQL의 복제 포맷에는 Statement, Row, Mixed 3가지 형태가 있습니다. Statement 방식은 MySQL 바이너리 로그 도입 당시부터 존재한 복제 포맷이며 5.7.7 이후 버전 부터는 Row 형태가 기본 포맷으로 변경되었습니다.

• Statement : 명령문 기반의 로깅 방식
• Row : 행 기반의 데이터 로깅 방식
• Mixed : Statement와 Row의 장점을 혼합한 로깅 방식

 

Statement 기반 바이너리 로그 포맷

Statement 방식의 경우 실행된 SQL 문을 그대로 바이너리 로그에 저장하는 방식입니다. 다수의 데이터가 수정된 경우에도 단순히 쿼리만 기록되기에 적은 용량의 로그파일을 관리할 수 있다는 장점이 있습니다.

-- 바이너리 로그 포맷 변경
mysql> SET SESSION binlog_format= 'STATEMENT';

-- 쿼리 실행
mysql > INSERT INTO test.tb_person(name,age) values ('Omty',20)

Binary Log상 기록된 쿼리

 

제한사항

 

REPEATABLE-READ 이상의 트랜잭션 격리 수준 사용

MySQL의 기본 격리 수준인 REPEATABLE-READ 이상을 사용해야만 합니다. READ-COMMITTED 방식의 경우 하나의 트랜잭션에서 실행 시점에 따라 스냅샷이 달라지기 때문에 데이터가 불일치하는 문제가 발생할 수 있습니다.

 

비 확정적으로 처리되는 쿼리문 사용 불가

아래의 쿼리문과 같이 정확한 결과 값을 추론할 수 없는 경우. 소스와 레플리카의 데이터가 일치하지 않을 수 있습니다.

동일한 파라미터 값을 입력하더라도 결과가 달라질 수 있는 사용자 정의 함수나 프로시저를 사용하는 쿼리

DELETE/UPDATE 쿼리에서 ORDER BY 없이 사용

SELECT ... FOR UPDATE 및 SELECT ... FOR SHARE쿼리에서 NOWAIT이나 SKIP LOCKED 사용

LOAD_FILE(), UUID(), UUID_SHORT(), USER(), FOUND_ROWS(), RAND(), VERSION() 등과 같은 함수를 사용하는 쿼리

간단한 예시로 uuid() 함수를 통해 데이터를 생성할 경우 데이터 값이 달라 질 수 있으며 이후 해당 데이터를 uuid() 기준으로 삭제할 때 레플리카의 데이터만 남게되는 문제가 발생하게 됩니다.

INSERT INTO test.tb_person(name,age,uuid) values ('Omty',20,uuid())

source 데이터

replica 데이터

 

Row 포맷보다 더 많은 Lock을 사용

데이터가 복사되는 Row 포맷과 달리 쿼리문을 호출하는 방식이기에 조건에 따라 더 많은 Lock을 걸어 복제 지연이 발생할 수 있습니다.

 

Row 기반 바이너리 로그 포맷

Row 방식의 경우 데이터를 로깅하는 방식이기에 복제 시 소스와 레플리카 서버의 데이터 일관성을 유지하는 안전한 방식입니다.

binary Log에 기록된 데이터

 

Statement 방식과 차이점

• 변경된 데이터가 바로 적용되기에 더 적은 Lock을 점유
• 모든 트랜잭션에서 사용이 가능
• 쿼리 결과에 의한 값을 전달하기에 변경된 데이터가 많을 수록 바이너리 로그 파일 크기가 커짐

대량의 데이터를 삭제했을 경우

다만 사용자 계정 생성 및 구한 또는 CREATE , ALTER, DROP 등 DDL 문의 경우 STATEMENT 형태로 로깅됩니다.

 

Row 포맷 Binary 파일 확인 방법

Row 포맷의 경우 실행된 쿼리를 확인하기 어렵기 때문에 mysqlbinlog 사용 시 옵션을 통해 쿼리 형태로 확인할 수 있는 기능을 제공합니다.

• -v : 변경된 데이터를 유사 SQL 형태로 변환
• -vv : -v 옵션의 변경된 데이터에 타입을 추가로 표시함
• --base64-output=DECODE-ROWS : Base64 문자열로 인코딩된 변경 데이터를 표시하지 않음

$ mysqlbinlog -uroot -p -v --base64-output=DECODE-ROWS binlog.000011 > binlog.sql

 

Mixed 포맷

Mixed 포맷의 경우 Statement방식과 Row방식을 혼합하여 사용합니다. 기본적으로는 Statement 방식을 사용하며 실행된 쿼리와 스토리지 엔진의 종류에 따라 필요시 자동으로 Row포맷으로 전환 후 로그에 기록합니다. 예시로 Statement 에서 비 확정적으로 처리되는 쿼리문의 경우 Row 포맷으로 전환되어 기록이됩니다. 장점만 합쳐놨다면 Mixed 포맷이 가장 좋다고 생각될 수 있지만 간혹 예상치 못한 방식으로 로깅될 가능성 또한 존재합니다.

 

복제 포맷 적용

[mysqld] 
--binlog-format=formt

-- 전역
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

-- 세션
mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';

binlog_format은 동적으로 수정이 가능하며 Session 단위 적용이 가능합니다. ROW 방식을 사용 중일 때 대량의 변경 사항 적용이 필요할 경우 해당 내용만 SESSION으로 Statement 포맷을 적용하게 되면 파일이 커지는 것을 방지 할 수 있습니다.

반대로 Statement 방식일 때 쿼리 실행 시간에 비해 변경되는 데이터가 적을 경우 Row 방식을 사용하면 처리 시간 및 Lock 점유를 줄일 수 있습니다.

 

예외

특정한 경우에는 포맷 변경이 불가합니다.

• 스토어드 함수 또는 트리거 내에서 사용
• NDB 스토리지 엔진
• 세션이 행 기반 복제 모드이고 열린 임시테이블이 존재할 경우

임시 테이블을 사용하는 경우 Statement 포맷은 기록이 되나 ROW 포맷의 경우 기록되지 않습니다.

 

 

 

참고

Real MySQL 8.0

명령문 기반 복제와 행 기반 복제의 장점과 단점

바이너리 로깅에서 안전 및 안전하지 않은 명령문의 결정

바이너리 로그 형식 설정