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

손상된 MySQL 테이블 복구: 실용적인 접근법

MySQL 테이블 손상 진단, 기존 데이터 보호, InnoDB 및 MyISAM에 대한 안전한 복구 경로 선택

손상된 MySQL 테이블 복구: 실용적인 접근법

테이블 손상은 신속하게 움직이면서도 신중하게 움직여야 하는 문제 중 하나입니다. 잘못된 복구 명령은 복구 가능한 사고를 영구적인 손실로 바꿀 수 있습니다. 첫 번째 목표는 테이블을 "고치는" 것이 아닙니다. 첫 번째 목표는 여전히 가지고 있는 모든 바이트를 보존하고, 손상이 확산되는 것을 막은 후에야 가장 위험이 적은 복구 경로를 선택하는 것입니다.

정확한 절차는 스토리지 엔진에 크게 의존합니다. InnoDB 복구는 일반적으로 서버를 충분히 오래 실행하여 깨끗한 데이터를 덤프하거나 백업에서 복원하는 것입니다. MyISAM 복구는 종종 테이블 복구 도구를 포함합니다. 이를 상호 교환 가능한 명령이 아닌 다른 플레이북으로 취급하십시오.

MySQL 테이블 손상 이해하기

복구에 뛰어들기 전에 테이블 손상이 무엇이며 왜 발생하는지 이해하는 것이 중요합니다. 손상은 테이블 파일 내부 구조나 데이터가 MySQL 서버에서 일관성이 없거나 읽을 수 없게 될 때 발생합니다.

손상의 일반적인 원인

여러 요인이 MySQL 테이블 손상에 기여할 수 있습니다:

  • 하드웨어 오류: 오작동하는 하드 드라이브, 결함 있는 RAM(특히 ECC 메모리 없이), 또는 신뢰할 수 없는 전원 공급 장치(UPS 없이)는 쓰기 중에 데이터가 잘못 기록되거나 손실되게 할 수 있습니다.
  • 운영 체제 문제: OS의 버그, 파일 시스템 오류 또는 커널 패닉은 MySQL이 데이터 파일을 일관되게 읽거나 쓰는 능력을 방해할 수 있습니다.
  • 부적절한 종료: 정상적인 종료 과정 없이 MySQL 서버가 갑자기 종료되면(예: 정전, kill -9 또는 시스템 충돌로 인해) 데이터 파일이 일관성 없는 상태로 남을 수 있습니다.
  • MySQL 버그: 안정적인 릴리스에서는 드물지만, MySQL 서버 자체의 특정 버그가 특정 상황에서 손상을 초래할 수 있습니다.
  • 디스크 공간 문제: 쓰기 작업 중 디스크 공간이 부족하면 불완전한 데이터 파일이 발생할 수 있습니다.
  • 악성코드/바이러스: 데이터베이스 서버에서는 덜 일반적이지만, 악성 소프트웨어가 파일을 손상시킬 수 있습니다.

손상의 증상

손상의 징후를 조기에 인식하면 복구에 크게 도움이 될 수 있습니다. 일반적인 증상은 다음과 같습니다:

  • 오류 메시지: MySQL 서버 로그 또는 클라이언트 애플리케이션에 "Table is marked as crashed and should be repaired", "Can't open file: '.frm'", "Got error N from storage engine" 또는 "Index for table '
    ' is corrupt"와 같은 오류가 표시됩니다.
  • 예상치 못한 쿼리 결과: 쿼리가 데이터를 포함해야 하는 테이블에 대해 잘못된 데이터, 불완전한 결과 또는 전혀 결과를 반환하지 않습니다.
  • 서버 충돌/재시작: 특정 테이블에 액세스하려고 할 때 MySQL 서버가 예기치 않게 충돌합니다.
  • 높은 CPU/I/O 사용량: 명확한 이유 없이 서버가 비정상적으로 높은 리소스 소비를 보이며, 이는 종종 손상된 데이터를 읽으려는 반복적인 실패 시도로 인해 발생합니다.
  • 테이블에 액세스할 수 없음: 테이블을 쿼리, 업데이트 또는 삭제할 수 없을 수 있습니다.

손상된 테이블 감지

신속한 감지는 데이터 손실 및 가동 중지 시간을 최소화하는 데 중요합니다. MySQL은 손상된 테이블을 식별하기 위한 여러 도구와 방법을 제공합니다.

