MySQL / MariaDB 복제(Replication) 정리 – 동작 원리, 설정, 복제 지연 해결

복제(Replication)는 DB 데이터를 두 대 이상에 복제해 두는 방식이다. 가장 기본 구성은 마스터(master) 1대 + 슬레이브(slave) 1대이고, master-master, 다중 slave 로도 쓴다. 비동기 방식이라 마스터의 변경이 슬레이브에 약간의 지연을 두고 반영된다.

왜 쓰나

실시간 백업 — 마스터가 원본, 슬레이브가 백업. 마스터 장애 시 슬레이브로 전환
부하 분산 — 마스터는 쓰기(insert·update·delete), 슬레이브는 읽기(select) 로 나눠 받음

마스터와 슬레이브의 역할

마스터: 데이터 변경(등록·수정·삭제)이 생기면 바이너리 로그(binary log) 에 기록하고 슬레이브에 전달한다. 마스터는 슬레이브가 몇 대인지 등은 모른다.
슬레이브: 마스터에서 받은 바이너리 로그를 자기 DB 에 반영한다.

바이너리 로그와 복제 형식

바이너리 로그(binlog) 는 DB 에서 일어난 모든 변경 내역이 기록되는 파일이다. 기본은 꺼져 있어서 켜 줘야 한다. 복제 형식은 세 가지다.

STATEMENT (SBR) — 실행된 SQL 문을 그대로 복사
ROW (RBR) — 변경된 행(row)만 복사. 지금 기본값이자 권장
MIXED — 둘을 상황에 따라 섞음

동작 과정

사용자가 insert/update/delete 로 데이터 변경을 요청한다.
마스터가 바이너리 로그에 변경 사항을 기록하고 스토리지 엔진에 커밋한다.
슬레이브의 I/O 스레드가 마스터에 이벤트를 요청하면, 마스터의 dump 스레드가 바이너리 로그를 전송한다.
슬레이브는 받은 내용을 릴레이 로그(relay log)에 기록한다.
슬레이브의 SQL 스레드가 릴레이 로그를 읽어 스토리지 엔진에 반영한다.

설정 — Master

마스터 설정 파일(/etc/mysql/mariadb.conf.d/50-server.cnf 또는 /etc/my.cnf)에 넣는다.

[mysqld]
server-id       = 1          # 마스터·슬레이브가 서로 다른 값이면 됨
log_bin         = mysql-bin  # 바이너리 로그 파일 이름
binlog_format   = ROW

[mysqld]

server-id = 1 # 마스터·슬레이브가 서로 다른 값이면 됨

log_bin = mysql-bin # 바이너리 로그 파일 이름

binlog_format = ROW

재시작 후 슬레이브가 접속할 복제 전용 계정을 만든다. (identified by 를 붙이는 옛 문법 대신 CREATE USER 로 나눠 쓰는 것이 지금 방식이다. 접속 IP 대역도 제한한다.)

CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY '<password>';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';

SHOW MASTER STATUS;   -- File 과 Position 을 적어 둔다

CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY '<password>';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';

SHOW MASTER STATUS; -- File 과 Position 을 적어 둔다

※ 참고 SHOW MASTER STATUS 의 File 명과 Position 번호를 기록해 둔다. DB 를 재시작하면 이 값이 바뀔 수 있으니, 슬레이브 설정 직전에 확인한다.

설정 — Slave

[mysqld]
server-id       = 2          # 마스터와 다른 값
relay_log       = relay-bin
read_only       = 1          # 슬레이브는 읽기 전용 권장

[mysqld]

server-id = 2 # 마스터와 다른 값

relay_log = relay-bin

read_only = 1 # 슬레이브는 읽기 전용 권장

재시작 후 마스터를 가리키게 설정한다.

CHANGE MASTER TO
  MASTER_HOST     = '10.0.0.11',
  MASTER_USER     = 'repl',
  MASTER_PASSWORD = '<password>',
  MASTER_PORT     = 3306,
  MASTER_LOG_FILE = 'mysql-bin.000001',   -- 마스터에서 기록한 File
  MASTER_LOG_POS  = 1234;                 -- 마스터에서 기록한 Position

START SLAVE;
SHOW SLAVE STATUS\G

CHANGE MASTER TO

MASTER_HOST = '10.0.0.11',

MASTER_USER = 'repl',

MASTER_PASSWORD = '<password>',

MASTER_PORT = 3306,

MASTER_LOG_FILE = 'mysql-bin.000001', -- 마스터에서 기록한 File

MASTER_LOG_POS = 1234; -- 마스터에서 기록한 Position

START SLAVE;

SHOW SLAVE STATUS\G

Slave_IO_Running 과 Slave_SQL_Running 이 둘 다 Yes 이고 Seconds_Behind_Master 가 0 에 가까우면 정상이다.

확인

마스터에서 DB·테이블·데이터를 만들고 슬레이브에서 그대로 보이는지 확인한다.

