데이터베이스를 운영하다 보면 한 가지 고민이 생깁니다. “서버가 갑자기 죽으면 어떡하지?” 이 질문에 대한 가장 현실적인 해답이 바로 복제(Replication)입니다. 오늘은 MySQL과 MariaDB에서 복제 환경을 구성하는 방법부터 문제가 생겼을 때 복구하는 방법까지, 실무에서 바로 적용할 수 있도록 차근차근 살펴보겠습니다.

 

 

[MySQL] Community Edition(무료)와 Enterprise Edition(유료) 라이센스 차이점

MySQL 1045 Access denied 에러 해결 방법

MySQL 날짜/시간 함수 완벽 정리, 예제포함

MySQL [CVE-2024-21087] 권한 상승 취약점 원인 및 패치방법

 

 

1. 데이터베이스 복제란 무엇이고, 왜 필요할까요?

복제(Replication)는 한 데이터베이스 서버(Source, 소스)의 데이터를 다른 서버(Replica, 복제본)로 자동 복사하는 기술입니다. 쉽게 말해, 원본 서버에서 일어난 모든 변경 사항을 실시간으로 다른 서버에 똑같이 반영하는 것이죠.

복제가 필요한 이유

실제 운영 환경에서 복제는 다음과 같은 상황에서 빛을 발합니다:

고가용성(High Availability) 확보 소스 서버에 장애가 발생했을 때 복제 서버를 바로 승격시켜 서비스 중단 시간을 최소화할 수 있습니다. 금융권이나 쇼핑몰처럼 24시간 서비스가 필수인 환경에서는 이 기능이 생명줄과 같습니다.

읽기 부하 분산(Read Scale-out) 모든 쓰기 작업은 소스 서버에서, 읽기 작업은 여러 복제 서버에서 처리하면 전체 시스템 성능이 크게 향상됩니다. 특히 읽기 작업이 많은 웹 서비스에서 효과적이죠.

안전한 백업 환경 복제 서버에서 백업을 수행하면 소스 서버의 성능에 영향을 주지 않고 안전하게 백업할 수 있습니다. 백업 중에도 원본 서버는 정상적으로 서비스를 제공할 수 있습니다.

데이터 분석 환경 분리 무거운 분석 쿼리를 복제 서버에서 실행하면 운영 서버의 성능 저하 없이 리포트를 생성할 수 있습니다.

 

 

2. 복제 방식의 종류: 어떤 것을 선택해야 할까요?

MySQL/MariaDB에서 제공하는 복제 방식은 크게 두 가지로 나눌 수 있습니다.

비동기식 복제(Asynchronous Replication)

기본 설정으로 사용되는 방식입니다. 소스 서버는 트랜잭션을 커밋한 후 복제 서버의 확인을 기다리지 않고 바로 다음 작업을 수행합니다.

장점:

• 성능이 가장 빠릅니다
• 네트워크 지연에 영향을 받지 않습니다

단점:

• 소스 서버 장애 시 일부 데이터 손실 가능성이 있습니다

준동기식 복제(Semi-Synchronous Replication)

소스 서버가 트랜잭션을 커밋하기 전에 최소 하나의 복제 서버가 해당 이벤트를 받았다는 확인(ACK)을 기다립니다.

-- MySQL 8.0.26 이상: 소스 서버
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = 1;

-- MySQL 8.0.26 이상: 복제 서버
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET GLOBAL rpl_semi_sync_replica_enabled = 1;

-- MySQL 8.0.25 이하: 소스 서버
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;

-- MySQL 8.0.25 이하: 복제 서버
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

장점:

• 데이터 무결성이 향상됩니다
• 장애 시 데이터 손실 위험이 크게 줄어듭니다

단점:

• 약간의 성능 저하가 발생합니다 (보통 2-10% 정도)

복제 식별 방식: Binary Log Position vs GTID

