[MySQL][Master-Slave] 복제 환경에서 Binary Log 특징

복제를 사용할 때 이진 로그에 대해 어떤 타입을 선택할지 정해야 하는데 이때 아래 내용들을 참고해서 설정하시면 도움이 됩니다. 복제를 할 때 safe(안전한), unsafe(안전하지 않은) 쿼리가 있는데 이것을 알면 업무 특성에 따라 어떤 바이너리 로그로 설정할지 도움이 됩니다.

 

■ Binary Logging의 종류

▶ Statement-based Binary Logging

Staatement-based 바이너리 로깅을 사용할 때 마스터는 SQL 문을 바이너리 로그에 씁니다. 슬레이브에서 마스터를 복제하면 슬레이브에서 SQL 문을 실행합니다. 이를 Statement-based Replication (SBR로 약칭 할 수 있음)이라고하며, 이는 MySQL Statement-based 바이너리 로깅 형식에 해당합니다.

 

▶ Row-based Logging

행 기반 로깅을 사용하는 경우 마스터는 개별 테이블 행이 변경되는 방법을 나타내는 이벤트를 이진 로그에 씁니다. 마스터에 대한 슬레이브 복제는 테이블 행의 변경 사항을 나타내는 이벤트를 슬레이브에 복사하여 작동합니다. 이를 Row-based Replication(RBR로 약칭 할 수 있음)이라고합니다.

 

▶ mix of both statement-based and row-based logging

변경 내용을 기록하기에 가장 적합한 명령문 기반(statement-based) 및 행 기반(row-based) 로깅을 함께 사용하도록 MySQL을 구성 할 수도 있습니다. 이것을 혼합 형식 로깅이라고 합니다. 혼합 형식 로깅을 사용하는 경우 기본적으로 명령문 기반 로그(statement-based)가 사용됩니다. 특정 명령문과 사용중인 스토리지 엔진에 따라 로그가 자동으로 행 기반으로 전환됩니다. 혼합 형식을 사용한 복제를 혼합 기반 복제 또는 혼합 형식 복제라고합니다.

 

■ Statement-Based 복제의 장점과 단점

▶ 장점

이미 입증된 기술로, 로그 파일에 적은 데이터가 기록됩니다. 업데이트 또는 삭제가 많은 행에 영향을주는 경우 로그 파일에 필요한 저장 공간이 훨씬 줄어 듭니다. 또한 백업을 가져오고 복원하는 것이 더 빠르게 수행 될 수 있음을 의미합니다. 로그 파일에는 데이터베이스를 감사하는 데 사용할 수 있도록 변경 한 모든 명령문이 포함됩니다.

 

▶ 단점

+ statement-based 복제는 안전하지 않습니다(unsafe). 명령문 기반(statement-based) 복제를 사용하여 데이터를 수정하는 모든 명령문 (예 : INSERT DELETE, UPDATE 및 REPLACE 문)을 복제 할 수있는 것은 아닙니다. 명령문 기반(statement-based) 복제를 사용할 때는 비 결정적(nondeterministic) 동작을 복제하기가 어렵습니다. 이러한 DML (Data Modification Language) 문의 예는 다음과 같습니다.

 

- UDF(User Define Fundtion) 또는 Stored Program에 의해 리턴 된 값 또는 제공된 매개 변수 이외의 요인에 따라 달라지기 때문에 결정적이지 않은(nondeterministic) UDF 또는 저장된 프로그램에 의존하는 명령문. (그러나 행 기반 복제는 UDF 또는 스토어드 프로그램이 리턴 한 값을 단순히 복제하므로 테이블 행 및 데이터에 미치는 영향은 마스터 및 슬레이브 모두에 ​​동일합니다.)

- ORDER BY없이 LIMIT 절을 사용하는 DELETE 및 UPDATE 문은 결정적이지 않습니다(nondeterministic).

- 결정적(deterministic) UDF는 슬레이브에 적용되어야합니다.

- 다음 함수들을 사용하는 명령문은 statement-based 복제를 사용하여 올바르게 복제 할 수 없습니다.

LOAD_FILE()

UUID(), UUID_SHORT()

USER()

FOUND_ROWS()

SYSDATE() (unless both the master and the slave are started with the --sysdate-is-now option)

GET_LOCK()

IS_FREE_LOCK()

IS_USED_LOCK()

MASTER_POS_WAIT()

RAND()

RELEASE_LOCK()

SLEEP()

VERSION()

