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

■ MySQL 바이너리 로그

▶  바이너리 로그란??

MySQL 서버에서 Create, Drop, Alter같은 DDL과 Insert, Update, Delete같은 DML을 통해 데이터베이스, 오브젝트, 데이터에 생성,수정,업데이트를 했을 시 그 변화된 이벤트를 기록하는 이진 파일이 있는데 이것을 바이너리 로그라고 합니다. show나 select등 조회 문법은 제외됩니다. 이 바이너리 로그는 두가지 중요한 용도가 있습니다. 그 두가지 용도는 다음과 같습니다.

 

1. 복제 구성에서 사용

MySQL에서는 복제라는 부하분산 기능을 제공하는데 이때 바이너리 로그를 사용합니다. 이 바이너리 로그는 마스터라는 서버에서 생성되고 슬레이브란 서버는 마스터 서버에 접속하여 이 마스터의 바이너리 로그를 읽어와서 똑같이 그 이벤트를 실행시켜 마스터서버와 슬레이브 서버를 동일하게 만듭니다.

 

2. 특정 시점 복구에 사용.

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

 

■ 바이너리 로그의 특징

▶ 바이너리 로그를 활성화하면 성능이 약간 느려집니다. 바이너리 로그를 생성하고 제어를 해야 하고 변경된 이벤트를 이진 파일에 써야 하기 때문입니다. 또한 위에서 설명했던 복제 기능을 이용하게 되면 마스터 서버에서 로그를 읽어가기 때문에 디스크 I/O또한 발생하기 때문입니다. 하지만 이미 모든걸 감안해서라도 이진로그는 반드시 활성해야 합니다. 위에서 설명했던 데이터베이스 장애발생시 이 바이너리 로그파일이 필요하기 때문입니다.

▶ 바이너리 로깅은 잠금이 해제되거나 커밋이 완료되기 전이어야 하지만 명령문 또는 트랜잭션이 완료된 직후 수행되기도 합니다. 이렇게하면 로그가 커밋 순서로 로그인됩니다.

▶ 트랜잭션이 아닌 테이블에 대한 업데이트는 실행 후 바로 이진 로그에 저장됩니다.

▶ 기본적으로 서버는 이벤트 길이와 이벤트 자체를 기록하고 이를 사용하여 이벤트가 올바르게 작성되었는지 확인합니다.

▶ 커밋되지 않은 트랜잭션 내에서 테이블과 같은 트랜잭션 테이블을 변경하는 모든 업데이트 ( UPDATE, DELETE또는 INSERT)는 서버 InnoDB에서 COMMIT 명령문을 받을 때까지 캐시 됩니다. 이 시점에서 mysqld는 전체 트랜잭션에 COMMIT을 실행하기 전에 이진 로그에 씁니다.

 

▶ 비 트랜잭션 테이블에 대한 수정은 롤백 할 수 없습니다. 롤백된 트랜잭션에 비 트랜잭션 테이블에 ROLLBACK 대한 수정 사항이 포함된 경우 해당 테이블에 대한 수정 사항이 복제되도록 하기 위해 전체 트랜잭션이 끝에 명령문으로 기록됩니다 .

 

▶ 바이너리 로그 작성 방법 설정을 Row-based 로깅을 사용하는 경우 동시 삽입은 CREATE ... SELECT 또는 INSERT ... SELECT문의 일반 삽입 명령문으로 변환됩니다. 이는 백업 조작 중 로그를 적용하여 테이블의 정확한 복사본을 다시 작성할 수 있도록하기 위해 수행됩니다. 명령문(statement)기반 로깅을 사용하는 경우 원래 명령문이 로그에 작성됩니다.

 

■ 바이너리 로그 사용 방법.

my.cnf에 다음의 설정옵션을 추가해 줍니다.

참고로 [mysqld] 섹션에 추가하는 것으로 검색시 많이 나오는데 [mysqld_safe]란 섹션에 추가해야 됩니다.

일단 [mysqld]에 추가해 보시고 만약 에러가 난다면 [mysqld_safe]에 추가해 보시기 바랍니다.

 

+ 바이너리 로그 수행방법.

- my.cnf에 설정시

log-bin=/data디렉토리/base_name

 

- 터미널에서 mysqld_safe로 시작시

mysqld_safe로 시작할시 뒤의 옵션에 다음 옵션을 추가해 줍니다.

shell > mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --log-bin=/data디렉토리/base_name

 