복제 시 “어디까지 복제했는지”를 추적하는 방식에도 두 가지가 있습니다.

구분	Binary Log Position	GTID
추적 방식	바이너리 로그 파일명 + 위치	전역 고유 트랜잭션 ID
장애 복구	수동으로 위치 지정 필요	자동 위치 지정(Auto-positioning)
토폴로지 변경	복잡함	간단함
최소 버전	모든 버전	MySQL 5.6+, MariaDB 10.0+
권장 여부	레거시 환경	신규 구성 시 권장

GTID(Global Transaction Identifier)는 각 트랜잭션에 고유한 ID를 부여하여 복제 위치를 자동으로 파악합니다. 새로 구성한다면 GTID 방식을 강력히 권장합니다.

 

 

3. 복제 구성 전 체크리스트: 이것만은 꼭 확인하세요

본격적인 설정에 앞서 다음 사항들을 확인해야 합니다:

네트워크 연결 확인

# 복제 서버에서 소스 서버로 접속 테스트
ping source_server_ip
telnet source_server_ip 3306

방화벽 설정 MySQL 기본 포트인 3306이 열려 있어야 합니다.

# CentOS/RHEL
sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --reload

# Ubuntu/Debian
sudo ufw allow 3306/tcp

서버 버전 확인 소스와 복제 서버 간 버전 호환성을 확인합니다. 일반적으로 복제 서버가 소스 서버와 같거나 높은 버전이어야 합니다.

SELECT VERSION();

 

 

4. 소스(Master) 서버 설정: 복제의 출발점 만들기

이제 실제 설정을 시작해보겠습니다. 먼저 소스 서버를 설정합니다.

my.cnf(또는 my.ini) 설정

설정 파일의 위치는 운영체제에 따라 다릅니다:

• Linux (MySQL): /etc/my.cnf 또는 /etc/mysql/my.cnf
• Linux (MariaDB): /etc/mysql/mariadb.conf.d/50-server.cnf

MySQL 8.0 설정

[mysqld]
# 서버 고유 ID (1 ~ 2^32-1 사이의 값, 복제 그룹 내에서 유일해야 함)
server-id = 1

# 바이너리 로그 활성화 (복제의 핵심!)
log_bin = mysql-bin

# 바이너리 로그 형식 (ROW 방식 권장)
binlog_format = ROW

# GTID 모드 활성화 (권장)
gtid_mode = ON
enforce_gtid_consistency = ON

# 외부 접속 허용 (보안상 주의 필요)
bind-address = 0.0.0.0

MariaDB 10.x 설정

[mysqld]
# 서버 고유 ID
server-id = 1

# 바이너리 로그 활성화
log_bin = mysql-bin

# 로그 파일 기본 이름 (MariaDB 전용 옵션, 호스트명 변경에 영향받지 않도록)
log-basename = master1

# 바이너리 로그 형식
binlog_format = ROW

# 외부 접속 허용
bind-address = 0.0.0.0

참고: MariaDB는 MySQL과 달리 gtid_mode와 enforce_gtid_consistency 옵션을 사용하지 않습니다. MariaDB의 GTID는 바이너리 로그가 활성화되면 자동으로 사용 가능합니다.

설정 후 MySQL/MariaDB를 재시작합니다:

sudo systemctl restart mysql
# 또는
sudo systemctl restart mariadb

복제 전용 사용자 생성

보안을 위해 복제 전용 계정을 만듭니다. 이 계정은 오직 복제 작업에만 사용됩니다.

-- MySQL 8.0 이상
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;

-- 특정 IP만 허용하려면 (더 안전함)
CREATE USER 'replication_user'@'192.168.1.%' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'192.168.1.%';
FLUSH PRIVILEGES;

현재 바이너리 로그 위치 확인

GTID를 사용하지 않는 경우 이 정보가 필요합니다:

-- MySQL 8.4 이상
SHOW BINARY LOG STATUS;