그러나 NOW() 등을 포함한 명령문 기반(statement-based) 복제를 사용하여 다른 모든 function이 올바르게 복제됩니다.

 

- 명령문 기반(statement-based) 복제를 사용하여 올바르게 복제 할 수없는 명령문은 다음과 같은 경고와 함께 기록됩니다.

[Warning] Statement is not safe to log in statement format.

이 경우에도 비슷한 경고가 클라이언트에게 발행됩니다. 클라이언트는 SHOW WARNINGS를 사용하여 이를 표시 할 수 있습니다.

- INSERT ... SELECT에는 행 기반 복제보다 많은 행 수준 잠금이 필요합니다.

- WHERE 절에서 인덱스가 사용되지 않으므로 테이블 스캔이 필요한 UPDATE 문은 행 기반 복제보다 많은 수의 행을 잠가야 합니다.

- InnoDB의 경우 : AUTO_INCREMENT를 사용하는 INSERT 문은 충돌하지 않는 다른 INSERT 문을 차단합니다.

- 복잡한 명령문의 경우, 행을 업데이트하거나 삽입하기 전에 명령문을 평가하고 슬레이브에서 실행해야합니다. 행 기반 복제를 사용하면 슬레이브는 영향을 받는 행만 수정하고 전체 문을 실행하지 않아도됩니다.

- 특히 복잡한 명령문을 실행할 때 슬레이브에 대한 평가 오류가있는 경우, 명령문 기반 복제는 시간이 지남에 따라 영향을받는 행에서 오류 마진을 천천히 증가시킬 수 있습니다.

- 저장된 함수는 호출 명령문과 동일한 NOW() 값으로 실행됩니다. 그러나 이것은 저장 프로 시저에는 해당되지 않습니다.

- 테이블 정의는 마스터와 슬레이브에서 (거의) 동일해야합니다. 

 

▶ Row-Based 복제의 장점과 단점

MySQL은 Statement-based Logging (SBL), Row-based Logging (RBL) 또는 Mixed-based Logging을 사용합니다. 사용되는 바이너리 로그 유형은 로깅의 크기와 효율성에 영향을줍니다. 따라서 Row-based Replication (RBR) 또는 Statement-based Replication (SBR) 중에서 선택하는 것은 응용 프로그램과 환경에 따라 다릅니다. 이 섹션에서는 행기반(Row-based) 형식 로그를 사용할 때 발생하는 알려진 문제와 복제시 이를 사용하는 모범 사례에 대해 알아봅니다.

 

+ 임시 테이블은 행 기반(Row-Based) 형식을 사용할 때 복제되지 않습니다. 혼합 형식 로깅(Mixed-based Logging)을 사용하는 경우 임시 테이블과 관련된 안전한(safe) 문은 statement-based 형식을 사용하여 기록됩니다.

 

행기반(row-based) 형식을 사용할 때는 필요하지 않으므로 임시 테이블은 복제되지 않습니다. 또한 임시 테이블은이를 생성 한 스레드에서만 읽을 수 있으므로 명령문 기반 형식을 사용하는 경우에도 복제하여 얻을 수있는 이점은 거의 없습니다.

 

임시 테이블이 작성된 경우에도 런타임시 명령문기반(statement-based)에서 행 기반(row-based) 바이너리 로깅 형식으로 전환 할 수 있습니다. MySQL 5.7.25부터 MySQL 서버는 각 임시 테이블을 만들 때 적용되었던 로깅 모드를 추적합니다. 클라이언트 세션이 종료되고, 서버가 여전히 실행중이고 statement-based 바이너리 로깅이 사용 중일 때 작성된 각 임시 테이블에 대해 DROP TEMPORARY TABLE IF EXISTS 문을 로깅합니다. 테이블 작성시 행 기반 또는 mixed 형식 바이너리 로깅을 사용중인 경우 DROP TEMPORARY TABLE IF EXISTS 문이 기록되지 않습니다. 이전 릴리스에서는 DROP TEMPORARY TABLE IF EXISTS 문이 유효한 로깅 모드에 관계없이 로깅되었습니다.

 

명령문에 의해 영향을받는 비 트랜잭션 테이블이 임시 테이블 일때 한해서, binlog_format = ROW를 사용할 때 임시 테이블을 포함하는 비 트랜잭션 DML 명령문이 허용됩니다.

 

+ 비 트랜잭션 테이블의 RBL(Row-Based Logging) 및 동기화.