만약 위에 특정 디렉토리를 지정해 주지 않으면 data디렉토리에 기본적으로 생성이 됩니다. 또한 base_name을 지정해주지 않으면 MySQL서버 시작시 자동으로 들어가는 --pid-file이란 옵션이 있습니다. 이 옵션은 시스템 이름을 읽어들여  이 파일이 생성되는데 이때 시스템 이름을 사용하여 .pid 파일이 생성됩니다. 이 시스템 이름을 참고하여 “시스템이름(base_name)”이란 파일명으로 생성됩니다. 즉 시스템 이름이 mdb01이라면 mdb01.00001이라고 생성됩니다. 참고로 로그 이름에 확장자를 준다고 하더라도 (예를 들면, --log-bin=base_name.extension), 그 확장자는 무시되고 제거가 됩니다.

 

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

▶ 바이너리 로그 생성 시기 및 일시 중지.

mysqld는 바이너리 로그 기본 이름에 숫자 확장자를 추가하여 이진 로그파일 이름을 생성합니다. 서버가 새 로그파일을 작성할 때마다 숫자가 증가하므로 순서가 지정된 파일이 작성됩니다. 이때 로그가 생성되는 시기는 다음과 같습니다.

+  로그를  시작하거나 플러시 할때

서버는 로그를 시작하거나 플러시 할 때마다 생성된 로그파일 순서 이후 번호를 생성하여 새 파일을 작성합니다.

+ 로그 옵션을 이용한 바이너리 파일 크기 제한

바이너리 로그 파일이 무한정으로 커지면 I/O에 대한 부담과 유지관리 측면에서도 굉장히 어려울 것입니다. 이때 사용하는 옵션이 max_binlog_size란 옵션입니다. 한 바이너리 로그 파일당 최대 크기를 정합니다. 로그 파일 작성 후 로그파일이 이 옵션 크기에 다다르게 되면 자동으로 로그 파일이 플러시 되고 신규 파일이 생성되어 계속 진행되게 됩니다.

+ 바이너리 로그 생성 중지

바이너리로그 생성 중 특정 이유로 인해 생성을 임시로 막고자 한다면 다음과 같이 실행합니다.

mysql> SET sql_log_bin=OFF

 

▶ 바이너리로그 캐시 사이즈(binlog_cache_size)

트랜잭션을 처리하는 스레드가 시작되면 binlog_cache_size 라는 옵션에 설정되어 있는 크기만큼 버퍼를 할당합니다. 그리고 명령문을 이곳에 저장합니다. 명령문이 이보다 큰 경우 스레드는 임시 파일을 생성하여 트랜잭션을 저장합니다. 스레드를 통한 작업이 종료되면 임시 파일이 제거됩니다.

https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_cache_size

 

Binlog_cache_use, Binlog_cache_disk_use

명령문을 실행하기 전에 binlog_cache_size란 버퍼캐쉬에 명령문을 저장한다고 위에서 설명했습니다. 이것에 대한 사용횟수를 Binlog_cache_use에 카운트 합니다. 만약 binlog_cache_size에 저장할 수 없을만큼 크기의 명령문이 들어왔을 때는 디스크에 만들어 저장하게 되는데 이때 디스크를 사용하는 횟수를 Binlog_cache_disk_use에 카운트 합니다. 만약 Binlog_cache_disk_use의 사용량이 Binlog_cache_use보다 월등히 높다면 binlog_cache_size의 크기를 늘려줘야 합니다. 메모리에서 처리하는것이 디스크에서 처리하는 것보다 빠르기 때문입니다.

 

▶ 비 트랜잭션 명령문 캐시 사이즈(binlog_stmt_cache_size)

트랜잭션 중에 발행된 비트랜잭션 문을 보관하기 위한 바이너리 로그의 메모리 버퍼 크기입니다. 서버에서 바이너리 로깅이 활성화되면(log_bin 시스템 변수가 ON으로 설정됨) 서버가 트랜잭션 스토리지 엔진을 지원하는 경우 별도의 바이너리 로그 트랜잭션 및 명령문 캐시가 각 클라이언트에 할당됩니다.트랜잭션에 사용된 비트랜잭션 문에 대한 데이터가 메모리 버퍼의 공간을 초과하는 경우 초과된 데이터는 임시 파일에 저장됩니다.

 

Binlog_stmt_cache_use, Binlog_stmt_cache_disk_use

트랜잭션 중에 큰 비트랜잭션 문을 자주 사용하는 경우 임시 파일에 쓸 필요성을 줄이거나 제거하여 이 캐시 크기를 늘려 더 나은 성능을 얻을 수 있습니다. Binlog_stmt_cache_use 및 Binlog_stmt_cache_disk_use 상태 변수는 이 변수의 크기를 조정하는 데 유용할 수 있습니다.

https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_stmt_cache_size

 

▶ max_binlog_cache_size

