DevOps 지식 허브
DevOps 지식 허브

일반적인 MySQL 복제 오류 신속하게 문제 해결하기

이 실용적인 가이드로 일반적인 MySQL 복제 오류를 신속하게 해결하세요. `SHOW REPLICA STATUS`의 오류 코드를 해석하고, MySQL 오류 로그를 검사하고, 바이너리 로그의 목적을 이해하는 방법을 알아보세요. 이 글은 복제본 설정이 정상적으로 유지되도록 중복 항목, 누락된 binlog 파일, 데이터 불일치와 같은 문제 진단을 위한 실행 가능한 단계와 모범 사례를 제공합니다.

38 조회수

일반적인 MySQL 복제 실패를 빠르게 문제 해결하기

MySQL 복제는 고가용성, 읽기 확장성 및 재해 복구에 필수적인 데이터베이스의 여러 복사본을 유지할 수 있게 해주는 강력한 기능입니다. 그러나 복제를 설정하고 유지 관리하는 과정에서 예기치 않은 오류가 발생할 수 있습니다. 이 가이드는 오류 코드를 이해하고 관련 로그를 검사하는 데 중점을 두어 일반적인 MySQL 복제 문제를 신속하게 진단하고 해결하는 실용적인 접근 방식을 제공합니다.

복제가 중단되면 중요한 작업이 멈출 수 있으므로 체계적인 문제 해결 과정이 필수적입니다. 이 가이드에서는 가장 자주 발생하는 문제를 다루며, 근본 원인을 파악하고 솔루션을 효율적으로 구현할 수 있는 지식을 제공합니다. 증상을 이해하고 단서를 찾아야 할 곳을 알면 가동 중지 시간을 최소화하고 복제 설정이 정상적으로 유지되도록 할 수 있습니다.

MySQL 복제 기본 이해

문제 해결에 들어가기 전에 MySQL 복제가 어떻게 작동하는지 간략히 다시 살펴보는 것이 좋습니다. 일반적인 마스터-슬레이브(또는 프라이머리-레플리카) 설정에서:

  • 프라이머리의 바이너리 로그(Binlog): 프라이머리 서버는 모든 데이터 변경 이벤트를 바이너리 로그 파일에 기록합니다.
  • 레플리카의 복제 스레드: 레플리카 서버에는 두 가지 스레드가 있습니다.
    • I/O 스레드: 프라이머리에 연결하여 프라이머리의 바이너리 로그에서 이벤트를 읽고 자체 릴레이 로그에 기록합니다.
    • SQL 스레드: 릴레이 로그에서 이벤트를 읽고 레플리카의 데이터베이스에서 실행합니다.

복제 실패는 일반적으로 I/O 스레드가 이벤트를 가져올 수 없거나 SQL 스레드가 이벤트를 적용할 수 없을 때 발생합니다.

일반적인 복제 오류 코드 및 의미

MySQL은 복제 문제에 대한 귀중한 통찰력을 제공하는 오류 코드를 제공합니다. SHOW REPLICA STATUS (이전 버전에서는 SHOW SLAVE STATUS) 명령은 복제 상태를 확인하는 주요 도구입니다.

SHOW REPLICA STATUS\G

다음 주요 필드를 확인하십시오:

  • Replica_IO_Running: Yes여야 합니다.
  • Replica_SQL_Running: Yes여야 합니다.
  • Last_IO_Errno 및 Last_IO_Error: I/O 스레드와 관련된 오류입니다.
  • Last_SQL_Errno 및 Last_SQL_Error: SQL 스레드와 관련된 오류입니다.
  • Seconds_Behind_Source: 레플리카가 프라이머리에 비해 지연된 시간을 나타냅니다.

다음은 몇 가지 일반적인 오류 번호와 그 일반적인 원인입니다:

오류 1062: 중복 항목

  • Last_SQL_Errno: 1062
  • Last_SQL_Error: Error 'Duplicate entry '...' for key '...' on query. Default database: '...'.

원인: SQL 스레드가 프라이머리에서 가져온 이벤트를 적용하려고 할 때 레플리카에서 중복 키 위반이 발생하는 경우입니다. 이는 레플리카가 뒤처져 동일한 데이터를 생성했을 수 있는 다른 쓰기 작업을 처리했거나, 레플리카에 수동으로 불일치가 도입되었을 때 자주 발생합니다.

