[MySQL][InnoDB] 문제해결

■ InnoDB 문제 해결

다음 지침은 InnoDB 문제 해결에 적용됩니다.

+ 작업이 실패하거나 버그가 의심되면 MySQL 서버 오류 로그를 확인합니다.

 

+ 장애가 교착 상태와 관련된 경우, 각 교착 상태에 대한 세부 사항이 MySQL 서버 오류 로그에 인쇄되도록 innodb_print_all_deadlocks 옵션을 사용하여 실행합니다.

 

+ InnoDB 데이터 딕셔너리와 관련된 문제에는 실패한 CREATE TABLE 문(고아 테이블 파일: orphan table files), “InnoDB 파일을 열 수 없음” 및 “시스템이 경로 지정 오류를 찾을 수 없음”이 포함됩니다.

 

+ 문제 해결시 일반적으로 mysqld_safe 또는 Windows 서비스가 아닌 명령 프롬프트에서 MySQL 서버를 실행하는 것이 가장 좋습니다. 그러면 mysqld가 콘솔에 인쇄하는 것을 볼 수 있으므로 진행 상황을 더 잘 파악할 수 있습니다. Windows에서 --console 옵션으로 mysqld를 시작하여 출력을 콘솔 창으로 보냅니다.

 

+ 문제에 대한 정보를 얻으려면 InnoDB 모니터를 활성화합니다. 문제가 성능 관련이거나 서버가 정지 된 것처럼 보이면 표준 모니터가 InnoDB의 내부 상태에 대한 정보를 인쇄 할 수 있도록 설정해야합니다. 잠금에 문제가있는 경우 잠금 모니터를 활성화합니다. 문제가 테이블 생성, 테이블 스페이스 또는 데이터 사전 작업에 문제가 있는 경우 InnoDB 정보 스키마 시스템 테이블을 참조하여 InnoDB 내부 데이터 사전의 내용을 검사합니다.

https://myinfrabox.tistory.com/70?category=814732

[MySQL][InnoDB] 모니터

 

InnoDB는 다음 조건에서 표준 InnoDB 모니터 출력을 일시적으로 활성화합니다.

1. 긴 세마포어 대기

2. InnoDB가 버퍼 풀에서 사용 가능한 블록을 찾을 수 없는경우.

3. 버퍼 풀의 67 % 이상이 잠금 힙 또는 적응형 해시 인덱스가 차지하는경우.

 

+ 테이블이 손상되었다고 의심되면 해당 테이블에서 CHECK TABLE을 실행합니다.

 

■ InnoDB I/O 문제 해결

InnoDB I/O 문제에 대한 문제 해결 단계는 MySQL 서버를 시작하는 동안 또는 파일 시스템 수준에서 발생한 문제로 인해 DML 또는 DDL 문이 실패할때 등 문제가 발생하는 시간에 따라 달라집니다.

 

▶︎ 초기화 문제

InnoDB가 테이블 스페이스 또는 로그 파일을 초기화하려고 시도할 때 문제가 발생하면 InnoDB가 생성 한 모든 파일 (모든 ibdata 파일 및 모든 ib_logfile 파일)을 삭제합니다. 일부 InnoDB 테이블을 이미 생성 한 경우 이러한 테이블에 해당하는 .frm 파일과 여러 테이블 스페이스를 사용하는 경우 .ibd 파일을 MySQL 데이터베이스 디렉토리에서 삭제합니다. 그런 다음 InnoDB 데이터베이스 작성을 다시 시도합니다. 가장 쉬운 문제 해결을 위해 명령 프롬프트에서 MySQL 서버를 시작하여 발생하는 상황을 확인합니다.

 

▶︎ 런타임 문제

InnoDB가 파일 작업 중에 운영 체제 오류를 인쇄하는 경우 일반적으로 다음 해결책 중 하나를 사용합니다.

+ InnoDB 데이터 파일 디렉토리와 InnoDB 로그 디렉토리가 존재하는지 확인합니다

+ mysqld가 그 디렉토리에 파일을 생성할 수 있는 액세스 권한이 있는지 확인합니다.

+ mysqld가 올바른 my.cnf 또는 my.ini 옵션 파일을 읽을 수 있도록 하여 지정한 옵션으로 시작합니다.

+ 디스크가 꽉 차지 않았고 디스크 할당량을 초과하지 않았는지 확인합니다.