max_binlog_cache_size 시스템 옵션은 다중 - 명령문 트랜젝션을 캐시하는데 사용되는 전체 크기를 제한할 수 있습니다. 트랜잭션이 이 바이트 수보다 크면 실패하고 롤백됩니다. 기본값은 4GB이며 최소값은 4096입니다.

 

▶ binlog_error_action

서버가 이진 로그에 쓰거나 이진 로그 파일을 플러시하거나 이진 로그를 디스크와 동기화 할 수없는 경우 복제 마스터의 이진 로그가 일치하지 않아 복제 슬레이브가 마스터와 동기화되지 않을 수 있습니다. binlog_error_action시스템 변수는 이 유형의 오류가 바이너리 로그에 발생했을 경우 취해지는 조치를 제어합니다.

 

+ 기본 설정인 ABORT_SERVER서버는 바이너리 로깅을 중지하고 종료합니다. 이 시점에서 오류의 원인을 식별하고 수정할 수 있습니다. 다시 시작하면 예기치 않은 서버 중지의 경우와 같이 복구가 진행됩니다

 

+ IGNORE_ERROR은 이전 버전의 MySQL과 호환됩니다. 이 설정을 사용하면 서버는 진행중인 트랜잭션을 계속하고 오류를 기록한 다음 바이너리 로깅을 중지하지만 업데이트는 계속 수행합니다. 이 시점에서 오류의 원인을 식별하고 수정할 수 있습니다. 바이너리 로깅을 재개하려면 log_bin서버를 다시 시작해야합니다. 이전 버전과의 호환성이 필요하고, 이 MySQL 서버 인스턴스에서 이진 로그가 필수적이지 않은 경우에만 이 옵션을 사용합니다. 예를 들어, 서버의 간헐적 감사 또는 디버깅에만 바이너리 로그를 사용하고 서버에서 복제하는데 사용하지 않거나 특정 시점 복원 작업에 의존하지 않을 수 있습니다.

 

▶ sync_binlog 옵션

기본적으로 이진 로그는 sync_binlog=1이라는 옵션에 의해 강제적으로 각 쓰기마다 디스크에 동기화됩니다. 만약이 sync_binlog 옵션이 활성화되지 않은 운영 체제 또는 서버 (혹은 MySQL 서버뿐만 아니라)가 크래쉬(긴급장애)된다면, 바이너리 로그에 이 변경사항이 동기화 되지 않아 복구할 수 있는 기회를 놓치게 될 수도 있습니다. 이를 방지하려면 sync_binlog모든 N커미트 그룹 후에 시스템 변수가 2 진 로그를 디스크와 동기화하도록 하십시오. 이를 방지하려면 일정횟수동안 그룹커밋을 한 후에 바이너리 로그를 디스크로 동기화할 수 이도록 sync_binlog 시스템 변수를 사용하는 것입니다.

 

예를 들어, InnoDB 테이블을 사용 중이고 MySQL서버가 COMMIT 명령문을 처리하는 경우 준비된 많은 트랜잭션을 이진 로그에 순서대로 쓰고 이진 로그를 동기화 한 다음, 이 트랜잭션을에 InnoDB에 커밋합니다. 만약 두 작업 사이에 서버가 충돌하면 InnoDB 재시작시 트랜잭션이 롤백 되지만 여전히 이진 로그에 존재합니다. 이러한 문제는 --innodb_support_xa기본값인 1로 설정되어 있으면 해결 됩니다.

이 옵션은 InnoDB의 XA 트랜잭션 지원과 관련이 있지만 또한 바이너리로그 및 InnoDB 데이터 파일이 동기화 되도록합니다. 이 옵션을 사용하여 안전성을 높이려면 트랜잭션을 커밋하기 전에 바이너리 로그와 InnoDB로그를 디스크 에 동기화하도록 MySQL 서버도 구성해야 합니다. InnoDB로그는 기본적으로 동기화되며, sync_binlog=1 옵션은 바이너리로그를 동기화하는 데 사용할 수 있습니다. 이 옵션의 효과는 크래쉬 발생후 재시작시, 트랜잭션 롤백을 수행한 후 MySQL 서버가 최신 바이너리 로그 파일을 스캔하여 트랜잭션 xid값을 수집하고 바이너리 로그 파일의 마지막 유효 위치를 계산하는 것입니다. 그런 다음 MySQL 서버가 바이너리 로그에 성공적으로 기록 된 준비된 트랜잭션을 완료하고 InnoDB에 알려주면, 바이너리 로그를 마지막 유효 위치로 자릅니다.이를 통해 이진 로그에 정확한 InnoDB테이블 데이터가 반영 되므로 롤백된 명령문을 수신하지 않아 슬레이브가 마스터와 동기화 상태를 유지합니다.

 