해결:
1. 문제적인 쿼리 식별: 오류 메시지에는 일반적으로 실패한 쿼리가 포함됩니다.
2. 트랜잭션 건너뛰기(주의 필요): 건너뛰어도 안전하다고 확신하는 경우 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;을 사용한 다음 START SLAVE SQL_THREAD; (또는 START REPLICA SQL_THREAD;)를 사용할 수 있습니다. 경고: 트랜잭션을 건너뛰면 데이터 불일치가 발생할 수 있습니다. 진행하기 전에 영향을 이해하십시오.
3. 데이터 불일치 조사: 건너뛰기가 옵션이 아닌 경우, 수동으로 데이터를 조정하거나 중복이 발생한 이유를 조사해야 할 수 있습니다. 레플리카가 심하게 동기화되지 않은 경우 특정 시점에서 복제를 재설정해야 할 수도 있습니다.

오류 1236: 바이너리 로그 인덱스에서 첫 번째 로그 파일 이름을 찾을 수 없습니다

  • Last_IO_Errno: 1236
  • Last_IO_Error: Error 'Could not find first log file name in binary log index' when trying to read event from the http client side...

원인: I/O 스레드가 프라이머리에서 지정한 바이너리 로그 파일을 찾을 수 없는 경우입니다. 이는 일반적으로 레플리카가 해당 파일을 읽기 전에 프라이머리에서 바이너리 로그 파일이 삭제되었거나, 레플리카가 더 이상 존재하지 않는 빈로그 파일을 사용하여 연결하려고 할 때 발생합니다.

해결:
1. 프라이머리의 빈로그 보존 기간 확인: 프라이머리의 expire_logs_days (또는 binlog_expire_logs_seconds)가 레플리카가 따라잡을 수 있을 만큼 충분히 오래 로그를 보존하는 값으로 설정되어 있는지 확인합니다.
2. 레플리카 재초기화: 가장 일반적인 해결책은 복제를 중지하고, 레플리카의 마스터 데이터를 재설정한 다음, 프라이머리의 최신 백업 또는 스냅샷에서 레플리카를 재초기화하여 새 프라이머리 로그 파일과 위치가 올바르게 설정되었는지 확인하는 것입니다.

오류 1577: 프라이머리의 바이너리 로그 위치가 필요합니다

  • Last_IO_Errno: 1577
  • Last_IO_Error: Error: The primary's binary log position is required for this operation.

원인: 이 오류는 일반적으로 레플리카에서 올바른 바이너리 로그 파일 이름과 위치를 지정하지 않고 복제를 시작하려고 할 때 발생합니다. 특정 구성 변경 또는 수동 개입 후에 발생할 수 있습니다.

해결:
1. CHANGE MASTER TO (또는 CHANGE REPLICATION SOURCE TO) 명령 확인: 복제를 설정할 때 MASTER_LOG_FILE 및 MASTER_LOG_POS (또는 SOURCE_LOG_FILE 및 SOURCE_LOG_POS)를 올바르게 지정했는지 확인합니다.
2. 재설정 및 재구성: 복제를 중지하고, 레플리카 상태를 재설정한 다음, 프라이머리에서 얻은 올바른 매개변수로 CHANGE MASTER TO 명령을 다시 적용합니다.

오류 1032: '...' 테이블에서 레코드를 찾을 수 없습니다

  • Last_SQL_Errno: 1032
  • Last_SQL_Error: Error 'Can't find record in '...' table' on query. Default database: '...'.

원인: 오류 1062와 유사하게, 이는 SQL 스레드가 레플리카에 존재하지 않는 레코드에 대해 UPDATE 또는 DELETE 작업을 수행하려고 함을 나타냅니다. 이는 이전의 건너뛴 트랜잭션 또는 수동 수정으로 인한 데이터 불일치를 의미합니다.

해결:
1. 쿼리 및 테이블 식별: 오류 메시지는 세부 정보를 제공합니다.
2. 데이터 드리프트 조사: 프라이머리와 레플리카에서 영향을 받는 테이블의 상태를 비교합니다.
3. 건너뛰기(매우 주의): 누락된 레코드가 중요하지 않거나 다른 방법으로 처리된 경우, SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 및 START REPLICA SQL_THREAD;를 사용하여 트랜잭션을 건너뛸 수 있습니다.
4. 수동 데이터 수정: 중요한 경우, 누락된 레코드를 수동으로 삽입하거나 테이블/데이터베이스를 다시 동기화해야 할 수 있습니다.

