MariaDB-바이너리로그

바이너리로그를 활성화시키려면 mariaDB 시작시 –log-bin 옵션에 로그파일이름을 지정해주면 된다. 확장자를 포함하여 파일이름을 지정할 수 있지만 확장자는 그냥 무시된다. 만약 이름을 명시하지 않으면 기본 위치는 datadir/log-basename-bin 으로 되거나 data/mysql-bin으로 되거나 datadir/mariadb-bin 으로 된다. (앞의 두 개는 basename이 지정되지 않은 경우이며 mariadb의 버전에 따라 다르다). –log-basename 옵션을 통해 경로를 지정하거나 파일이름 지정시 전체 경로를 명시하는것을 추천한다. 그래야 호스트명이 바뀌거나 서버에 어떤 변경이 일어났을 때 replication(복제)가 중단되는 것을 방지할 수 있다.

바이너르 로그를 보관하는 디렉토리는 해당 로그 파일뿐만 아니라 로그 인덱스도 보유한다. 파일이름의 확장자로 연속적인 숫자가 오게 되는데, 바이너리 로그가 추가되면 숫자도 증가한다. mariaDB가 재기동되면 새로운 바이너리로그가 생성된다. 또는 최대사이즈에 도달했을 때도 새로운 바이너리 로그가 생성된다. (max_binlog_size에 의해 결정, 기본 1G) 인덱스 파일은 바이너리 로그에 대한 리스트를 가지고 있는 마스터 파일라고 할 수 있다. 바이너리 로그 디렉토리를 보면 아래와 같이 존재하는 것을 볼 수 있다.

shell> ls -l 
total 100
...
-rw-rw---- 1 mysql adm 2098 Apr 19 00:46 mariadb-bin.000079
-rw-rw---- 1 mysql adm  332 Apr 19 00:56 mariadb-bin.000080
-rw-rw---- 1 mysql adm  347 Apr 19 07:36 mariadb-bin.000081
-rw-rw---- 1 mysql adm  306 Apr 20 07:15 mariadb-bin.000082
-rw-rw---- 1 mysql adm  332 Apr 20 07:41 mariadb-bin.000083
-rw-rw---- 1 mysql adm  373 Apr 21 07:56 mariadb-bin.000084
-rw-rw---- 1 mysql adm  347 Apr 21 09:09 mariadb-bin.000085
-rw-rw---- 1 mysql adm  398 Apr 21 21:24 mariadb-bin.000086
-rw-rw---- 1 mysql adm  816 Apr 21 17:05 mariadb-bin.index

 

인덱스파일은 log-bin-index 파라미터를 이용해 이름을 명시할 수 있다. 바이너리 로그의 포맷은 statement기반, row기반, 두가지를 합친 것으로 총 3가지의 형식이 가능하다.

 

Using and Maintaining the Binary Log

Purging log files

모든 로그파일을 삭제하려면 RESET MASTER 명령을 사용해라. 특정 시간 이전이나 특정 넘버의 로그만 지우려면 PURGE BINARY LOGS 명령을 이용해라.

※ 슬레이브가 활성화되어 있고 삭제하려는 바이너르 로그를 읽고있다면 삭제할 수 없다. 그러나 슬레이브가 비활성화되어 있다면 삭제할 수 있다. 또한 삭제한 이후부터 슬레이브와 다시 연결하기 전까지는 replication이 이뤄지지 않는다.

또 expire_logs_days 파라미터에 의해 정해진 기간에 도달하면 삭제되기도 한다. 기본 값은 0이지만 day 단위로 값을 지정할 수 있으며 적어도 슬레이브 lag 시간 보다는 길게 설정해야 한다.

※ 인덱스 파일이 삭제되거나 인위적으로 잘못 수정되면, 위에 언급한 purge 작업은 실패하게 된다. 인덱스 파일은 텍스트 파일이기 때문에 직접 수정이 가능하며 현재 존재하는 로그파일에 대해 시간순으로 리스트를 담고있다.

Examples

PURGE BINARY LOGS TO 'mariadb-bin.000063';
PURGE BINARY LOGS BEFORE '2013-04-22 09:55:22';

 

Safely purging binary log files while replicating