MySQL 서버가 응급 복구시 바이너리 로그가 이전보다 짧았다는 것을 발견하면, 성공적으로 커밋 된 InnoDB트랜잭션이 하나 이상 부족 하다는 것을 얘기합니다. sync_binlog=1 이고 디스크/파일 시스템이 요청 될 때(일부는 그렇지 않은 경우), 실제 동기화를 수행하지 않았다면 서버가 다음과 같은 오류 메시지를 출력합니다(The binary log file_name is shorter than its expected size). 만약 동기화를 해두었으면 이런 일이 발생 하지 않습니다. 만약 동기화시키지 않은경우 이진 로그가 올바르지 않으므로 마스터 데이터의 새로운 스냅 샷에서 복제를 다시 시작해야합니다.

 

추가 : 만약 일정부분 데이터 손실을 감내해 낼 수 있고 안정성보다는 속도에 우선순위를 둔다면 sync_binlog=1이 옵션을 다른 값으로 변경해서 사용할 수도 있습니다. 즉 비동기 옵션으로 설정을 바꾸면 성능이 대폭 향상될 수도 있습니다. 실시간으로 디스크에 쓰지 않아도 되기 때문에 성능에 큰 도움이 됩니다. 

 

▶ 바이너리 로그 관리

사용된 바이너리 로그 파일을 추적하기 위해 시스템 자체적으로 로그파일이름.index란 파일을 만듭니다. 이 파일 안에 내용을 보면 어떤 로그 파일들이 사용되었는지 확인할 수 있습니다. 특별한 내용은 없고 로그 파일 리스트만 적혀있습니다.

shell > cat 로그파일이름.index

로그파일이름.00001

로그파일이름.00002

로그파일이름.00003

로그파일이름.00004

로그파일이름.00005

 

 

 

■ 바이너리 로그 포멧을 온라인 상태에서 바꾸는 방법

어떤 특정한 이유로 바이너리 로그 포멧을 온라인상에서 바꾸어야 할때가 있습니다(예를 들어 안전하지 않은 명령문을 안전한 명령으로 할수 있게끔 하고 싶은경우). 이럴때 자신에게만 적용되는 로컬 세션과 서버 전체로 바꿀 수 있는 글로벌 세션이 있습니다. 사용방법은 다음과 같습니다. 이건 아무나 변경할 수 있는것은 아니고 권한이 있어야 변경이 가능합니다. Super 권한이 있어야 해서 아무나 수행할 수 있는 명령문은 아닙니다.

 

▶ 로컬 세션 범위의 변경방법

mysql> SET GLOBAL binlog_format = '상태';

mysql> SET GLOBAL binlog_format = 'ROW';

mysql> SET GLOBAL binlog_format = 'MIXED';

개별 클라이언트는 binlog_format의 세션 값을 설정하여 자체 명령문의 로깅 형식을 제어 할 수 있습니다.

 

▶ 서버 전체(글로벌) 범위의 변경방법.

mysql> SET SESSION binlog_format = 'STATEMENT';

mysql> SET SESSION binlog_format = 'ROW';

mysql> SET SESSION binlog_format = 'MIXED';

 

■ 바이너리 로그 내용 확인.

mysqlbinlog 프로그램을 사용하여 바이너리 로그의 내용을 텍스트 형태로 확인해 볼 수 있습니다. 서버 운영중 특정 시점으로 복구할 일이 생길때 이 로그의 특정 시점내용을 추출해 내어 풀백업을 이용해서 원하는 시점으로 되돌릴 수 있습니다. 이때 유용한 프로그램입니다. 간단한 사용방법은 다음과 같습니다.

shell > mysqlbinlog log_file | mysql -h server_name

 

▶ 풀 덤프 파일과 바이너리 로그 파일을 사용하여 복구하는 방법은 아래와 같습니다.

바이너리 로그를 이용하여 특정시점 복구 방법

[MySQL][Backup n Recovery] Binary Log 를 이용하여 특정 시점 복구하기

풀덤프를 이용하여 백업 및 복구하는 방법

[MySQL][Backup n Recovery] mysqldump 프로그램 제대로 파해치기

 

■ 복제에 적용되는 세션 변수

MySQL은 마스터 슬레이브 구조라는 방식을 이용해서 부하분산을 하는데, 이때 바이너리 로그를 이용해서 서버끼리 동기화를 합니다. 이때 다음 시스템 변수의 세션 값은 바이너리 로그에 기록되고 바이너리 로그를 구문 분석 할 때 복제 슬레이브에 의해 적용됩니다

sql_mode, foreign_key_checks, unique_checks, character_set_client, collation_connection, collation_database, collation_server, sql_auto_is_null

 