복제 로그 검사

SHOW REPLICA STATUS 외에도 MySQL 오류 로그와 바이너리 로그 자체는 매우 귀중한 자료입니다.

MySQL 오류 로그

일반적으로 /var/log/mysql/error.log (또는 OS 및 구성에 따라 유사한 경로)에 위치하며, 이 로그는 복제 스레드와 관련된 오류를 포함하여 MySQL 서버에서 발생한 오류에 대한 자세한 정보를 포함합니다.

찾아야 할 것:
* 오류에 대한 상세 스택 트레이스.
* 프라이머리와 레플리카 간의 연결 문제.
* 시간 초과 및 네트워크 관련 문제.

프라이머리의 바이너리 로그

레플리카의 릴레이 로그가 SQL 스레드에 중요하지만, 프라이머리의 바이너리 로그를 검사하면 실패로 이어지는 이벤트 순서를 이해하는 데 도움이 될 수 있습니다. 이를 위해 mysqlbinlog 유틸리티를 사용할 수 있습니다.

예시: 특정 바이너리 로그 파일의 이벤트를 보려면:

mysqlbinlog /path/to/mysql-bin.000001

예시: 특정 시간 또는 위치 주변의 이벤트를 보려면:

mysqlbinlog --start-datetime="2023-10-27 10:00:00" --stop-datetime="2023-10-27 11:00:00" /path/to/mysql-bin.000001

사용 사례:
* 레플리카 SQL 오류를 유발한 정확한 트랜잭션 이해.
* 기록되는 이벤트의 일관성 확인.

일반적인 문제 해결 단계

복제가 중단되면 다음 단계를 따르십시오:

  1. SHOW REPLICA STATUS 확인: 항상 여기서부터 시작하십시오. 문제 요약을 얻는 가장 빠른 방법입니다.
  2. Last_IO_Error 및 Last_SQL_Error 검토: 특정 오류 코드와 메시지를 이해합니다.
  3. MySQL 오류 로그 참조: 서버 측에서 더 자세한 컨텍스트를 찾습니다.
  4. 네트워크 연결 확인: 레플리카가 프라이머리에 도달할 수 있는지 확인합니다 (방화벽, DNS).
  5. 사용자 권한 확인: 프라이머리의 복제 사용자는 필요한 권한(REPLICATION SLAVE)을 가지고 있어야 합니다.
  6. 프라이머리가 복제용으로 구성되었는지 확인: log_bin이 활성화되어 있고 server_id가 고유한지 확인합니다.
  7. 레플리카의 read_only 설정 확인: 레플리카에서 read_only가 활성화되어 있으면, 특정 조건이 충족되거나 일시적으로 비활성화되지 않는 한 프라이머리로부터의 쓰기 작업을 적용하지 않습니다.

장애 방지를 위한 모범 사례

  • 복제 지연 모니터링: Seconds_Behind_Source가 과도하게 증가할 때 경고를 보내도록 모니터링 도구를 사용합니다.
  • 정기 백업: 프라이머리의 일관된 백업을 유지하여 레플리카를 신속하게 재초기화할 수 있도록 합니다.
  • 충분한 빈로그 보존: 프라이머리에 expire_logs_days를 적절하게 구성합니다.
  • 고유한 server_id: 복제 토폴로지의 모든 서버가 고유한 server_id를 가지고 있는지 확인합니다.
  • 장애 조치 절차 테스트: 복제 설정이 강력한지 확인하기 위해 역할 전환을 정기적으로 연습합니다.

결론

MySQL 복제 실패를 문제 해결하려면 체계적인 접근 방식이 필요합니다. 일반적인 오류 코드를 이해하고, SHOW REPLICA STATUS 출력 해석 방법을 알며, MySQL의 오류 로그와 mysqlbinlog 유틸리티를 활용함으로써 대부분의 복제 문제를 효율적으로 진단하고 해결할 수 있습니다. 사전 예방적 모니터링과 모범 사례 준수는 이러한 문제의 발생을 더욱 최소화하여 데이터베이스 환경의 안정성과 가용성을 보장할 것입니다.