-- MySQL 8.0 이하
SHOW MASTER STATUS;

출력 예시:

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 862      |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

File과 Position 값을 메모해 두세요. 복제 서버 설정 시 필요합니다.

 

 

5. 복제(Replica/Slave) 서버 설정: 소스와 연결하기

my.cnf 설정

MySQL 8.0 복제 서버 설정

[mysqld]
# 서버 고유 ID (소스와 다른 값 사용)
server-id = 2

# 릴레이 로그 설정
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index

# 읽기 전용 모드 (복제 서버에 권장)
read_only = ON

# GTID 모드 활성화 (소스와 동일하게)
gtid_mode = ON
enforce_gtid_consistency = ON

# 복제 서버의 변경사항도 로깅 (체인 복제 시 필요)
# MySQL 8.0.26+: log_replica_updates, 이전 버전: log_slave_updates
log_replica_updates = ON

MariaDB 복제 서버 설정

[mysqld]
# 서버 고유 ID
server-id = 2

# 릴레이 로그 설정
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index

# 읽기 전용 모드
read_only = ON

# 복제 서버의 변경사항도 로깅
log_slave_updates = ON

설정 후 서비스를 재시작합니다.

소스 서버와 연결 설정

GTID 방식 (권장)

-- MySQL 8.0.23 이상
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'source_server_ip',
    SOURCE_PORT = 3306,
    SOURCE_USER = 'replication_user',
    SOURCE_PASSWORD = 'StrongPassword123!',
    SOURCE_AUTO_POSITION = 1;

-- MySQL 8.0.22 이하
CHANGE MASTER TO
    MASTER_HOST = 'source_server_ip',
    MASTER_PORT = 3306,
    MASTER_USER = 'replication_user',
    MASTER_PASSWORD = 'StrongPassword123!',
    MASTER_AUTO_POSITION = 1;

Binary Log Position 방식

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'source_server_ip',
    SOURCE_PORT = 3306,
    SOURCE_USER = 'replication_user',
    SOURCE_PASSWORD = 'StrongPassword123!',
    SOURCE_LOG_FILE = 'mysql-bin.000003',
    SOURCE_LOG_POS = 862;

복제 시작

-- MySQL 8.0.22 이상
START REPLICA;

-- MySQL 8.0.21 이하 또는 MariaDB
START SLAVE;

 

 

6. 복제 상태 확인과 모니터링: 정상 작동 여부 점검하기

복제가 제대로 동작하는지 확인하는 것은 매우 중요합니다.

복제 상태 확인

-- MySQL 8.0.22 이상
SHOW REPLICA STATUS\G

-- MySQL 8.0.21 이하 또는 MariaDB
SHOW SLAVE STATUS\G

핵심 확인 항목:

Replica_IO_Running: Yes        -- IO 스레드 정상
Replica_SQL_Running: Yes       -- SQL 스레드 정상
Last_Error:                    -- 에러가 없어야 함
Seconds_Behind_Source: 0       -- 지연 시간 (0에 가까울수록 좋음)

두 항목이 모두 Yes로 표시되어야 정상입니다. 만약 No가 있다면 Last_Error를 확인하세요.

복제 지연 모니터링

-- 복제 지연 시간 확인
SELECT 
    CHANNEL_NAME,
    SOURCE_UUID,
    LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP,
    APPLYING_TRANSACTION
FROM performance_schema.replication_applier_status_by_worker;

실시간 모니터링 스크립트 예시

#!/bin/bash
# replication_monitor.sh
while true; do
    clear
    echo "=== MySQL Replication Status ==="
    echo "Time: $(date)"
    mysql -e "SHOW REPLICA STATUS\G" | grep -E "Replica_IO_Running|Replica_SQL_Running|Seconds_Behind|Last_Error"
    sleep 5
done

 

 

7. 기존 데이터가 있는 상태에서 복제 구성하기

