일반적인 MySQL 오류와 빠른 해결 방법

MySQL 오류는 일반적으로 빠른 첫 번째 읽기가 필요합니다: 오류 로그를 확인하고, 실패한 쿼리나 스레드를 식별하며, 애플리케이션 증상만으로 추측하지 마십시오. 성능 병목 현상부터 중요한 서비스 장애까지 일반적인 오류를 신속하게 진단하고 해결하는 방법을 이해하는 것은 높은 가용성을 유지하는 데 필수적입니다.

이 가이드는 빠르게 분류할 수 있는 일반적인 MySQL 장애를 다룹니다: 느린 쿼리, 교착 상태, 복제 지연, 손상 경고.

MySQL 오류 식별 및 진단

수정을 적용하기 전에 정확한 식별이 핵심입니다. MySQL 진단 정보의 주요 출처는 MySQL 오류 로그와 느린 쿼리 로그입니다. 먼저 이를 확인하는 것이 문제의 근본 원인을 파악하는 가장 효과적인 방법입니다.

MySQL 오류 로그 확인

오류 로그는 중요한 서버 이벤트, 시작/종료 정보 및 심각한 오류를 기록합니다. 위치는 운영 체제 및 구성에 따라 다르지만 일반적으로 데이터 디렉터리에 있습니다.

팁: SHOW VARIABLES LIKE 'log_error';와 같은 명령을 사용하여 정확한 경로를 찾으십시오.

느린 쿼리 로그 활용

명시적인 오류 메시지 없이 성능이 저하되는 경우 느린 쿼리 로그가 다음 단계입니다. 미리 정의된 실행 시간을 초과하는 쿼리를 캡처합니다.

활성화하려면(아직 활성화되지 않은 경우) 구성 파일(my.cnf 또는 my.ini)에서 다음 변수를 설정하고 서버를 다시 시작해야 합니다:

[mysqld]
slow_query_log = 1
long_query_time = 2  # 2초 이상 걸리는 쿼리 기록
slow_query_log_file = /var/log/mysql/mysql-slow.log

일반적인 오류 시나리오 및 즉각적인 수정

다음은 MySQL 환경에서 가장 자주 발생하는 네 가지 운영 문제와 이를 해결하기 위한 실행 가능한 단계입니다.

1. 느린 쿼리 성능

느린 쿼리는 가장 일반적인 성능 저하 원인입니다. 종종 인덱스 누락, 비효율적인 쿼리 구조 또는 잘못된 데이터베이스 설계에서 비롯됩니다.

진단

느린 쿼리 로그를 분석합니다. 특정 느린 쿼리의 경우 EXPLAIN 명령을 사용하여 MySQL이 어떻게 실행하는지 확인합니다:

EXPLAIN SELECT * FROM large_table WHERE column_a = 'value';

type: ALL(전체 테이블 스캔) 또는 과도하게 검사된 행을 찾습니다.

빠른 수정

누락된 인덱스 추가: EXPLAIN이 자주 필터링되는 열에서 전체 스캔을 보여주는 경우 해당 열에 인덱스를 생성합니다: CREATE INDEX idx_column_a ON large_table (column_a);
쿼리 다시 작성: 프로덕션 코드에서 SELECT *를 피하십시오. JOIN을 신중하게 사용하고 WHERE 절이 인덱스된 열을 사용하는지 확인하십시오.
테이블 통계 분석: 때로는 오래된 통계가 최적화 프로그램을 혼란스럽게 합니다. ANALYZE TABLE table_name;을 실행합니다.

2. 트랜잭션 교착 상태

교착 상태는 두 개 이상의 트랜잭션이 서로가 보유한 잠금을 기다리면서 정체 상태가 될 때 발생합니다. MySQL(InnoDB 사용)은 일반적으로 이를 감지하고 하나의 트랜잭션을 롤백하여 자동으로 해결합니다.

진단

**LATEST DETECTED DEADLOCK**을 참조하는 오류 로그 메시지를 확인합니다. InnoDB 상태를 확인할 수도 있습니다:

SHOW ENGINE INNODB STATUS;

TRANSACTIONS 섹션 아래에서 어떤 트랜잭션이 관련되었고 어떤 문이 대기를 유발했는지 보여주는 상세 교착 상태 그래프를 찾습니다.

빠른 수정

트랜잭션 단축: 트랜잭션을 가능한 한 짧게 유지합니다. 신속하게 커밋하거나 롤백합니다.
일관된 액세스 순서: 모든 애플리케이션 코드가 동일한 정의된 순서로 테이블과 행에 액세스하는지 확인합니다. 트랜잭션 A가 테이블 X를 잠근 다음 테이블 Y를 잠그는 경우 트랜잭션 B도 X를 잠근 다음 Y를 잠가야 합니다.
행 수준 잠금 사용: UPDATE 및 DELETE 문에서 적절한 WHERE 절을 사용하여 InnoDB가 전체 테이블이 아닌 필요한 행만 잠글 수 있도록 합니다(InnoDB는 트랜잭션 테이블에 대해 기본적으로 행 수준 잠금을 사용하지만).

