카테고리 없음

MySQL - binlog

필유아사 2024. 5. 28. 15:09

 

환경

- window10

- MySQL version : 8.0.32

 

The Binary Log

바이너리 로그에는 테이블 생성 작업이나 테이블 데이터 변경과 같은 데이터베이스 변경을 설명하는 "이벤트"가 포함되어 있습니다. 또한 행 기반 로깅을 사용하지 않는 한 잠재적으로 변경했을 수 있는 명령문(예: 행과 일치하지 않는 DELETE)에 대한 이벤트도 포함됩니다. 바이너리 로그에는 각 명령문이 업데이트된 데이터에 소요된 시간에 대한 정보도 포함되어 있습니다. 바이너리 로그에는 두 가지 중요한 목적이 있습니다.

  • 복제의 경우 복제 소스 서버의 바이너리 로그는 복제본으로 전송될 데이터 변경 사항에 대한 기록을 제공합니다. 소스는 바이너리 로그에 포함된 정보를 복제본으로 전송하며, 복제본은 해당 트랜잭션을 재현하여 소스에서 수행된 것과 동일한 데이터 변경을 수행합니다.
  • 특정 데이터 복구 작업에는 바이너리 로그를 사용해야 합니다. 백업이 복원된 후에는 백업 이후에 기록된 바이너리 로그의 이벤트가 다시 실행됩니다. 이러한 이벤트는 백업 시점부터 데이터베이스를 최신 상태로 유지합니다. 

# binlog 관련 변수

  • log_bin : 서버의 바이너리 로깅 상태를 활성화(ON) 또는 비활성화(OFF)로 표시. 바이너리 로깅이 활성화되면 서버는 데이터를 변경하는 모든 명령문을 백업 및 복제에 사용되는 바이너리 로그에 기록. ON은 바이너리 로그를 사용할 수 있음을 의미하고, OFF는 사용 중이 아님을 의미
  • log_bin_basename : 바이너리 로그 파일의 경로와 기본 이름. 최대 변수 길이는 256. 기본 위치는 데이터 디렉터리
  • log_bin_index : binlog 파일의 이름을 저장하고 있는 파일
  • bin_log_format : ROW / MIXED / STATEMENT
  • max_binlog_size : 바이너리 로그에 쓰기로 인해 현재 로그 파일 크기가 이 변수의 값을 초과하는 경우 서버는 현재 파일을 닫고 다음 파일을 엽니다. 최소값은 4096바이트입니다. 최대값과 기본값은 1GB입니다. 
  • sql_log_bin : 현재 세션에 대해 바이너리 로그에 대한 로깅 활성화 여부를 제어(log_bin 은 활성화된 상태에서). 기본값은 ON. 현재 세션에 대해 로깅을 비활성화하거나 활성화하려면 sql_log_bin 변수를 OFF 또는 ON으로 설정

binary 로그 확인

mysqlbinlog 유틸리티로 binary log 파일을 텍스트 형식으로 확인할 수 있다.

 

[사용법]
mysqlbinlog "bin 로그경로\binlog 파일명" > binlog.sql

 

[예시] 아래 예시는 Window10에 설치된 MySQL의 binary log 내용을 확인하는 명령어이다.

mysqlbinlog C:\ProgramData\MySQL\MySQL Server 8.0\Data\DESKTOP-bin.000115 > binlog.sql

 

 

bin_log_format 비교

bin_log_format 설정에는 ROW, MIXED, STATEMENT 세가지가 있는데 각각의 차이점은 아래와 같다.

 

  • STATEMENT :  명령문 기반 로깅
  • ROW : 행 기반 로깅. (default)
  • MIXED : STATEMENT와 ROW 혼합 형식 로깅. 기본적으로 STATEMENT 기반 로깅이 사용되지만 특정 경우에는  ROW 기반으로 로깅.

STATEMENT와 ROW 기반 로깅.

1) STATEMENT기반 로깅

STATEMENT 기반의 로깅 설정 시 생성된 binary log 파일을 mysqlbinlog 명령어로 수행해 보면 아래와 같은 형식으로 저장되어 있는 것을 볼 수 있다.

 

 

2) ROW 기반 로깅

ROW 기반의 로깅 설정 시 생성된 binary log 파일을 mysqlbinlog 명령어로 수행해 보면 아래와 같은 형식으로 저장되어 있는 것을 볼 수 있다.

 

 

3) MIXED기반 로깅

기본적으로 Statement-Based Format으로 기록되나, 필요에 따라 Row-base Format으로 Binary Log에 기록된다.

UUID()함수, sysdate() 함수 등 사용 시에는 ROW 기반으로 수행된다.

now(), current_timestamp() 는 STATEMENT-Based Format으로 기록된다. Master와 Slave 간 데이터 차이가 발생하지 않는 것은 binlog의 TIMESTAMP 값으로 설정하는 것 같다.

 

[ sysdate() 함수 사용 시 binary log]

 

[ now() 함수 사용 시 binary log]

 

저작자표시 비영리 변경금지