실제 운영 환경에서는 이미 데이터가 있는 상태에서 복제를 구성하는 경우가 많습니다.

mysqldump를 이용한 방법

소스 서버에서 백업

# MySQL 8.0.26 이상 (GTID 방식)
mysqldump -u root -p --all-databases --single-transaction \
    --routines --triggers --events \
    --source-data=2 --set-gtid-purged=ON > full_backup.sql

# MySQL 8.0.25 이하 (GTID 방식)
mysqldump -u root -p --all-databases --single-transaction \
    --routines --triggers --events \
    --master-data=2 --set-gtid-purged=ON > full_backup.sql

# Binary Log Position 방식 (모든 버전)
mysqldump -u root -p --all-databases --single-transaction \
    --routines --triggers --events \
    --master-data=2 > full_backup.sql

옵션 설명: --source-data(또는 --master-data)의 값이 2이면 복제 설정 정보를 주석으로 포함시키고, 1이면 실행 가능한 SQL문으로 포함시킵니다.

복제 서버에서 복원

mysql -u root -p < full_backup.sql

복제 시작

백업 파일 상단의 주석에서 로그 파일과 위치를 확인한 후:

-- GTID 방식
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'source_ip',
    SOURCE_USER = 'replication_user',
    SOURCE_PASSWORD = 'password',
    SOURCE_AUTO_POSITION = 1;

START REPLICA;

대용량 데이터베이스의 경우

데이터가 수십 GB 이상인 경우 Percona XtraBackup 사용을 권장합니다:

# 소스 서버에서 백업
xtrabackup --backup --target-dir=/backup/full

# 백업 준비
xtrabackup --prepare --target-dir=/backup/full

# 복제 서버로 전송 후 복원
rsync -avpP /backup/full/ replica_server:/var/lib/mysql/

 

 

8. 장애 상황별 복구 방법: 문제가 생겼을 때 대처하기

복제 운영 중 가장 자주 마주치는 문제들과 해결 방법을 알아봅니다.

상황 1: 복제가 멈추고 에러가 발생한 경우

먼저 에러 내용을 확인합니다:

SHOW REPLICA STATUS\G

“테이블이 존재하지 않음” 에러의 경우:

특정 쿼리를 건너뛰고 복제를 재개할 수 있습니다:

-- 복제 중지
STOP REPLICA;  -- MySQL 8.0.22+, 이전 버전: STOP SLAVE;

-- 에러를 유발한 이벤트 1개 건너뛰기
-- MySQL 8.0.26+
SET GLOBAL sql_replica_skip_counter = 1;
-- MySQL 8.0.25 이하 또는 MariaDB
SET GLOBAL sql_slave_skip_counter = 1;

-- 복제 재시작
START REPLICA;  -- MySQL 8.0.22+, 이전 버전: START SLAVE;

주의: 이 방법은 임시 방편입니다. 데이터 불일치가 발생할 수 있으므로 근본 원인을 해결해야 합니다.

상황 2: 중복 키 에러(Duplicate Key Error)

-- Last_Error에 "Duplicate entry" 메시지가 있는 경우
STOP REPLICA;  -- 또는 STOP SLAVE;

-- 문제가 되는 레코드를 복제 서버에서 삭제 후 재시도
-- 또는 해당 이벤트 건너뛰기
SET GLOBAL sql_slave_skip_counter = 1;  -- 모든 버전에서 작동
START REPLICA;  -- 또는 START SLAVE;

상황 3: 복제 서버와 소스 서버 데이터 불일치

데이터 일관성 검사 및 동기화를 위해 Percona Toolkit을 사용합니다:

# 데이터 일관성 검사
pt-table-checksum --host=source_server --user=root --password=xxx

# 불일치 데이터 동기화
pt-table-sync --execute --sync-to-master replica_server

상황 4: 처음부터 다시 복제 구성하기

복구가 어려운 경우, 복제를 완전히 재설정합니다:

-- 복제 서버에서
STOP REPLICA;
RESET REPLICA ALL;  -- 또는 RESET SLAVE ALL;

-- 소스 서버에서 새로 덤프 후 복원
-- 위 섹션 7의 과정을 다시 수행

상황 5: IO 스레드 연결 실패

-- 네트워크 문제인 경우 연결 재시도 간격 조정
CHANGE REPLICATION SOURCE TO
    SOURCE_CONNECT_RETRY = 10,    -- 10초마다 재시도
    SOURCE_RETRY_COUNT = 86400;   -- 최대 재시도 횟수

START REPLICA;

 

 

9. MariaDB 복제 구성 시 차이점

MariaDB도 MySQL과 거의 동일한 방법으로 복제를 구성할 수 있지만, 몇 가지 차이점이 있습니다.

용어 차이

MariaDB는 MySQL보다 먼저 MASTER/SLAVE 용어를 PRIMARY/REPLICA로 변경하기 시작했습니다. 하지만 하위 호환성을 위해 기존 명령어도 계속 사용 가능합니다.

-- MariaDB에서 권장하는 명령어
CHANGE MASTER TO ...  -- 여전히 작동
START REPLICA;        -- MariaDB 10.5+

MariaDB GTID

MariaDB의 GTID 형식은 MySQL과 다릅니다:

• MySQL GTID: source_uuid:transaction_id
• MariaDB GTID: domain_id-server_id-sequence_number

-- MariaDB에서 GTID 복제 설정
CHANGE MASTER TO
    MASTER_HOST = 'source_ip',
    MASTER_USER = 'replication_user',
    MASTER_PASSWORD = 'password',
    MASTER_USE_GTID = slave_pos;

MySQL에서 MariaDB로 복제

MySQL 5.5에서 MariaDB로 복제는 대부분 원활하게 작동합니다. 단, MySQL 8.0에서 MariaDB로 복제할 때는 추가 설정이 필요할 수 있습니다. binlog_checksum을 NONE으로 설정해야 할 수도 있습니다:

# MariaDB 복제 서버에서
binlog_checksum = NONE

 

 

10. 실무 운영 팁: 안정적인 복제 환경 유지하기

복제 지연 최소화

-- 병렬 복제 활성화 (MySQL 5.7+)
-- MySQL 8.0.26+
SET GLOBAL replica_parallel_workers = 4;
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';

-- MySQL 8.0.25 이하
SET GLOBAL slave_parallel_workers = 4;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';

-- MariaDB 10.0+
SET GLOBAL slave_parallel_threads = 4;

바이너리 로그 관리

-- 바이너리 로그 보관 기간 설정 (7일)
SET GLOBAL binlog_expire_logs_seconds = 604800;

-- 수동 정리
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

읽기 전용 모드 강제

복제 서버에서 실수로 데이터를 변경하는 것을 방지합니다:

SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;  -- SUPER 권한 사용자도 차단

복제 필터링

특정 데이터베이스만 복제하거나 제외할 수 있습니다:

# my.cnf
# 특정 데이터베이스만 복제
replicate-do-db = important_db

# 특정 데이터베이스 제외
replicate-ignore-db = test_db

 

 

마무리

MySQL/MariaDB 복제는 처음에는 복잡해 보일 수 있지만, 원리를 이해하고 나면 강력한 도구가 됩니다. 요약하자면:

1. GTID 방식을 사용하면 관리가 훨씬 편해집니다
2. server-id는 반드시 고유해야 합니다
3. 복제 상태를 주기적으로 모니터링해야 합니다
4. 장애 복구 절차를 미리 테스트해두세요

참고 자료

• MySQL 8.0 Replication 공식 문서
• MySQL 8.4 Configuring Replication
• MariaDB Replication 공식 문서
• MariaDB Setting Up Replication
• MySQL GTID Replication 가이드