16.3.3 복제 (2) - 바이너리 로그 파일 위치 기반 복제

MySQL의 복제는 소스 서버의 바이너리 로그에 기록된 변경 내역(바이너리 로그 이벤트)들을 식별하는 방식에 따라 바이너리 로그 파일 위치 기반 복제(Binary Log File Position Based Replication)과 글로벌 트랜잭션 ID 기반 복제(Global Transaction Identifiers Based Replication)로 나뉘는데, 각 방식의 동작 원리와 구축 방법이 있다.

바이너리 로그 파일 위치 기반 보겢는 MySQL에 복제 기능이 처음 도입됐을 때부터 제공된 방식으로, 레플리카 서버에서 소스 서버의 바이너리 로그 파일명과 파일 내에서의 위치(Offset 또는 Position)로 개별 바이너리 로그 이벤트를 식별하여 복제되는 형태를 말한다.

바이너리 로그 파일 위치 기반 복제는 소스 서버에서 발생한 각 이벤트에 대한 식별이 반드시 필요하다.

복제를 처음 구축할 때 레플리카 서버에 소스서버의 어떤 이벤트로부터 동기화를 수행할 것인가에 대한 정보를 설정해야 한다.
복제가 설정된 레플리카 서버는 소스 서버의 어느 이벤트까지 로컬 디스크로 가져왔고 또 적용했는지에 대한 정보를 관리하며 소스 서버에 해당 정보를 전달해 그 이후 바이너리 이벤트로 가져온다.

바이너리 로그 파일 위치 기반 복제에서는 이러한 이벤트 하나하나를 소스 서버의 바이너리 로그 파일명과 파일 내에서의 위치 값(File Offset)의 조합으로 식별하고 이를 통해 자신의 적용 내역을 추적할 수 있다.

복제를 일시적으로 중단할 수 있다.
재개할 때도 자신이 마지막으로 적용했던 이벤트 이후의 이벤트들부터 다시 읽어올 수 있다.

바이너리 로그에는 각 이벤트별로 이 이벤트가 최초로 발생한 MySQL 서버를 식별하기 위해 server_id를 함께 저장하기 때문에 바이너리 로그 파일 위치 기반 복제에 참여한 MySQL 서버들이 모두 고유한 server_id 값을 가지고 있어야 한다.

server_id는 MySQL 서버의 시스템 변수 중 하나로, 사용자가 서버마다 원하는 값으로 설정할 수 있다. (기본값은 1)
server_id가 동일할 경우 자신의 서버에서 발생한 이벤트로 간주해서 발생한 이벤트를 적용하지 않는다.

바이너리 로그 파일 위치 기반의 복제 구축

MySQL 서버 간에 복제를 설정할 때는 각 서버에 데이터가 이미 존재하는지 여부와 복제를 어떻게 활용할 것인지 등에 따라 복제 설정 과정 및 구축 방법이 달라진다.

설정 준비

소스 서버에서 반드시 바이너리 로그가 활성화돼 있어야 한다.
복제 구성원이 되는 각 MySQL 서버가 고유한 server_id값을 가져야한다.

MySQL 8.0 에서는 바이너리 로그가 기본적으로 활성화돼 있어, 서버 시작 시 데이터 디렉터리 밑에 binlog라는 이름으로 바이너리 로그 파일이 자동으로 생성된다.
server_id 값도 기본적으로 1로 설정되는데, 서버마다 고유한 값을 가져야하므로 다른 값을 설정해주는 것이 좋다.

소스 서버 설정

바이너리 로그 파일 위치나 파일명을 따로 설정하고 싶다면 log_bin 시스템 변수를 통해 원하는 값으로 설정할 수 있다.
필요에 따라 로그 동기화 방식, 바이너리 로그를 캐시하기 위한 메모리 크기, 바이너리 로그 파일 크기, 보관 주기 등도 지정할 수 있다.

1
2
3
4
5
6
7
8
[mysqld]
server_id=1
log_bin=/binary-log-dir-path/binary-log-name
sync_binlog=1
binlog_cache_size=5M
max_binlog_size=512M
binlog_expire_logs_seconds=1209600
...

소스 서버에서 바이너리 로그가 정상적으로 기록되고 있는지는 소스 서버에 로그인하여 SHOW MASTER STATUS 명령을 실행해보면 된다.

레플리카 서버 설정

레플리카 서버에서 복제를 위해 생성하는 릴레이 로그 파일도 복제 설정 시 기본적으로 데이터 디렉터리 밑에 자동으로 생성된다.
릴레이 로그에 기록된 이벤트는 레플리카 서버에 적용되면 더이상 필요하지 않게 되는데, 필요없어진 릴레이 로그 파일은 레플리카 서버가 자동으로 삭제한다.
- 릴레이 로그 파일을 자동으로 삭제하지 않고 유지하려면 relay_log_purge 시스템 변수를 OFF로 설정하면 된다.
- OFF로 설정하면 레플리카 서버의 디스크 여유 공간이 부족하지 않은지 모니터링하는 것이 좋다.
레플리카 서버는 일반적으로 읽기 전용으로 사용되므로 read_only 설정을 사용하는 것이 좋다.
소스 서버의 장애로 레플리카 서버가 소스 서버로 승격될 수 있음을 고려하면 log_slave_updates 시스템 변수도 명시하는 것이 좋다.
기본적으로 레플리카 서버는 복제에 의한 데이터 변경 사항은 자신의 바이너리 로그에 기록하지 않는데 log_slave_updates 시스템 변수를 설정하면 복제에 의한 데이터 변경 내용도 자신의 바이너리 로그에 기록하게 된다.

