[MySQL] CHECK TABLE & Repair TABLE

■ CHECK TABLE 명령

CHECK TABLE tbl_name [, tbl_name] ... [option] ...

option: {
    FOR UPGRADE
  | QUICK
  | FAST
  | MEDIUM
  | EXTENDED
  | CHANGED
}

CHECK TABLE은 테이블에 오류가 있는지 확인합니다. MyISAM 테이블의 경우 주요 통계도 업데이트됩니다. CHECK TABLE은 뷰 정의에서 더 이상 존재하지 않는 테이블을 참조하는 문제같은 것들을 확인할 수도 있습니다.

 

테이블을 확인하려면 테이블에 대한 권한이 있어야합니다.

 

CHECK TABLE은 InnoDB, MyISAM, ARCHIVE 및 CSV 테이블에서 작동합니다.

 

InnoDB 테이블에서 CHECK TABLE을 실행하기 전에 InnoDB 테이블에 대한 CHECK TABLE 사용 정보를 참조하십시오.

 

CHECK TABLE은 파티션된 테이블에 대해 지원되며 ALTER TABLE ... CHECK PARTITION을 사용하여 하나 이상의 파티션을 점검 할 수 있습니다.

 

CHECK TABLE은 인덱싱되지 않은 가상 생성 컬럼을 무시합니다.

+ CHECK TABLE 출력

+ 버전 호환성 확인

+ 데이터 일관성 확인

+ InnoDB 테이블에 대한 CHECK TABLE 사용시 참고 사항

+ MyISAM 테이블의 CHECK TABLE 사용시 참고 사항

 

▶︎ CHECK TABLE 출력

CHECK TABLE은 다음 표에 표시된 컬럼이 있는 결과를 가져옵니다.

컬럼	값
Table	테이블 이름
Op	항상 검사
Msg_type	status(상태), error(에러), info(정보), note(참고사항), warning(경고)
Msg_text	정보성 메세지

명령문은 점검된 각 테이블에 대해 많은 정보 행을 생성할 수 있습니다. 마지막 행에는 status의 Msg_type 값이 있으며 일반적으로 Msg_text는 정상입니다. MyISAM 테이블의 경우 OK메세지나 테이블의 최신 상태를 얻지 못하면 일반적으로 테이블 복구를 실행해야합니다. 테이블이 이미 최신 상태인 경우 테이블의 스토리지 엔진이 테이블을 확인할 필요가 없음을 나타냅니다.

 

▶︎ 버전 호환성 확인

FOR UPGRADE 옵션은 사용자가 만든 테이블이 현재 버전의 MySQL과 호환되는지 확인합니다. FOR UPGRADE를 사용하면 서버는 각 테이블을 검사하여 테이블을 만든 이후 테이블의 데이터 형식이나 인덱스에 호환되지 않는 변경 사항이 있는지 확인합니다. 특별한 메세지가 출력되지 않는다면 검사가 성공한 것입니다. 그렇지 않으면, 비 호환 가능성이있는 경우 서버는 테이블에서 전체 검사를 실행합니다 (시간이 오래 걸릴 수 있습니다). 전체 검사에 성공하면 서버는 테이블의 .frm 파일을 현재 MySQL 버전 번호로 표시합니다. .frm 파일을 표시하면 동일한 버전의 서버로 테이블을 추가로 확인하는 것이 빠릅니다.

 

데이터 유형의 스토리지 형식이 변경되었거나 정렬 순서가 변경되어 비 호환성이 발생할 수 있습니다. 우리의 목표는 이러한 변화를 피하는 것이지만 때로는 릴리스간 비 호환성보다 더 나쁜 문제를 해결하기 위함입니다.

 

FOR UPGRADE는 다음과 같은 비 호환성을 찾아냅니다.

+ InnoDB 및 MyISAM 테이블에 대한 TEXT컬럼의 끝 공간에 대한 인덱싱 순서는 MySQL 4.1과 5.0 사이에서 변경되었습니다.

+ 새로운 DECIMAL 데이터 타입의 저장 방법은 MySQL 5.0.3과 5.0.5 사이에서 변경되었습니다.