■ Binary Logging Formats

MySQL 서버는 바이너리 로그를 작성하는데 특별한 포멧(형식)이 있습니다. 이 포멧을 사용해서 바이너리 로그에 저장하는데 MySQL버전에 따라 틀립니다. MySQL에서 제공하는 방식은 총 3가지인데 이것에 대해 알아보겠습니다.

 

▶ STATEMENT 방식.

가장 전통적인 방식으로 문장방식 로깅이라고 합니다. 마스터에서 실행한 SQL을 그대로 바이너리 로그에 작성하고 그 로그를 슬레이브로 전송하여 슬레이브에도 실행하게 하는 방식입니다.

설정방법 : 

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

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

 

▶ Row-Based 방식.

행기반 방식입니다. Statement방식과는 좀 다릅니다. 마스터는 개별 테이블 행이 어떻게 영향을 받는지를 나타내는 이벤트를 이진 로그에 씁니다. 즉 SQL 문법이 아니라 결과값을 바이너리 로그에 저장하게 됩니다. 따라서 테이블은 항상 Primary 키를 사용하여 행을 효율적으로 식별 할 수 있도록하는 것이 중요합니다.

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

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

 

▶ Mix-Logging 방식.

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

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

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

 

▶ 로그 포멧 설정시 유의사항.

로깅 형식은 사용중인 스토리지 엔진에 의해 설정되거나 제한될 수도 있습니다. 이는 다른 스토리지 엔진을 사용하는 마스터와 슬레이브간에 특정 명령문을 복제할 때 문제를 제거하는데 도움이됩니다.

 

또한 명령문 기반 복제의 경우 비 결정적(nondeterministic) Statement 복제에 문제가 있을 수 있습니다. 주어진 명령문이 Statement 기반 복제에 안전한지 여부를 결정할 때, MySQL은 명령문 기반 로깅을 사용하여 명령문을 복제 할 수 있는지 여부를 결정합니다. MySQL이 보증을 할 수 없는 경우, 명령문을 잠재적으로 신뢰할 수없는 것으로 표시하고 경고를 발생시킵니다. 명령문은 명령문 형식으로 로깅하는것이 안전하지 않을 수 있습니다. 이유는 만약 상황에 따라 값이 틀려지는 경우(예를 들면 서버의 현재시간을 값으로 읽어들여 Insert하는 경우 슬레이브에서 실행하면 같은 sql문으로 실행하더라도 값이 틀려짐.)가 있기 때문에 안전하지 않은것으로 표시를 하는 것입니다. 이때 row 포멧 방식으로 로깅을 하면 값을 복제하는것이기 때문에 안전한 것으로 판단을 합니다.

 

■ 바이너리 설정방법 및 유의사항. [케이스별 바이너리 포멧을 결정하는 방법 포함]

클라이언트가 세션별로 바이너리 로깅을 설정하려는 이유는 여러 가지가 있습니다.

- 작은 변경이 많은 세션의 데이터베이스는 row-based로깅 사용을 원할 수 있습니다.

- WHERE 절에서 많은 행과 일치하는 업데이트를 수행하는 세션은 여러 행보다 몇 개의 명령문을 기록하는 것이 더 효율적이므로 statement-based 로깅을 사용하려고 할 수 있습니다.

- 일부 명령문은 마스터에서 많은 실행 시간이 필요하지만 몇 개의 행만 수정됩니다. 따라서 행 기반 로깅을 사용하여 복제하는 것이 좋습니다.

 

런타임에 복제 형식을 전환 할 수 없는 예외경우가 있습니다.

- 저장된 기능 또는 트리거 내에서.

- NDB 스토리지 엔진이 사용 가능한 경우

- 세션이 현재 행 기반 복제 모드이고 열려있는 임시 테이블이있는 경우

이러한 경우에 형식을 전환하려고하면 오류가 발생합니다.

 

복제가 진행되는 동안 복제 형식을 전환하면 문제가 발생할 수도 있습니다. 각 MySQL 서버는 유일한 바이너리 로깅 형식만 설정할 수 있습니다(binlog_format이 전역 또는 세션 범위로 설정되어 있는지 여부). 이는 복제 마스터에서 로깅 형식을 변경해도 슬레이브가 로깅 형식을 일치하도록 변경하지 않습니다(바이너리 포멧 형식이 중간에 바뀌게 되면 파일 포멧이 꼬이기 때문에 문제가 발생하는데 이를 예방히기 위함). STATEMENT 모드를 사용하면 binlog_format 시스템 변수가 복제되지 않습니다. MIXED 또는 ROW 로깅 모드를 사용하는 경우 복제되지만 슬레이브는 이를 무시합니다.

 