1
2
3
4
5
6
7
[mysqld]
server_id=2
relay_log=/relay-log-dir-path/relay-log-name
relay_log_purge=ON
read_only
log_slave_updates
...

복제 계정 준비

레플리카 서버가 소스 서버로부터 바이너리 로그를 가져오려면 소스 서버에 접속해야 하므로 접속 시 사용할 DB 계정이 필요하다.(복제용 계정이라 함)

복제를 위해 특별히 새로운 계정을 만들 필요 없이 기존 사용 중인 계정에 복제 관련 권한을 추가로 부여해도 되지만 복제에서 사용되는 계정의 비밀번호는 레플리카 서버의 커넥션 메타데이터에 평문으로 저장되므로 보안 측변을 고려하여 복제에 사용되는 권한만 주어진 별도의 계정을 생성해 사용하는 것이 좋다.

복제용 계정은 복제를 시작하기 전 소스 서버에 미리 준비돼 있어야 하며, 이 계정은 반드시 REPLICATION SLAVE 권한을 가지고 있어야 한다.

1
2
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'repl_user_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';

설명을 위해 복제 계정의 호스트 제한을 “%“로 설정했지만 보안을 위해 꼭 필요한 IP 대역에서만 복제 연결이 가능하도록 설정하는 것이 좋다.

데이터 복사

소스 서버의 데이터를 레플리카 서버로 가져와서 적재할때는 MySQL 엔터프라이즈 백업이나 mysqldump 같은 툴을 이용해 소스 서버에서 데이터를 내려받아 레플리카 서버로 복사한다.

mysqldump를 사용해 소스 서버의 데이터를 덤프할 때는 --single-transaction, --master-data 옵션을 반드시 사용해야 한다.

--single-transaction: 데이터를 덤프할 때 하나의 트랜잭션을 사용해 덤프가 진행되게 해서 테이블이나 레코드에 잠금을 걸지 않고 InnoDB 테이블들에 대해 일관된 데이터를 덤프받을 수 있게 한다.
--master-data: 덤프 시작 시점의 소스 서버의 바이너리 로그 파일명과 위치 정보를 포함하는 복제 설정 구문(CHANGE REPLICATION SOURCE TO, CHANGE MASTER TO)이 덤프 파일 헤더에 기록될 수 있게 한다.
- 해당 옵션을 사용하면 MySQL 서버에서 바이너리 로그의 위치를 순간적으로 고정하기 위해 FLUSH TABLES WITH READ LOCK 명령을 실행해 글로벌 락(모든 테이블에 대한 읽기 잠금)을 건다.
- 1: 덤프 파일 내의 복제 설정 구문이 실제 실행 가능한 형태로 기록된다.
- 2: 해당 구문이 주석으로 처리되어 참조만 할 수 있는 형태로 기록된다.

1
2
mysqldump -uroot -p --single-transaction --master-data=2 \
--opt --routines --triggers --hex-blob --all-databases > source_data.sql

데이터 덤프가 완료되면 source_data.sql 파일을 레플리카 서버로 옮겨 데이터 적재를 진행한다.

1
2
## 서버에 직접 접속해 데이터 적재 명령을 실행
SOURCE /tmp/master_data.sql

1
2
3
4
## MySQL 서버에 로그인하지 않고 데이터 적재 명령을 실행
## 다음 두 명령어 중 하나를 사용
mysql -uroot -p < /tmp/source_data.sql
cat /tmp/source_data.sql | mysql -uroot -p

mysqldump에 지정된 –master-data 옵션으로 FLUSH TABLES WITH READ LOCK 명령이 실행되기 전에 이미 장시간 실행중인 쿼리가 있다면 글로벌 락 명령어가 실행 중인 쿼리에서 참조하고 있는 테이블들에 대한 잠금을 획득할 수 없어 대기하게 된다.
글로벌 락 명령어가 대기하는 상황이 발생하면 그 뒤로 유입되는 다른 쿼리들도 연달아 대기해서 쿼리가 실행되지 못하고 적체되어 서비스에 문제가 될 수 있으므로 장시간 실행 중인 쿼리가 있는지, 명령 실행 후에도 대기 현상이 발생하고 있지 않은지 미리 확인하는 것이 좋다.

복제 시작

mysqldump를 이용해 데이터를 덤프하고, 덤프한 데이터를 레플리카 서버에 적재가 완료되는 기간까지 소스 서버의 데이터가 반영이 되지 않은 상태이다.