+ 테이블이 현재 실행중인 것과 다른 버전의 MySQL 서버에 의해 생성된 경우 FOR UPGRADE는 테이블에 호환되지 않는 버전의 .frm 파일이 있음을 나타냅니다. 이 경우 CHECK TABLE에 의해 반환된 결과 집합에는 Msg_type 값이 error이고 Msg_text 값이 Table upgrade인 행이 포함됩니다. 수정하려면 "REPAIR TABLE`tbl_name`"을 수행합니다,

+ 테이블 인덱스를 다시 작성해야하는 문자 집합이나 데이터 정렬이 변경되는 경우가 있습니다.

+ YEAR(2) 데이터 유형은 더 이상 사용되지 않으며 MySQL 5.7.5에서 지원되지 않습니다. YEAR(2) 컬럼이 포함된 테이블의 경우 CHECK TABLE은 2자리 YEAR(2)컬럼을 4자리 YEAR컬럼으로 변환하는 REPAIR TABLE을 권장합니다.

+ MySQL 5.7.2부터 트리거 생성 시간이 유지됩니다. 트리거가 있는 테이블에 대해 실행하면 CHECK TABLE ... FOR UPGRADE는 MySQL 5.7.2 이전에 생성된 각 트리거에 대해 이 경고를 표시합니다.

Trigger db_name.tbl_name.trigger_name does not have CREATED attribute.

경고는 정보 제공용입니다. 트리거는 변경되지 않습니다.

+ MySQL 5.7.7부터, 테이블에 5.6.4 이전 형식의 오래된 시간컬럼(부분 초의 정밀도를 지원하지 않는 TIME, DATETIME 및 TIMESTAMP 컬럼)이 포함되어 있고 avoid_temporal_upgrade 시스템 변수가 비활성화 되어 있는 경우 테이블을 다시 작성해야한다고 보고됩니다. 이를 통해 MySQL 업그레이드 절차에서 오래된 시간 컬럼이 포함된 테이블을 감지하고 업그레이드 할 수 있습니다. 

 

avoid_temporal_upgrade가 사용 가능한 경우 FOR UPGRADE는 테이블에있는 이전 시간 컬럼을 무시합니다. 결과적으로 업그레이드 절차는 업그레이드 절차를 수행하지 않습니다.

+ 이러한 임시 컬럼을 포함하고 재 빌드가 필요한 테이블을 확인하려면 CHECK TABLE ... FOR UPGRADE를 실행하기 전에 avoid_temporal_upgrade를 사용 안함으로 설정합니다.

+ 비 네이티브 파티셔닝은 MySQL 5.7에서 더 이상 사용되지 않고 MySQL 8.0에서 제거되므로 비 네이티브 파티셔닝을 사용하는 테이블에 대해 경고가 발행됩니다.

 

▶︎ 데이터 일관성 검사

다음 표는 다른 점검 옵션을 보여줍니다. 이러한 옵션은 스토리지 엔진으로 전달되며이를 사용하거나 무시할 수 있습니다.

 

형식	의미
QUICK	잘못된 링크를 확인하기 위해 행을 스캔하지 마십시오. InnoDB 및 MyISAM 테이블 및보기에 적용됩니다.
FAST	제대로 닫히지 않은 테이블 만 확인하십시오. InnoDB에서는 무시됩니다. MyISAM 테이블 및보기에만 적용됩니다.
CHANGED	마지막 확인 이후 변경되었거나 제대로 닫히지 않은 테이블 만 확인하십시오. InnoDB에서는 무시됩니다. MyISAM 테이블 및보기에만 적용됩니다.
MEDIUM	행을 스캔하여 삭제 된 링크가 유효한지 확인하십시오. 또한 행에 대한 키 체크섬을 계산하고 키에 대해 계산 된 체크섬으로이를 확인합니다. InnoDB에서는 무시됩니다. MyISAM 테이블 및보기에만 적용됩니다.
EXTENDED	각 행의 모든 키에 대해 전체 키 조회를 수행합니다. 이렇게하면 테이블이 100 % 일관되지만 시간이 오래 걸립니다. InnoDB에서는 무시됩니다. MyISAM 테이블 및보기에만 적용됩니다.

 