1. MySQL 오류 로그

error.log 파일(위치는 OS에 따라 다름, 예: Linux의 경우 /var/log/mysql/error.log)은 첫 번째 방어선입니다. MySQL은 서버 시작, 종료 및 테이블 손상과 관련된 오류를 포함한 중요한 오류에 대한 자세한 정보를 기록합니다. 정기적으로 이 로그를 검토하십시오.

2. CHECK TABLE 문

CHECK TABLE SQL 문은 하나 이상의 테이블에서 오류를 확인하는 가장 간단한 방법입니다. 각 테이블의 상태를 반환하여 OK인지 Corrupted인지 나타냅니다.

-- 단일 테이블 확인
CHECK TABLE your_database.your_table;

-- 여러 테이블 확인
CHECK TABLE tbl_name1, tbl_name2, tbl_name3;

-- 확장 확인 수행(더 철저하지만 느림)
CHECK TABLE your_database.your_table EXTENDED;

3. mysqlcheck 유틸리티

mysqlcheck는 테이블을 확인, 복구, 최적화 및 분석하는 명령줄 클라이언트입니다. 기본적으로 CHECK TABLE, REPAIR TABLE, ANALYZE TABLE 및 OPTIMIZE TABLE 문을 래핑하여 일괄 작업에 편리합니다.

# 특정 데이터베이스의 모든 테이블 확인
mysqlcheck -u root -p --databases your_database --check

# 모든 데이터베이스의 모든 테이블 확인
mysqlcheck -u root -p --all-databases --check

# 모든 데이터베이스에 대해 확인 및 복구 결합(자동 복구)
mysqlcheck -u root -p --all-databases --check --auto-repair

시작하기 전에: 중요한 준비

복구를 시도하기 전에 다음 중요한 단계를 따라 추가 데이터 손실을 방지하십시오.

1. 즉시 백업! (논리적 및/또는 물리적)

이것은 가장 중요한 단계입니다. 손상이 의심되더라도 복구를 시도하기 전에 백업을 생성하여 대체 수단을 확보하십시오. 서버가 여전히 실행 중이고 영향을 받은 데이터를 읽을 수 있는 경우 mysqldump를 사용한 논리적 백업을 우선시하십시오. 서버가 다운되었거나 불안정한 경우 MySQL이 중지된 상태에서 데이터 디렉토리 또는 영향을 받은 데이터베이스 디렉토리의 물리적 복사본을 만드십시오. 환경이 스냅샷을 사용하는 경우 설정을 변경하기 전에 하나를 만드십시오.

# 예: 데이터베이스의 논리적 백업 생성
mysqldump -u root -p your_database > /path/to/your_database_backup_pre_corruption.sql

2. 영향을 받은 테이블/데이터베이스에 대한 쓰기 중지

복구 프로세스 중 추가 손상을 방지하고 데이터 일관성을 보장하려면 영향을 받은 테이블 또는 전체 데이터베이스에 대한 모든 쓰기 작업을 중단하십시오. 다음 방법으로 이를 달성할 수 있습니다:

  • 데이터베이스와 상호 작용하는 애플리케이션 서버 중지.
  • 데이터베이스를 읽기 전용 모드로 전환(가능한 경우).
  • FLUSH TABLES WITH READ LOCK; 사용(슈퍼 권한 필요, UNLOCK TABLES;가 실행될 때까지 모든 쓰기 차단).
  • 손상이 심각한 경우 MySQL 서버를 완전히 중지.

3. 스토리지 엔진 식별

MySQL은 주로 InnoDB 및 MyISAM의 다양한 스토리지 엔진을 지원합니다. 복구 절차는 엔진 간에 크게 다릅니다. 손상된 테이블의 스토리지 엔진을 확인하십시오:

SHOW CREATE TABLE your_database.your_table;

출력에서 ENGINE= 절을 찾으십시오. ENGINE=InnoDB는 InnoDB 테이블을 나타내고 ENGINE=MyISAM은 MyISAM 테이블을 나타냅니다. InnoDB는 기본값이며 일반적으로 더 강력하고 MyISAM은 더 오래되었고 내결함성이 떨어집니다.