-- 마스터
CREATE DATABASE test_db;
USE test_db;
CREATE TABLE user_tb (
  user_idx  INT PRIMARY KEY AUTO_INCREMENT,
  user_name VARCHAR(10) NOT NULL
);
INSERT INTO user_tb(user_name) VALUES ('kim');

-- 슬레이브에서
SHOW DATABASES;        -- test_db 보임
USE test_db;
SELECT * FROM user_tb; -- 같은 데이터 보임

-- 마스터

CREATE DATABASE test_db;

USE test_db;

CREATE TABLE user_tb (

user_idx INT PRIMARY KEY AUTO_INCREMENT,

user_name VARCHAR(10) NOT NULL

);

INSERT INTO user_tb(user_name) VALUES ('kim');

-- 슬레이브에서

SHOW DATABASES; -- test_db 보임

USE test_db;

SELECT * FROM user_tb; -- 같은 데이터 보임

주의

마스터와 슬레이브의 MySQL/MariaDB 버전을 같게 맞춘다. 꼭 달라야 하면 슬레이브가 상위 버전이어야 한다.
마스터 → 슬레이브 순서로 가동한다.
바이너리 로그는 계속 쌓이므로 보관 기간을 정한다. (지금은 expire_logs_days 대신 binlog_expire_logs_seconds 를 쓴다. 예: 604800 = 7일)
슬레이브에 직접 쓰기를 하면 데이터가 어긋나 복제가 깨진다. 슬레이브는 read_only(가능하면 super_read_only) 로 둔다.

복제 지연(Replication Lag)

마스터에서는 여러 스레드가 동시에 쓰기를 하는데, 슬레이브에서 이를 반영하는 SQL 스레드는 기본적으로 1개라 처리가 밀린다. 이것이 복제 지연이다. 병렬 복제로 스레드를 늘려도 근본 원인은 남는다.

지연 원인

느린 쿼리(Long Query) — STATEMENT 복제에서 1시간짜리 쿼리를 슬레이브가 그대로 다시 실행하면, 그 1시간 동안 지연된다.
쓰기량 증가 — 쓰기 쿼리 자체가 늘면 복제할 양도 늘어 지연. 사용량 증가 때문이면 계속 벌어질 수 있고, 보통은 낮에 벌어졌다가 새벽에 따라잡는 패턴
락(lock) 이슈 — 슬레이브에서 메타데이터 락 등으로 쿼리가 막힘
하드웨어 — 디스크 I/O 성능, OS 파라미터, 느린 디스크(7200rpm HDD 등)
슬레이브 부하 — 슬레이브의 읽기 트래픽·배치·백업이 겹쳐 처리 성능이 떨어짐
데이터 불일치로 인한 복제 중단 — 지울 행이 없거나 넣을 행이 이미 있으면 슬레이브가 판단 못 하고 복제를 멈춤. 누군가 슬레이브에 직접 쓰기를 한 경우가 많다.

해결

쿼리 성능 개선 — 느린 쿼리면 인덱스 추가·쿼리 수정으로 실행 속도를 높인다.
병렬 복제 — 복제 적용 worker 스레드를 늘린다. 무작정 많다고 좋진 않고, 16개 이상은 효과가 거의 없는 경우가 많다.

STOP SLAVE;
SET GLOBAL slave_parallel_type   = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 8;
START SLAVE;

STOP SLAVE;

SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';

SET GLOBAL slave_parallel_workers = 8;

START SLAVE;

※ 참고 위는 MySQL 변수명이다. MySQL 8.0.26+ 는 replica_parallel_workers, replica_parallel_type 로 이름이 바뀌었고, MariaDB 는 slave_parallel_threads 를 쓴다.

복제 형식 조정(SBR/RBR) — 1시간 돌아도 결국 1건만 바꾸는 쿼리라면 세션에서 ROW 로 바꾸는 게 낫다.

SET SESSION binlog_format = 'ROW';     -- 또는 'STATEMENT'

1	SET SESSION binlog_format = 'ROW'; -- 또는 'STATEMENT'

데이터 경량화 — 데이터가 많을수록 불리하다. 서비스에 꼭 필요한 데이터만 남기고 로그성 테이블은 다른 DB 로 분리하거나 삭제한다.

복제가 깨졌을 때

데이터 불일치로 멈췄을 때, 원인을 확인한 뒤에만 한 건을 건너뛰거나 위치를 다시 지정한다.

-- 문제 되는 한 건만 건너뛰기 (원인 확인 후에만)
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

-- 문제 되는 한 건만 건너뛰기 (원인 확인 후에만)

STOP SLAVE;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

START SLAVE;

참고한 자료: DANIDANI – MySQL(MariaDB) Replication, rlawls1991 – MySQL Replication

anydragon

참고자료

최신 글

보관함

MySQL / MariaDB 복제(Replication) 정리 – 동작 원리, 설정, 복제 지연 해결

왜 쓰나

마스터와 슬레이브의 역할

바이너리 로그와 복제 형식

동작 과정

설정 — Master

설정 — Slave

확인

주의

복제 지연(Replication Lag)

지연 원인

해결

복제가 깨졌을 때

관련

Leave a Reply Cancel reply

태그

링크

메타