MySQL 복제 일상 운영과 장애 시나리오 (8.0 / 8.4)

구성된 source + replica 토폴로지(async / semi-sync, GTID 또는 binlog 위치)의 일상 운영 및 장애 복구 런북입니다: 상태 및 지연 점검, 계획된 switchover, failover, 기존 source 재합류, replica 재구성(clone / XtraBackup / mysqldump), errant GTID, 1062/1032 오류, 지연 replica를 이용한 데이터 복구, replica 이전, binlog 정리. 모든 시나리오는 진단 → 안전장치 → 적용 → 검증 → 후속 조치 순서를 따릅니다. 설치 가이드가 아닙니다: 복제를 처음 구성하려면 «MySQL GTID 복제» 가이드를, InnoDB Cluster(Group Replication)는 «InnoDB Cluster 운영» 가이드를 여십시오.

구성: source 1대 + replica 1..5대 (async / semi-sync)

시나리오

• 상태 및 지연 점검 (복제가 정상인가?) — 읽기 전용: IO/SQL 스레드, 최근 오류, 실제 지연, semi-sync 상태, errant GTID를 점검합니다. 아무것도 변경하지 않으며 다운타임도 없습니다. 무엇을 해야 할지 확실하지 않으면 여기서 시작하십시오: 마지막 단계가 출력에 따라 알맞은 시나리오를 안내합니다.
• 계획된 switchover (source 역할을 replica로 이관) — source와 replica가 정상이며, 유지보수·하드웨어 교체·업그레이드를 위해 source 역할을 계획적으로 replica에 넘기는 경우입니다. 쓰기가 몇 분간 중단되지만 데이터 손실은 없습니다. 원복은 같은 시나리오를 반대로 수행합니다.
• source 유실: 가장 앞선 replica로 failover — source가 다운되었거나 접근할 수 없고 곧 복구되지 않을 상황입니다(replica의 IO 스레드가 Connecting, 2003/2013). 가장 앞선 replica를 새 source로 만들고 나머지는 여기에 연결합니다. 새 source가 열릴 때까지 쓰기가 중단되며, async에서는 source의 마지막 몇 개 트랜잭션이 유실될 수 있습니다.
• failover 후 기존 source를 replica로 재합류 — failover가 끝났고 기존 source 서버가 다시 살아났습니다. 먼저 쓰기를 막고 binlog를 복사한 뒤 errant transaction을 점검합니다. 깨끗하면 바로 replica가 되고, 그렇지 않으면 새 source에서 재구성합니다. 새 source에는 다운타임이 없습니다.
• replica를 처음부터 재구성 (clone / XtraBackup / mysqldump) — replica에 필요한 binlog가 source에서 삭제되었거나(1236), 데이터가 source와 어긋났거나, 1062/1032가 반복되거나, replica의 디스크가 유실된 경우입니다. replica의 모든 데이터를 source의 사본으로 교체합니다. source는 계속 운영되며 대상 replica는 그동안 사용할 수 없습니다.
• Errant GTID: source에 없는 트랜잭션이 replica에 있음 — replica에 직접 기록되었거나(super_read_only가 꺼져 있을 때) 이전 source에서 남은 트랜잭션이 있습니다. failover 시 이 트랜잭션이 다른 replica로 퍼지거나 1236으로 복제를 중단시킵니다. 해결: 빈 트랜잭션, source에 적용, 또는 재구성. 다운타임은 없습니다.
• 복제 중단: 1062 Duplicate entry / 1032 Can't find record — SQL 스레드가 데이터 오류로 중단되었습니다(Replica_SQL_Running: No, Last_SQL_Errno 1062/1032/1146/1677…). 먼저 해당 행을 source의 상태로 맞추고, 그것이 불가능하면 최후의 수단으로 트랜잭션 하나를 건너뜁니다. 이 replica만 영향을 받습니다: 뒤처지게 되고, 여기서 읽는 애플리케이션은 오래된 데이터를 보게 됩니다.
• 지연 replica (SOURCE_DELAY): 구성 또는 삭제된 데이터 복구 — replica를 의도적으로 N초 뒤처지게 유지하거나(지연 설정 / 해제), 실수로 삭제·변경된 데이터(DROP, WHERE 없는 DELETE/UPDATE)를 지연 replica에 아직 적용되기 전에 되살리려는 경우입니다. source는 계속 운영됩니다.
• replica를 다른 source로 이전 (체인 / 재연결) — replica를 다른 replica 아래(체인 / 중간 replica)로 붙이거나 source로 다시 연결하려는 경우입니다. 데이터는 복사되지 않고 연결만 바뀝니다. 이전하는 replica는 몇 초간 멈춥니다.
• binlog 정리와 보존 기간 (디스크 부족) — binlog가 디스크를 채우고 있거나 보존 기간을 조정하려는 경우입니다. replica와 PITR에 아직 필요한 파일은 지우지 않고 정리합니다. 다운타임은 없습니다.

