[MySQL] 1812 Error Tablespace is missing for table(이노디비 오류)

InnoDB(이노디비) 사용하면서 테이블이 깨진 경우가 있어 복구한 것에 대한 정리 글입니다.

이노디비 테이블 깨진 경우(1812 Tablespace is missing for table)

InnoDB : 테이블에 대한 테이블 스페이스가 없습니다.
Last_Errno: 1812
Last_Error: Error executing row event: 'Tablespace is missing for table testdb.test.'
참고: https://dba.stackexchange.com/questions/56849/innodb-tablespace-is-missing-for-table

원인

컴퓨터를 강제로 껏을때 발생한 현상. 찾아본 결과다 대략 비슷한 환경에서 문제가 발생 될 수 있다는 것을 알 수 있다.
. 테이블 파일의 소유권/사용 권한이 잘못되었습니다.
. 테이블 파일이 잘못 배치되었습니다.
. 데이터 파일이 손상되었거나 삭제되었습니다.
. 하드 디스크 오류

해결 방법은

• 백업 한것이 있으면 그것으로 복구 하는것이다. 여기서도 알 수 있듯이 백업은 필수 이며 중요 업무중 하나 이다.
  

최근엔 클라우드 환경도 그렇고 대체로 테이블당 파일이 생성 되는 방식으로 설정이 되어 있다
innodb_file_per_table=0 : ibdata1 파일 하나로 데이터 저장

InnoDB 복구방법

아래 참고링크에서 이노디비 복구에 대한 순서를 정리한것을 가져온것입니다.

1. 서비스 종료
2. ibd, frm 파일들을 data 경로에서 다른 경로로 이동(원본쪽 디렉토리에서 파일이 없어야함)
3. 서비스 다시 시작 후 이동한 테이블 삭제된 상태 확인
4. 제거된 테이블 다시 생성 (생성 query)
5. 테이블 스페이스 제거: alter table [테이블명] discard tablespace;
6. 이동된 테이블 파일(ibd, frm)을 다시 data 경로로 복사
7. 테이블 스페이스 복원: alter table [테이블명] import tablespace;
   

Lost connection to MySQL server during query 에러 발생과 함께 서비스가 종료되면 

1. 백업된 DB와 동일한 이름의 DB(gradius) 생성
   
2. 서비스 종료
   
3. 백업된 gradius 데이터 파일(frm, idb)과 ibdata1(data 밑에 있음)을 함께 이관
   
4. 서비스 시작
   서비스 시작 시 DB 자동 복구가 실행된다.
   
   참고: https://yenbook.tistory.com/111
   참고: http://idchowto.com/innodb-%ED%85%8C%EC%9D%B4%EB%B8%94-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%B3%B5%EC%9B%90/
   
   

테이블 깨지는 경우와 이노디비 복구방법

1. InnoDB 테이블이 손상되는 경우는 상당히 희박하다.
2. uble write, Checksum 그리고 기타 Validation 로직들과 버그 보완으로 인해 MYISAM에 비해 INNODB 테이블 스페이스 및 데이터 파일은 상당히 안정적임
3. 대부분의 손상은 인덱스에서 발생한다.
4. NODB 데이터 파일의 손상은 80-90% 정도가 인덱스에 발생한 손상인 경우이며 이 경우 단순히 ALTER TABLE 또는 데이터 덤프파일 덮어쓰기 만으로 해결된다.
5. 이 이외의 INNODB 테이블의 문제인 경우 DB 전체를 덤프 후 다시 로드하는 것으로 해결될 수 있음
6. INNODB는 myisamchk와 같은 별도의 복구 도구를 제공하지 않음

7. InnoDB는 완료되지 못한 트랜잭션, 디스크에 일부만 기록된 데이터 페이지 등에 대한 복구 작업이 자동으로 진행됨

8. 데이터파일이 손상된 경우, MYSQL을 기동시켜서 데이터를 덤프받는 것이 유일한 방법입니다.

9. 서비스 중지
10. MYSQL 설정파일(my.ini)에 아래와 같이 설정 innodb_force_recovery = 1
11. InnoDB는 Boot-up 과정에서 여러 가지 체크 및 정리 작업들을 하게 되는데, 이 중에서 하나라도 문제가 있을 경우 시작이 되지 않기 때문에 위와 같이 설정하면, 데이터 파일의 손상된 페이지가 발견되어도 무시하고 MySQL을 기동시킨다. 일단 MySQL이 기동 되면, SELECT * FROM tbl_name; 명령문을 실행하여 데이터를 덤프 하여 다시 적재하거나 다른 데이터베이스로 이전하는 것이 좋다.
12. 서비스 시작
13. 서비스가 정상 기동되지 않을 시 다른 복구 모드 (1~6) 선택하여 서비스 재 시작 (점차 윗 단계로! 대신 윗 단계로 갈수록 데이터 손실 가능성 늘어남)
14. 데이터베이스 덤프 - MySql설치폴더/bin에서 mysqldump -u 계정 -p 데이터베이스명 > 백업할 파일명.sql
15. 데이터베이스 복원 - MySql설치폴더/bin에서 mysqldump -a -u 계정 -p 데이터베이스명 < 백업한 파일명.sql
16. 복구 모드 삭제 후 서비스 재시작
    

innodb_force_recovery 옵션 값

1. 손상된 페이지가 발견되어도 무시하고 mysql을 가동한다. 가동이 되면 테이블을 덤프하여 복구하거나 다른 데이터베이스로 이전하는 것이 좋다. (손상된 레코드와 페이지는 모두 건너뛰게 되므로 데이터를 잃게 됨)
2. 메인 쓰레드가 구동되지 못하도록 한다. 만일 퍼지 연산 (purge operation)이 진행되는 동안 크래시가 발생한다면, 이 복구 값은 퍼지 연산이 실행되는 것을 막게 된다.
3. mysql종료하던 시점에 진행중인 트랜잭션이 있다면 mysql 단순히 그 연결을 끊는다. 다시 실행 후 innodb엔진이 롤백을 실행하는데 만약 데이터가 손상된 경우 롤백을 실행할 수 없기 때문에 이경우 사용되는 복구 모드이다.
4. INSERT, UPDATE, DELETE 연산자를 실행하지 않도록 한다. 테이블 통계값을 계산하지 않도록 한다.
5. 데이터베이스를 시작할 때 운도 로그 (undo log)를 검사하지 않는다: InnoDB는 완벽하지 않은 트랜잭션도 실행된 것으로 다루게 된다.
6. mysql이 재시작전 가장 뒤에 발생한 체크포인트 이후 모든 트랜잭션을 버리고 복구하는 모드이다 복구 연결에서 로그 롤-포워드 (roll-forward)를 실행하지 않고 강제복구 한다.
   참고: https://yenbook.tistory.com/108

• 
• 
• 
• 
• 
• 
  하단 카페(Cafe)를 선택해 주세요.
• 
• 
• 
•