Binary 로그란

 

 

 

바이너리 로그

 

DDL(Create, Drop, Alter)과 DML(Insert, Update, Delete)을 통해 데이터베이스, 오브젝트, 데이터에 생성,수정,업데이트를 했을 시 그 변화된 이벤트를 기록하는 이진 파일이 있는데 이것을 바이너리 로그라고 한다.

(show나 select등 조회 문법은 제외된다)

 

 

---

 

용도

1. 복제 구성에서 사용

• 바이너리 로그는 마스터라는 서버에서 생성되고, 슬레이브 서버는 마스터 서버에 접속하여 이 바이너리 로그를 읽어와서 똑같이 그 이벤트를 실행시켜 마스터서버와 슬레이브 서버를 동일하게 만듭니다.

 

2. 특정 시점 복구에 사용

• 데이터베이스를 사용하다보면 데이터 삭제나 데이터베이스가 어떤 이유로 장애나 크래쉬가 발생할 시 복구를 해야할 때가 있다.
  이때 특정 시점 시간으로 돌아갈 때 바이너리 로그가 필요하다.

 

 

 

---

 

 

 

 

 

특징

 

바이너리 로그의 필요성

성능 저하: 바이너리 로그를 활성화하면 서버 성능이 약간 느려질 수 있습니다. 로그를 생성하고, 제어하며, 변경된 이벤트를 파일에 저장해야 하기 때문입니다.
중요성: 하지만 데이터베이스 장애 발생 시 복구를 위해 이 로그 파일이 꼭 필요합니다. 따라서 성능 저하를 감수하고라도 활성화해야 합니다.

 

---

로그 작성 시점:

트랜잭션 완료 후: 바이너리 로그는 트랜잭션이 완료된 직후에 작성됩니다. 이렇게 하면 로그가 커밋 순서대로 기록됩니다.
트랜잭션이 아닌 경우: 트랜잭션이 아닌 테이블에 대한 업데이트는 실행 후 바로 로그에 저장됩니다.

 

 

---

 

이벤트 기록:

기본적으로 서버는 이벤트의 길이와 내용을 기록하여 이벤트가 올바르게 작성되었는지 확인합니다.

 

 

---

 

커밋되지 않은 트랜잭션:

캐싱: InnoDB에서 커밋되지 않은 트랜잭션 내의 모든 업데이트(INSERT, UPDATE, DELETE)는 서버가 COMMIT 명령을 받을 때까지 캐시됩니다.
이진 로그 기록: COMMIT을 실행하기 전에 전체 트랜잭션을 이진 로그에 기록합니다.

 

 

---

 

비 트랜잭션 테이블:

롤백 불가능: 비 트랜잭션 테이블에 대한 수정은 롤백할 수 없습니다.
복제 보장: 롤백된 트랜잭션에 비 트랜잭션 테이블 수정이 포함된 경우, 해당 수정 사항이 복제되도록 전체 트랜잭션이 로그에 기록됩니다.

 

 

 

 

 

---

 

 

 

 

 

 

 

바이너리 로그 설정 옵션과 설명

 

mysqld는 바이너리 로그 기본 이름에 숫자 확장자를 추가하여 이진 로그파일 이름을 생성합니다.

서버가 새 로그파일을 작성할 때마다 숫자가 증가하므로 순서가 지정된 파일이 작성됩니다

 

• 로그를  시작하거나 플러시 할때 로그 생성
  (서버는 로그를 시작하거나 플러시 할 때마다 생성된 로그파일 순서 이후 번호를 생성하여 새 파일을 작성합니다.)
• 로그 옵션을 이용한 바이너리 파일 크기 제한
  (max_binlog_size 옵션을 이용하여 로그 파일당 최대 크기를 지정합니다.)
• 옵션을 이용한 바이너리 로그 생성 중지
  (mysql> SET sql_log_bin=OFF)

 

---

 

로그 옵션

