DevOps 도구 및 베스트 프랙티스 마스터 - DevOps 지식 허브 DevOps 도구 및 베스트 프랙티스 마스터 - DevOps 지식 허브

일반적인 MySQL 복제 실패를 빠르게 해결하는 방법

이 실용적인 가이드를 통해 일반적인 MySQL 복제 실패를 신속하게 해결하세요. `SHOW REPLICA STATUS`에서 오류 코드를 해석하고, MySQL 오류 로그를 검사하며, 바이너리 로그의 목적을 이해하는 방법을 배웁니다. 이 문서는 중복 항목, 누락된 binlog 파일, 데이터 불일치와 같은 문제를 진단하고 건강한 복제 설정을 유지하기 위한 실행 가능한 단계와 모범 사례를 제공합니다.

일반적인 MySQL 복제 실패를 빠르게 해결하는 방법

MySQL 복제 실패는 두 가지 질문을 분리하면 더 쉽게 해결할 수 있습니다: 복제본이 소스에서 이벤트를 가져올 수 있는지, 그리고 이미 가져온 이벤트를 적용할 수 있는지 여부입니다. 이는 서로 다른 실패입니다. 네트워크 문제, 누락된 바이너리 로그, 잘못된 비밀번호 또는 잘못된 호스트 권한은 일반적으로 I/O 스레드를 중지시킵니다. 중복 키, 누락된 행, DDL 불일치 또는 데이터 드리프트는 일반적으로 SQL 스레드를 중지시킵니다.

상태 출력부터 시작하세요. 최신 MySQL에서는:

SHOW REPLICA STATUS\G

이전 시스템에서는:

SHOW SLAVE STATUS\G

서버가 지원하는 명령어를 사용하세요. 최신 출력은 Replica_IO_Running, Replica_SQL_Running, Seconds_Behind_Source와 같은 이름을 사용합니다. 이전 출력은 Slave_IO_Running, Slave_SQL_Running, Seconds_Behind_Master를 사용합니다.

첫 번째로 유용한 정보는:

  • Replica_IO_Running: 복제본이 소스 바이너리 로그에 연결되어 읽고 있는지 여부.
  • Replica_SQL_Running: 복제본이 릴레이 로그 이벤트를 적용하고 있는지 여부.
  • Last_IO_Errno 및 Last_IO_Error: 가져오기가 실패한 이유.
  • Last_SQL_Errno 및 Last_SQL_Error: 적용이 실패한 이유.
  • Relay_Master_Log_File, Exec_Master_Log_Pos 또는 최신 소스 위치 필드: 복제본이 스트림에서 어디에 있는지.

바로 해결 방법으로 건너뛰지 마세요. 먼저 전체 상태 출력을 인시던트 노트에 복사하세요. RESET REPLICA를 실행하거나, 트랜잭션을 건너뛰거나, 복제본을 다시 지정하면 최상의 증거가 사라집니다.

I/O 스레드가 중지된 경우

Replica_IO_Running이 No인 경우, 복제본이 소스에서 성공적으로 읽고 있지 않습니다. SQL 스레드는 잠시 동안 이전 릴레이 로그 이벤트를 계속 적용할 수 있지만, 결국에는 고갈됩니다.

일반적인 원인은:

  • 소스 호스트 또는 포트가 잘못됨.
  • 방화벽, 보안 그룹 또는 라우팅 규칙이 연결을 차단함.
  • 복제 사용자 비밀번호가 잘못됨.
  • 복제 사용자가 복제본이 실제로 사용하는 호스트와 다른 호스트에서 허용됨.
  • 소스에서 바이너리 로깅이 비활성화됨.
  • 소스가 복제본이 요청한 바이너리 로그 파일을 제거함.
  • TLS 설정이 변경되어 복제본이 더 이상 인증할 수 없음.

복제본 호스트에서 테스트:

mysql -h source-db.example.com -u repl_user -p

직접 로그인이 실패하면 복제도 실패합니다. 소스에서 계정 확인:

SHOW GRANTS FOR 'repl_user'@'replica_host_or_ip';

계정에는 REPLICATION SLAVE 권한이 필요합니다. 권한 이름은 MySQL 권한에서 여전히 "SLAVE"를 사용합니다.

또한 바이너리 로깅이 활성화되어 있는지 확인:

SHOW VARIABLES LIKE 'log_bin';
SHOW MASTER STATUS;

최신 버전에서는 SHOW BINARY LOG STATUS를 사용할 수 있습니다. 요점은 동일합니다: 소스에는 바이너리 로그가 있어야 하며, 요청된 파일이 여전히 존재해야 합니다.

