[MySQL] (복제구조4️⃣) 복제 환경에서 자주 발생하는 문제와 해결법

MySQL 복제를 운영하다 보면 처음에는 잘 작동하지만,
시간이 지나면서 다양한 이유로 복제가 중단되거나 지연되는 문제가 발생한다.
이 문제들은 대부분 사소한 실수나 설정 누락에서 시작되며,
사전 예방이나 정확한 원인 파악만으로도 쉽게 해결 가능하다.

❗ 1. 복제 중단 (Replica_SQL_Running: No)

원인 예시

• Master에서 DROP/ALTER된 테이블을 Slave에서 찾지 못함
• 제약 조건 충돌 (PK 중복, FK 오류 등)
• 데이터 불일치로 쿼리 실행 실패

해결 방법

SHOW REPLICA STATUS\G
-- 또는
SHOW SLAVE STATUS\G

Last_SQL_Error 메시지 확인 후, 데이터 손실을 감수할 수 있다면 다음 명령으로 복구 가능

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START REPLICA;

주의: 오류가 반복될 경우, Slave 데이터를 초기화 후 재복제하는 것이 안전

반응형

❗ 2. Seconds_Behind_Source 값이 계속 증가함

원인 예시

• Slave 서버 리소스 부족 (CPU, Disk I/O 등)
• Master에서 대량 트랜잭션 발생
• 네트워크 지연으로 I/O 쓰레드가 밀림

해결 방법

• SHOW PROCESSLIST로 Slave SQL 쓰레드 상태 확인
• SHOW ENGINE INNODB STATUS로 록 병목 확인
• 리소스 부족 시 인스턴스 사양 업그레이드 또는 Slave 분산 구성

❗ 3. 바이너리 로그 삭제로 복제 불가능

상황 예시

Master에서 오래된 binlog가 자동 삭제되었고,
Slave가 아직 해당 위치를 읽지 못했을 경우

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log

해결 방법

• RESET REPLICA ALL 또는 RESET SLAVE ALL 후
  새로 백업을 받아 복제 재구성 필요

STOP REPLICA;
RESET REPLICA ALL;
CHANGE REPLICATION SOURCE TO ...
START REPLICA;

반응형

❗ 4. GTID 복제에서 복제 안됨 (GTID Mismatch)

원인 예시

• GTID가 충돌함 (같은 GTID가 Master, Slave 양쪽에 존재)
• Slave가 실행한 트랜잭션을 Master가 갖고 있지 않음

해결 방법

• Slave에서 현재 GTID 목록 확인

SHOW VARIABLES LIKE 'gtid_executed';

• 문제 해결이 복잡할 경우, Slave 재동기화가 가장 빠름

❗ 5. Duplicate entry 에러로 복제 멈춤

Last_SQL_Error: Error 'Duplicate entry '1001' for key 'PRIMARY''

원인

• Slave에서 수동으로 입력한 데이터가 Master와 충돌
• 복제 쿼리 실행 시 PK 충돌 발생

해결 방법

• 데이터 정합성 확보 후 SQL_SLAVE_SKIP_COUNTER로 건너뛰기
• 또는 Slave 초기화 후 Master에서 다시 덤프 복제

🧠 실무 팁 요약

• SHOW REPLICA STATUS\G는 복제 트러블슈팅의 핵심
• 복제 지연 원인은 대부분 I/O, SQL 쓰레드, 락, 자원 부족
• Slave 서버에는 read_only = 1 설정 필수
• 정기적으로 binlog 보존일 수명 설정 확인 (expire_logs_days)
• 장애 발생 시 대비해 복제 백업 자동화 스크립트 구축 권장

📘 공식 문서 참고

https://dev.mysql.com/doc/refman/8.0/en/replication-solutions.html