[MySQL] Container MySQL Primary-Replica 셋팅
읽기와 쓰기가 같은 DB 인스턴스를 공유하면 검색 쿼리가 커넥션과 CPU를 독점하여 쓰기 트랜잭션이 타임아웃된다.
이 문서는 기존 데이터를 유지하면서 단일 MySQL 컨테이너를 Primary(쓰기 전용) - Replica(읽기 전용) 구조로 전환하는 전 과정을 다룬다.
아키텍처 개요
[Application]
│
├── Write (INSERT/UPDATE/DELETE) → [Primary :3306] 쓰기 전용
└── Read (SELECT) → [Replica :3307] 읽기 전용
[Primary]
Binlog(ROW) ──── 비동기 복제 ────→ [Replica]
max_connections=60 max_connections=100
쓰기 부하에 최적화 읽기 트래픽 집중 처리
super_read_only=ONPrimary는 모든 쓰기를 처리하고 변경 내역을 Binlog에 기록한다.
Replica는 Binlog를 실시간으로 수신해 동일한 상태를 유지하며 읽기 트래픽만 처리한다.
다이어그램에 등장한 Binlog, super_read_only 등 각 요소가 왜 필요하고 어떻게 설정하는지는 이어지는 섹션에서 하나씩 다룬다.
핵심 원리
1. log_bin / binlog_format = ROW
log_bin = mysql-bin
binlog_format = ROWBinlog는 Primary에서 발생한 모든 데이터 변경(INSERT/UPDATE/DELETE/DDL)을 파일로 기록한다.
Replica는 이 파일을 읽어 동일한 변경을 자신에게 재실행한다. MySQL Replication 전체가 이 파일을 기반으로 동작한다.
[Primary] 쓰기 요청 → 데이터 변경 → Binlog 기록
↓
[Replica] Binlog 수신 → 동일 변경 적용binlog_format은 변경 내역을 어떤 형태로 기록할지 결정한다.
| format | 기록 방식 |
| STATEMENT | 실행된 SQL 문장을 그대로 기록 |
| ROW | 변경된 실제 행 데이터(Before/After)를 기록 |
| MIXED | 상황에 따라 STATEMENT와 ROW를 혼용 |
ROW를 사용하는 이유: 변경된 실제 행 데이터를 기록하므로 NOW(), RAND() 같은 비결정적 함수에서도 Primary와 Replica의 결과가 항상 일치한다. STATEMENT는 동일한 SQL이라도 실행 시점에 따라 결과가 달라질 수 있어 복제 불일치가 발생한다.
2. gtid_mode = ON / enforce-gtid-consistency = ON
gtid_mode = ON
enforce-gtid-consistency = ONGTID(Global Transaction Identifier)는 각 트랜잭션에 고유 ID를 부여하는 방식이다. 형식은 서버UUID:트랜잭션번호다.
116890f4-fb6f-11f0-ad76-fe8f92702259:1-500이 설정 이전에는 Binlog 파일명과 오프셋을 직접 지정해야 했다. GTID를 사용하면 Replica가 "나는 500번까지 적용했으니 501번부터 보내줘"라고 자동으로 요청한다(SOURCE_AUTO_POSITION=1). Failover 시 새 Primary로 전환할 때도 위치를 수동으로 계산할 필요가 없어진다.
주의할 점: GTID 활성화 이전에 실행된 트랜잭션은 GTID가 없다.
기존 데이터가 있는 상태에서 이 설정을 켜면 그 이전 데이터는 자동 복제 대상에서 제외된다.
3. binlog_row_image = FULL
binlog_row_image = FULLROW 형식에서도 Binlog에 기록할 범위를 선택할 수 있다.
| 값 | 기록 범위 |
| MINIMAL | 변경된 컬럼만 기록 |
| NOBLOB | BLOB 제외 전체 |
| FULL | 변경 전/후 전체 행 기록 |
FULL을 사용하는 이유:
- Debezium 등 CDC 도구가 변경 이전 값(before image)을 캡처하려면
FULL이 필수다. - Replica가 어떤 상황에서도 대상 행을 정확히 찾아 업데이트할 수 있다.
복제 흐름
위 설정들이 실제로 어떻게 동작하는지 전체 흐름이다.
[Primary]
변경 발생 → InnoDB 적용 → Binlog(ROW) 기록
↓
Binlog Dump Thread가 전송
[Replica]
IO Thread : Primary 연결 → Binlog 수신 → Relay Log 저장
SQL Thread : Relay Log 읽기 → InnoDB 적용설정 파일 작성
원리를 이해했다면 이제 이 설정들을 실제 파일로 작성한다.
MySQL은 /etc/mysql/conf.d/ 디렉터리에서 .cnf 또는 .ini 확장자 파일만 로드한다.
설정 파일은 .cnf 확장자로 생성한다.
Primary와 Replica 모두 CPU와 Memory는 2Core, 6GB로 설정해서 진행했다.
1. primary.cnf
[mysqld]
# ── 복제 ───────────────────────────────────────────────────────────
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL # CDC 도구 연동 시 필수
gtid_mode = ON
enforce-gtid-consistency = ON
log_replica_updates = ON # Replica가 받은 변경도 자신의 Binlog에 기록
# ── Binlog 관리 ─────────────────────────────────────────────────────
binlog_expire_logs_seconds = 604800 # 7일 후 자동 삭제 (디스크 고갈 방지)
max_binlog_size = 500M
# ── 식별 ───────────────────────────────────────────────────────────
report_host = search-db-primary
# ── 성능 튜닝 ──────────────────────────────────────────────────────
innodb_buffer_pool_size = 4G
innodb_log_file_size = 512M
thread_cache_size = 50
tmp_table_size = 256M
max_heap_table_size = 256M
max_connections = 60 # 쓰기 전용: 낮게 유지주요 설정 해설
| 항목 | 역할 |
| server-id = 1 | 복제 토폴로지 내 고유 식별자. Primary와 Replica가 서로 다른 값이어야 복제 루프를 방지한다. |
| inlog_expire_logs_seconds = 604800 | 7일이 지난 Binlog를 자동 삭제한다. 설정하지 않으면 Binlog가 무한 누적되어 디스크가 고갈된다. |
| log_replica_updates = ON | Replica가 수신한 변경도 자신의 Binlog에 기록한다. CDC 도구가 Replica를 소스로 사용하거나 체인 복제(A→B→C) 구성에서 필요하다. |
| max_connections = 60 | Primary는 쓰기 전용이므로 낮게 유지한다. 커넥션이 많을수록 메모리 오버헤드가 커진다. |
2. replica1.cnf
[mysqld]
# ── 복제 ───────────────────────────────────────────────────────────
server-id = 2
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
gtid_mode = ON
enforce-gtid-consistency = ON
log_replica_updates = ON
# ── Replica 전용 ────────────────────────────────────────────────────
# read_only / super_read_only 는 Docker 초기화와 충돌하므로 config에 두지 않는다.
# 복제 연결 후 아래 명령으로 영구 적용한다:
# SET PERSIST read_only = ON;
# SET PERSIST super_read_only = ON;
relay_log = mysql-relay-bin
replica_net_timeout = 60 # 60초 내 Primary 응답 없으면 재연결
# ── 식별 ───────────────────────────────────────────────────────────
report_host = search-db-replica
# ── 성능 튜닝 (읽기 집중 워크로드) ─────────────────────────────────
innodb_buffer_pool_size = 4G
innodb_log_file_size = 512M
thread_cache_size = 50
tmp_table_size = 256M
max_heap_table_size = 256M
max_connections = 100 # 읽기 트래픽 집중 → Primary보다 높게Primary와 다른 항목 해설
| 항목 | 역할 |
| relay_log = mysql-relay-bin | Primary로부터 받은 Binlog를 저장하는 중간 로그. IO Thread가 쓰고, SQL Thread가 읽어 InnoDB에 적용한다. |
| replica_net_timeout = 60 | Primary와 60초 동안 통신이 없으면 IO Thread가 재연결을 시도한다. 네트워크 순단 상황에서 복제가 자동 복구된다. |
| ax_connections = 100 | 읽기 트래픽이 집중되는 Replica는 Primary보다 더 많은 동시 연결이 필요하다. |
read_only와 super_read_only를 conf에 두지 않는 이유:
Docker MySQL 컨테이너는 최초 기동 시 Temporary 서버를 띄워 root 비밀번호 설정, 사용자 생성 등 초기화 SQL을 실행한다.
이 시점에 conf에 super_read_only = ON이 있으면 Temporary 서버도 해당 설정을 적용하여 초기화 SQL이 차단된다.
결과적으로 컨테이너는 기동되지만 root 비밀번호가 설정되지 않아 접속 자체가 불가능해진다.SET PERSIST를 사용하면 초기화 완료 후 적용되므로 이 문제를 피할 수 있다.
Docker Compose 구성
설정 파일을 작성했다면 이제 이 파일들을 마운트하고 두 컨테이너의 기동 순서를 제어하는 Docker Compose를 구성한다.
볼륨 이름과 서비스 이름은 별개다
Docker 볼륨은 서비스 이름이 아닌 볼륨 이름으로 식별된다.
서비스 이름을 변경해도 볼륨 이름을 동일하게 유지하면 기존 데이터가 보존된다.
# 기존
services:
mysql-db:
volumes:
- mysql:/var/lib/mysql # 볼륨 이름: mysql
# 변경 후 (기존 데이터 보존)
services:
mysql-db-primary: # 서비스 이름 변경
volumes:
- mysql:/var/lib/mysql # 볼륨 이름이 같으므로 데이터 그대로 유지healthcheck와 depends_on: 기동 순서 보장
depends_on만으로는 컨테이너가 "시작됨(started)" 상태인지만 확인한다.
MySQL이 실제로 요청을 받을 수 있는 상태인지는 healthcheck로 확인해야 한다.
Primary가 아직 준비 중인데 Replica가 먼저 연결을 시도하면 복제 설정이 실패한다.
mysql-db-primary:
healthcheck:
test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
interval: 10s
timeout: 5s
retries: 10
start_period: 30s # 초기 기동 시간 확보
mysql-db-replica:
depends_on:
mysql-db-primary:
condition: service_healthy # Primary가 healthy 상태일 때만 기동최종 docker-compose.yml
services:
mysql-db-primary:
image: mysql:8.0.35
container_name: mysql-db-primary
restart: always
environment:
MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
MYSQL_DATABASE: ${MYSQL_DATABASE}
MYSQL_USER: ${MYSQL_USER}
MYSQL_PASSWORD: ${MYSQL_PASSWORD}
MYSQL_REPLICATION_USER: ${MYSQL_REPLICATION_USER}
MYSQL_REPLICATION_PASSWORD: ${MYSQL_REPLICATION_PASSWORD}
TZ: Asia/Seoul
ports:
- "${MYSQL_PORT}:3306"
volumes:
- mysql:/var/lib/mysql
- ./mysql/primary.cnf:/etc/mysql/conf.d/primary.cnf
- ./mysql/init:/docker-entrypoint-initdb.d
networks:
- mysql-network
mem_limit: 6g
cpus: 2.0
healthcheck:
test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
interval: 10s
timeout: 5s
retries: 10
start_period: 30s
mysql-db-replica:
image: mysql:8.0.35
container_name: mysql-db-replica
restart: always
depends_on:
mysql-db-primary:
condition: service_healthy
environment:
MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
TZ: Asia/Seoul
ports:
- "${MYSQL_REPLICA_PORT}:3306"
volumes:
- mysql-replica:/var/lib/mysql
- ./mysql/replica1.cnf:/etc/mysql/conf.d/replica1.cnf
networks:
- mysql-network
mem_limit: 6g
cpus: 2.0
volumes:
mysql: # Primary: 기존 볼륨 재사용 → 데이터 보존
driver: local
mysql-replica: # Replica: 신규 볼륨
driver: local.env 필수 항목
MYSQL_ROOT_PASSWORD=<root 비밀번호>
MYSQL_DATABASE=<데이터베이스명>
MYSQL_USER=<애플리케이션 계정>
MYSQL_PASSWORD=<애플리케이션 비밀번호>
MYSQL_PORT=3306
MYSQL_REPLICA_PORT=3307
MYSQL_REPLICATION_USER=<복제 계정명>
MYSQL_REPLICATION_PASSWORD=<복제 비밀번호>복제 계정 생성
Replica가 Primary에 접속해 Binlog를 읽으려면 REPLICATION SLAVE 권한을 가진 전용 계정이 필요하다.
기존 데이터가 있는 경우 주의:
Docker init 스크립트(/docker-entrypoint-initdb.d/)는 데이터 디렉터리가 비어 있을 때만 실행된다.
Primary가 기존 볼륨을 사용하면 init 스크립트가 실행되지 않으므로 계정을 직접 생성해야 한다.
docker exec -it mysql-db-primary \
mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" -e "
CREATE USER IF NOT EXISTS '${MYSQL_REPLICATION_USER}'@'%'
IDENTIFIED WITH mysql_native_password
BY '${MYSQL_REPLICATION_PASSWORD}';
GRANT REPLICATION SLAVE ON *.* TO '${MYSQL_REPLICATION_USER}'@'%';
FLUSH PRIVILEGES;
"mysql_native_password를 지정하는 이유:
MySQL 8.0의 기본 인증 방식은 caching_sha2_password다.
이 방식은 첫 연결 시 RSA 공개키 교환이 필요한데, Replica IO Thread가 공개키를 받지 못하면 연결에 실패한다.mysql_native_password는 이 문제가 없다.caching_sha2_password를 유지하려면 복제 연결 시 GET_SOURCE_PUBLIC_KEY=1 옵션을 함께 지정하면 된다.
계정 생성 확인:
docker exec -it mysql-db-primary \
mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" -e "
SELECT user, host, plugin FROM mysql.user WHERE user = '${MYSQL_REPLICATION_USER}';
SHOW GRANTS FOR '${MYSQL_REPLICATION_USER}'@'%';
"기존 데이터 동기화
이 단계가 필요한 경우:
GTID가 비활성화된 상태에서 이미 데이터가 쌓인 MySQL을 Primary-Replica 구조로 전환하는 경우다.
새로 구축하는 환경(데이터가 없거나, GTID 활성화 후 처음으로 데이터가 INSERT된 경우)이라면 이 단계를 건너뛰고 복제 연결부터 진행한다.
왜 수동 동기화가 필요한가
GTID 기반 복제는 GTID가 붙은 트랜잭션만 복제한다.
GTID 활성화 이전에 INSERT된 기존 데이터는 GTID가 없으므로, Replica를 아무리 연결해도 받지 못한다.
Primary에서 확인:
SHOW GLOBAL VARIABLES LIKE 'gtid_executed';
-- 예: 116890f4-...:1 (GTID 활성화 이후 1건만 존재)
-- 기존 수백만 건은 GTID 없음 → 복제 불가mysqldump --set-gtid-purged=ON을 사용하면 덤프 파일에 다음 구문이 포함된다.
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '116890f4-...:1';이 구문의 의미: "이 덤프에는 GTID :1까지의 내용이 포함되어 있으니, Replica는 :2부터 복제를 시작해라."
덤프 임포트 후 Replica의 GTID 이력이 Primary와 동기화되어 이후 트랜잭션부터 자동 복제가 이어진다.
1. 동기화 절차
# 1. Replica 상태 초기화 (임포트 전 기존 GTID 이력 제거)
# RESET MASTER는 이름과 달리 실행한 서버(여기서는 Replica) 자신의 GTID_EXECUTED/PURGED를 초기화한다.
# Primary에는 영향을 주지 않는다.
docker exec mysql-db-replica \
mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" -e "
STOP REPLICA;
RESET REPLICA ALL;
RESET MASTER;
"
# 2. Primary 덤프
# --single-transaction : InnoDB 스냅샷 격리, 테이블 락 없이 일관된 덤프
# --set-gtid-purged=ON : 덤프 파일에 GTID 이력 포함
# --databases : 대상 DB 지정 (--all-databases 사용 금지, 아래 주의사항 참고)
docker exec mysql-db-primary mysqldump \
-uroot -p"${MYSQL_ROOT_PASSWORD}" \
--single-transaction \
--set-gtid-purged=ON \
--databases ${MYSQL_DATABASE} \
> /tmp/primary_dump.sql
# 3. Replica에 임포트
docker exec -i mysql-db-replica \
mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" \
< /tmp/primary_dump.sql
# 4. 임포트 후 덤프 파일 삭제
rm /tmp/primary_dump.sql--all-databases 사용 금지
mysql, information_schema, performance_schema 등 시스템 DB까지 포함된다.
Replica에 임포트하면 root 계정 정보와 권한 테이블이 덮어써져 접속 불가 상태가 될 수 있다.
반드시 --databases <DB명>으로 대상을 명시한다.
복제 연결 및 검증
데이터 동기화가 완료되었다면 이제 Replica가 Primary에 연결해 복제를 시작하도록 설정한다.
1. 복제 소스 설정 및 시작
아래 명령은 .env의 환경변수를 사용한다. 실행 전에 변수가 쉘에 설정되어 있는지 확인한다.
# .env 파일이 있는 디렉터리에서 실행
source .env
docker exec mysql-db-replica \
mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" -e "
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='search-db-primary',
SOURCE_USER='${MYSQL_REPLICATION_USER}',
SOURCE_PASSWORD='${MYSQL_REPLICATION_PASSWORD}',
SOURCE_AUTO_POSITION=1,
GET_SOURCE_PUBLIC_KEY=1;
START REPLICA;
"| 옵션 | 역할 |
| SOURCE_AUTO_POSITION=1 | GTID 기반 자동 위치 추적. Binlog 파일명과 오프셋을 수동으로 지정할 필요가 없다. |
| GET_SOURCE_PUBLIC_KEY=1 | caching_sha2_password 인증 시 RSA 공개키를 자동 획득한다. mysql_native_password 환경에서도 명시하면 안전하다. |
2. 복제 상태 확인
docker exec mysql-db-replica \
mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" \
-e "SHOW REPLICA STATUS\G" 2>/dev/null정상 상태 핵심 지표
| 항목 | 기대값 | 의미 |
| Replica_IO_Running | Yes | IO Thread가 Primary에 연결되어 Binlog 수신 중 |
| Replica_SQL_Running | Yes | SQL Thread가 Relay Log를 읽어 변경 적용 중 |
| Seconds_Behind_Source | 0 | Replica가 Primary를 완전히 따라잡은 상태 |
| Auto_Position | 1 | GTID 기반 자동 위치 추적 활성 |
| Last_IO_Error | (공백) | 결/수신 에러 없음 |
| Last_SQL_Error | (공백) | 경 적용 에러 없음 |
3. 데이터 동기화 검증
# Primary 행 수 확인
docker exec mysql-db-primary \
mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" \
-D ${MYSQL_DATABASE} \
-e "SELECT COUNT(*) AS primary_count FROM <테이블명>;" 2>/dev/null
# Replica 행 수 확인
docker exec mysql-db-replica \
mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" \
-D ${MYSQL_DATABASE} \
-e "SELECT COUNT(*) AS replica_count FROM <테이블명>;" 2>/dev/null보안 강화: super_read_only 적용
복제가 정상 동작하는 것을 확인한 후 Replica를 읽기 전용으로 고정한다.
1. read_only vs super_read_only
| 설정 | 차단 범위 |
| read_only = ON | 일반 사용자의 쓰기 차단. SUPER 권한 계정은 쓰기 가능. |
| super_read_only = ON | SUPER 권한 계정의 쓰기도 차단. 복제 스레드는 예외. |
super_read_only를 켜면 read_only도 자동으로 활성화된다.
2. SET PERSIST로 영구 적용
SET PERSIST는 MySQL 8.0에서 도입된 기능이다.
변수를 현재 세션에 즉시 적용하면서 동시에 데이터 디렉터리 내 mysqld-auto.cnf에 영구 저장한다.
컨테이너가 재시작되어도 MySQL이 mysqld-auto.cnf를 자동으로 읽어 적용한다.
docker exec mysql-db-replica \
mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" -e "
SET PERSIST read_only = ON;
SET PERSIST super_read_only = ON;
"적용 확인:
docker exec mysql-db-replica \
mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" \
-e "SHOW VARIABLES LIKE '%read_only%';" 2>/dev/null운영 모니터링
구축이 완료되었다. 이후에는 복제가 정상적으로 유지되고 있는지 주기적으로 확인해야 한다.
복제 지연 모니터링
Seconds_Behind_Source는 Replica의 SQL Thread가 Primary보다 얼마나 뒤처져 있는지를 초 단위로 나타낸다.
docker exec mysql-db-replica \
mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" \
-e "SHOW REPLICA STATUS\G" 2>/dev/null \
| grep -E "IO_Running|SQL_Running|Seconds_Behind|Last_Error"지연 상태별 원인과 대응
| 상황 | Seconds_Behind_Source | 원인 | 대응 |
| 정상 | 0 | — | — |
| 일시 지연 | 1~수십 | 대량 쓰기 후 따라잡는 중 | 자연 해소 대기 |
| 지속 지연 | 수백 이상 | Replica 처리 성능 부족 | 병렬 복제 설정 검토 |
| NULL | — | IO/SQL Thread 중단 | Last_Error 확인 후 원인 해결 |
Primary에서 연결된 Replica 확인
docker exec mysql-db-primary \
mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" \
-e "SHOW REPLICAS;" 2>/dev/null재시작 후 확인
복제 스레드는 컨테이너 재시작 시 자동으로 시작된다. 상태만 확인하면 된다.
docker compose restart mysql-db-replica
sleep 10
docker exec mysql-db-replica \
mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" \
-e "SHOW REPLICA STATUS\G" 2>/dev/null \
| grep -E "IO_Running|SQL_Running|Seconds_Behind"