많은 행이 영향을 받으면, 일련의 변경 사항이 여러 이벤트로 분할됩니다. 명령문이 커밋되면 이러한 모든 이벤트가 이진 로그에 기록됩니다. 슬레이브에서 실행할 때 관련된 모든 테이블에서 테이블 잠금이 수행 된 다음 행이 배치 모드로 적용됩니다. 슬레이브의 테이블 사본에 사용 된 엔진에 따라 이는 효과적이거나 효과적이지 않을 수 있습니다.

 

+ 지연 시간 및 이진 로그 크기

RBL(Row-Based Logging)은 각 행의 변경 사항을 이진 로그에 기록하므로 크기가 매우 빠르게 증가 할 수 있습니다. 이로 인해 마스터의 슬레이브와 일치하는 슬레이브를 변경하는 데 필요한 시간이 크게 늘어날 수 있습니다. 어플리케이션에서 이러한 지연이 발생할 가능성을 알고 있어야합니다.

 

+ 이진 로그를 읽을 때.

mysqlbinlog는 BINLOG 문을 사용하여 이진 로그에 행 기반 이벤트를 표시합니다. 이 명령문은 이벤트를 기본 64-인코딩 문자열로 표시하며 그 의미는 분명하지 않습니다. --base64-output=DECODE-ROWS 및 --verbose 옵션으로 호출하면 mysqlbinlog는 이진 로그의 내용을 사람이 읽을 수 있도록 형식화합니다. 이진 로그 이벤트가 행 기반 형식으로 작성되고 복제 또는 데이터베이스 실패를 읽거나 복구하려는 경우 이 명령을 사용하여 이진 로그의 내용을 읽을 수 있습니다.

 

+ 또한 slave_exec_mode를 STRICT로 설정하면 충분합니다. 스토리지 엔진에 대한 기본값입니다.

 

+ 서버 ID를 기반으로 한 필터링은 지원되지 않습니다. CHANGE MASTER TO 문에 IGNORE_SERVER_IDS 옵션을 사용하여 서버 ID를 기준으로 필터링 할 수 있습니다. 이 옵션은 명령문 기반(statements-based) 및 행 기반(Row-Based) 로깅 형식과 함께 작동합니다. 일부 슬레이브에서 변경 사항을 필터링하는 또다른 방법은 @@ server_id <> id_value 절과 UPDATE 및 DELETE 문이 포함 된 WHERE 절을 사용하는 것입니다. 예를 들어 WHERE @@ server_id <> 1 같은 쿼리는 행 기반 로깅에서 제대로 작동하지 않습니다. 명령문 필터링에 server_id 시스템 변수를 사용하려면 명령문 기반 로깅을 사용하셔야 합니다.

 

+ 데이터베이스 레벨 복제 옵션.

--replicate-do-db, --replicate-ignore-db 및 --replicate-rewrite-db 옵션의 효과는 행 기반 또는 명령문 기반 로깅의 사용 여부에 따라 상당히 다릅니다. 따라서 데이터베이스 수준 옵션을 피하고 대신 --replicate-do-table 및 --replicate-ignore-table과 같은 테이블 수준 옵션을 사용하는 것이 좋습니다.

 

+ RBL(Row-Based Logging), 비 트랜잭션 테이블 및 중지 된 슬레이브 행 기반 로깅을 사용할 때 슬레이브 스레드가 비 트랜잭션 테이블을 업데이트하는 동안 슬레이브 서버가 중지되면 슬레이브 데이터베이스가 불일치 상태에 도달 할 수 있습니다. 이러한 이유로 Row-based 형식을 사용하여 복제 되는 모든 테이블은 InnoDB와 같은 트랜잭션 스토리지 엔진을 사용하는 것이 좋습니다. 슬레이브 MySQL 서버를 종료하기 전에 STOP SLAVE 또는 STOP SLAVE SQL_THREAD를 사용하면 문제가 발생하는 것을 방지 할 수 있으며 사용하는 로깅 형식 또는 스토리지 엔진에 관계없이 항상 권장됩니다.

 

 

 

■ 바이너리 로깅에서 안전(safe)하고 안전하지(unsafe) 않은 문법의 결정

MySQL Replication에서 명령문의 "안전성"은 명령문과 그 효과가 명령문 기반(statement-based) 형식을 사용하여 올바르게 복제 될 수 있는지 여부를 나타냅니다. 이것이 문법에 해당되는 경우, 우리는 그 문법을 안전한 것으로 참고합니다. 그렇지 않으면 안전하지 않은 것으로 참고합니다.

 