+ 하위 디렉토리 및 데이터 파일에 지정한 이름이 충돌하지 않는지 확인합니다.

+ innodb_data_home_dir 및 innodb_data_file_path 값의 구문을 다시 확인합니다. 특히, innodb_data_file_path 옵션의 MAX 값은 하드 한계이며이 한계를 초과하면 치명적인 오류가 발생합니다.

 

■ InnoDB 강제 복구

데이터베이스 페이지 손상을 조사하기 위해 SELECT ... INTO OUTFILE을 사용하여 데이터베이스에서 테이블을 덤프 할 수 있습니다. 일반적으로 이러한 방식으로 얻은 대부분의 데이터는 손상되지 않습니다. 심각한 손상으로 인해 SELECT * FROM tbl_name 문 또는 InnoDB 백그라운드 작업이 중단되어 더이상 진행이 안되는 경우, InnoDB 롤 포워드 복구가 중단 될 수 있습니다. 이러한 경우 innodb_force_recovery 옵션을 사용하여 백그라운드 작업이 실행되지 않도록 InnoDB 스토리지 엔진을 강제로 시작하여 테이블을 덤프 할 수 있습니다. 예를 들어, 서버를 다시 시작하기 전에 옵션 파일의 [mysqld] 섹션에 다음 줄을 추가 할 수 있습니다.

[mysqld]
innodb_force_recovery = 1

 

경고

응급 상황에서는 innodb_force_recovery를 0보다 큰 값으로만 설정하면 InnoDB를 시작하고 테이블을 덤프 할 수 있습니다. 이를 수행하기 전에 데이터베이스를 다시 작성해야 할 경우를 대비하여 데이터베이스의 백업 사본이 있는지 확인합니다. 4이상의 값은 데이터 파일을 영구적으로 손상시킬 수 있습니다. 별도의 실제 데이터베이스 복사본에서 설정을 성공적으로 테스트한 후에는 프로덕션 서버 인스턴스에서 innodb_force_recovery 설정을 4이상만 사용하십시오. InnoDB 복구를 강제할 때는 항상 innodb_force_recovery = 1로 시작하고 필요에 따라 값을 하나씩 늘려야합니다.

 

innodb_force_recovery는 기본적으로 0입니다 (강제 복구없이 정상 시작). innodb_force_recovery에 허용되는 0이 아닌 값은 1-6입니다. 값이 클수록 값이 작을 수 있습니다. 예를 들어, 값 3은 값 1과 2의 모든 기능을 포함합니다.

 

innodb_force_recovery 값이 3 이하인 테이블을 덤프 할 수 있으면 손상된 개별 페이지의 일부 데이터 만 손실되는 것이 상대적으로 안전합니다. 데이터 파일이 영구적으로 손상 될 수 있으므로 4 이상의 값은 위험한 것으로 간주됩니다. 데이터베이스 페이지가 사용되지 않는 상태로 남아 B- 트리 및 기타 데이터베이스 구조에 더 많은 손상이 발생할 수 있으므로 값 6은 과감한 것으로 간주됩니다.

 

안전 조치로 InnoDB는 innodb_force_recovery가 0보다 클 때 INSERT, UPDATE 또는 DELETE 작업을 방지합니다. innodb_force_recovery 설정이 4 이상이면 InnoDB가 읽기 전용 모드입니다.

+ 1 (SRV_FORCE_IGNORE_CORRUPT)

손상된 페이지를 감지하더라도 서버가 실행되도록 합니다. SELECT * FROM tbl_name이 손상된 인덱스 레코드 및 페이지를 건너뛰기 때문에 테이블 덤프에 도움이 됩니다.

 

+ 2 (SRV_FORCE_NO_BACKGROUND)

마스터 스레드 및 제거 스레드가 실행되지 않도록합니다. 제거 작업 중에 충돌이 발생하면 이 복구 값으로 인해 충돌이 방지됩니다.

 

+ 3 (SRV_FORCE_NO_TRX_UNDO)

응급 복구 후 트랜잭션 롤백을 실행하지 않습니다.

 

+ 4 (SRV_FORCE_NO_IBUF_MERGE)