옵션	설명
binlog_cache_size	트랜잭션을 처리하는 스레드가 시작되면 설정된 크기만큼 버퍼를 할당합니다. 그리고 명령문을 이곳에 저장 후, 명령문이 이보다 큰 경우 스레드는 임시파일을 생성하여 트랜잭션을 저장합니다. 스레드를 통한 작업이 종료되면 임시 파일이 제거됩니다.
binlog_cache_use	명령문이 실행되기 전에 binlog_cache_size라는 메모리 버퍼에 저장된 횟수를 카운트합니다. (Binlog_cache_disk_use가 Binlog_cache_use보다 훨씬 높다면, 이는 명령문이 자주 디스크에 저장되고 있다는 의미입니다.  디스크보다 메모리에서 처리하는 것이 더 빠르기 때문에, 이런 경우 binlog_cache_size를 늘려주어야 합니다.)
binlog_cache_disk_use	만약 명령문이 너무 커서 메모리 버퍼에 저장할 수 없는 경우, 디스크에 저장하게 되는데, 이때 디스크를 사용한 횟수를 카운트합니다.
binlog_stmt_cache_size	비트랜잭션 명령문을 보관하기 위한 바이너리 로그의 메모리 버퍼 크기입니다. 트랜잭션 중에 발행된 비트랜잭션 문을 저장하며, 메모리 버퍼를 초과하면 임시 파일에 저장됩니다. (Binlog_stmt_cache_use, Binlog_stmt_cache_disk_use는 캐시 사용량을 모니터링하는 데 사용됩니다.)
max_binlog_cache_size	다중-명령문 트랜잭션을 캐시하는 데 사용되는 전체 크기를 제한합니다. (설정된 크기보다 큰 트랜잭션은 실패하고 롤백됩니다.)
binlog_error_action	서버가 바이너리 로그에 쓰거나 플러시, 동기화할 수 없을 경우 취해지는 조치를 제어합니다. ABORT_SERVER: 서버가 바이너리 로깅을 중지하고 종료합니다.  IGNORE_ERROR: 서버가 오류를 기록하고 바이너리 로깅을 중지하지만 업데이트는 계속 수행합니다.
sync_binlog	바이너리 로그를 디스크와 동기화하는 빈도를 제어합니다.(기본값: sync_binlog=1, 각 쓰기마다 디스크에 동기화) 안정성이 우선이면 sync_binlog=1, 성능이 우선이면 더 큰 값으로 설정할 수 있습니다.

 

 

 

 

 

---

 

 

 

 

 

 

 

바이너리 로깅 포멧

 

STATEMENT 방식

(문장방식 로깅)

마스터에서 실행한 SQL을 그대로 바이너리 로그에 작성하고 그 로그를 슬레이브로 전송하여 슬레이브에도 실행하게 하는 방식입니다.

 

설정방법 : 

- mysql 서버를 실행할때 다음 옵션을 수행 : --binlog-format=STATEMENT

- 환경파라미터에 다음 설정을 추가 : binlog_format=STATEMENT

 

 

---

 

Row-Based 방식

(행기반 방식)

마스터는 개별 테이블 행이 어떻게 영향을 받는지를 나타내는 이벤트를 이진 로그에 씁니다.

즉 SQL 문법이 아니라 결과값을 바이너리 로그에 저장하게 됩니다.

설정방법 :

- mysql 서버를 실행할때 다음 옵션을 수행 : --binlog-format=ROW

- 환경파라미터에 다음 설정을 추가 : binlog_format=ROW 

 

 

---

 

 

Mix-Logging 방식

(혼합로깅 방식)

기본적으로 명령문 기반 로깅이 사용되지만 특정 경우 로깅 모드가 자동으로 행 기반으로 전환됩니다. 

 

설정방법 :

- mysql 서버를 실행할때 다음 옵션을 수행 : --binlog-format= MIXED

- 환경파라미터에 다음 설정을 추가 : binlog_format= MIXED 

 

 

 

---

참고사이트

[MySQL][Admin] Binary 로그 소개 및 특징