QUICK, MEDIUM 또는 EXTENDED 옵션이 지정되지 않은 경우 동적 형식 MyISAM 테이블의 기본 검사 유형은 MEDIUM입니다. 이것은 테이블에서 myisamchk --medium-check tbl_name을 실행하는 것과 동일한 결과를 갖습니다. CHANGED 또는 FAST가 지정되지 않은 경우 기본 형식은 정적 형식 스토리지인 MyISAM 테이블의 경우 MEDIUM입니다. 이 경우 기본값은 QUICK입니다. 행이 거의 손상되지 않으므로 CHANGED 및 FAST에 대한 행 스캔을 건너 뜁니다.

 

다음 예와 같이 테이블을 빠르게 확인하여 테이블이 올바르게 닫혔는지 확인하는 확인 옵션을 결합 할 수 있습니다.

mysql> CHECK TABLE test_table FAST QUICK;

 

참고사항

CHECK TABLE에서 "손상됨"또는 "올바로 닫히지 않은"것으로 표시된 테이블에서 문제가 발견되지 않으면 CHECK TABLE에서 마크를 제거 할 수 있습니다.

 

테이블이 손상된 경우 데이터 부분이 아닌 인덱스에 문제가 있을 가능성이 큽니다. 위의 모든 검사 유형은 인덱스를 철저히 검사하므로 대부분의 오류를 찾아야 합니다.

 

괜찮다고 가정하는 테이블을 점검하려면 점검 옵션이 없거나 QUICK 옵션을 사용합니다. 후자는 급한 상태에서 사용해야 하며 QUICK이 데이터 파일에서 오류를 찾지 못하는 매우 작은 위험을 감수할 수 있습니다. (대부분의 경우 정상적인 사용시 MySQL은 데이터 파일에서 오류를 찾아야 합니다. 이 경우 테이블이 "손상됨"으로 표시되어 복구 될 때까지 사용할 수 없습니다.)

 

FAST 및 CHANGED는 주로 스크립트 (예 : cron에서 실행)에서 테이블을 주기적으로 확인하는데 사용됩니다. 대부분의 경우 FAST가 CHANGED보다 선호됩니다. 선호하지 않는 유일한 경우는 MyISAM 코드에서 버그를 발견한 것으로 의심되는 경우입니다.

 

EXTENDED는 일반 검사를 실행한 후에만 ​​사용되지만 MySQL이 행을 업데이트하거나 키로 행을 찾으려고 할 때 테이블에서 오류가 발생합니다. 정상 점검에 성공한 경우에는 거의 불가능합니다.

 

CHECK TABLE ... EXTENDED를 사용하면 쿼리 최적화 프로그램에서 생성 한 실행 계획에 영향을 줄 수 있습니다.

 

CHECK TABLE에 의해 보고된 일부 문제점은 자동으로 정정될 수 없습니다.