삽입 버퍼 병합 조작을 방지합니다. 충돌이 발생하면 작동하지 않습니다. 테이블 통계를 계산하지 않습니다. 이 값은 데이터 파일을 영구적으로 손상시킬 수 있습니다. 이 값을 사용한 후에는 모든 보조 인덱스를 삭제하고 다시 만들 수 있도록 준비합니다. InnoDB를 읽기 전용으로 설정합니다.

 

+ 5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

데이터베이스를 시작할 때 실행 취소 로그를 보지 않음 : InnoDB는 불완전한 트랜잭션조차도 커밋된 것으로 간주합니다. 이 값은 데이터 파일을 영구적으로 손상시킬 수 있습니다. InnoDB를 읽기 전용으로 설정합니다.

 

+ 6 (SRV_FORCE_NO_LOG_REDO)

복구와 관련하여 리두 로그 롤 포워드를 수행하지 않습니다. 이 값은 데이터 파일을 영구적으로 손상시킬 수 있습니다. 데이터베이스 페이지를 사용하지 않는 상태로 두면 B-트리 및 기타 데이터베이스 구조가 더 손상 될 수 있습니다. InnoDB를 읽기 전용으로 설정합니다.

 

테이블에서 SELECT하여 덤프할 수 있습니다. innodb_force_recovery 값이 3이하이면 테이블을 DROP 또는 CREATE 할 수 있습니다. DROP TABLE은 innodb_force_recovery 값이 3보다 크고 MySQL 5.7.17까지 지원됩니다. MySQL 5.7.18부터는 4보다 큰 innodb_force_recovery 값으로 DROP TABLE을 사용할 수 없습니다.

 

주어진 테이블이 롤백시 충돌을 일으키는 것을 알고 있으면 삭제할 수 있습니다. 대량 가져 오기 또는 ALTER TABLE 실패로 인해 런 어웨이 롤백이 발생하면 mysqld 프로세스를 종료하고 innodb_force_recovery를 3으로 설정하여 롤백없이 데이터베이스를 가져온 다음 런 어웨이 롤백(runaway rollback:장시간의 롤백)을 유발하는 테이블을 DROP 할 수 있습니다.

 

테이블 데이터 내의 손상으로 인해 전체 테이블 내용을 덤프할 수 없는 경우 ORDER BY primary_key DESC 절을 사용하는 쿼리는 손상된 부분 다음에 테이블 부분을 덤프할 수 있습니다.

 

InnoDB를 시작하기 위해 높은 innodb_force_recovery 값이 필요한 경우 복잡한 쿼리 (WHERE, ORDER BY 또는 기타 절을 포함하는 쿼리)가 실패할 수 있는 데이터 구조가 손상되었을 수 있습니다. 이 경우 기본 SELECT * FROM t 쿼리만 실행할 수 있습니다.

 

 

■ InnoDB 데이터 사전 작업 문제 해결

테이블 정의에 대한 정보는 .frm 파일과 InnoDB 데이터 사전에 모두 저장됩니다. .frm 파일을 이동하거나 데이터 사전 작업 도중 서버가 충돌하면 이러한 정보 소스가 일치하지 않을 수 있습니다.

 

데이터 사전 손상 또는 일관성 문제로 인해 InnoDB를 시작할 수 없는 경우 수동 복구에 대한 자세한 내용은 위에 “InnoDB 복구 수행”을 참고합니다.

▶︎ 고아 테이블(Orphan Table)로 인한 CREATE TABLE 오류

동기화되지 않은 데이터 사전의 증상은 CREATE TABLE 문이 실패한다는 것입니다. 이 경우 서버의 오류 로그를 확인합니다. 로그에 테이블이 InnoDB 내부 데이터 사전 내에 이미 존재한다고 표시되면 InnoDB 테이블 스페이스 파일 내에 해당 .frm 파일이 없는 고아 테이블이있는 것입니다. 오류 메시지는 다음과 같습니다.

InnoDB: Error: table test/parent already exists in InnoDB internal

InnoDB: data dictionary. Have you deleted the .frm file

InnoDB: and not used DROP TABLE? Have you used DROP DATABASE

InnoDB: for InnoDB tables in MySQL version <= 3.23.43?

InnoDB: See the Restrictions section of the InnoDB manual.

InnoDB: You can drop the orphaned table inside InnoDB by

InnoDB: creating an InnoDB table with the same name in another

InnoDB: database and moving the .frm file to the current database.

InnoDB: Then MySQL thinks the table exists, and DROP TABLE will