복제 슬레이브는 ROW 로깅 형식으로 수신된 바이너리 로그 항목을 자체 바이너리 로그에 사용하기 위해 STATEMENT 형식으로 변환 할 수 없습니다. 만약 마스터가 ROW라면 슬레이브는 ROW 또는 MIXED 형식을 사용해야합니다. 

 

복제가 STATEMENT 형식의 슬레이브에 진행되는 동안 마스터의 바이너리 로깅 형식을 STATEMENT에서 ROW 또는 MIXED로 변경하면 행 이벤트 실행 오류와 같은 오류로 인해 복제가 실패 할 수 있습니다.

'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'

마스터가 여전히 MIXED 또는 ROW 형식을 사용하는 경우 슬레이브의 바이너리 로깅 형식을 STATEMENT 형식으로 변경하면 동일한 유형의 복제 오류가 발생합니다. 형식을 안전하게 변경하려면 복제를 중지하고 마스터와 슬레이브 모두에서 동일한 변경이 이루어 지도록해야합니다.

 

InnoDB 테이블을 사용 중이고 트랜잭션 분리 레벨이 READ COMMITTED 또는 READ UNCOMMITTED 인 경우 Row 기반 로깅 만 사용할 수 있습니다. 로깅 형식을 STATEMENT로 변경할 수 있지만 런타임시 그렇게하면 InnoDB가 더 이상 삽입을 수행 할 수 없으므로 오류가 매우 빠르게 발생합니다.

 

이진 로그 형식을 ROW로 설정하면 Row-Based 형식을 사용하여 많은 변경 내용이 바이너리 로그에 기록됩니다. 그러나 일부 변경 사항은 여전히 ​​명령문 기반 형식을 사용합니다. 예제에는 CREATE TABLE, ALTER TABLE 또는 DROP TABLE과 같은 모든 DDL (데이터 정의 언어) 문이 포함됩니다.

 

--binlog-row-event-max-size 옵션은 행 기반 복제가 가능한 서버에 사용할 수 있습니다. ROW들은 이 옵션의 값을 초과하지 않는 바이트 단위의 크기로 이진 로그에 청크들로 저장됩니다. 값은 256의 배수여야합니다. 기본값은 8192입니다.

 

statement-based 로깅을 복제에 사용하는 경우, 데이터 수정이 비 결정적(nondeterministic) 방식으로 명령문이 설계되면 마스터 및 슬레이브의 데이터가 다르게 될 수 있습니다. 즉, query optimizer 프로그램의 실행에 맡겨집니다. 일반적으로 이는 복제 외부에서도 좋은 방법이 아닙니다.

 

 

 

■ MIXED 로깅 형식

MIXED 로깅 형식으로 실행하면 서버는 다음 조건이 만족할 때 Statement-based로깅에서 Row-based로깅으로 자동 전환됩니다.

▶ 함수에 UUID()가 포함 된 경우

▶ AUTO_INCREMENT 컬럼이있는 하나 이상의 테이블이 업데이트되고 트리거 또는 저장된 함수가 호출 된 경우. 다른 모든 안전하지 않은(Unsafe) 명령문과 마찬가지로 binlog_format = STATEMENT 인 경우 경고가 생성됩니다.

▶ 뷰 내용에 Row-based 복제가 필요한 경우 뷰를 생성하는 문법에서도 뷰를 사용합니다. 예를 들어, 뷰를 작성하는 명령문이 UUID() 함수를 사용할 때 발생합니다.

▶ UDF 호출이 관련된 경우.

▶ 명령문이 행별로 로깅되고 명령문을 실행 한 세션에 임시 테이블이있는 경우, 해당 세션에서 사용중인 모든 임시 테이블이 삭제 될 때까지 모든 후속 명령문(임시 테이블에 액세스하는 명령 제외)에 행별 로깅이 사용됩니다.

+ 실제로 임시 테이블이 기록되는지 여부에 관계없이 적용됩니다.

+ 임시 테이블은 Row-based 형식을 사용하여 기록 할 수 없습니다. 따라서 행 기반 로깅이 사용되면 해당 테이블을 사용하는 모든 후속 명령문은 안전하지(Unsafe) 않습니다. 서버는 세션 중에 더 이상 임시 테이블을 보유하지 않을 때까지 세션 중에 실행 된 모든 명령문을 안전하지 않은 것으로 처리하여 이 조건에 만족하도록 합니다.

▶ FOUND_ROWS() 또는 ROW_COUNT()를 사용

▶ USER(), CURRENT_USER() 또는 CURRENT_USER를 사용

▶ 명령문이 하나 이상의 시스템 변수를 나타냄(단, 아래 예외처리는 제외함)

+ 예외 처리.