일반적으로 문법이 결정적(deterministic)이면 안전(safe)하고, 그렇지 않으면 안전하지 않습니다(unsafe). 그러나 특정 비 결정적(nondeterministic) 기능(function)은 안전하지 않은 것으로 고려되지 않습니다 (이 섹션 뒷부분의 안전하지 않은 것으로 간주되는 비 결정적 기능 참조). 또한 하드웨어 종속적 인 부동 소수점 수학 함수의 결과를 사용하는 명령문은 항상 안전하지 않은 것으로 간주됩니다.

 

▶ 안전(safe)하고 안전하지 않은(unsafe) 명령(문법) 처리.

명령문은 명령문이 안전하다고 간주되는지 여부 및 이진 로깅 형식 (즉, 현재 binlog_format 값)과 관련하여 다르게 처리됩니다.

 

+ 행 기반 로깅을 사용하는 경우 안전하고 안전하지 않은 명령문을 처리 할 때 구별되지 않습니다.

 

+ 혼합 형식 로깅(mixed-format logging)을 사용하는 경우 안전하지 않은 것으로 표시된 명령문은 행 기반 형식을 사용하여 로깅됩니다. 안전(safe)하다고 간주되는 명령문은 명령문 기반 형식을 사용하여 기록됩니다.

 

+ 명령문 기반 로깅(statement-based logging)을 사용할 때 안전하지 않은 것으로 표시된 명령문은 이 영향에 대한 경고를 생성합니다. 안전한 명령문은 정상적으로 기록됩니다.

 

▶ 안전하지 않은(unsafe) 것으로 표시된 각 명령문은 경고를 생성합니다. 이전에는 마스터에서 많은 수의 명령문이 실행 된 경우 오류 로그 파일이 지나치게 커질 수 있습니다. 이를 방지하기 위해 MySQL 5.7은 다음과 같이 작동하는 경고 억제 메커니즘을 제공합니다. 가장 최근 50 개의 ER_BINLOG_UNSAFE_STATEMENT 경고가 50 초 동안 50 회 이상 생성 될 때마다 경고 억제가 활성화됩니다. 활성화되면 이러한 경고가 오류 로그에 기록되지 않습니다. 대신 이 유형의 각 50 개의 경고에 대해 마지막 S 초 동안 마지막 경고가 N 번 반복되는것은 오류 로그에 기록됩니다. 가장 최근에 발생한 경고 50 개가 50 초 이내에 발생하는 한 계속됩니다. 한번 이 비율이 임계 값 아래로 떨어지면 경고가 다시 한 번 정상적으로 기록됩니다. 경고 억제는 명령문 기반 로깅에 대한 명령문의 안전성이 결정되는 방법이지만, 경고가 클라이언트에 전송되는 방법에는 영향을 미치지 않습니다. MySQL 클라이언트는 여전히 각 해당 명령문에 대해 하나의 경고를 받습니다.

 

▶ 안전하지 않은(unsafe) 것으로 간주되는 명령문들.

다음과 같은 특성을 가진 진술은 안전하지 않은 것으로 간주됩니다.

 

+ 슬레이브에서 다른 값을 반환 할 수 있는 시스템 기능이 포함 된 명령문. 이러한 함수에는 다음 함수들이 포함됩니다.

FOUND_ROWS(), GET_LOCK(), IS_FREE_LOCK(), IS_USED_LOCK(), LOAD_FILE(), MASTER_POS_WAIT(), PASSWORD(), RAND(), RELEASE_LOCK(), ROW_COUNT(), SESSION_USER(), SLEEP(), SYSDATE(), SYSTEM_USER(), USER(), UUID(), UUID_SHORT()

 

+ 비 결정적(Nondeterministic) 기능은 안전하지(unsafe) 않은 것으로 간주됩니다. 이러한 함수는 결정적(deterministic)이지 않지만 이들은 로깅 및 복제를 위한 목적으로써 안전한것으로 다루어집니다.

CONNECTION_ID(), CURDATE(), CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP(), CURTIME(),, LAST_INSERT_ID(), LOCALTIME(), LOCALTIMESTAMP(), NOW(), UNIX_TIMESTAMP(), UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP().

 

+ 시스템 변수에 대한 참조 대부분의 시스템 변수는 명령문 기반 형식을 사용하여 올바르게 복제되지 않습니다.

 

+ UDF들. UDF의 기능을 제어 할 수 없으므로 안전하지 않은(unsafe) 명령문을 실행한다고 가정해야합니다.

 

