데이터베이스 서버에서 테이블의 구조 정보와 스토어드 프로그램 드으이 정보를 데이터 딕셔너리 또는 메타데이터라고 하는데, Mysql 5.7 버전까지 테이블의 구조를 FRM 파일에 저장하고 일부 스토어드 프로그램
또한 파일(*TRN, *TRG, *PAR...) 기반으로 관리했다.
하지만 이러한 파일기반의 메타데이터는 생성 및 변경 작업이 트랜잭션을 지원하지 않기 때문에 테이블의 생성 또는 변경 도중에 Mysql 서버가 비정상적으로 종료되면 일관되지 않은 상태로 남는 문제가 있었다.
많은 사용자들이 이같은 현상을 가리켜 '데이터베이스나 테이블이 깨졌다' 라고 표현한다.
Mysql 8.0 버전부터는 이러한 문제점을 해결하기 위해 테이블의 구조 정보나 스토어드 프로그램의 코드 관련 정보를 모두 InnoDB의 테이블에 저장하도록 개선됐다.
Mysql 서버가 작동하는 데 기본적으로 필요한 테이블들을 묶어서 시스템 테이블이라고 하는데, 대표적으로 사용자의 인증과 권한에 관련된 테이블들이 있다.
Mysql 서버 8.0 버전부터는 이런 시스템 테이블을 모두 InnoDB 스토리지 엔진을 사용하도록 개선했으며,
시스템 테이블과 데이터 딕셔너리 정보를 모두 모아서 mysql DB에 저장하고 있다.
-> mysql 8.0 부터 데이터 딕셔너리 정보라는 것이 생기므로 해서 fork DB라고 불리는 Maridb 와는 다른 길을 가게 된다.
다시말하면 MariaDB 의 스키마 단위 백업해서 재생성은 가능하지만 통째로 백업해서 mysql 8.0에 넣을 수는 없다.
mysql DB는 통째로 mysql.ibd라는 이름의 테이블스페이스에 저장된다.
그래서 Mysql 서버의 데이터 디렉터리에 존재하는 mysql.ibd라는 파일은 다른 *.ibd 파일과 함께 특별하게 주의해야 한다.
-> 서버의 비정상적인 종료가 되더라도 innodb 엔진을 사용하여 mysql.ibd 파일에 쌓음으로써 데이터 정합성을 보장하게 수정되었다는 얘기
mysql DB에 데이터 딕셔너리를 저장하는 테이블이 저장된다고 했는데, 실제 mysql DB에서 테이블의 목록을 살펴보면
실제 테이블의 구조가 저장된 테이블은 보이지 않을 것이다.
데이터 딕셔너리 테이블의 데이터를 사용자가 임의로 수정하지 못하게 사용자의 화면에 보여주지만 않을 뿐 실제로 테이블은 존재한다.
대신 mysql 서버는 데이터 딕셔너리 정보를 information_schema DB의 TABLE와 COLUMNS 등과 같은 뷰를 통해 조회할 수 있게 하고 있다.
실제 information_schema에서 TABLES 테이블의 구조를 보면 뷰로 만들어져 있고 TABLES 뷰는 mysql DB의 table이라는 이름의 테이블을 참조하고 있음을 확인할 수 있다.
mysql> show create table information_schema.tables;
+--------+-------------------------------------+
| Table | Create Table
+--------+-------------------------------------+
| TABLES | CREATE TEMPORARY TABLE `TABLES` (
`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
`TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
`ENGINE` varchar(64) DEFAULT NULL,
`VERSION` bigint(21) unsigned DEFAULT NULL,
`ROW_FORMAT` varchar(10) DEFAULT NULL,
`TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
`AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
`DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
`MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
`INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
`DATA_FREE` bigint(21) unsigned DEFAULT NULL,
`AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
`CREATE_TIME` datetime DEFAULT NULL,
`UPDATE_TIME` datetime DEFAULT NULL,
`CHECK_TIME` datetime DEFAULT NULL,
`TABLE_COLLATION` varchar(32) DEFAULT NULL,
`CHECKSUM` bigint(21) unsigned DEFAULT NULL,
`CREATE_OPTIONS` varchar(255) DEFAULT NULL,
`TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
+--------+-------------------------------------+
// 그리고 mysql DB에서 tables 라는 이름의 테이블에 대해 select 를 실행해보면 '테이블이 없음' 에러가 아니라 다음과 같이 '접근이 거절됨'이라고 출력된다.
mysql> select * from mysql.tables limit 1;
ERROR 3554 (HY000): Access to data dictionary table 'mysql.tables' is rejected.
mysql 8.0 버전부터 데이터 딕셔너리와 시스템 테이블이 모두 트랜젝션 기반의 innoDB 스토리지 엔진에 저장되도록 개선되면서
이제 스키마 변경 작업 중간에 Mysql 서버가 비정상적으로 종료된다고 하더라도 스키마 변경이 완전한 성공 또는 완전한 실패로 정리된다.
기존의 파일 기반 메타데이터를 사용할 때와 같이 적업 진행 중인 상태로 남으면서 문제를 유발하지 않게 개선된 것이다.
->비정상적인 DB 종료시 파일이 깨짐으로 DB가 정상적으로 startup 못하는 현상 개선
Mysql 서버에서 InnoDB 스토리지 엔진을 사용하는 테이블은 메타 정보가 InnoDB 테이블 기반의 딕셔너리에 저장되지만
MyISAM이나 CSV 등과 같은 스토리지 엔진의 메타 정보는 여전히 저장할 공간이 필요하다.
Mysql 서버는 InnoDB 스토리지 엔진 이외의 스토리지 엔진을 사용하는 테이블들을 위해 SDI(Serialized Dictionary Information) 파일을 사용한다.
InnoDB 이외의 테이블들에 대해서는 SDI 포맷의 *.sdi 파일이 존재하며, 이 파일은 기존의 *.FRM 파일과 동일한 역할을 한다.
그리고 SDI는 이름 그대로 직렬화(Serialized)를 위한 포맷이므로 InnoDB 테이블들의 구조도 SDI 파일로 변환할 수 있다.
ibd2sdi 유틸리티를 이용하면 InnoDB 테이블스페이스에서 스키마 정보를 추출할 수 있는데,
다음 예제는 mysql DB에 포함된 테이블의 스키마를 JSON 파일로 덤프한 것이다.
ibd2sdi 유틸리티로 추출한 테이블 정보 중에는 Mysql 서버에서 show tables 명령으로는 확인할 수 없던
mysql.tables 딕셔너리 테이블을 위한 테이블 구조도 볼 수 있다.