세션 범위와 함께 사용되는 경우에만 다음 시스템 변수로 인해 로깅 형식이 전환되지 않습니다.

auto_increment_increment, auto_increment_offset, character_set_client, character_set_connection, character_set_database,character_set_server, collation_connection, collation_database, collation_server, foreign_key_checks, identity, last_insert_id, lc_time_names, pseudo_thread_id, sql_auto_is_null, time_zone, timestamp, unique_checks

▶ 관련된 테이블 중 하나가 mysql 데이터베이스의 로그 테이블 인 경우.

▶ LOAD_FILE() 함수를 사용하는 경우

+ 참고사항

LOAD_FILE()은 Row-based로깅 명령문입니다. 이것뿐만이 아니라 만약 Row-based 로깅을 사용하여 작성해야하는 명령을 Statement-based 로깅을 사용하여 명령문을 실행하려고하면 경고가 생성됩니다. 경고는 클라이언트 (SHOW WARNINGS 출력)와 mysqld 오류 로그를 통해 표시됩니다. 이러한 명령문이 실행될 때마다 SHOW WARNINGS 테이블에 경고가 입력됩니다. 그러나 각 클라이언트 세션에 대해 경고를 생성한 첫 번째 명령만 오류 로그에 기록되어 로그가 과다하게 쌓이는것을 방지합니다.

 

▶ 위의 결정 외에도 개별 엔진은 테이블의 정보가 업데이트 될 때 사용되는 로깅 형식을 결정할 수도 있습니다. 개별 엔진의 로깅 기능은 다음과 같이 정의 할 수 있습니다.

-엔진이 Row-based 로깅을 지원하는 경우 엔진은 행 로깅이 가능합니다.

-엔진이 Statement-based 로깅을 지원하는 경우 엔진은 명령문 로깅이 가능합니다.

 

▶ 지정된 스토리지 엔진은 로깅 형식 중 하나 또는 둘 다를 지원할 수 있습니다. 다음 표는 각 엔진이 지원하는 형식을 보여줍니다.

Storage Engine	Row Logging 지원	Statement Logging 지원
ARCHIVE	Yes	Yes
BLACKHOLE	Yes	Yes
CSV	Yes	Yes
EXAMPLE	Yes	No
FEDERATED	Yes	Yes
HEAP	Yes	Yes
InnoDB	Yes	Yes when the transaction isolation level is REPEATABLE READ or SERIALIZABLE; No otherwise.
MyISAM	Yes	Yes
MERGE	Yes	Yes
NDB	Yes	No

 

▶ 명령문의 로그 여부 및 사용되는 로깅 모드는 명령문 유형 (안전, 안전하지 않거나 이진 삽입), 이진 로깅 형식 (STATEMENT, ROW 또는 MIXED) 및 로깅 기능에 따라 결정됩니다. 스토리지 엔진 (문장 가능, 행 가능, 둘 다 또는 둘 다). 바이너리 주입은 ROW 형식을 사용하여 기록해야하는 변경 사항을 기록하는 것을 말합니다.

 

▶ 명령문은 경고의 유무에 관계없이 기록 될 수 있습니다. 실패한 명령문은 로그되지 않지만 로그에 오류가 발생합니다. 이것은 다음 결정 테이블에 표시되어 있습니다. 유형, binlog_format, SLC (Statement-Logging Capable) 및 RLC(Row-Logging Capable)열은 조건을 설명하고 오류/경고 및 로깅된 열은 해당 작업을 나타냅니다. SLC는 "상태 기록 가능"을 나타내고 RLC는 "행 기록 가능"을 나타냅니다.