오류 1236: 누락되었거나 읽을 수 없는 바이너리 로그

Last_IO_Errno: 1236은 일반적으로 복제본이 소스가 제공할 수 없는 바이너리 로그 파일이나 위치를 요청하고 있음을 의미하는 오류 중 하나입니다. 정확한 메시지는 다양합니다. 첫 번째 로그 파일을 찾을 수 없거나, 로그 이벤트를 읽을 수 없거나, 읽는 동안 소스가 연결을 닫았다고 말할 수 있습니다.

가장 일반적인 운영 사례는 간단합니다: 복제본이 너무 오랫동안 다운되었고, 소스가 필요한 바이너리 로그를 제거한 경우입니다.

소스에 남아 있는 로그 확인:

SHOW BINARY LOGS;

그런 다음 해당 목록을 복제본 상태에 명명된 파일과 비교하세요. 복제본이 mysql-bin.000120이 필요하고 소스가 이제 mysql-bin.000140에서 시작하는 경우, 복제본은 바이너리 로그에서 따라잡을 수 없습니다.

세 가지 현실적인 선택이 있습니다:

  • 소스에서 가져온 새 백업에서 복제본을 복원하거나 재구축.
  • 필요한 데이터가 여전히 있는 다른 복제본을 복제 소스로 사용(프로세스가 지원하는 경우).
  • GTID를 사용하고 누락된 트랜잭션이 다른 곳에 존재하는 경우, 이를 제공할 수 있는 유효한 소스에서 재구성.

복제를 시작하기 위해 더 높은 로그 위치를 추측하지 마세요. 그러면 누락된 트랜잭션이 있는 복제본이 생성됩니다. 조용히 잘못된 데이터를 반환하면서 건강해 보일 수 있습니다.

복구 후, 디스크 용량이 허용하면 바이너리 로그 보존 기간을 늘리세요:

[mysqld]
binlog_expire_logs_seconds=604800

이 예시는 약 7일입니다. 유지보수나 인시던트 중 복제본이 오프라인 상태일 수 있는 시간을 기준으로 값을 선택하세요.

SQL 스레드가 중지된 경우

Replica_SQL_Running이 No인 경우, 복제본이 이벤트를 가져왔지만 적용하지 못했습니다. 이는 종종 연결 문제가 아닌 데이터 일관성 문제입니다.

전체 Last_SQL_Error를 읽으세요. 일반적으로 테이블, 키, 실패한 작업, 때로는 소스 로그 위치를 알려줍니다. 그런 다음 변경하기 전에 소스와 복제본 모두에서 관련 행을 검사하세요.

알려진 바이너리 로그 위치 주변의 실패한 이벤트의 경우, mysqlbinlog로 이벤트를 표시할 수 있습니다:

mysqlbinlog --start-position=123456 --stop-position=124500 /var/lib/mysql/mysql-bin.000321

소스 바이너리 로그가 로컬 호스트에 없는 경우, 원격 옵션을 사용하거나 복사된 로그 파일을 검사하세요. 행 기반 이벤트의 경우 주의하세요: 읽을 수 있도록 디코딩 옵션과 테이블 메타데이터가 필요할 수 있습니다.

오류 1062: 중복 항목

Last_SQL_Errno: 1062는 복제본이 행을 삽입하거나 업데이트하려고 시도했지만 이미 존재하는 고유 키와 충돌했음을 의미합니다.

일반적인 원인은:

  • 누군가가 복제본에 직접 썼습니다.
  • 복제본이 잘못된 스냅샷에서 초기화되었습니다.
  • 이전 복제 오류가 건너뛰어졌습니다.
  • 다중 소스 또는 액티브-액티브 설계에서 자동 증가 설정이 잘못되었습니다.
  • 실수로 애플리케이션 쓰기가 두 개의 쓰기 가능한 서버로 전송되었습니다.

유혹적인 해결 방법은:

STOP REPLICA;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START REPLICA;

이전 구문은 STOP SLAVE 및 START SLAVE를 사용합니다. 이는 행이 중요하지 않다고 확인한 후 일회용 보고 복제본에 대해 허용될 수 있습니다. 나중에 승격될 수 있는 복제본에는 위험합니다. 건너뛰기는 복제본이 더 이상 소스와 동일한 트랜잭션 기록을 가지고 있지 않음을 의미합니다.