InnoDB: succeed.

오류 메시지에 제공된 지시 사항에 따라 고아 테이블(Orphan Table)을 삭제할 수 있습니다. 여전히 DROP TABLE을 사용할 수 없다면, mysql 클라이언트에서 이름이 완성되어 문제가 발생한 것일 수 있습니다. 이 문제를 해결하려면 --skip-auto-rehash 옵션으로 mysql 클라이언트를 시작하고 DROP TABLE을 다시 시도합니다. (이름 완성을 사용하면 mysql은 테이블 이름 목록을 구성하려고 시도하는데, 이는 방금 설명한 것과 같은 문제가 존재할 때 실패합니다.)

 

▶︎ Cannot Open Datafile

innodb_file_per_table을 사용하도록 설정했을때(기본값) 테이블 당 테이블 스페이스 파일 (.ibd 파일)이없는 경우 시작시 다음 메시지가 표시 될 수 있습니다.

[ERROR] InnoDB: Operating system error number 2 in a file operation.

[ERROR] InnoDB: The error means the system cannot find the path specified.

[ERROR] InnoDB: Cannot open datafile for read-only: './test/t1.ibd' OS error: 71

[Warning] InnoDB: Ignoring tablespace `test/t1` because it could not be opened.

이러한 메시지를 해결하려면 DROP TABLE 문을 발행하여 누락 된 테이블에 대한 데이터를 데이터 사전에서 제거합니다.

 

▶︎ Cannot Open File Error

비동기 데이터 사전의 또 다른 증상은 MySQL이 InnoDB 파일을 열 수 없다는 오류를 출력한다는 것입니다.

ERROR 1016: Can't open file: 'child2.ibd'. (errno: 1)

 

오류 로그에서 다음과 같은 메시지를 찾을 수 있습니다.

InnoDB: Cannot find table test/child2 from the internal data dictionary

InnoDB: of InnoDB though the .frm file for the table exists. Maybe you

InnoDB: have deleted and recreated InnoDB data files but have forgotten

InnoDB: to delete the corresponding .frm files of InnoDB tables?

This means that there is an orphan .frm file without a corresponding table inside InnoDB. You can drop the orphan .frm file by deleting it manually.

이는 InnoDB 내부에 해당 테이블이없는 고아 .frm 파일이 있음을 의미합니다. 고아 .frm 파일을 수동으로 삭제하여 삭제할 수 있습니다.

 

▶︎ 고아 중간 테이블(Orphan Intermediate Tables)

내부 ALTER TABLE 조작 (ALGORITHM = INPLACE) 중에 MySQL이 종료되면 시스템의 공간을 차지하는 고아 중간 테이블이 남아있을 수 있습니다. 또한 비어있는 일반 테이블 스페이스의 고아 중간 테이블은 일반 테이블 스페이스를 삭제하지 못하게합니다. 이 섹션에서는 고아 중간 테이블을 식별하고 제거하는 방법에 대해 설명합니다.

 

중간 테이블 이름은 #sql-ib 접두사로 시작합니다 (예 : # sql-ib87-856498050). 함께 제공되는 .frm 파일은 # sql-* 접두사를 가지며 이름이 다릅니다 (예 : # sql-36ab_2.frm).

 

시스템에서 고아 중간 테이블을 식별하기 위해 INFORMATION_SCHEMA.INNODB_SYS_TABLES를 조회할 수 있습니다. #sql로 시작하는 테이블 이름을 찾습니다. 원래 테이블이 테이블당 파일 테이블 스페이스에 있는 경우 고아 중간 테이블의 테이블 스페이스 파일 (#sql-*. ibd 파일)이 데이터베이스 디렉토리에 표시되어야합니다.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

 

고아 중간 테이블을 제거하려면 다음 단계를 수행합니다.

1. 데이터베이스 디렉토리에서 #sql-*. frm 파일의 이름을 바꾸어 고아 중간 테이블의 기본 이름과 일치 시킵니다.

shell> mv #sql-36ab_2.frm #sql-ib87-856498050.frm

 

노트

.frm 파일이 없으면 다시 만들 수 있습니다. .frm 파일은 고아 중간 테이블과 동일한 테이블 스키마를 가져야하며 (같은 컬럼과 인덱스가 있어야 함) 고아 중간 테이블의 데이터베이스 디렉토리에 있어야합니다.

 

