MySQL 8.0 ibd 파일을 이용한 복구

MySQL 8.0 에서 data dictionary 가 새롭게 생성되면서 기존에 사용하던 .frm / .trn / .trg / .par 등의 파일들은 더 이상 이용되지 않음.

 

MySQL 데이터베이스의 데이터들은 mysql.ibd 테이블 스페이스에 저장됨.

 

InnoDB 엔진 외 metadata 저장은 SDI (Serailized Dictionary Information) 파일에 json 으로 저장됨.

general_log 와 slow_log 테이블에서 .sid 파일을 확인할 수 있음.

 

 

 

ibd 파일을 이용하여 복구를 진행하기 위해선 ① ibd 파일 ② CREATE TABLE 문 (table 구조) 가 필요함.

 

 

1. 데이터 베이스 생성

> CREATE DATABASE [데이터베이스명]; > USE [데이터베이스명];

 

 

2. 테이블 구조 생성

> CREATE TABLE `[테이블명]` ([구조...]) ENGINE=InnoDB;

 

 

3. 테이블 스페이스 삭제 (기존에 연동되어있던 데이터를 해제, 해당 명령어 입력 시 현재 .ibd 파일이 삭제됨)

> ALTER TABLE [테이블명] DISCARD TABLESPACE; > flush privileges;

 

- 에러 발생 시 

**에러발생**

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

 

**해결방법**

Mysql이 테이블 간 관계가 있는 데이터를 함부로 수정하거나 삭제할 수 없도록 안전장치를 해두었기 때문에 에러를 통해 경고

> SET foreign_key_checks = 0; > SET foreign_key_checks = 1; \\ 원상복구

 

 

 

4. 기존 ibd파일을 운영중인 mysql 디렉터리에 복사 및 권한 설정 

# cp -arp [백업 ibd 파일 위치].ibd [복구할 ibd 파일 위치].ibd # chown mysql:mysql [백업ibd].ibd

 

 

5. 백업받은 ibd 파일을 통해 현제 데이터와 연동 

> ALTER TABLE [테이블명] IMPORT TABLESPACE;

 

 

6. 확인

> USE [데이터베이스명]; > SELECT * FROM  [테이블명];

 

 

 

 

---

 

* 위 작업을 중복적으로 진행해야할 경우 아래의 스크립트 이용. (단 테이블 구조는 모두 들어가있는 상태여야함)

#!/bin/bash db_user="root" db_pass="[DB root 패스워드]" database="[복구할 데이터베이스명]" for table in `mysql -u${db_user} -p${db_pass}  -e"show tables" $database | /bin/grep -v "^$" | /bin/grep -v "Tables_in_$database"` do        echo "${table} 복구시도..."        mysql -u${db_user} -p${db_pass} -e "use $database; SET foreign_key_checks = 0; ALTER TABLE ${table} DISCARD TABLESPACE; flush privileges;"        cp -arp [백업해놓은 ibd 파일 디렉터리]/${table}.ibd [실제 ibd 파일이 위치할 경로]        mysql -u${db_user} -p${db_pass} -e "use $database; SET foreign_key_checks = 0; ALTER TABLE ${table} IMPORT TABLESPACE; flush privileges;" done   exit 0

 

 

 

 

 

참고 URL :

1) https://osskorea.wordpress.com/2018/10/08/mysql-8-0-%EB%8B%AC%EB%9D%BC%EC%A7%84-meta-data-%EA%B4%80%EB%A6%AC-data-dictionary/ 

 

2) https://dev.mysql.com/doc/refman/8.0/en/innodb-table-import.html  

3) https://www.chriscalender.com/recovering-an-innodb-table-from-only-an-ibd-file/ 

 

4) https://dba.stackexchange.com/questions/288860/restoring-mysql-data-from-ibd-file