더 안전한 프로세스는:

  1. 충돌하는 테이블과 키를 식별합니다.
  2. 소스와 복제본의 행을 비교합니다.
  3. 복제본 행을 삭제, 업데이트할지 또는 복제본을 재구축할지 결정합니다.
  4. 결정을 기록합니다. 이는 이제 데이터 일관성 이벤트이기 때문입니다.

복제본이 장애 조치용인 경우, 알 수 없는 여러 차이점을 수동으로 패치하는 것보다 재구축이 더 깔끔한 경우가 많습니다.

오류 1032: 레코드를 찾을 수 없음

Last_SQL_Errno: 1032는 일반적으로 복제본이 로컬에 존재하지 않는 행을 업데이트하거나 삭제하려고 시도했음을 의미합니다. 이는 많은 중복 키 문제의 거울 이미지입니다. 소스에는 행이 있었지만 복제본에는 없었습니다.

일반적인 원인은:

  • 복제본에서 행이 수동으로 삭제되었습니다.
  • 이전 트랜잭션이 건너뛰어졌습니다.
  • 초기 덤프에서 데이터가 누락되었습니다.
  • 복제 필터가 이전 쓰기를 제외했습니다.

누락된 행이 무해하다고 가정하지 마세요. UPDATE가 행을 찾을 수 없으면 복제본은 이미 소스와 다릅니다. 영향을 받는 키 주변의 개수와 샘플 데이터를 비교하세요. 테이블이 작으면 테이블 재로드가 합리적일 수 있습니다. 크거나 중요한 경우 일관성 도구를 사용하거나 복제본을 재구축하세요.

인증 및 호스트 권한 문제

비밀번호 순환 또는 네트워크 변경 후 매우 일반적인 실패는 액세스 거부처럼 보이는 I/O 오류입니다:

Access denied for user 'repl_user'@'10.0.2.15'

오류의 호스트는 MySQL이 보는 호스트입니다. NAT, 프록시 또는 컨테이너 네트워킹으로 인해 예상한 호스트 이름과 일치하지 않을 수 있습니다.

소스에서 사용자 검사:

SELECT user, host, plugin FROM mysql.user WHERE user = 'repl_user';

복제본이 10.0.2.15에서 연결하는 경우, 'repl_user'@'replica.internal'에 대한 권한은 이름 확인과 권한이 일치하지 않으면 일치하지 않을 수 있습니다. 네트워크 설계와 일치하는 명시적 호스트 패턴을 선호하세요.

플러그인이 다른 경우, 이전 클라이언트는 최신 인증 플러그인을 사용하는 계정에 대해 실패할 수 있습니다. 일반적으로 인증을 약화시키는 것보다 클라이언트를 업데이트하는 것이 좋지만, 혼합 버전 환경에서는 계획된 호환성 변경이 필요할 수 있습니다.

릴레이 로그 문제

때로는 소스 연결은 괜찮지만 복제본에 릴레이 로그 손상 또는 로컬 디스크 문제가 있습니다. 오류는 릴레이 로그 읽기 실패, 잘린 이벤트 또는 릴레이 로그 위치를 언급할 수 있습니다.

먼저 디스크 상태와 여유 공간을 확인하세요. 가득 찬 디스크는 여러 가지 이상한 복제 증상을 만들 수 있습니다:

df -h
iostat -xz 1

릴레이 로그가 손상되었지만 소스에 필요한 바이너리 로그가 여전히 있는 경우, 종종 릴레이 로그를 재설정하고 복제본이 다시 가져오도록 할 수 있습니다. 정확한 명령은 버전과 토폴로지에 따라 다릅니다. 재설정 명령을 가볍게 실행하지 마세요. 이미 실행된 소스 로그 파일과 위치를 확인하세요.

많은 경우, 이런 종류의 문제는 복제본 호스트에 기본 저장소 문제가 있음을 나타냅니다. 복제본을 다시 신뢰하기 전에 이를 해결하세요.

복제 지연이 항상 실패는 아닙니다

Seconds_Behind_Source는 두 스레드가 모두 실행 중일 때 높을 수 있습니다. 이는 복제가 살아 있지만 뒤쳐져 있음을 의미합니다. 지연을 중지된 스레드와 다르게 처리하세요.

확인:

  • 복제본 디스크가 포화 상태인가요?
  • 소스가 쓰기 버스트를 생성하고 있나요?
  • 복제본의 긴 읽기가 SQL 스레드와 경쟁하고 있나요?
  • 복제본이 소스보다 작거나 느린가요?
  • 동시에 백업 작업이나 스냅샷이 시작되었나요?