복제를 설정하는 명령은 CHANGE REPLICARTION SOURCE TO(CHANGE MASTER TO) 명령으로, mysqldump로 백업 받은 파일의 헤더 부분에서 해당 명령어를 참조할 수 있다.

백업받은 파일을 열어 24 라인쯤에 위치한 CHANGE MASTER로 시작하는 내용을 복사해둔다.

1
2
3
4
5
6
7
less /tmp/source_data.sql

...
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='binary-log.000002', MASTER_LOG_POS=2708;

복사해둔 내용(CHANGE MASTER TO MASTER_LOG_FILE='binary-log.000002', MASTER_LOG_POS=2708;)에 소스 서버 MySQL 서버의 호스트명, 포트, 복제용 사용자 계정, 비밀번호 등을 다음과 같이 추가해 복제 설정 명령을 준비한다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
-- // MySQL 8.0.23 이상
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='source_server_host',
    SOURCE_PORT=3306,
    SOURCE_USER='repl_user',
    SOURCE_PASSWORD='repl_user_password',
    SOURCE_LOG_FILE='binary-log.000002',
    SOURCE_LOG_POS='2708,
    GET_SOURCE_PUBLIC_KEY=1;

-- // MySQL 8.0.23 이하
CHANGE MASTER TO
    MASTER_HOST='source_server_host',
    MASTER_PORT=3306,
    MASTER_USER='repl_user',
    MASTER_PASSWORD='repl_user_password',
    MASTER_LOG_FILE='binary-log.000002',
    MASTER_LOG_POS=2708,
    GET_MASTER_PUBLIC_KEY=1;

GET_[SOURCE | MASTER]_PUBLIC_KEY: RSA 키 기반 비밀번호 교환 방식의 통신을 위해 공개키를 소스 서버에 요청할 것인지 여부

복제 설정에 보안된 연결과 관련된 옵션들을 명시하지 않아 레플리카 서버가 소스 서버와 함호화되지 않는 통신 방식으로 연결되는 경우 별도로 설정하지 않으면 에러가 발생한다.

위 명령을 레플리카 서버의 MySQL에 로그인하여 실행한 후 SHOW [REPLICA | SLAVE] STATUS 명령을 실행해보면 복제 관련 정보가 등록되고 동기화 명령을 대기하는 상태가 되어 [Replica | Slave]_IO_Running과 [Replica | Slave]_SQL_Running 컬럼이 No 표시된다.

이 상태에서 START [Replica | Slave] 명령을 실행하면 두 컬럼 값이 Yes로 바뀌며 동기화되지 않았던 변경사항들을 소스 서버로 부터 가져와 적용한다.

SHOW [REPLICA | SLAVE] STATUS 명령의 결과에 나타나는 Seconds_Behind_[Source | Master]의 값이 0이 되면 완전히 동기화됐음을 의미한다.

START [REPLICA | SLAVE] 명령을 실행했는데도 Yes로 변경되지 않는다면 아래 내용들을 확인한다.

소스 서버의 호스트명
MySQL의 포트
레플리카 서버에서 사용하는 복제용 접속 계정과 비밀번호
소스 서버와 레플리카 서버 간에 네트워크사으이 문제

바이너리 로그 파일 위치 가반의 복제에서 트랜잭션 건너뛰기

복제로 구성돼 있는 MySQL 서버들을 운영하다 보면 레플리카 서버에서 소스 서버로부터 넘어온 트랜잭션이 제대로 실행되지 못하고 에러가 발생해 복제가 멈추는 현상이 발생하기도 한다.

대부분 사용자의 실수로 인해 발생하는 경우가 많다.(중복 키 에러)

경우에 따라 레플리카 서버에서 문제되는 소스 서버의 트랜잭션을 무시하고 넘어가도록 처리해도 괜찮다면 sql_slave_skip_counter 시스템 변수를 이용해 문제되는 트랜잭션을 건너뛸 수 있다.

1
2
3
STOP [SLAVE | REPLICA] SQL_THREAD;
SET GLOBAL sql_slave_skip_counter=1;
START [SLAVE | REPLICA] SQL_THREAD;

sql_slave_skip_counter 시스템 변수에는 적용하지 않고 건너뛸 바이너리 로그 이벤트 그룹 수를 지정한다.

1로 설정했을때 DML 쿼리 문장 하나를 가진 바이너리 로그 이벤트 1개를 무시하는 것이 아닌 현재 이벤트를 포함한 이벤트 그룹을 무시한다.

이벤트 그룹은 트랜잭션을 지원하는 테이블의 경우에는 트랜잭션이 하나의 이벤트 그룹이 되며, 트랜잭션을 지원하지 않는 테이블에서는 DML 문장 하나하나가 이벤트 그룹이 된다.

하나의 트랜잭션에 여러 개의 DML 쿼리들이 포함되는 경우가 존재한다면 에러가 발생한 쿼리 외에 다른 쿼리들이 예상치 못하게 함께 무시될 수 있으므로 주의해야한다.