시작하기 전에

• 모든 서버에서 MySQL …… (8.0.26 이상)…이 같은 마이너 버전이고…, gtid_mode = ON, enforce_gtid_consistency = ON…, binlog가 켜져 있음…; 운영체제 root가 mysql로 비밀번호 없이 접속할 수 있습니다(auth_socket 또는 ~/.mylogin.cnf). 토폴로지는 복제 설치 가이드 또는 동등한 방식으로 구성되어 있습니다.
• 역할이 입력값과 일치함을 확인했습니다: … (…)…는 유실된 기존 source…는 현재 source(read_only = 0, replica 설정 없음)…이고, 목록의 서버는 replica입니다.… failover 때 source로 승격된 서버: … (…).…
• … 계정의 비밀번호를 알고 있습니다. 계정은 각 서버의 IP에 대해 존재합니다('…'@'<ip>', REQUIRE SSL). 각 단계는 이 계정으로 다른 서버의 GTID 세트 같은 값을 읽고, 역할 전환 시 복제 연결에도 사용합니다. 비밀번호에 ', ", \가 없습니다.
• 서버끼리 … 포트로 서로 접근할 수 있습니다.
• 모든 서버에 semi-sync 플러그인 두 개가 모두 설치되어 ACTIVE 상태입니다: rpl_semi_sync_source(semisync_source.so)와 rpl_semi_sync_replica(semisync_replica.so). 역할 전환 시 각 서버에서 양쪽 모두 켜고 끕니다. 이전 이름인 rpl_semi_sync_master / rpl_semi_sync_slave(8.0.26 이전, 8.4에서 deprecated)가 설치되어 있으면 단계에서 쓰는 rpl_semi_sync_source_enabled / rpl_semi_sync_replica_enabled 변수가 없습니다: 먼저 이전 플러그인을 UNINSTALL PLUGIN으로 제거하고 새 플러그인을 설치하십시오(같은 서버에 둘을 함께 로드할 수 없고, 읽기 전용 서버에서는 INSTALL PLUGIN이 거부되므로 super_read_only를 일시적으로 끕니다).
• 새 source / 중간 서버가 될 replica에서 log_bin = ON, log_replica_updates = ON(8.x 기본값)입니다. 그렇지 않으면 그 서버의 binlog에 source에서 온 트랜잭션이 들어 있지 않아 다른 replica가 연결할 수 없습니다.
• 파일 전송을 위해 관련 서버 간 root SSH(키 기반) 접근이 가능합니다…: … → ……. 불가능하면 사내에서 승인된 방법으로 파일을 옮기십시오. 각 단계에 파일 경로가 표시됩니다.
• donor(…)와 대상(…)에 MySQL과 같은 주 버전의 Percona XtraBackup이 설치되어 있고, donor에 백업 계정 bkpuser(auth_socket)가 있습니다 — XtraBackup 가이드의 처음 두 페이즈.
• 변경 승인과 연락 채널(애플리케이션 팀, 유지보수 시간대)이 준비되어 있습니다. 역할 전환 시 애플리케이션의 쓰기 엔드포인트(VIP, DNS, ProxySQL / HAProxy / Router)를 누가 어떻게 바꿀지 정해져 있습니다.