지연이 줄어들고 있으면 복제본이 따라잡고 있는 것입니다. 지연이 증가하고 있으면 부하를 제거하거나 용량을 추가하세요. 지연된 복제본을 다시 시작해도 지속적인 리소스 병목 현상을 해결하는 경우는 드뭅니다.

필터 및 다중 소스 복제

복제 필터는 실패를 읽기 어렵게 만들 수 있습니다. 복제본은 의도적으로 일부 데이터베이스나 테이블을 무시할 수 있지만, 애플리케이션은 여전히 관련 데이터가 존재하기를 기대할 수 있습니다. 필터를 사용하는 경우, 복제본이 손상되었다고 가정하기 전에 검사하세요:

SHOW REPLICA STATUS\G

Replicate_Do_DB, Replicate_Ignore_DB, Replicate_Do_Table 또는 재작성 규칙을 언급하는 필드를 찾으세요. 이전 출력은 SHOW SLAVE STATUS 아래에서 동일한 일반 이름을 사용합니다.

필터링은 특히 교차 데이터베이스 쓰기에서 위험합니다. 트랜잭션이 app.orders와 audit.order_events를 업데이트하지만 복제본이 audit를 필터링하는 경우, 결과 복사본은 필터와 기술적으로 일관될 수 있지만 감사 행을 기대하는 워크플로우에는 쓸모가 없습니다. 명령문 기반 로깅은 선택된 기본 데이터베이스가 이벤트 복제 여부에 영향을 줄 수 있기 때문에 데이터베이스 필터를 더욱 예상치 못하게 만들 수 있습니다.

다중 소스 복제는 또 다른 계층을 추가합니다. 한 채널은 정상이고 다른 채널은 중지될 수 있습니다. 이 경우, 출력의 첫 번째 블록만 읽지 말고 모든 채널의 상태를 확인하세요:

SHOW REPLICA STATUS\G

채널 기반 설정에서 상태 출력에는 채널 이름이 포함됩니다. 정상 채널을 재설정하지 않고 실패한 채널을 수정하세요. 두 소스가 동일한 테이블에 중복 키를 쓸 수 있는 경우, 중복 키 오류는 일회성 복제 실패보다는 설계 문제인 경우가 많습니다.

숨겨진 데이터 드리프트 방지

가장 나쁜 복제 실패는 Yes라고 말하면서도 여전히 잘못된 데이터를 포함하는 것입니다. 드리프트는 건너뛴 트랜잭션, 복제본에 대한 직접 쓰기, 실패한 가져오기, 잘못된 필터 또는 수동 복구 후에 발생할 수 있습니다.

중요한 복제본의 경우 일관성 검사를 예약하세요. Percona Toolkit의 pt-table-checksum이 일반적으로 사용되며, pt-table-sync는 통제된 상황에서 차이점을 복구하는 데 도움이 될 수 있습니다. 이러한 도구는 부하를 생성할 수 있으므로 먼저 테스트하고 프로덕션 환경에 맞는 제한으로 실행하세요.

또한 실수로 쓰지 않도록 복제본을 보호하세요:

[mysqld]
read_only=ON
super_read_only=ON

애플리케이션 읽기에 별도의 자격 증명을 사용하세요. "만약을 대비해" 애플리케이션 사용자에게 복제본에 대한 광범위한 쓰기 권한을 부여하지 마세요.

빠른 인시던트 체크리스트

복제가 중단되면 이 순서를 사용하세요:

  1. SHOW REPLICA STATUS\G 출력을 저장합니다.
  2. I/O 스레드 또는 SQL 스레드가 중지되었는지 확인합니다.
  3. Last_IO_Error 또는 Last_SQL_Error를 읽습니다. 오류 번호에만 의존하지 마세요.
  4. 일치하는 타임스탬프에 대해 MySQL 오류 로그를 확인합니다.
  5. I/O 실패의 경우 네트워크, 자격 증명, 권한, TLS 및 바이너리 로그 가용성을 테스트합니다.
  6. SQL 실패의 경우 소스와 복제본 모두에서 영향을 받는 행이나 테이블을 검사합니다.
  7. 복구, 문서화된 위험과 함께 건너뛰기, 테이블 재로드 또는 복제본 재구축 중에서 결정합니다.
  8. 복구 후 실제 쓰기 테스트를 실행하고 지연을 모니터링합니다.

대부분의 MySQL 복제 실패는 하나의 마법 명령어로 해결되지 않습니다. 증거를 보존하고, 어떤 스레드가 실패했는지 식별하고, 실행 중이지만 신뢰할 수 없는 복제본을 남기지 않는 수정을 선택함으로써 해결됩니다.