[MySQL] Replication 구축하기

▼ 먼저 보면 좋은 글

HA(High Availability, 고가용성) 개념

 

 

 

 

 

Replication 실습 아키텍처

 

1. 보안그룹 설정

 

 

 

2. Primary, Replica 설정

Primary 설정

1. 설정 파일 수정

sudo vi /etc/my.cnf

 

mysqld 아래에 추가

# Replication에서 서버를 구분하는 고유 번호
server-id=1

# Binary Log 활성화
log_bin=binlog

# Binary Log를 Row 기반으로 사용
binlog_format=ROW

bind-address=0.0.0.0

 

2. 재시작 및 확인

sudo systemctl restart mysqld
sudo systemctl status mysqld

SHOW VARIABLES LIKE 'log_bin';

 

log_bin의 값이 ON인지 확인

 

Replica 설정

1. 설정 파일 수정

sudo vi /etc/my.cnf

server-id=2

# Replica가 Binary Log를 저장하는 파일
relay_log=relay-bin

# 읽기 전용
read_only=ON

 

2. 재시작 및 확인

sudo systemctl restart mysqld
sudo systemctl status mysqld

SHOW VARIABLES LIKE 'read_only';

 

read_only 값이 ON인지 확인

 

Relay Log:
Primary로부터 받아온 Binary Log 데이터를 Replica의 디스크에 임시로 저장해 두는 버퍼 파일

1. Replica의 I/O Thread가 Primary의 Binary Log를 읽어와 Relay Log 파일로 저장
2. Replica의 SQL Thread가 Relay Log를 순차적으로 읽어 SQL을 실행

네트워크 문제로 연결이 잠시 끊기더라도 이미 받아둔 Relay Log부터 안전하게 DB에 적용할 수 있으며, I/O 작업과 SQL 실행 작업을 분리하여 복제 성능과 안정성을 극대화하기 위해 필요하다.

 

 

3. Replication 구축

Replication User 생성 (Primary)

CREATE USER 'repl'@'%' IDENTIFIED BY 'Qwer1234!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

SHOW GRANTS FOR 'repl'@'%';

 

REPLICATION SLAVE 권한:
Replica 서버가 Primary 서버에 접속하여 Binary Log 스트림을 읽어갈 수 있도록 허용하는 권한

 

데이터 백업 (Primary)

mysqldump \
-u root -p \
--single-transaction \
--source-data=2 \
--routines \
--databases employees \
> ~/backup/employees.sql

--source-date=2 옵션:
mysqldump 백업 시 백업이 시작된 시점의 Binary Log 파일명과 위치(Position) 정보를 주석으로 기록해 주는 옵션
(MySQL 8.0.26 전 버전에서는 --master-data로 사용)

백업 파일(.sql) 상단에 아래와 같은 주석이 추가됨
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000011', MASTER_LOG_POS=858;

옵션 값 1 = 주석 처리 없이 실행 가능한 문장으로 출력 (백업 파일 실행 시 바로 적용됨)
옵션 값 2 = 주석 처리되어 출력 (권장)

--source-date=2 (--master-data=2) 옵션이 핵심

백업 파일 안에 CHANGE REPLICATION SOURCE TO 정보를 자동 기록

 

데이터 복원 (Replica)

1. 백업 파일 Replica로 복사

scp \
-i pemkeyname.pem \
~/backup/employees.sql \
ec2-user@ReplicaIP:/home/ec2-user/

# employees.sql 백업 파일이 있는지 확인
ls ~

scp 명령어 (Secure Copy Protocol):
SSH 기반으로 파일이나 디렉토리를 송수신하는 리눅스 명령어

scp [옵션] [원천 경로] [목적지 경로] 형태로 사용
* 원격 서버는 계정명@IP주소:경로 형식으로 지정
* 옵션 i [key] = SSH 접속 시 사용할 pem key 파일 경로 지정

내보내기 예시
# 단일 파일
scp -i keyName.pem ./app.jar ec2-user@IPAddress:/home/ec2-user/
# 디렉토리 전체
scp -i keyName.pem -r ./my-folder ec2-user@IPAddress:/home/ec2-user/

