반응형
1. ibd2sdi
ibd2sdi는 테이블스페이스의 SDI(serialized dictionary information)를 JSON형태로 추출하는 프로그램입니다.
INNODB의 경우 idb파일에 스키마 정보가 같이 포함되어 있는데 이부분을 추출하는 프로그램입니다. INNODB의 테이블 스페이스는 innodb_file_per_table에 따라 생성의 차이가 발생합니다. mysql 8.x부터는 innodb_file_per_table의 기본값이 ON이며 보통 디스크 사용량 등을 줄이기 위해서 innodb_file_per_table를 사용합니다. (myISAM은 따로 SDI 파일이 존재함.)
1) INNODB 테이블 스페이스
SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES;
+------------+------------------+-------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
| SPACE | NAME | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE |
+------------+------------------+-------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
| 4294967294 | mysql | 18432 | Any | 16384 | 0 | General | 4096 | 37748736 | 37752832 | 0 | 8.0.31 | 1 | N | normal |
| 4294967293 | innodb_temporary | 4096 | Compact or Redundant | 16384 | 0 | System | 4096 | 12582912 | 12582912 | 0 | 8.0.31 | 1 | N | normal |
| 4294967279 | innodb_undo_001 | 0 | Undo | 16384 | 0 | Undo | 4096 | 50331648 | 50331648 | 0 | 8.0.31 | 1 | N | active |
| 4294967278 | innodb_undo_002 | 0 | Undo | 16384 | 0 | Undo | 4096 | 33554432 | 33554432 | 0 | 8.0.31 | 1 | N | active |
| 6 | sys/sys_config | 16417 | Dynamic | 16384 | 0 | Single | 4096 | 114688 | 114688 | 0 | 8.0.31 | 1 | N | normal |
| 1218 | mysql/t1 | 16417 | Dynamic | 16384 | 0 | Single | 4096 | 114688 | 114688 | 0 | 8.0.31 | 1 | N | normal |
| 1419 | testdb/t1 | 16417 | Dynamic | 16384 | 0 | Single | 4096 | 114688 | 114688 | 0 | 8.0.31 | 1 | N | normal |
| 1820 | testdb/t2 | 16417 | Dynamic | 16384 | 0 | Single | 4096 | 114688 | 114688 | 0 | 8.0.31 | 1 | N | normal |
| 1821 | testdb/t3 | 16417 | Dynamic | 16384 | 0 | Single | 4096 | 114688 | 114688 | 0 | 8.0.31 | 1 | N | normal |
+------------+------------------+-------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
9 rows in set (0.00 sec)
$ ls
t1.ibd t2.ibd t3.ibd t4.MYD t4.MYI t4_2222.sdi
-- t1, t2, t3는 INNODB 엔진 테이블
-- t4는 MyISAM 엔진 테이블$ ibd2sdi t1.ibd
["ibd2sdi"
,
{
"type": 1,
"id": 1819,
"object":
{
"mysqld_version_id": 80031,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Table",
"dd_object": {
"name": "t1",
"mysql_version_id": 80031,
"created": 20221124080044,
"last_altered": 20221124080044,
"hidden": 1,
"options": "avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;",
"columns": [
{
"name": "col1",
"type": 4,
"is_nullable": true,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 1,
"char_length": 11,
"numeric_precision": 10,
"numeric_scale": 0,
"numeric_scale_null": false,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "table_id=2528;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "int",
"elements": [],
"collation_id": 255,
"is_explicit_collation": false
},
{
"name": "DB_ROW_ID",
"type": 10,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 2,
"ordinal_position": 2,
"char_length": 6,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "",
"se_private_data": "table_id=2528;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "",
"elements": [],
"collation_id": 63,
"is_explicit_collation": false
},
{
"name": "DB_TRX_ID",
"type": 10,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 2,
"ordinal_position": 3,
"char_length": 6,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "",
"se_private_data": "table_id=2528;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "",
"elements": [],
"collation_id": 63,
"is_explicit_collation": false
},
{
"name": "DB_ROLL_PTR",
"type": 9,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 2,
"ordinal_position": 4,
"char_length": 7,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "",
"se_private_data": "table_id=2528;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "",
"elements": [],
"collation_id": 63,
"is_explicit_collation": false
}
],
"schema_ref": "testdb",
"se_private_id": 2528,
"engine": "InnoDB",
"last_checked_for_upgrade_version_id": 0,
"comment": "",
"se_private_data": "",
"engine_attribute": "",
"secondary_engine_attribute": "",
"row_format": 2,
"partition_type": 0,
"partition_expression": "",
"partition_expression_utf8": "",
"default_partitioning": 0,
"subpartition_type": 0,
"subpartition_expression": "",
"subpartition_expression_utf8": "",
"default_subpartitioning": 0,
"indexes": [
{
"name": "PRIMARY",
"hidden": true,
"is_generated": false,
"ordinal_position": 1,
"comment": "",
"options": "",
"se_private_data": "id=1636;root=4;space_id=1419;table_id=2528;trx_id=31003;",
"type": 2,
"algorithm": 2,
"is_algorithm_explicit": false,
"is_visible": true,
"engine": "InnoDB",
"engine_attribute": "",
"secondary_engine_attribute": "",
"elements": [
{
"ordinal_position": 1,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 1
},
{
"ordinal_position": 2,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 2
},
{
"ordinal_position": 3,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 3
},
{
"ordinal_position": 4,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 0
}
],
"tablespace_ref": "testdb/t1"
}
],
"foreign_keys": [],
"check_constraints": [],
"partitions": [],
"collation_id": 255
}
}
}
,
{
"type": 2,
"id": 1424,
"object":
{
"mysqld_version_id": 80031,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Tablespace",
"dd_object": {
"name": "testdb/t1",
"comment": "",
"options": "autoextend_size=0;encryption=N;",
"se_private_data": "flags=16417;id=1419;server_version=80031;space_version=1;state=normal;",
"engine": "InnoDB",
"engine_attribute": "",
"files": [
{
"ordinal_position": 1,
"filename": "./testdb/t1.ibd",
"se_private_data": "id=1419;"
}
]
}
}
}
]
[root@37bfcb726d10 testdb]# ㅣㄴ
bash: ㅣㄴ: command not found
[root@37bfcb726d10 testdb]# ls
t1.ibd t2.ibd t3.ibd
[root@37bfcb726d10 testdb]# ls
t1.ibd t2.ibd t3.ibd
[root@37bfcb726d10 testdb]# ibd2sdi t1.ibd
["ibd2sdi"
,
{
"type": 1,
"id": 1819,
"object":
{
"mysqld_version_id": 80031,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Table",
"dd_object": {
"name": "t1",
"mysql_version_id": 80031,
"created": 20221124080044,
"last_altered": 20221124080044,
"hidden": 1,
"options": "avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;",
"columns": [
{
"name": "col1",
"type": 4,
"is_nullable": true,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 1,
"char_length": 11,
"numeric_precision": 10,
"numeric_scale": 0,
"numeric_scale_null": false,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "table_id=2528;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "int",
"elements": [],
"collation_id": 255,
"is_explicit_collation": false
},
{
"name": "DB_ROW_ID",
"type": 10,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 2,
"ordinal_position": 2,
"char_length": 6,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "",
"se_private_data": "table_id=2528;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "",
"elements": [],
"collation_id": 63,
"is_explicit_collation": false
},
{
"name": "DB_TRX_ID",
"type": 10,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 2,
"ordinal_position": 3,
"char_length": 6,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "",
"se_private_data": "table_id=2528;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "",
"elements": [],
"collation_id": 63,
"is_explicit_collation": false
},
{
"name": "DB_ROLL_PTR",
"type": 9,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 2,
"ordinal_position": 4,
"char_length": 7,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "",
"se_private_data": "table_id=2528;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "",
"elements": [],
"collation_id": 63,
"is_explicit_collation": false
}
],
"schema_ref": "testdb",
"se_private_id": 2528,
"engine": "InnoDB",
"last_checked_for_upgrade_version_id": 0,
"comment": "",
"se_private_data": "",
"engine_attribute": "",
"secondary_engine_attribute": "",
"row_format": 2,
"partition_type": 0,
"partition_expression": "",
"partition_expression_utf8": "",
"default_partitioning": 0,
"subpartition_type": 0,
"subpartition_expression": "",
"subpartition_expression_utf8": "",
"default_subpartitioning": 0,
"indexes": [
{
"name": "PRIMARY",
"hidden": true,
"is_generated": false,
"ordinal_position": 1,
"comment": "",
"options": "",
"se_private_data": "id=1636;root=4;space_id=1419;table_id=2528;trx_id=31003;",
"type": 2,
"algorithm": 2,
"is_algorithm_explicit": false,
"is_visible": true,
"engine": "InnoDB",
"engine_attribute": "",
"secondary_engine_attribute": "",
"elements": [
{
"ordinal_position": 1,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 1
},
{
"ordinal_position": 2,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 2
},
{
"ordinal_position": 3,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 3
},
{
"ordinal_position": 4,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 0
}
],
"tablespace_ref": "testdb/t1"
}
],
"foreign_keys": [],
"check_constraints": [],
"partitions": [],
"collation_id": 255
}
}
}
,
{
"type": 2,
"id": 1424,
"object":
{
"mysqld_version_id": 80031,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Tablespace",
"dd_object": {
"name": "testdb/t1",
"comment": "",
"options": "autoextend_size=0;encryption=N;",
"se_private_data": "flags=16417;id=1419;server_version=80031;space_version=1;state=normal;",
"engine": "InnoDB",
"engine_attribute": "",
"files": [
{
"ordinal_position": 1,
"filename": "./testdb/t1.ibd",
"se_private_data": "id=1419;"
}
]
}
}
}
]반응형
'Database > MySQL' 카테고리의 다른 글
| [mysql] errorlog file 사이즈 축소 (0) | 2023.03.08 |
|---|---|
| mysqlshow (0) | 2022.11.25 |
| mysqlslap (0) | 2022.11.25 |
| MySQL Shell (1) - 설치 (0) | 2022.11.21 |
| mysqlcheck (0) | 2022.11.18 |