3. 복제 지연 또는 실패

소스-복제본 설정에서 복제 지연은 복제본이 소스보다 뒤쳐져 오래된 읽기를 초래할 때 발생합니다. 이전 MySQL 명령 및 필드는 여전히 master 및 slave 용어를 사용하므로 프로덕션에서 두 이름을 모두 볼 수 있습니다.

진단

IO 및 SQL 스레드를 사용하여 복제본의 상태를 확인합니다:

SHOW REPLICA STATUS\G
-- 이전 MySQL 버전: SHOW SLAVE STATUS\G

확인해야 할 주요 필드:

Replica_IO_Running 또는 Slave_IO_Running: Yes여야 합니다.
Replica_SQL_Running 또는 Slave_SQL_Running: Yes여야 합니다.
Seconds_Behind_Source 또는 Seconds_Behind_Master: 지연 시간(초)을 나타냅니다. 이 값이 증가하면 복제본이 뒤쳐지고 있는 것입니다.

빠른 수정

SQL 스레드 오류 해결: SQL 적용자가 중지된 경우 마지막 SQL 오류를 검토합니다. sql_slave_skip_counter 또는 최신 복제 명령으로 이벤트를 건너뛰면 데이터 드리프트가 발생할 수 있으므로 실패한 트랜잭션을 이해하고 데이터를 조정할 계획이 있는 경우에만 사용하십시오.
복제본 리소스 증가: 쓰기 부하가 심한 상태에서 지연이 지속되면 복제본이 바이너리 로그 이벤트를 충분히 빠르게 처리하기 위해 더 많은 CPU 또는 더 빠른 디스크 I/O가 필요할 수 있습니다.
재동기화: 지연이 심각하거나 복제본이 손상된 경우 복제를 중지하고 복제본이 소스의 올바른 바이너리 로그 위치를 가리키는지 확인한 후 다시 시작합니다.

4. 데이터 손상 오류

데이터 손상은 최신 InnoDB 설정에서 드물지만 서버 시작 불가, 체크섬 오류 또는 이상한 쿼리 결과로 나타날 수 있습니다. 손상은 종종 하드웨어 장애(디스크/메모리) 또는 잘못된 종료를 나타냅니다.

진단

손상은 일반적으로 오류 로그의 시작 실패 메시지를 통해 즉시 명백해지며, 종종 테이블스페이스 또는 특정 페이지가 체크섬 테스트에 실패했음을 참조합니다.

빠른 수정

테이블 검사/복구 실행(MyISAM): MyISAM 테이블의 경우 CHECK TABLE table_name; 다음에 REPAIR TABLE table_name;을 사용합니다.
InnoDB 복구 모드: InnoDB가 시작되지 않으면 일시적으로 복구 모드로 실행하여 데이터를 덤프할 수 있습니다:
```
[mysqld]
innodb_force_recovery = 1
```
서버를 시작하고 즉시 mysqldump를 사용하여 모든 중요한 데이터를 덤프한 다음 종료하고 손상된 데이터 파일을 제거한 후 복구 플래그 없이 다시 시작합니다.

경고: innodb_force_recovery는 영구적으로 사용해서는 안 됩니다. 중요한 검사를 우회하며 쓰기가 시도되면 추가 데이터 손상을 초래할 수 있습니다.
백업에서 복원: 심각한 손상에 대한 가장 안전한 해결책은 마지막으로 알려진 양호한 백업에서 전체 데이터베이스를 복원하는 것입니다.

핵심 요점

추측이 아닌 증거를 기반으로 MySQL 문제를 해결하십시오. 오류 로그, 느린 쿼리 로그, EXPLAIN, InnoDB 상태 및 복제 상태는 일반적으로 다음 단계를 보여줍니다. 손상 복구 또는 복제 건너뛰기 명령을 건드리기 전에 백업이 테스트되었는지 확인하십시오.

모범 사례: 사전 예방적 모니터링

가장 빠른 수정은 종종 예방입니다. 포괄적인 모니터링 도구(Prometheus/Grafana, Percona Monitoring and Management(PMM) 또는 클라우드 제공업체 도구)를 구현하여 주요 지표를 관찰하십시오:

연결 수 및 스레드 캐시 적중률.
InnoDB 버퍼 풀 사용량 및 적중률.
복제 지연(Seconds_Behind_Master).
디스크 I/O 사용률.

이러한 지표를 기반으로 한 알림을 통해 느린 쿼리 또는 복제 문제가 심각한 장애로 확대되기 전에 해결할 수 있습니다.