+ Fulltext 플러그인. 이 플러그인은 다른 MySQL 서버에서 다르게 작동 할 수 있습니다. 따라서 이에 따라 계산 된 결과가 다를 수 있습니다. 이러한 이유로 전체 텍스트 플러그인에 의존하는 모든 명령문은 안전하지 않은 것으로 취급됩니다.

 

+ 트리거 또는 저장된 프로그램은 AUTO_INCREMENT 열이있는 테이블을 업데이트합니다. 행이 업데이트되는 순서가 마스터와 슬레이브에서 다를 수 있기 때문에 안전하지 않습니다(unsafe).

또한, 이 복합 키의 첫 번째 열이 아닌 AUTO_INCREMENT 열을 포함하는 복합 기본키가 있는 테이블에 대한 INSERT는 안전하지 않습니다.

 

+ INSERT ... ON DUPLICATE KEY UPDATE 문은 여러 개의 Primary 또는 Unique 키가 있는 테이블에 있습니다. 둘 이상의 Primary 또는 Unique 키가 포함 된 테이블에 대해 실행될 때 이 명령문은 안전하지 않은 것으로 간주되며, 스토리지 엔진이 키를 확인하는 순서에 영향을 미치며, 결정적이지 않으며, MySQL 서버 의존성에 의해 선택된 행들이 업데이트됩니다.

 

하나 이상의 고유 또는 기본 키가있는 테이블에 대한 INSERT ... ON DUPLICATE KEY UPDATE 문은 명령문 기반 복제에 안전하지 않은 것으로 표시됩니다.

 

+ LIMIT를 사용하여 업데이트합니다. 행이 검색되는 순서는 지정되지 않았으므로 안전하지 않은(unsafe) 것으로 간주됩니다.

 

+ 로그 테이블에 액세스하거나 참조합니다. 시스템 로그 테이블의 내용은 마스터와 슬레이브간에 다를 수 있습니다.

 

+ 트랜잭션 작업 후 비 트랜잭션 작업 트랜잭션 내에서 트랜잭션 읽기 또는 쓰기가 수행 된 후 비 트랜잭션 읽기 또는 쓰기가 실행되도록 허용하는 것은 안전하지 않은(unsafe) 것으로 간주됩니다.

 

+ 자체 로깅 테이블에 액세스하거나 참조합니다. 자체 로깅 테이블에 대한 모든 읽기 및 쓰기는 안전하지 않은(unsafe) 것으로 간주됩니다. 트랜잭션 내에서 자체 로깅 테이블에 대한 읽기 또는 쓰기 뒤에 오는 명령문도 안전하지 않은 것으로 간주됩니다.

 

+ LOAD DATA 문. LOAD DATA는 안전하지 않은(unsafe) 것으로 취급되며 binlog_format = mixed 일 때 명령문은 행 기반 형식으로 로그됩니다. binlog_format = statement LOAD DATA는 다른 안전하지 않은 명령문과 달리 경고를 생성하지 않습니다.

 

+ XA 트랜잭션. 마스터에서 병렬로 커밋 된 두 개의 XA 트랜잭션이 역순으로 슬레이브에서 준비되는 경우, 안전하게 해결할 수 없는 명령문 기반 복제로 잠금 종속성이 발생할 수 있으며 슬레이브에서 교착 상태로 인해 복제가 실패 할 수 있습니다. binlog_format = STATEMENT가 설정되면 XA 트랜잭션 내부의 DML 문이 안전하지 않은 것으로 플래그가 지정되고 경고가 생성됩니다. binlog_format = MIXED 또는 binlog_format = ROW가 설정되면 XA 트랜잭션 내부의 DML 문은 행 기반 복제를 사용하여 기록되며 잠재적인 문제는 없습니다.

 

■ Master-Slave 복제 환경 관련 참고글

[Master-Slave] 복제소개 및 사용방법

[MySQL][Master-Slave] 복제소개 및 사용방법

[Master-Slave] 복제환경 모니터링

[MySQL][Master-Slave] 복제 환경 모니터링

[Master-Slave] 복제 필터링 - 복제평가방법

[MySQL][Master-Slave] 복제 필터링 - 복제평가방법

[Master-Slave] 복제 필터링 - 명령문 및 적용방법

[MySQL][Master-Slave] 복제 필터링 - 명령문 및 적용방법

[Master-Slave] GTID를 이용한 복제 - 이론

[MySQL][Master-Slave] GTID를 이용한 복제 - 이론

[Master-Slave] GTID를 이용한 복제 - 구성방법

[MySQL][Master-Slave] GTID를 이용한 복제 - 구성방법