테이블에 액세스할 수 없고 SHOW CREATE TABLE이 실패하면 백업, 배포 마이그레이션 파일 또는 동일한 스키마를 가진 다른 환경에서 메타데이터를 검사하십시오. 추측은 위험합니다. MyISAM용 명령이 InnoDB에 쓸모없거나 위험할 수 있기 때문입니다.

실용적인 분류 체크리스트

무엇이든 복구하기 전에 알고 있는 내용을 기록하십시오:

  1. 어떤 테이블 또는 데이터베이스가 영향을 받았습니까?
  2. MySQL이 실행 중입니까, 충돌 루프 중입니까, 아니면 시작을 거부합니까?
  3. 영향을 받은 테이블이 InnoDB입니까 아니면 MyISAM입니까?
  4. 마지막으로 알려진 양호한 백업은 언제입니까?
  5. 복제본이 정상이며 동일한 손상을 보이고 있습니까?
  6. 애플리케이션을 읽기 전용 모드로 전환할 수 있습니까?

이 체크리스트는 최선의 답변이 "정상 복제본 승격" 또는 "어젯밤 백업 복원"일 수 있고 "프로덕션에서 복구 명령 실행"이 아닐 수 있기 때문에 중요합니다. 복제가 있는 경우 모든 것을 다시 시작하기 전에 복제본을 확인하십시오. 지연된 복제본은 때때로 오래된 백업을 복원하는 것보다 더 나을 수 있지만, 손상 이벤트를 재생하기 전에 중지하는 경우에만 가능합니다.

손상된 테이블 복구: 단계별 접근법

InnoDB 테이블의 경우

InnoDB 테이블은 트랜잭션에 안전하고 충돌에 안전하도록 설계되었습니다. 대부분의 경우 MySQL의 내장 충돌 복구 메커니즘은 다시 시작 시 자동으로 불일치를 처리합니다. 그러나 심각한 손상은 수동 개입이 필요할 수 있습니다.

1. InnoDB의 자동 충돌 복구

서버가 충돌한 경우 MySQL을 다시 시작하면 문제가 해결되는 경우가 많습니다. InnoDB는 자동으로 불완전한 트랜잭션을 롤백하고 데이터 파일을 일관된 상태로 만들려고 시도합니다.

2. innodb_force_recovery 사용(매우 주의해서 사용!)

자동 복구가 실패하고 서버가 시작되지 않거나 테이블에 계속 액세스할 수 없는 경우 innodb_force_recovery를 사용할 수 있습니다. 이 옵션은 손상이 감지되더라도 InnoDB가 시작되도록 강제하여 데이터를 덤프할 수 있게 합니다. 정기적인 작업이 아닌 데이터를 추출하기 위한 최후의 수단으로만 사용해야 합니다. 더 높은 수준은 정상 복구 작업을 건너뛰고 일관성 없는 데이터를 노출할 수 있습니다.

my.cnf(또는 my.ini) 파일을 편집하고 [mysqld] 섹션 아래에 innodb_force_recovery 설정을 추가하거나 수정하십시오. 레벨 1부터 시작하여 필요에 따라 점진적으로 증가시키십시오. 복구 시도 후 이 설정을 제거하는 것을 잊지 마십시오. 레벨은 다음과 같습니다(가장 덜 공격적인 것부터 가장 공격적인 것까지):

  • 1 (SRV_FORCE_IGNORE_CORRUPT): 손상된 페이지를 무시합니다. 테이블에서 SELECT를 허용합니다.
  • 2 (SRV_FORCE_NO_BACKGROUND): 마스터 스레드가 실행되지 않도록 하여 백그라운드 작업을 중지합니다.
  • 3 (SRV_FORCE_NO_TRX_UNDO): 트랜잭션 롤백을 실행하지 않습니다.
  • 4 (SRV_FORCE_NO_IBUF_MERGE): 삽입 버퍼 병합을 방지합니다.
  • 5 (SRV_FORCE_NO_UNDO_LOG_SCAN): 실행 취소 로그를 보지 않습니다. SELECT 문이 실패할 수 있습니다.
  • 6 (SRV_FORCE_NO_LOG_REDO): 리두 로그 롤포워드를 수행하지 않습니다. 데이터 손실 위험이 가장 높습니다.

