Development/MySql

[MySQL] 테이블 깨졌을 때 복구하기

곽진돔 2023. 8. 4. 10:57

어느날 DB에 데이터가 없어서 확인하려고 조회를 해봤더니 테이블이 깨져있었다.ː̗̀(ꙨꙨ)ː̖́

산넘어 산 .. 갑자기 왜이럴까요

오류내용

select 시 아래처럼 경고창이 표시되었다.

Table './DB명/테이블명' is marked as crashed and last (automatic?) repair failed

테이블이 깨졌구나(올것이 왔구나)

이제 작업을 하기전에 먼저 mysql의 버전을 다시 한번 확인해 준다.

select version()

 

mysql 버전 확인

확인해보니 5.6.11 버전이다 ㅎ

ANALYZE TABLE 명을 통해 테이블을 조회해본다.

ANALYZE TABLE 명

MySQL이 테이블의 키 분포에 대한 통계를 수집하도록 한다. 이 통계는 MySQL 쿼리 최적화기가 쿼리를 더 효율적으로 실행할 수 있도록 돕는다. 또한, 이 명령은 테이블에 문제가 있는지 확인하는 데도 사용될 수 있다.(내가 필요한 기능)

analyze table [테이블명]

조회하면 아래처럼 테이블 정보를 볼 수 있다.(테이블명은 가렸음)

ANALYZE TABLE 조회 내용

 

테이블 복구하기

REPAIR TABLE 테이블명

REPAIR TABLE 명령은 MyISAM, ARCHIVE, CSV 테이블에 사용할 수 있다. 이 명령은 테이블 또는 테이블의 인덱스가 손상되었을 때 이를 복구하는 데 사용된다.

하지만, InnoDB 테이블의 경우 REPAIR TABLE은 복구 작업을 수행하지 않으며 mysqlcheck 또는 innodb_force_recovery 등 다른 방법을 사용해야 한다.

나의 테이블은 엔진이 MyISAM이므로 계속 진행한다.

 

주의사항

  • REPAIR TABLE은 읽기-쓰기 잠금을 사용하므로 실행 중에는 테이블에 대한 모든 다른 세션이 차단된다. 따라서, 이 작업은 저 트래픽 시간에 수행하는 것이 좋다.
  • REPAIR TABLE로 복구할 수 없는 손상 상태도 있다. 테이블의 데이터 파일이나 인덱스 파일이 물리적으로 삭제된 경우, 데이터 복구가 불가능 할 수 있다.
  • 복구 작업을 수행하기 전에 데이터베이스 백업은 반드시 해야한다. 만약 무엇인가 잘못되면, 복구 작업은 더 많은 손상을 초래할 수 있다.
  • REPAIR TABLE은 ANALYZE TABLE과 OPTIMIZE TABLE을 자동으로 실행하지 않는다. 복구 작업 후 이들 명령을 수동으로 실행하여 테이블 통계를 업데이트하거나 테이블 공간을 회수할 수 있다.

테이블의 엔진이 InnoDB인 경우, REPAIR TABLE을 사용하지 않고 다음과 같은 절차를 따르는 것이 일반적이다:

  1. mysqldump을 사용하여 테이블을 백업
  2. DROP TABLE을 사용하여 테이블을 삭제
  3. mysqldump으로 생성된 백업 파일을 사용하여 테이블을 복원

이렇게 하는 이유는 REPAIR TABLE 명령이 InnoDB 테이블에 대해 동작하지 않기 때문이다. InnoDB 테이블이 손상되었을 경우, InnoDB의 내부 복구 메커니즘을 사용하거나 MySQL 서버를 재시작하여 복구를 시도해 볼 수 있다. 이러한 접근 방법은 데이터를 복구할 수 없을 경우를 대비한 것이며, 데이터를 잃을 위험이 있는 경우에만 시도해야 한다.

반드시, 복구 작업을 시작하기 전에 데이터베이스의 완전한 백업을 만드는 것이 중요하다.

 

[root@localhost ~]# /usr/local/mysql/bin/mysqldump -u root -p[패스워드] [DB명] [테이블명] > [백업파일명].sql
Warning: Using a password on the command line interface can be insecure.
mysqldump: Got error: 144: Table './DB명/테이블명' is marked as crashed and last (automatic?) repair failed when doing LOCK TABLES

백업하려고 했으나..테이블이 깨져서 조회가 되지않기 때문에 dump 파일 생성도 불가하다. ㅎr,,

다행히 REPAIR 가 정상적으로 끝났다. 이전 데이터의 백업이나 자료가 없어서 손실없이 온전히 복구되었는지는 모르겠다.

복구된 데이터를 보니 400만건이 넘어서 정확한 이유는 확인하지 못했지만 테이블이 커지면서 깨진 것 같다.

이전에는 update작업하다가 도중에 강제로 취소하면서 테이블이 깨진적이 있다. 근데 그 테이블도 대용량 데이터인데 실수로 update문에 where 조건을 잘못걸고 실행을 눌러버려서 강제로 종료하려다가 테이블이 깨진 경우였다. (신입시절..)

그 테이블은 데이터가 1000만건이 넘는데다가 용량도 커서(text타입이 있었음) 조회도 좀 힘들었었다.

아무래도 테이블에 데이터가 쌓이면 쌓일수록 문제가 발생할 요인이 커지는 것 같다. 이래나저래나 항상 백업을 생활화 합시다..