Type	binlog_format	SLC	RLC	Error / Warning	Logged as
*	*	No	No	Error: Cannot execute statement: Binary logging is impossible since at least one engine is involved that is both row-incapable and statement-incapable.	-
Safe	STATEMENT	Yes	No	-	STATEMENT
Safe	MIXED	Yes	No	-	STATEMENT
Safe	ROW	Yes	No	Error: Cannot execute statement: Binary logging is impossible since BINLOG_FORMAT = ROW and at least one table uses a storage engine that is not capable of row-based logging.	-
Unsafe	STATEMENT	Yes	No	Warning: Unsafe statement binlogged in statement format, since BINLOG_FORMAT = STATEMENT	STATEMENT
Unsafe	MIXED	Yes	No	Error: Cannot execute statement: Binary logging of an unsafe statement is impossible when the storage engine is limited to statement-based logging, even if BINLOG_FORMAT = MIXED.	-
Unsafe	ROW	Yes	No	Error: Cannot execute statement: Binary logging is impossible since BINLOG_FORMAT = ROW and at least one table uses a storage engine that is not capable of row-based logging.	-
Row Injection	STATEMENT	Yes	No	Error: Cannot execute row injection: Binary logging is not possible since at least one table uses a storage engine that is not capable of row-based logging.	-
Row Injection	MIXED	Yes	No	Error: Cannot execute row injection: Binary logging is not possible since at least one table uses a storage engine that is not capable of row-based logging.	-
Row Injection	ROW	Yes	No	Error: Cannot execute row injection: Binary logging is not possible since at least one table uses a storage engine that is not capable of row-based logging.	-
Safe	STATEMENT	No	Yes	Error: Cannot execute statement: Binary logging is impossible since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine that is not capable of statement-based logging.	-
Safe	MIXED	No	Yes	-	ROW
Safe	ROW	No	Yes	-	ROW
Unsafe	STATEMENT	No	Yes	Error: Cannot execute statement: Binary logging is impossible since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine that is not capable of statement-based logging.	-
Unsafe	MIXED	No	Yes	-	ROW
Unsafe	ROW	No	Yes	-	ROW
Row Injection	STATEMENT	No	Yes	Error: Cannot execute row injection: Binary logging is not possible since BINLOG_FORMAT = STATEMENT.	-
Row Injection	MIXED	No	Yes	-	ROW
Row Injection	ROW	No	Yes	-	ROW
Safe	STATEMENT	Yes	Yes	-	STATEMENT
Safe	MIXED	Yes	Yes	-	STATEMENT
Safe	ROW	Yes	Yes	-	ROW
Unsafe	STATEMENT	Yes	Yes	Warning: Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT.	STATEMENT
Unsafe	MIXED	Yes	Yes	-	ROW
Unsafe	ROW	Yes	Yes	-	ROW
Row Injection	STATEMENT	Yes	Yes	Error: Cannot execute row injection: Binary logging is not possible because BINLOG_FORMAT = STATEMENT.	-
Row Injection	MIXED	Yes	Yes	-	ROW
Row Injection	ROW	Yes	Yes	-	ROW

 

 

▶ 결정(DETERMINISTIC)에 의해 경고가 생성되면 표준 MySQL 경고가 생성되며 SHOW WARNINGS를 명령을 통하여 확인해볼 수 있습니다. 이 정보는 mysqld 에러 로그에도 기록됩니다. 클라이언트 컨넥션 당 각 오류 인스턴스에 대해 하나의 오류만 기록되어 로그 플러딩(로그가 과다하게 쌓임)을 방지합니다. 로그 메시지에는 수행한 SQL문이 포함됩니다.

 

▶ 슬레이브 서버에서 log_error_verbosity가 2 이상인 경우, 슬레이브는 다른 릴레이 로그로 전환 할 때 작업을 시작하는 바이너리 로그 및 릴레이 로그 좌표, 연결이 끊긴 후 다시 연결될 때, 명령문 기반 로깅에 안전하지 않은 명령문 등과 같은 상태에 대한 정보를 제공하기 위해 오류 로그에 메시지를 인쇄합니다.

 

■ mysql 데이터베이스 테이블을 직접 수정할때 로깅 포멧

Query Tool을 이용하여 mysql 시스템 데이터베이스를 직접 수정할 때가 있습니다. 이때 바이너리 로그 포멧별 전송되는 방식입니다.

 

mysql 데이터베이스에 있는 grant 테이블의 내용은 직접 (예 : INSERT 또는 DELETE로) 또는 간접적으로 (예 : GRANT 또는 CREATE USER로) 수정할 수 있습니다. mysql 데이터베이스 테이블에 영향을주는 명령문은 다음 규칙을 사용하여 바이너리 로그에 기록됩니다.

 

mysql 데이터베이스 테이블에서 데이터를 직접 변경하는 데이터 조작 명령문은 binlog_format 시스템 변수의 설정에 따라 기록됩니다. 이것은 INSERT, UPDATE, DELETE, REPLACE, DO, LOAD DATA, SELECT 및 TRUNCATE TABLE과 같은 명령문과 관련이 있습니다.

 

mysql 데이터베이스를 간접적으로 변경하는 명령문은 binlog_format 값에 관계없이 명령문으로 기록됩니다. 이는 GRANT, REVOKE, SET PASSWORD, RENAME USER, CREATE (CREATE TABLE ... SELECT를 제외한 모든 양식), ALTER (모든 양식) 및 DROP (모든 양식)과 같은 명령문과 관련됩니다.

 

CREATE TABLE ... SELECT는 데이터 정의와 데이터 조작의 조합입니다. CREATE TABLE 부분은 명령문 형식을 사용하여 기록되고 SELECT 부분은 binlog_format 값에 따라 기록됩니다.