innodb_force_recovery를 사용한 복구 프로세스:

  1. 다시 백업: 진행하기 전에 가능한 최신 백업이 있는지 확인하십시오.
  2. MySQL 중지: sudo systemctl stop mysql(또는 이와 동등한 명령).
  3. my.cnf 편집: innodb_force_recovery = 1을 추가하십시오.
  4. MySQL 시작: sudo systemctl start mysql.
  5. 데이터 덤프 시도: 서버가 시작되면 즉시 mysqldump로 영향을 받은 데이터베이스/테이블을 덤프하십시오. 하나의 테이블이 실패하면 정상 테이블을 별도로 덤프하여 하나의 잘못된 객체가 전체 구조를 차단하지 않도록 하십시오.
    mysqldump -u root -p your_database > /path/to/your_database_dump_forced.sql
  6. MySQL 중지: sudo systemctl stop mysql.
  7. my.cnf에서 innodb_force_recovery 제거: 이것은 중요합니다.
  8. MySQL 시작: sudo systemctl start mysql.
  9. 손상된 데이터베이스/테이블 삭제: 덤프가 성공하면 문제가 있는 데이터베이스/테이블을 삭제하십시오.
    DROP DATABASE your_database;
  10. 재생성 및 가져오기: 데이터베이스를 재생성하고 덤프 파일에서 데이터를 가져오십시오.
    mysql -u root -p -e "CREATE DATABASE your_database;"
mysql -u root -p your_database < /path/to/your_database_dump_forced.sql

3. 백업에서 복원

최근의 정상 백업이 있는 경우 심각한 InnoDB 손상에 대해 가장 빠르고 안정적인 복구 방법인 경우가 많습니다. 손상된 데이터베이스/테이블을 삭제하고 백업에서 복원하십시오.

가능하면 별도의 인스턴스에 먼저 복원하십시오. 이를 통해 백업을 사용할 수 있는지 확인하고, 애플리케이션 스모크 테스트를 실행하고, 프로덕션 데이터를 교체하기 전에 행 수를 비교할 수 있습니다. 존재하지만 복원된 적이 없는 백업은 여전히 가정일 뿐입니다.

MyISAM 테이블의 경우

MyISAM 테이블은 더 간단하지만 트랜잭션이 아니므로 부적절한 종료로 인한 손상에 더 취약합니다. 복구에는 일반적으로 복구 유틸리티 사용이 포함됩니다.

1. REPAIR TABLE 문

REPAIR TABLE 문은 손상된 MyISAM 테이블을 수정하려고 시도합니다. 테이블 파일을 백업한 후에만 사용하십시오. 복구는 손상 및 복구 모드에 따라 인덱스를 재구성하거나 손상된 행을 폐기할 수 있습니다.

-- 표준 복구
REPAIR TABLE your_database.your_table;

-- 빠른 복구(덜 철저함, 더 빠름)
REPAIR TABLE your_table QUICK;

-- 확장 복구(더 철저함, 더 느림, 인덱스 재구성 가능)
REPAIR TABLE your_table EXTENDED;

2. mysqlcheck 유틸리티(복구 옵션 사용)

앞서 언급했듯이 mysqlcheck는 복구도 수행할 수 있습니다. 이는 여러 테이블 또는 데이터베이스를 일괄 복구하는 데 유용합니다.

# 특정 데이터베이스의 모든 테이블 복구
mysqlcheck -u root -p --databases your_database --repair

# 모든 데이터베이스의 모든 테이블 복구
mysqlcheck -u root -p --all-databases --repair

3. myisamchk 유틸리티(명령줄)

myisamchk는 MyISAM 테이블을 직접 확인하고 복구하기 위한 저수준 명령줄 유틸리티입니다. 물리적 .MYI(인덱스) 및 .MYD(데이터) 파일에서 작동합니다. 중요: 추가 손상이나 파일 충돌을 방지하려면 myisamchk를 사용할 때 MySQL 서버가 중지되어 있어야 합니다.