바이너리 로그를 삭제하는 도중 replication이 깨지지 않도록 하려면 아래 절차를 잘 지켜라.

  1. SHOW BINARY LOGS 명령을 통해 현존하는 로그 파일의 리스트를 확인해라.(마스터에서)
  2. 각 슬레이브에서 SHOW SLAVE STATUS 명령을 통해 각각의 슬레이브가 현재 읽고 있는 로그 파일이 어떤 것인지 확인해라.
  3. 슬레이브들이 읽고 있는 것 중 가장 오래된 파일을 확인해라. 이 파일 이전의 로그파일은 필요없다.
  4. 필요하다면 로그 파일을 백업해둬라.
  5. 그리고 가장 오래된 파일로 확인했던 로그 파일 이전의 것들을 삭제해라.

 

Selectively logging to the binary log

기본적으로 모든 데이터, 데이터구조를 변화시키는 쿼리가 기록된다. –binlog-ignore-db=database_name 옵션이나 –binlog-do-db=database_name 옵션을 통해 로그를 남기지 않을 데이터베이스나 남길 데이터베이스를 선택할 수 있다.

※ 여러 DB를 명시할 때는 콤마를 쓰면 안된다(DB명에 콤마가 있을 수도 있기 때문). 대신 이 옵션을 여러번 반복해서 쓰면 된다.

–binlog-ignore-db 옵션이나 –binlog-d-db 옵션은 로그파일 포맷이 statement기반이냐, row기반이냐에 따라 조금씩 다르다. 로그파일 형식이 statement 기반일 경우엔 USE명령으로 사용하고 있는 기본 데이터베이스가 해당 옵션에 있는 경우에만 적용된다고 보면 된다. 가령 DB가 A,B,C,D 가 있고 파일포맷이 statement이며, ignore  (또는 do) 대상이 A,B 인 상황에서 내가 C 데이터베이스를 기본으로 사용하고 있다면 내가 입력하는 작업내용은 기록되지 않는 (또는 기록되는)것이다. 로그파일 형식이 row기반일 경우엔 기본 데이터베이스와 상관없이 적용된다.

 

Binary Log Formats

파일형식은 statement기반, row기반, 이 둘을 합친 것해서 총 3가지가 지원된다. statement기반이라고 해서 텍스트형식으로 저장되는 것이 아니라 기본적으로 항상 바이너리 형식으로 저장된다. 따라서 mysqlbinlog 라고 하는 CLI기반의 명령어를 통해 텍스트로 변환하여 볼 수 있다. 파일 형식은 binlog_format 시스템 변수를 통해 정할 수 있다.

Statement-based

파일 형식의 기본 설정이며 –binlog-format=STATEMENT 로 지정할 수 있다. 데이터나 테이블 구조에 변경을 가하는 모든 SQL구문을 기록한다. 어떤 경우엔 SQL구문이 확정적(Deterministic)이지 않은 경우가 존재하며 이 경우 replication이 안전하지 않게 된다. 이 경우 log_warning 시스템 변수에 정의된 곳에 경고문이 뜨게 된다.

Row-based

테이블의 개별적인 row가 영향받는 event를 기록한다. –binlog-format=ROW 로 지정한다.

Mixed

일단 statement-based가 기본적으로 사용되지만 replication에 안전하지 않은 statement라고 mariaDB가 판단하는 경우 row-based 방식으로 기록하는 방식이다. statement방식 대신 row방식으로 기록되는 경우는 아래와 같다.

  • INSERT-DELAYED 구문이 사용된 경우
  • AUTO_INCREMENT 컬럼을 가진 테이블이 업데이트, 트리거되거나 스토어드 함수가 사용된 경우
  • LOAD_FILE() 함수가 사용된 경우
  • ROW_COUNT() 함수나 FOUND_ROWS() 함수가 사용된 경우
  • USER()나 CURRENT_USER()함수가 사용된 경우
  • UUID() 함수가 사용된 경우
  • 구문에 포함된 테이블 중 하나가 mysql database의 로그 테이블일 경우
  • 구문이 시스템 변수를 참조하는 경우(세션단위에서 사용하는 것은 예외)
  • 유저정의 함수가 사용되는 경우

참고로 마스터와 슬레이브간 서로 다른 스토리지 엔진을 쓰는 것보다 같은 엔진을 사용할 때 에러가 줄어든다.

Changing the binary log format

로그포맷의 기본값은 statement기반이며 mariaDB 기동 중에 변경할 수 있다.