+ Found row where the auto_increment column has the value 0.(auto_increment 컬럼의 값이 0인 행을 찾았습니다.

이는 테이블에 AUTO_INCREMENT 인덱스 컬럼에 값 0이 포함된 행이 있음을 의미합니다. UPDATE 문으로 열을 0으로 명시적으로 설정하여 AUTO_INCREMENT 컬럼이 0인 행을 작성할 수 있습니다.

 

이것은 그 자체로는 오류가 아니지만 테이블을 덤프하여 복원하거나 테이블에서 ALTER TABLE을 수행하면 문제가 발생할 수 있습니다. 이 경우 AUTO_INCREMENT 열은 AUTO_INCREMENT컬럼 규칙에 따라 값을 변경하므로 중복키 오류와 같은 문제가 발생할 수 있습니다.

 

경고를 제거하려면 UPDATE 문을 실행하여 컬럼을 0이외의 값으로 설정합니다.

 

이것은 그 자체로는 오류가 아니지만 테이블을 덤프하여 복원하거나 테이블에서 ALTER TABLE을 수행하면 문제가 발생할 수 있습니다. 이 경우 AUTO_INCREMENT 열은 AUTO_INCREMENT 컬럼 규칙에 따라 값을 변경하므로 중복키 오류와 같은 문제가 발생할 수 있습니다.

 

경고를 제거하려면 UPDATE 문을 실행하여 컬럼을 0이외의 값으로 설정합니다.

 

▶︎ InnoDB 테이블의 CHECK TABLE 사용법 메모

다음 참고 사항은 InnoDB 테이블에 적용됩니다.

+ CHECK TABLE이 손상된 페이지를 발견하면 오류 전파를 막기 위해 서버가 종료됩니다. 2차 인덱스에서 손상이 발생했지만 테이블 데이터를 읽을 수 있는 경우 CHECK TABLE을 실행하면 여전히 서버가 종료 될 수 있습니다.

+ CHECK TABLE이 클러스터형 인덱스에서 손상된 DB_TRX_ID 또는 DB_ROLL_PTR 필드를 발견하면 CHECK TABLE로 인해 InnoDB가 잘못된 undo(실행 취소) 로그 레코드에 액세스하여 MVCC관련 서버가 종료 될 수 있습니다.

+ CHECK TABLE에서 InnoDB 테이블 또는 인덱스에 오류가 발생하면 해당 오류를 보고합니다. 일반적조치로 인덱스에 오류를 표시하고, 테이블을 손상된 것으로 표시하여 인덱스 또는 테이블을 더 이상 사용할 수 없게 합니다. 이러한 오류에는 보조 인덱스의 잘못된 항목수 또는 잘못된 링크가 포함됩니다.

+ CHECK TABLE이 보조 인덱스에서 잘못된 수의 항목을 발견하면 오류를보고하지만 서버를 종료하지 않거나 파일에 대한 액세스를 차단하지 않습니다.

+ CHECK TABLE은 인덱스 페이지 구조를 조사한 다음 각 키 항목을 조사합니다. 클러스터된 레코드에 대한 키 포인터의 유효성을 검증하거나 BLOB 포인터의 경로를 따르지 않습니다.

+ InnoDB 테이블이 자체 .ibd 파일에 저장된 경우 .ibd 파일의 처음 3페이지에는 테이블 또는 인덱스 데이터가 아닌 헤더 정보가 포함됩니다. CHECK TABLE 문은 헤더 데이터에만 영향을 주는 불일치를 발견하지 않습니다. InnoDB .ibd 파일의 전체 내용을 확인하려면 innochecksum 명령을 사용합니다.

+ 큰 InnoDB 테이블에서 CHECK TABLE을 실행할 때 CHECK TABLE 실행 중에 다른 스레드가 차단 될 수 있습니다. 시간 종료를 피하기 위해 CHECK TABLE 조작의 경우 세마포어 대기 임계 값 (600 초)이 2시간 (7200 초) 연장됩니다. InnoDB가 240초 이상의 세마포어 대기를 감지하면 InnoDB모니터 출력을 오류 로그에 인쇄하기 시작합니다. 잠금 요청이 세마포어 대기 임계 값을 초과하면 InnoDB가 프로세스를 중단합니다. 세마포어 대기 시간 종료가 완전히 발생하지 않도록하려면 CHECK TABLE 대신 CHECK TABLE QUICK을 실행합니다.

+ InnoDB SPATIAL 인덱스의 CHECK TABLE 기능에는 R-트리 유효성 검사 및 R-트리 행 수가 클러스터 된 인덱스와 일치하는지 확인하는 검사가 포함됩니다.

+ CHECK TABLE은 InnoDB에서 지원하는 가상 생성 열에서 보조 인덱스를 지원합니다.

 

▶︎ MyISAM 테이블의 CHECK TABLE 사용법 메모

다음 참고 사항은 MyISAM 테이블에 적용됩니다.

+ CHECK TABLE은 MyISAM 테이블에 대한 주요 통계를 업데이트 합니다.

+ CHECK TABLE 출력이 OK를 반환하지 않거나 Table이 이미 최신 상태를 가져오지 못하는 경우 일반적으로 테이블 복구를 실행해야합니다.

+ CHECK TABLE 옵션 QUICK, MEDIUM 또는 EXTENDED가 지정되지 않은 경우 동적 형식 MyISAM 테이블의 기본 검사 유형은 MEDIUM입니다. 이것은 테이블에서 myisamchk --medium-check tbl_name을 실행하는 것과 동일한 결과를 갖습니다. CHANGED 또는 FAST가 지정되지 않은 경우 기본 형식은 정적 형식 MyISAM 테이블의 경우 MEDIUM입니다. 이 경우 기본값은 QUICK입니다. 행이 거의 손상되지 않으므로 CHANGED 및 FAST에 대한 행 스캔을 건너 뜁니다.

 

■ REPAIR TABLE 명령

REPAIR [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...
    [QUICK] [EXTENDED] [USE_FRM]

REPAIR TABLE은 특정 스토리지 엔진에 대해서만 손상된 테이블을 복구합니다.

 

이 명령문에는 테이블에 대한 SELECT 및 INSERT 권한이 필요합니다.

 

일반적으로 REPAIR TABLE을 실행할 필요는 없지만 재해가 발생하면 이 명령문은 MyISAM 테이블에서 모든 데이터를 다시 가져올 가능성이 높습니다. 테이블이 자주 손상된 경우 REPAIR TABLE을 사용할 필요가 없도록 이유를 찾아야 합니다.

 

REPAIR TABLE은 업그레이드가 필요한지 테이블을 확인합니다. 그렇다면 업그레이드 확인 테이블과 동일한 규칙에 따라 업그레이드를 수행합니다.

 

중요사항

테이블 복구 조작을 수행하기 전에 테이블을 백업합니다. 경우에 따라 작업으로 인해 데이터가 손실 될 수 있습니다. 가능한 원인은 파일 시스템 오류를 포함하지만 이에 국한되지는 않습니다.

 

REPAIR TABLE 조작중 서버가 충돌하는 경우, 서버를 재시작한 후 다른 조작을 수행하기 전에 테이블에 대해 다른 REPAIR TABLE문을 즉시 실행해야 합니다. 최악의 경우 데이터 파일에 대한 정보가 없는 새로운 인덱스 파일이 새로 생성된 후 다음에 수행할 작업에서 데이터 파일을 덮어 쓸 수 있습니다. 이는 백업할만큼의 노력이 필요하지는 않지만 가능한 시나리오입니다.

 

소스의 테이블이 손상되어 REPAIR TABLE을 실행하면 원래 테이블에 대한 모든 변경 사항이 복제본으로 전파되지 않습니다.

+ REPAIR TABLE 스토리지 엔진 및 파티셔닝 지원

+ REPAIR TABLE 옵션

+ REPAIR TABLE 출력

+ REPAIR TABLE 고려 사항

 

▶︎ 복구 테이블 스토리지 엔진 및 파티셔닝 지원

REPAIR TABLE은 MyISAM, ARCHIVE 및 CSV 테이블에서 작동합니다. MyISAM 테이블의 경우 기본적으로 myisamchk --recover tbl_name과 동일한 효과가 있습니다. 이 문장은 뷰에서는 작동하지 않습니다.

 

REPAIR TABLE은 파티션된 테이블에 지원됩니다. 그러나 USE_FRM 옵션은 파티션된 테이블에서 이 명령문과 함께 사용할 수 없습니다.

 

ALTER TABLE ... REPAIR PARTITION을 사용하여 하나 이상의 파티션을 복구할 수 있습니다.

 

▶︎ REPAIR TABLE 옵션

+ NO_WRITE_TO_BINLOG 또는 LOCAL

기본적으로 서버는 REPAIR TABLE 문을 바이너리 로그에 작성하여 복제본에 복제합니다. 로깅을 억제하려면 선택적 NO_WRITE_TO_BINLOG 키워드 또는 별명 LOCAL을 지정합니다.

 

+ QUICK

QUICK 옵션을 사용하면 REPAIR TABLE은 데이터 파일이 아닌 인덱스 파일만 복구하려고 합니다. 이러한 유형의 수리는 myisamchk --recover --quick에 의해 수행되는 것과 같습니다.

 

+ EXTENDED

EXTENDED 옵션을 사용하면 MySQL은 정렬을 통해 한번에 하나의 인덱스를 생성하는 대신 행별로 인덱스를 생성합니다. 이러한 유형의 수리는 myisamchk --safe-recover에 의해 수행되는 것과 같습니다.

 

+ USE_FRM

.MYI 색인 파일이 없거나 헤더가 손상된 경우 USE_FRM 옵션을 사용할 수 있습니다. 이 옵션은 MySQL에게 .MYI 파일 헤더의 정보를 신뢰하지 않고 .frm 파일의 정보를 사용하여 정보를 재 작성하도록 지시합니다. 이러한 종류의 수리는 myisamchk로 수행 할 수 없습니다.

 

주의

일반 REPAIR 모드를 사용할 수 없는 경우에만 USE_FRM 옵션을 사용합니다. .MYI 파일을 무시하도록 서버에 지시하면 .MYI에 저장된 중요한 테이블 메타 데이터를 복구 프로세스에서 사용할 수 없게되며 이는 심각한 결과를 초래할 수 있습니다.

+ 현재 AUTO_INCREMENT 값이 손실됩니다.

+ 테이블에서 삭제된 레코드에 대한 링크가 손실되므로 삭제된 레코드의 여유 공간이 이후에도 비어있게 됩니다.

+ .MYI 헤더는 테이블의 압축 여부를 나타냅니다. 서버가 이 정보를 무시하면 테이블이 압축되었다고 알 수 없으므로 복구로 인해 테이블 ​​내용이 변경되거나 유실될 수 있습니다. 이는 USE_FRM을 압축 테이블과 함께 사용해서는 안됨을 의미합니다. 압축 테이블은 읽기 전용이므로 손상되지 않아야 합니다.

현재 실행중인 것과 다른 버전의 MySQL 서버에서 작성된 테이블에 USE_FRM을 사용하는 경우 REPAIR TABLE은 테이블 복구를 시도하지 않습니다. 이 경우 REPAIR TABLE에 의해 반환된 결과 집합에는 Msg_type 값이 error이고 Msg_text 값이 Failed repairing incompatible .FRM 파일이 있는 행이 포함됩니다.

USE_FRM이 사용되는 경우 REPAIR TABLE은 업그레이드가 필요한지 여부를 확인하기 위해 테이블을 확인하지 않습니다.

 

▶︎ REPAIR TABLE 출력

REPAIR TABLE은 다음 표에 표시된 컬럼이 있는 결과 집합을 반환합니다.

컬럼	값
Table	테이블 이름
Op	항상 수리
Msg_type	status(상태), error(에러), info(정보), note(참고사항), warning(경고)
Msg_text	정보성 메세지

REPAIR TABLE문은 복구된 각 테이블에 대해 많은 정보 행을 생성할 수 있습니다. 마지막 행에는 status의 Msg_type 값이 있으며 일반적으로 Msg_test는 정상이어야 합니다. MyISAM 테이블의 경우, 확인이되지 않으면 myisamchk --safe-recover를 사용하여 복구를 시도해야 합니다. REPAIR TABLE은 myisamchk의 모든 옵션을 구현하지는 않습니다. myisamchk --safe-recover를 사용하면 --max-record-length와 같이 REPAIR TABLE이 지원하지 않는 옵션을 사용할 수도 있습니다.

 

REPAIR TABLE테이블은 기존의 손상된 파일에서 새로 작성된 파일로 테이블 통계를 복사하는 동안 발생하는 오류를 포착하고 발생시킵니다. 예를 들어 .frm, .MYD 또는 .MYI 파일 소유자의 사용자 ID가 mysqld 프로세스의 사용자 ID와 다른 경우 루트사용자로 mysqld를 시작하지 않으면 REPAIR TABLE에서 "cannot change ownership of the file(파일의 소유권을 변경할 수 없습니다)"오류가 발생합니다.

 

▶︎ 테이블 복구 고려 사항

REPAIR TABLE은 5.6.4 이전 형식의 오래된 시간 컬럼(정수 초 정밀도를 지원하지 않는 TIME, DATETIME 및 TIMESTAMP 컬럼)을 포함하고 avoid_temporal_upgrade 시스템 변수가 비활성화된 경우 테이블을 업그레이드합니다. avoid_temporal_upgrade가 사용 가능한 경우 REPAIR TABLE은 테이블에  있는 기존 시간 컬럼을 무시하고 업그레이드하지 않습니다.

이러한 임시 컬럼이 포함된 테이블을 업그레이드하려면 REPAIR TABLE을 실행하기 전에 avoid_temporal_upgrade를 비활성화해야 합니다.

특정 시스템 변수를 설정하여 REPAIR TABLE 성능을 향상시킬 수 있습니다. 다음 포스팅을 참고 부탁드립니다.

myinfrabox.tistory.com/146

[MySQL] REPAIR TABLE 최적화 문법