가이드 단계

• 상태: source, replica, 지연 (읽기 전용) — source의 역할, binlog 위치, 연결된 replica 표시 · replica의 스레드 상태, 오류, 지연 표시 · performance_schema로 실제 지연과 적용 대기열 측정 · Errant GTID 점검 (source에 없는 트랜잭션이 replica에 있는가?) · 결과 해석 및 알맞은 시나리오 선택
• 1. 진단: switchover 준비가 되었는가? — source와 연결된 replica 점검 · 후보 replica의 적합성 확인 · 모든 replica가 정상인지 점검
• 2. 안전장치 — 모든 서버의 현재 상태 기록
• 3. 적용: 역할 이관 — 애플리케이션 쓰기 중지 · source 쓰기 차단 및 마지막 위치 확인 · replica가 source를 따라잡을 때까지 대기 · 후보를 source로 승격 (promote) · 기존 source를 새 source의 replica로 전환 · 다른 replica를 새 source에 연결 (…) …
• 4. 검증 — 새 source에서 테스트 쓰기 · 모든 서버에서 복제와 테스트 행 점검
• 5. 후속 조치 — 테스트 데이터베이스 삭제 및 운영 정보 갱신
• 1. 진단: source가 정말 유실되었는가, 어느 replica가 가장 앞서 있는가? — source 접근 마지막 시도 (후보 replica에서) · 기존 source 격리 (fencing) · 각 replica에서 relay log의 트랜잭션 적용 대기 · 가장 앞선 replica 판별 · 가장 앞선 replica 판별 (위치 기반)
• 2. 안전장치 — replica의 현재 상태 기록
• 3. 적용: failover — 후보를 source로 승격 (promote) · 다른 replica를 새 source에 연결 (…) · 새 source에서 쓰기 허용 · 애플리케이션을 새 source로 전환
• 4. 검증 — 새 source에서 테스트 쓰기 · replica에서 복제와 테스트 행 점검
• 5. 후속 조치 — 유실 트랜잭션, 백업, 기존 source 처리 계획
• 1. 진단: 기존 source는 깨끗한가? — 기존 source 상태 표시 · errant transaction 찾기 (새 source와 비교) · 위치 기반에서의 재합류 판단
• 2. 안전장치 — 기존 source 즉시 쓰기 차단 및 상태 기록 · errant transaction을 별도 파일로 추출
• 3. 적용: replica로 연결 — 기존 source를 새 source에 연결
• 4. 검증 — 기존 source가 replica로 동작하는지 검증
• 5. 후속 조치 — 토폴로지와 기존 source의 향후 계획
• 1. 진단: errant 세트는 무엇인가? — errant GTID 세트 계산
• 2. 안전장치: errant transaction 내용 저장 — binlog에서 errant transaction을 해독해 저장
• 3. 적용: source에 빈 트랜잭션 주입 — errant GTID를 source에서 빈 트랜잭션으로 실행
• 3. 적용: errant transaction을 source에 적용 — errant transaction을 파일로 추출해 source로 복사 · 트랜잭션을 source에 적용
• 4. 검증 — errant 세트가 남지 않았는지 검증
• 5. 후속 조치: 재발 방지 — 근본 원인 파악 및 replica 보호
• 1. 진단: 어떤 트랜잭션, 어떤 행인가? — 오류와 실패한 트랜잭션 표시 · relay log에서 실패한 트랜잭션의 행 해독 · source와 replica의 행 비교
• 2. 안전장치 — replica 상태와 테이블의 현재 상태 기록
• 3. 적용: replica의 행 수정 — 수정 SQL 준비 · binlog에 기록하지 않고 수정 적용 후 복제 시작
• 3. 적용: 실패한 트랜잭션 건너뛰기 (최후의 수단) — 실패한 트랜잭션만 건너뛰기
• 4. 검증 — 양쪽에서 행과 테이블 체크섬 비교
• 5. 후속 조치 — 근본 원인 파악
• 1. 진단 — replica의 현재 지연 설정 표시
• 2. 안전장치 — replica 상태 기록