2. DROP TABLE 문을 실행하여 테이블 이름 앞에 #mysql50#을 붙이고 테이블 이름을 백틱으로 묶어 고아 중간 테이블을 삭제합니다. 예를 들면 다음과 같습니다.

mysql> DROP TABLE `#mysql50##sql-ib87-856498050`;

앞에서 #mysql50# 로 시작하며, MySQL에게 MySQL 5.1에 도입된 파일 이름을 안전하게 인코딩을 무시하도록 지시합니다. “#”과 같은 특수 문자를 사용하여 테이블 이름에 대해 SQL 문을 수행하려면 테이블 이름을 백틱으로 묶어야합니다.

백틱(backticks) : 외부 프로그램을 실행 후 그 결과값을 저장하는 방법입니다.

보통 (`)를 앞뒤로 묶어 외부 프로그램을 실행 후  그 결과값을 변수로 받아 처리합니다.

$lines=`ls`;

$lines=`dir`;

 

#mysql50# 접두사는 MySQL 5.1에 도입된 파일 이름 안전 인코딩을 무시하도록 MySQL에 지시합니다. 테이블 이름에 "#"와 같은 특수 문자를 사용하여 SQL 문을 수행하려면 테이블 이름을 백틱으로 묶어야 합니다.

 

참고사항 1

테이블을 다른 테이블 스페이스로 이동하는 명령인 내부 ALTER TABLE 조작중에 충돌이 발생하면 복구 프로세스는 테이블을 원래 위치로 복원하지만 고아 중간 테이블을 대상 테이블 스페이스에 남겨 둡니다.

 

참고사항 2

파티션된 테이블에서 내부 ALTER TABLE 조작 중에 MySQL이 종료되면 파티션당 하나씩 여러 개의 고아 중간 테이블(orphan intermediate tables)이 남아있을 수 있습니다. 이 경우 다음 프로시저를 사용하여 고아 중간 테이블을 제거합니다.

1. 동일한 MySQL 버전의 별도 인스턴스에서 분할된 테이블과 동일한 스키마 이름 및 컬럼을 사용하여 분할되지 않은 테이블을 만듭니다.

2. 파티션되지 않은 테이블의 .frm 파일을 고아 중간 테이블(orphan intermediate tables)이 있는 데이터베이스 디렉토리에 복사합니다.

3. 각 테이블에 대해 .frm 파일을 복사하고 .frm 파일의 이름을 변경하여 고아 중간 테이블(orphan intermediate tables)의 이름과 일치시킵니다 (위 설명 참조).

4. 각 테이블에 대해 DROP TABLE 작업을 수행합니다 (위 설명 참조).

 

▶︎ 고아 임시 테이블(Orphan Temporary Tables)

테이블 복사인 ALTER TABLE 작업 (ALGORITHM = COPY) 중에 MySQL이 종료되면 시스템의 공간을 차지하는 고아 임시 테이블(orphan temporary tables)이 남아있을 수 있습니다. 또한 비어있는 일반 테이블 스페이스의 고아 임시 테이블(orphan temporary tables)은 일반 테이블 스페이스를 삭제하지 못하게 합니다. 이 섹션에서는 고아 임시 테이블을 식별하고 제거하는 방법에 대해 설명합니다.

 

고아 임시 테이블 이름은 # sql-로 시작합니다 (예 : # sql-540_3). 함께 제공되는 .frm 파일은 고아 임시 테이블과 동일한 기본 이름을 갖습니다.

 

노트

.frm 파일이 없으면 다시 만들 수 있습니다. .frm 파일은 고아 임시 테이블과 동일한 테이블 스키마를 가져야하며 (같은 열과 인덱스가 있어야 함) 고아 임시 테이블(orphan temporary tables)의 데이터베이스 디렉토리에 있어야합니다.

 

시스템에서 고아 임시 테이블(orphan temporary tables)을 식별하기 위해 INFORMATION_SCHEMA.INNODB_SYS_TABLES를 조회할 수 있습니다. #sql로 시작하는 테이블 이름을 찾습니다. 원래 테이블이 테이블당 파일 테이블 스페이스에 있는 경우 고아 임시 테이블(orphan temporary tables)의 테이블 스페이스 파일 (#sql-*. ibd 파일)이 데이터베이스 디렉토리에 표시되어야합니다.

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

 

고아 임시 테이블(orphan temporary tables)을 제거하려면 테이블 이름 앞에 # mysql50 #을 접두사로 붙여 DROP TABLE 문을 실행하고 테이블 이름을 백틱으로 묶어 테이블을 삭제합니다. 예를 들면 다음과 같습니다.

mysql> DROP TABLE `#mysql50##sql-540_3`;

#mysql50#과 같은 문법으로 시작하며 MySQL에게 MySQL 5.1에 도입된 파일 이름 안전한 인코딩(safe encoding)을 무시하도록 지시합니다. “#”과 같은 특수 문자를 사용하여 테이블 이름에 대해 SQL 문을 수행하려면 테이블 이름을 백틱으로 묶어야합니다.

백틱 : 특정 명령문을 외부에서 수행할 수 있도록 (`)로 묶는방법

$linuxdir=`ls -l`

$windir=`dir`

 

참고사항

파티션된 테이블에서 테이블 복사 ALTER TABLE 조작 중에 MySQL이 종료되면 파티션마다 하나씩 여러 개의 고아 임시 테이블이 남아있을 수 있습니다. 이 경우 다음 절차대로 고아 임시 테이블(orphan temporary tables)을 제거합니다.

1. 동일한 MySQL 버전의 별도 인스턴스에서 분할된 테이블과 동일한 스키마 이름 및 컬럼을 사용하여 분할되지 않은 테이블을 만듭니다.

2. 파티션되지 않은 테이블의 .frm 파일을 고아 임시 테이블(orphan temporary tables)이있는 데이터베이스 디렉토리에 복사합니다.

3. 각 테이블에 대해 .frm 파일의 복사본을 만들고 고아 임시 테이블(orphan temporary tables)의 이름과 일치하도록 .frm 파일의 이름을 바꿉니다 (위 설명 참조).

4. 각 테이블에 대해 DROP TABLE 작업을 수행하십시오 (위 설명 참조).

 

▶︎ 테이블 스페이스가 존재하지 않습니다

innodb_file_per_table을 사용하면 .frm 또는 .ibd 파일 (또는 둘 다)이 없는 경우 다음 메시지가 발생할 수 있습니다.

InnoDB: in InnoDB data dictionary has tablespace id N,

InnoDB: but tablespace with that id or name does not exist. Have

InnoDB: you deleted or moved .ibd files?

InnoDB: This may also be a table created with CREATE TEMPORARY TABLE

InnoDB: whose .ibd and .frm files MySQL automatically removed, but the

InnoDB: table still exists in the InnoDB internal data dictionary.

 

이 문제가 발생하면 다음 절차에 따라 문제를 해결합니다.

1. 다른 데이터베이스 디렉토리에 일치하는 .frm 파일을 만들어 고아 테이블(orphan tables)이 있는 데이터베이스 디렉토리에 복사합니다.

2. 원래 테이블에 대해 DROP TABLE을 실행합니다. 성공적으로 테이블을 삭제하고 InnoDB는 ‘.ibd 파일이 없다’는 경고 메세지를 오류 로그에 남겨야 합니다.

 

▶︎ 테이블당 고아(Orphan) 파일 ibd 파일 복원

이 절차에서는 고아(Orphan) 파일당 .ibd 파일을 다른 MySQL 인스턴스로 복원하는 방법에 대해 설명합니다. 시스템 테이블 스페이스가 유실되거나 복구가 불가능하고 새 MySQL 인스턴스에서 .ibd 파일 백업을 복원하려는 경우 이 절차를 사용할 수 있습니다.

 

일반 테이블 스페이스 .ibd 파일에 대해서는 프로 시저가 지원되지 않습니다.

 

이 절차에서는 .ibd 파일 백업만 있고 초기에 고아 .ibd 파일을 만든 동일한 버전의 MySQL로 복구하고 .ibd 파일 백업이 깨끗하다고 가정합니다.

1. 새로운 MySQL 인스턴스에서 같은 이름의 데이터베이스에 테이블을 다시 생성합니다.

mysql> CREATE DATABASE sakila;
mysql> USE sakila;
mysql> CREATE TABLE actor (
         actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
         first_name VARCHAR(45) NOT NULL,
         last_name VARCHAR(45) NOT NULL,
         last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
         PRIMARY KEY  (actor_id),
         KEY idx_actor_last_name (last_name)
       )ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

2. 새로 작성된 테이블의 테이블 스페이스를 버립니다.

mysql> ALTER TABLE sakila.actor DISCARD TABLESPACE;

 

3. 백업 디렉토리에서 새 데이터베이스 디렉토리로 orphan .ibd 파일을 복사합니다.

shell> cp /backup_directory/actor.ibd path/to/mysql-5.7/data/sakila/

 

4. .ibd 파일에 필요한 파일 권한이 있는지 확인합니다.

5. 고아(orphan) .ibd 파일을 가져옵니다. InnoDB가 스키마 확인없이 파일을 가져 오려고 한다는 경고가 표시됩니다.

mysql> ALTER TABLE sakila.actor IMPORT TABLESPACE; SHOW WARNINGS;    
Query OK, 0 rows affected, 1 warning (0.15 sec)

Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory)
Error opening './sakila/actor.cfg', will attempt to import without schema verification
('./sakila/actor.cfg'를 여는 동안 오류가 발생했습니다. 스키마 확인 없이 가져오기를 시도합니다.)

 

6. 테이블을 쿼리하여 .ibd 파일이 성공적으로 복원되었는지 확인합니다.

mysql> SELECT COUNT(*) FROM sakila.actor;
+----------+
| count(*) |
+----------+
|      200 |
+----------+

 

■ InnoDB 에러 처리

다음 항목은 InnoDB가 오류 처리를 수행하는 방법을 설명합니다. InnoDB는 때때로 실패한 명령문만 롤백하고 다른 경우에는 전체 트랜잭션을 롤백합니다.

 

+ 테이블 스페이스의 파일 공간이 부족하면 MySQL 테이블이 ‘full error(꽉 참,여유공간이 없음 오류)’가 발생하고 InnoDB가 SQL 문을 롤백합니다.

+ 트랜잭션 교착 상태로 인해 InnoDB가 전체 트랜잭션을 롤백합니다. 이 경우 전체 트랜잭션을 재 시도합니다.

잠금 대기 시간 초과로 인해 InnoDB는 잠금을 대기하다가 시간 초과가 발생한 단일 문만 롤백합니다. (전체 트랜잭션을 롤백하려면 --innodb-rollback-on-timeout 옵션을 사용하여 서버를 시작하십시오.) 현재 동작을 사용하는 경우 명령문을 다시 시도하거나 --innodb-rollback-on-timeout을 사용하는 경우 전체 트랜잭션을 다시 시도합니다.

사용량이 많은 서버에서는 교착 상태와 잠금 대기 시간이 모두 보통이므로 응용 프로그램이 이러한 시간이 발생할 수 있음을 인식하고 다시 시도하여 처리해야 합니다. 트랜잭션 중에 첫 번째 데이터 변경과 커밋 사이의 작업을 최대한 적게 수행하여 잠금을 가능한 한 짧은 시간 동안 가능한 한 적은 수의 행으로 유지할 수 있습니다. 때로는 서로 다른 트랜잭션 간에 작업을 분할하는 것이 실용적이고 유용할 수 있습니다.

교착 상태 또는 잠금 대기 시간 종료로 인해 트랜잭션 롤백이 발생하면 트랜잭션 내 명령문의 영향을 취소합니다. 그러나 시작 트랜잭션 명령문이 START TRANSACTION 또는 BEGIN 문인 경우 롤백은 해당 명령문을 취소하지 않습니다. COMMIT, ROLLBACK 또는 내재된 커미트를 유발하는 일부 SQL 문이 발생할 때까지 추가 SQL문이 트랜잭션의 일부가 됩니다.

+ 명령문에 IGNORE 옵션을 지정하지 않은 경우 중복 키 오류가 SQL 문을 롤백합니다.

+ 행이 너무 길면 SQL 문이 롤백됩니다.

+ 기타 오류는 대부분 MySQL 코드 계층 (InnoDB 스토리지 엔진 수준 이상)에 의해 감지되며 해당 SQL 문을 롤백합니다. 단일 SQL 문의 롤백에서 잠금이 해제되지 않습니다.

 

암시적 롤백 및 명시적 ROLLBACK SQL 문 실행 중에 SHOW PROCESSLIST는 관련 연결의 상태 열에 롤백을 표시합니다.