MySQL HA 리플리케이션 포맷

1.1.2. 리플리케이션 포맷

1.1.2.1. 리플리케이션 포맷 설정하기

1.1.2.2. 혼합 리플리케이션 포맷

1.1.2.3. 명령문 기반 리플리케이션과 열 기반 리플리케이션 비교

1.1.2.4. mysql 데이터베이스 테이블로 변경하기 위한 로깅 포맷

리플리케이션은 마스터의 바이너리 로그에 기록되어 있는 이벤트를 읽어와서 슬레이브에서 처리를 한다. 각 이벤트는 기록된 방식에 따라서 서로 다른 포맷을 가지고 있다. 이벤트 포맷은 다음과 같다:

MySQL 리플리케이션은 기본적으로 마스터에서 슬레이브로 전달되는 SQL 명령문에 기반을 두고 있다. 이것을 명령문 기반 리플리케이션 (statement-based replication (SBR))이라고 부른다.
열 기반 리플리케이션 (row-based replication (RBR))의 경우에는, 마스터가 이벤트를 개별적인 테이블 열에 영향을 주는 바이너리 로그에 기록한다. RBR은 MySQL 5.1.5 버전부터 적용되었다.
MySQL 5.1.8 버전부터는 또 다른 세 번째 옵션을 사용할 수 있게 되었다: 혼합 기반 리플리케이션 (mixed-based replication (MBR)). MBR을 사용하면, 명령문 기반 리플리케이션이 디폴트로 사용되지만, 특정 상황이 되면 자동으로 열 기반 리플리케이션으로 변환이 된다.

MySQL 5.1.12 버전 이후부터는, 특정 리플리케이션 포맷을 지정하지 않는 한 혼합 기반 리플리케이션 (MBR)이 모든 리플리케이션 환경에서 디폴트로 사용된다.

MySQL 클러스터 역시 열 기반 리플리케이션을 잘 활용해서 구동한다.

MySQL의 전통적인 명령문 기반 리플리케이션의 경우에는 스토어드 루틴 또는 트리거에서 여러 가지 문제가 발생하였으나, MySQL 5.1.5 버전 이후부터는 열 기반 리플리케이션을 지원함으로써 이러한 문제를 해결할 수 있게 되었다.

소스 코드를 사용해서 MySQL을 설정하는 경우에는, configure를 --without-row-based-replication 옵션으로 실행하지 않는 한 열 기반 리플리케이션이 디폴트로 적용된다.

1.1.2.1. 리플리케이션 포맷 설정하기

디폴트 리플리케이션 포맷은 MySQL 버전에 따라서 달라진다:

MySQL 5.1.11 이전 버전의 경우에는 명령문 기반 리플리케이션이 디폴트로 적용된다.
MySQL 5.1.12 이후 버전부터는 혼합 기반 리플리케이션이 디폴트로 적용된다.

--binlog-format=type 옵션을 사용하면 디폴트 리플리케이션 포맷을 강제로 지정할 수 있다. 이 옵션을 사용해서 디폴트 포맷을 지정하면, 모든 리플리케이션 슬레이브는 이벤트를 지정 포맷으로 읽게 된다. 지원하는 옵션은 다음과 같다:

ROW — 열 기반 리플리케이션을 디폴트로 설정한다.
STATEMENT — 명령문 기반 리플리케이션을 디폴트로 설정한다.
MIXED — 혼합 기반 리플리케이션을 디폴트로 설정한다.

런 타임 때 로깅 포맷을 변환 시킬 수 있다. 모든 클라이언트에 글로벌하게 포맷을 지정하기 위해서는 글로벌 시스템 변수 binlog_format을 설정한다. (글로벌 변수 값을 변경하기 위해서는 SUPER 권한이 필요하다.)

명령문 기반 포맷으로 변환 시키기 위해서는, 아래의 명령문 중에 하나를 사용한다:

mysql> SET GLOBAL binlog_format = 'STATEMENT';

mysql> SET GLOBAL binlog_format = 1;

열 기반 포맷으로 변환하기 위해서는, 아래의 명령문 중에 하나를 사용한다:

mysql> SET GLOBAL binlog_format = 'ROW';

mysql> SET GLOBAL binlog_format = 2;

혼합 포맷으로 변환하기 위해서는, 아래의 명령문 중에 하나를 사용한다:

mysql> SET GLOBAL binlog_format = 'MIXED';

mysql> SET GLOBAL binlog_format = 3;

각각의 클라이언트는 binlog_format의 세션 값을 설정해서 자신의 명령문 로깅 포맷을 제어할 수 있다. 예를 들면:

mysql> SET SESSION binlog_format = 'STATEMENT';

mysql> SET SESSION binlog_format = 'ROW';

mysql> SET SESSION binlog_format = 'MIXED';

슬레이브 서버는 로깅 포맷을 수동뿐만 아니라 자동으로도 변환 시킬 수 있다. 슬레이브 서버가 STATEMENT 또는 MIXED 포맷으로 구동을 하고 있으면서 ROW 로깅 포맷으로 작성된 바이너리 로그 열을 만나게 되면 자동 변환이 일어난다. 이런 상황이 되면, 슬레이브는 그 이벤트에 대해서는 임시로 열 기반 리플리케이션 포맷으로 변환하고, 나중에 이전 포맷으로 다시 변환한다.

접속 별 (per-connection) 기반으로 리플리케이션 로깅을 설정하기를 바라는 이유는 두 가지가 있다:

데이터베이스에 작은 변경을 많이 발생 시키는 쓰레드는 열 기반 로깅을 선호한다. WHERE 구문에 있는 많은 열과 매치가 되는 업데이트를 실행하는 쓰레드는 명령문 기반 로깅을 선호하는데, 그 이유는 많은 열을 로깅하는 것 보다는 적은 명령문 로깅이 효과적이기 때문이다.
마스터에서 장 시간 실행되기는 하지만 비교적 적은 수의 열만을 수정하는 명령문들이 있다. 이러한 명령문들은 열 기반 로깅을 사용해서 복제하는 것이 보다 효과적이다.

런 타임 때 리플리케이션 포맷을 변환 시킬 수 없는 경우는 다음과 같다:

스토어드 함수 또는 트리거 내부.
NDB가 활성화된 경우.
세션이 현재 열 기반 리플리케이션 모드이고 임시 테이블이 열려 있는 경우.

이러한 경우에 리플리케이션 포맷을 변환 시키고자 한다면 에러가 발생한다.

임시 테이블이 존재할 때 런 타임 중간에 리플리케이션 포맷을 변환하는 것은 권장할 만한 것이 못 되는데, 그 이유는 임시 테이블이 명령문 기반 리플리케이션을 사용할 때에만 로깅되기 때문이다. 혼합 리플리케이션을 사용하면 임시 테이블을 로깅할 수 있다; 사용자 정의 함수 및 UUID() 함수를 사용하는 경우는 예외임.

binlog 포맷으로 ROW를 설정하면, 열 기반 포맷을 사용하는 바이너리 로그에 대부분의 변경 사항을 기록할 수 있다. 하지만, 여전히 명령문 기반 포맷을 사용하는 변경도 존재한다. CREATE TABLE, ALTER TABLE, 또는 DROP TABLE와 같은 모든 DDL (data definition language) 명령문이 여기에 해당한다.

--binlog-row-event-max-size 옵션은 열 기반 리플리케이션 포맷 서버에서 사용할 수 있다. 열은 이 옵션 값을 초과하지 않는 범위에서 바이트 단위로 바이너리 로그에 저장된다. 이 옵션 값은 256의 배수가 되어야 하고, 디폴트는 1024이다.

1.1.2.2. 혼합 리플리케이션 포맷

리플리케이션을 MIXED 모드에서 구동하면, 다음과 같은 조건에서 명령문 기반 리플리케이션이 열 기반 리플리케이션으로 자동 변환된다:

DML 명령문이 NDB 테이블을 업데이트 하는 경우
함수에 UUID()가 포함되는 경우
AUTO_INCREMENT 컬럼을 사용해서 2 개 이상의 테이블이 업데이트되는 경우
INSERT DELAYED가 존재하는 경우
뷰 (View)가 열 기반 리플리케이션을 요구할 때, 뷰를 생성하는 명령문 역시 그것을 사용하는 경우 — 예를 들면, 뷰를 생성하는 명령문이 UUID() 함수를 사용할 때
UDF 호출이 실행되는 경우.

1.1.2.3. 명령문 기반 리플리케이션과 열 기반 리플리케이션 비교

각 바이너리 로깅 포맷은 장점과 단점을 모두 가지고 있다. 혼용 기반 리플리케이션 포맷은 대부분의 사용 환경에서 효과적인 데이터 집적도 (integrity)와 성능을 제공한다. 이 섹션에서는 열 기반 리플리케이션과 명령문 기반 리플리케이션이 가지고 있는 장점 및 단점을 요약해서 설명하기로 한다.

명령문 기반 리플리케이션의 장점:

MySQL 3.23 이후부터 사용된 검증된 기법.
로그 파일이 작음. 많은 수의 열을 업데이트 또는 삭제하는 경우에는 로그 파일이 더욱 작게 됨. 로그 파일이 작을수록 디스크 공간이 덜 필요하고 백업 속도가 증가한다.
로그 파일은 데이터를 변경하는 모든 명령문을 가지고 있기 때문에, 그것을 사용해서 데이터베이스를 감사 (audit)할 수 있다.
로그 파일을 리플리케이션 목적이 아닌 포인트-인-타임 (point-in-time) 복구용으로 사용할 수 있다.
테이블 열 구조가 서로 다른 경우에도 마스터 버전보다 상위 버전을 슬레이브에 설치해서 사용할 수 있다. 마스터 버전을 업그레이드 하지 않은 채로 최신 슬레이브 버전을 테스트 또는 평가할 경우에 유리하다.

명령문 기반 리플리케이션의 단점:

모든 UPDATE 명령문을 리플리케이션할 수 없다: 명령문 기반 리플리케이션을 사용하면 SQL 명령문에서 랜덤 함수를 사용하는 것과 같은 논-디터미니스틱 (non-deterministic) 동작을 거의 리플리케이션할 수 없다. 논-디터미니스틱 UDF를 사용하는 명령문의 경우에는, 명령문 기반 리플리케이션을 사용해서 그 결과를 리플리케이션하는 것이 불가능하다. 반면에, 열 기반 리플리케이션은 UDF가 리턴하는 결과를 복제한다.
논-디터미니스틱 UDF를 사용하는 명령문은 올바르게 복제할 수 없다.
아래의 함수 중에 하나를 사용하는 명령문은 올바르게 복제할 수 없다:

LOAD_FILE()
UUID()
USER()
FOUND_ROWS()
SYSDATE() (서버가 --sysdate-is-now 옵션으로 시작하지 않는 한)

다른 모든 함수는 올바르게 복제된다 (RAND(), NOW(), LOAD DATA INFILE, 등).

INSERT ... SELECT 명령문은 열 기반 리플리케이션보다 훨씬 많은 수의 열-레벨 잠금을 요구한다.
테이블 스캔이 필요한 UPDATE 명령문 은 열 기반 리플리케이션보다 많은 수의 열을 잠가야 한다. (WHERE 구문에서 인덱스가 사용되지 않았기 때문에)
InnoDB의 경우: AUTO_INCREMENT를 사용하는 INSERT 명령문은 충돌하지 않는 다른 INSERT 명령문을 잠근다.
복잡한 쿼리의 경우, 열을 업데이트 또는 삽입하기 전에 슬레이브에서 명령문을 평가하고 실행해야 한다. 열 기반 리플리케이션을 사용하면, 슬레이브는 전체 쿼리 대신에 차이가 있는 명령문만을 구동한다.
스토어드 함수 (스토어드 프로시저가 아님)는 호출하는 명령문과 동일한 NOW() 값을 가지고 실행한다.
디터미니스틱 UDF 함수는 슬레이브에서 실행되어야 한다.
마스터와 슬레이브에 있는 테이블들은 서로 달라야 한다.

열 기반 리플리케이션의 장점:

모든 것을 복제한다. 이 방식이 가장 안전한 리플리케이션 기법이다.

5.1.14 이전 버전의 경우, CREATE TABLE와 같은 DDL (data definition language) 명령문은 명령문 기반 리플리케이션을 사용하는 반면에, GRANT 및 REVOKE 명령문 같은 DML (data manipulation language) 명령문은 열 기반 리플리케이션을 사용하였다.

MySQL 5.1.14 이후 버전부터는 mysql 데이터베이스는 복제되지 않는다. mysql 데이터베이스는 노드 관련 데이터베이스로 대체되었다. 열 기반 리플리케이션은 이러한 테이블을 지원하지 않는다. 대신에, GRANT, REVOKE, 트리거, 그리고 스토어드 루틴/프로시저 정보를 업데이트하는 명령문은 명령문 기반 리플리케이션을 사용해서 슬레이브에 복제된다.

CREATE ... SELECT, CREATE와 같은 명령문은 테이블 정의문에서 생성이 된 후에 명령문 기반으로 복제되는 반면에, 열 삽입은 열 기반으로 복제된다.

대부분의 다른 데이터베이스 관리 시스템과 동일한 기법을 사용한다.
대부분의 경우, 프라이머리 키를 가지고 있는 슬레이브 테이블에 보다 빠르게 데이터를 적용한다.
다음과 같은 명령문은 마스터에서 보다 적은 잠금을 요구한다:

INSERT ... SELECT
AUTO_INCREMENT를 사용하는 INSERT 명령문
키를 사용하지 않거나 또는 검사를 마친 열에 대해서는 변경을 하지 않는 WHERE 구문을 사용하는 UPDATE 또는 DELETE 명령문

INSERT, UPDATE, 또는 DELETE 명령문에 대해서는 슬레이브에서 보다 적은 잠금이 발생함.

열 기반 리플리케이션의 단점:

로그 파일이 크다.
롤백된 큰 명령문용 데이터는 바이너리 로그가 가지게 된다.
명령문 (예를 들면, UPDATE 또는 DELETE 명령문)을 복제하기 위해 열 기반 리플리케이션을 사용하면, 변경된 각 열은 반드시 바이너리 로그에 기록되어야 한다. 반대로, 명령문 기반 리플리케이션을 사용하면 명령문만이 바이너리 로그에 기록된다. 명령문이 많은 수의 열을 변경하면, 열 기반 리플리케이션이 매우 많은 양의 데이터를 바이너리 로그에 기록할 수 있다. 이러한 경우, 바이너리 로그는 데이터를 기록하기 위해 오랜 시간 동안 잠기기 때문에, 동기화 문제가 발생할 수 있다.
대형 BLOB 값을 생성하는 디터미니스틱 UDF 함수를 복제하는데 엄청난 시간을 소비한다.
어떤 명령문이 실행되었는지를 알아 보기 위한 로그 분석을 할 수 없다.
마스터에서 어떤 명령문을 전달 받아서 실행했는지를 슬레이브에서는 확인할 수 없다.
비-트랜젝션 스토리지 엔진을 포함하는 벌크 연산 (bulk operation)을 만드는 경우에는, 변경 사항이 마치 명령문을 실행하는 것처럼 적용된다. 이것은 열 기반 리플리케이션 로깅을 사용하면 명령문이 실행되는 동안에 바이너리 로그가 기록된다는 것을 의미한다. 마스터 서버에서는 벌크 연산을 마치기 전까지 테이블이 잠기기 때문에 동기화 문제가 발생하지 않지만, 슬레이브는 변경된 데이터가 벌크 연산의 일부분인지를 알지 못하기 때문에, 슬레이브가 변경 사항을 적용하는 동안에 테이블은 잠기지 않게 된다.

이 시나리오에서 보면, 마스터에 있는 테이블에서 데이터를 추출하면 (예를 들면, SELECT * FROM table_name), 테이블이 읽기 잠금 상태이기 때문에 서버는 SELECT 명령문을 실행하기 전에 벌크 연산이 종료하기를 기다리게 된다. 하지만, 슬레이브는 잠금이 존재하지 않기 때문에 기다리지 않게 된다. 이것은, 슬레이브에서 벌크 연산이 종료하기 전까지 마스터와 슬레이브가 동일한 SELECT 쿼리에 대해서 서로 다른 결과를 가지게 된다는 것을 의미하는 것이다.

이러한 동작은 후에 변경될 예정이며, 위와 같은 시나리오에서는 열 기반 리플리케이션 대신에 명령문 기반 리플리케이션을 사용하도록 한다.

1.1.2.4. mysql 데이터베이스 테이블로 변경하기 위한 로깅 포맷

mysql 데이터베이스의 그랜트 테이블은 직접 (예를 들면, INSERT 또는 DELETE를 사용해서) 또는 간접적으로 (예를 들면, GRANT 또는 CREATE USER를 사용해서) 수정할 수 있다. MySQL 5.1.17 버전 이후부터, mysql 데이터베이스에 영향을 주는 명령문은 다음과 같은 규칙을 사용해서 바이너리 로그에 기록된다:

mysql 데이터베이스 테이블에 있는 데이터를 직접 변경하는 명령문은 binlog_format 시스템 변수 설정 값에 따라서 기록된다. INSERT, UPDATE, DELETE, REPLACE, DO, LOAD DATA INFILE, SELECT, 그리고 TRUNCATE 명령문이 여기에 속한다.
mysql 데이터베이스를 간접적으로 변경하는 명령문은 binlog_format 값과는 상관없이 명령문 형태로 기록된다. GRANT, REVOKE, SET PASSWORD, RENAME USER, CREATE (CREATE TABLE ... SELECT 형태를 제외한 모든 형태), ALTER (모든 형태), 그리고 DROP (모든 형태) 명령문이 여기에 속한다.

CREATE TABLE ... SELECT는 데이터 정의문과 데이터 처리문의 조합이다. CREATE TABLE 부분은 명령문 포맷을 사용해서 기록되고, SELECT 부분은 binlog_format 값에 따라서 기록된다.