가져오기 예시
# 단일 파일
scp -i keyName.pem ec2-user@IPAddress:/home/ec2-user/backup.sql ./
# 디렉토리 전체
scp -i keyName.pem -r ec2-user@IPAddress:/home/ec2-user/logs ./

 

2. 백업 파일 실행

mysql -u root -p employees < employees.sql

테이블 및 데이터 복구 완료

 

Replication 연결

1. Binary Log 확인 (Primary)

SHOW BINARY LOG STATUS;

 

2. Replication 연결 (Replica)

# Replication 연결 설정
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='PrimaryPrivateIP', # Primary 주소
SOURCE_PORT=3306,
SOURCE_USER='repl',
SOURCE_PASSWORD='Qwer1234!',
SOURCE_LOG_FILE='binlog.000011', # 어느 Binary Log부터 읽을지
SOURCE_LOG_POS=858; # 어느 Position부터 읽을지

START REPLICA; # 복제 시작

SHOW REPLICA STATUS\G # 상태 확인

 

확인할 주요 필드

• Replica_IO_Running: Yes
• Replica_SQL_Running: Yes
• Last_IO_Error: 빈값 (에러 없음)

 

연결 실패

 

연결 실패 원인:

MySQL 8.0의 기본 인증 방식인 caching_sha2_password는 보안 접속(SSL/TLS)이 연결되어 있거나 Public Key를 가져올 수 있어야 하는데, 현재 SSL 미사용 설정(Source_SSL_Allowed: No) 상태에서 비보안 접속을 시도하다가 인증에 실패한 것.

 

MySQL 8.0부터 기본 인증 방식으로 사용되는 caching_sha2_password는 보안을 위해 비밀번호를 평문으로 전송하지 않고, RSA 비대칭 암호화 방식을 사용하여 비밀번호를 암호화한 뒤 Primary로 전달.

즉, 비밀번호를 암호화하려면 Replica는 Primary의 RSA 공용키를 가지고 있어야 함.
하지만 기본적으로 MySQL은 보안상 네트워크를 통해 서버의 공용키를 자동으로 가져오는 동작을 차단함. (GET_SOURCE_PUBLIC_KEY=0이 기본값)

동시에 SSL/TLS 보안 연결(Source_SSL_Allowed: No)도 적용되어 있지 않았기 때문에, Replica는 비밀번호를 암호화할 방법이 없어 인증 에러(Error 2061)를 발생시키며 연결을 거부했던 것.

 

 

해결 방법:

GET_SOURCE_PUBLIC_KEY=1 추가

* Replica가 Source(Primary)로부터 RSA 공용키(Public Key)를 직접 자동으로 요청해서 받아오도록 허용하는 설정

* 실무에서는 아래 방식 중에서 사용

• SSL/TLS 적용: 통신 채널 자체를 SSL로 암호화하여 공용키 없이 안전하게 접속
• 공용키 파일 전달: Primary의 공용키 파일(.pem)을 복사해 Replica 서버에 두고 SOURCE_PUBLIC_KEY_PATH 옵션으로 경로 지정하여 사용

CHANGE REPLICATION SOURCE TO
SOURCE_HOST='PrimaryPrivateIP',
SOURCE_PORT=3306,
SOURCE_USER='repl',
SOURCE_PASSWORD='Qwer1234!',
SOURCE_LOG_FILE='binlog.000011',
SOURCE_LOG_POS=858,
GET_SOURCE_PUBLIC_KEY=1;

START REPLICA;

SHOW REPLICA STATUS\G

연결 성공

 

\G :
쿼리 결과를 가로가 아닌 세로로 출력하는 MySQL CLI 전용 명령어
세미콜론을 붙이지 않음

 

 

4. Replication 테스트

1. 데이터 INSERT (Primary)

INSERT INTO departments VALUES ('d99', 'TEST');

 

 

2. 데이터 확인 (Replica)

SELECT * FROM departments WHERE dept_no = 'd99';

Primary에서 Insert한 데이터가 Replica에서 조회됨

 

마찬가지로 UPDATE, DELETE도 적용됨