myisamchk를 사용한 복구 프로세스:

  1. 백업! your_table.frm, your_table.MYI 및 your_table.MYD 파일을 안전한 위치에 복사하십시오.
  2. MySQL 중지: sudo systemctl stop mysql(또는 sudo service mysql stop).
  3. 데이터 디렉토리로 이동: 데이터베이스 파일이 저장된 디렉토리로 변경하십시오(예: /var/lib/mysql/your_database_name).
    cd /var/lib/mysql/your_database_name
  4. 테이블 확인:
    myisamchk your_table.MYI
    그러면 테이블 상태에 대한 정보가 출력됩니다.
  5. 테이블 복구:
    • 안전한 복구: myisamchk -r your_table.MYI(손상된 행을 롤백, 더 안전함)
    • 적극적인 복구: myisamchk -o your_table.MYI 또는 myisamchk -f your_table.MYI(인덱스 재구성 시도, 일부 데이터 손실 가능; -r이 실패하면 사용)
    • 매우 적극적인 복구: myisamchk -r -f your_table.MYI(재구성 및 강제 결합)
  6. MySQL 다시 시작: sudo systemctl start mysql(또는 sudo service mysql start).

MyISAM 복구 후에는 애플리케이션 수준 검사를 실행하십시오. 테이블이 구조적으로 복구되었지만 비즈니스에 중요한 행이 여전히 누락될 수 있습니다. 예를 들어, 주문 테이블이 CHECK TABLE을 통과할 수 있지만 결제 기록, 로그 또는 백업과의 조정이 필요한 간격이 여전히 있을 수 있습니다.

향후 손상 방지

복구 방법을 아는 것이 필수적이지만, 처음부터 손상을 방지하는 것이 항상 최선의 전략입니다. 다음 모범 사례를 구현하십시오:

  • 정기적이고 검증된 백업: 강력한 백업 전략(논리적 및 물리적 모두)을 구현하고 정기적으로 백업을 테스트하여 복원 가능한지 확인하십시오.
  • 정상 종료: systemctl stop mysql, mysqladmin shutdown 또는 서비스 관리자를 사용하여 항상 MySQL을 정상적으로 종료하십시오. kill -9를 피하십시오.
  • 견고한 하드웨어: ECC RAM(오류 수정 코드 메모리) 및 디스크 중복을 위한 RAID 구성을 포함한 안정적인 하드웨어에 투자하십시오. UPS(무정전 전원 공급 장치)를 사용하여 정전으로부터 보호하십시오.
  • 시스템 리소스 모니터링: 디스크 공간, I/O 성능, CPU 사용량 및 메모리를 주시하십시오. 리소스 고갈은 예기치 않은 문제로 이어질 수 있습니다.
  • InnoDB 사용(기본 및 권장): InnoDB는 트랜잭션에 안전하며 MyISAM에 비해 우수한 충돌 복구 기능을 제공합니다. 새 테이블의 기본 선택이어야 합니다.
  • MySQL 업데이트 유지: 최신 MySQL 버전을 유지하고 보안 패치 및 버그 수정을 신속하게 적용하십시오. 최신 버전에는 안정성 및 데이터 무결성 개선 사항이 포함되는 경우가 많습니다.
  • 오류 로그 정기적으로 검토: MySQL 오류 로그를 확인하여 경고 신호가 본격적인 손상으로 확대되기 전에 포착하는 습관을 들이십시오.
  • 파일 시스템 및 OS 모범 사례: 강력한 파일 시스템(예: ext4, XFS)을 사용하고 운영 체제가 잘 유지 관리되도록 하십시오.

"복구됨"의 의미

"서버가 시작된다"는 것에서 멈추지 마십시오. 복구된 데이터베이스는 몇 가지 실용적인 검사를 통과해야 합니다:

  • CHECK TABLE 또는 엔진에 적합한 검증이 깨끗한 결과를 반환합니다.
  • 애플리케이션 읽기 및 쓰기 스모크 테스트가 통과합니다.
  • 중요한 테이블의 행 수가 예상 또는 알려진 백업 수와 일치합니다.
  • 오류 로그에 더 이상 반복적인 스토리지 엔진 오류가 표시되지 않습니다.
  • 백업이 재개되었고 최소한 하나의 새로운 복원 테스트가 성공했습니다.

MySQL 테이블 손상은 심각하지만, 증거를 보존하고, 쓰기를 중지하고, 엔진을 식별하고, 대체 수단이 확보될 때까지 공격적인 복구 명령을 피하면 복구 경로는 관리할 수 있습니다. 많은 사고에서 가장 안전한 수정은 검증된 복원입니다. innodb_force_recovery, REPAIR TABLE 또는 myisamchk와 같은 구조 도구가 필요한 경우 정기적인 유지 관리가 아닌 추출 및 통제된 복구에 사용하십시오.