※ replication을 사용중에는 파일 포맷 변경하는 것을 조심해야 한다. 바이너리 로그 형식은 서버 자신에 의해서만 변경될 수 있다. 따라서 마스터에서 로그 포맷을 바꾸더라도 슬레이브의 로그 포맷에는 영향을 미치지 않으며 로그의 불일치를 가져올 수도 있다. 또한 Global 파라미터로 변경하더라도 현재 기동 중인 쓰레드를 보유한 세션에는 영향을 주지 않는다. 그리고 parallel 복제를 사용하는 환경에서 slave가 STOP SLAVE 명령 및 재기동되어도 slave_parallel_threads 값이 변경되기 전까지는 워커 쓰레드가 계속 기동하게 된다. 따라서 이때에는 slave_parallel_threads=0 으로 값을 변경해야 워커 쓰레드가 즉시 종료된다.

MariaDB 10.0.22 부터는 로그 형식과는 상관없이 마스터로에서 발생하는 어떤 이벤트도 적용할 수 있게 되었다.

비록 로그 포맷을 바꾸는 일은 거의 없겠지만 아래의 케이스는 알아두면 좋겠다.

  • 단일 구문 또는 적은 구문을 통한 update가 많은 수의 row를 업데이트할 때는 statement로깅기법이 더 효과적이다.
  • 많은 SQL문이 결과적으로 미미한 양의 row를 변경하는 경우 row로깅기법이 더 효과적이다.
  • 매우 오래 수행되지만 결과적으로 매우 적은 row가 변경되는 경우에도 row로깅기법이 더 효과적이다.

 

Binary Logging of Stored Routines

statement기반의 로깅을 사용하는 경우 마스터와 슬레이브간 서로 다른 결과를 만들어내는 SQL구문이 존재할 수도 있다. 스토어드 루틴같은 경우는 2가지 이유 때문에 이러한 경향이 더욱 짙다.

  1. 스토어드 루틴은 확정적(Deterministic)이지 않다. 다른 말로 repeatable하지 않다는 의미로 실행될 때마다 다른 결과 값을 만들어낸다.
  2. 슬레이브에서 스토어드 루틴을 실행하는 쓰레드는 전체 권한을 가지고 있는 반면, 마스터에서는 그렇지 않은 경우가 있다.

리플리케이션에서 발생하는 문제는 오직 statement기반의 로깅에서만 일어난다. 만약 row기반의 로깅이 사용되면, 마스터의 row를 기반으로 변경사항이 전달되기 때문에 sync가 어긋날 가능성이 완전히 사라지게 된다.

기본적으로 row기반의 리플리케이션을 사용하면, 마스터에서 트리거를 수행시켜 슬레이브로 해당 내용이 전달되도록 한다. 그러나 MariaDB 10.1.1부터는 슬레이브에서도 트리거를 수행시킬 수 있게 되었다.

How MariaDB handles statement-based binary logging of routines

이번 장에서 설명하는 내용은 statement기반의 로깅이 활성화된 케이스에만 해당한다.

  • 바이너리 로그가 활성화된 상태에서 스토어드 펑션이 만드려면 반드시 DETERMINISTIC이나 NO SQL 또는 READS SQL DATA 를 선언해주어야 한다. 안그러면 에러가 발생한다.
  • mariaDB는 펑션이 확정적(Deterministic)인지 판단하지 않으며 대신 올바른 정의가 사용되었는지를 본다.(?)
  • 스토어드 펑션을 만드려면 정규권한 외에도 SUPER 권한이 필요하다.
  • log_bin_trust_function_creators=1로 설정되면 위에서 언급한 조건은 필요없다.
  • 트리거…

 

PURGE logs

PURGE { BINARY | MASTER } LOGS { TO ‘log_name’ | BEFORE datetime }

명시한 로그파일명 혹은 날짜값 이전에 있는 로그파일을 삭제한다. 인덱스 파일내 목록에서도 삭제되므로 명시했던 파일명이 첫 번째로 존재하는 로그파일이 된다.

삭제하기 전 SHOW BINARY LOGS 명령을 통해 로그 리스트를 확인하고, 어떤 로그파일이 읽히고 있는지를 확인해보려면 SHOW SLAVE STATUS 명령을 이용하면 된다. 슬레이브가 읽고 있는 파일 중 가장 old한 것보다 더 이전에 생긴 로그파일부터 지울 수 있다.

전체 로그파일을 다 삭제하려면 RESET MASTER 명령을 날리면 된다. (여태까지의 로그파일이 모두 삭제되고 새로운 로그파일부터 시작함)

Examples

PURGE BINARY LOGS TO 'mariadb-bin.000063';

PURGE BINARY LOGS BEFORE '2013-04-21';

PURGE BINARY LOGS BEFORE '2013-04-22 09:55:22';

 

 

 

 

 

 

 

댓글 남기기