通常運用時には使う事は無いと思いますが、メタデータ参照出来ないケースやMySQLのAuthenticationエラーでデータディクショナリから確認出来ない場合等に使える方法なのかなと思い。念の為に、基本的な挙動のみ列挙しておきます。MySQL8.0.11からJSON Prettyフォーマットオプションが追加されDefaultに成った様です。
シリアライズディクショナリ情報 (SDI)
シリアライズされた形式のディクショナリオブジェクトメタデータ。 SDI は JSON 形式で格納されます。MySQL 8.0.3 では、SDI は一時テーブルスペースおよび undo テーブルスペースファイルを除くすべての InnoDB テーブルスペースファイルに存在します。 SDI がテーブルスペースファイルに存在すると、メタデータの冗長性が提供されます。 たとえば、データディクショナリが使用できなくなった場合は、ibd2sdi ユーティリティを使用してテーブルスペースファイルからディクショナリオブジェクトメタデータを抽出できます。
MyISAM テーブルの場合、SDI はスキーマディレクトリの .sdi メタデータファイルに格納されます。 IMPORT TABLE 操作を実行するには SDI メタデータファイルが必要です。
HELP
[root@POC]# ibd2sdi --help
ibd2sdi Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2015, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Usage: ibd2sdi [-v] [-c <strict-check>] [-d <dump file name>] [-n] filename1 [filenames]
See http://dev.mysql.com/doc/refman/8.0/en/ibd2sdi.html for usage hints.
-h, --help Display this help and exit.
-v, --version Display version information and exit.
-d, --dump-file=name
Dump the tablespace SDI into the file passed by user.
Without the filename, it will default to stdout
-s, --skip-data Skip retrieving data from SDI records. Retrieve only id
and type.
-i, --id=# Retrieve the SDI record matching the id passed by user.
-t, --type=# Retrieve the SDI records matching the type passed by
user.
-c, --strict-check=name
Specify the strict checksum algorithm by the user.
Allowed values are innodb, crc32, none.
-n, --no-check Ignore the checksum verification.
-p, --pretty Pretty format the SDI output.If false, SDI would be not
human readable but it will be of less size
(Defaults to on; use --skip-pretty to disable.)
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
dump-file (No default value)
skip-data FALSE
id 0
type 0
strict-check crc32
no-check FALSE
pretty TRUE
[root@POC]# ibd2sdi は、InnoDB テーブルスペースファイルから serialized dictionary information (SDI) を抽出するためのユーティリティです。 SDI データは、すべての永続 InnoDB テーブルスペースファイルに存在します。ibd2sdi は、SDI (id、type および data フィールドを含む) を JSON 形式で出力します。
4.6.1 ibd2sdi — InnoDB テーブルスペース SDI 抽出ユーティリティ
- ibd2sdi は、file-per-table テーブルスペースファイル (.ibd ファイル)、general tablespace ファイル (.ibd ファイル)、system tablespace ファイル (ibdata* ファイル) およびデータディクショナリテーブルスペース (mysql.ibd) で実行できます。 一時テーブルスペースまたは undo テーブルスペースでの使用はサポートされていません。
- ibd2sdi は、実行時またはサーバーのオフライン中に使用できます。 SDI に関連する DDL 操作、ROLLBACK 操作および undo ログのパージ操作中に、ibd2sdi がテーブルスペースに格納されている SDI データの読取りに失敗する短い間隔がある場合があります。
- ibd2sdi は、指定されたテーブルスペースから SDI のコミットされていない読取りを実行します。 redo ログおよび undo ログはアクセスされません。
Sample Table
root@localhost [POC]> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
`note` varchar(255) NOT NULL,
`footnote` varchar(255) DEFAULT NULL,
`updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)root@localhost [POC]> select * from t1;
+----+-----------------------+------------------------------------+---------------------+
| id | note | footnote | updated_time |
+----+-----------------------+------------------------------------+---------------------+
| 1 | inplace | instant | 2022-07-28 03:10:31 |
| 2 | configure replica | MySQL 8.0 SOURCE and REPLICA | 2022-08-07 08:21:06 |
| 3 | configure replica2 | MySQL 8.0 SOURCE and REPLICA | 2022-08-07 08:27:46 |
| 4 | configure replica3 | MySQL 8.0 SET IGNORE SESSION TABLE | 2022-08-07 08:52:03 |
| 5 | Reconfirm | This is confirmation of replica | 2022-08-12 00:25:38 |
| 6 | binlog check 20221011 | confirm binlog | 2022-10-11 05:29:39 |
| 7 | binlog check 20221011 | confirm binlog shinya | 2022-10-11 05:39:45 |
+----+-----------------------+------------------------------------+---------------------+
7 rows in set (0.00 sec)Using ibd2sdi Utility
[root@POC]# ibd2sdi t1.ibd
["ibd2sdi"
,
{
"type": 1,
"id": 3958,
"object":
{
"mysqld_version_id": 80030,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Table",
"dd_object": {
"name": "t1",
"mysql_version_id": 80030,
"created": 20220728030926,
"last_altered": 20220728030926,
"hidden": 1,
"options": "avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;",
"columns": [
{
"name": "id",
"type": 4,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": true,
"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": false,
"srs_id_null": true,
"srs_id": 0,
"default_value": "AAAAAA==",
"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": "physical_pos=0;table_id=2254;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 2,
"column_type_utf8": "int",
"elements": [],
"collation_id": 8,
"is_explicit_collation": false
},
{
"name": "note",
"type": 16,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 2,
"char_length": 1020,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": true,
"default_value_null": false,
"srs_id_null": true,
"srs_id": 0,
"default_value": "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=",
"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": "physical_pos=3;table_id=2254;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "varchar(255)",
"elements": [],
"collation_id": 255,
"is_explicit_collation": false
},
{
"name": "footnote",
"type": 16,
"is_nullable": true,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 3,
"char_length": 1020,
"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": "interval_count=0;",
"se_private_data": "physical_pos=4;table_id=2254;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "varchar(255)",
"elements": [],
"collation_id": 255,
"is_explicit_collation": false
},
{
"name": "updated_time",
"type": 19,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 4,
"char_length": 19,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 0,
"has_no_default": false,
"default_value_null": false,
"srs_id_null": true,
"srs_id": 0,
"default_value": "gAAAAAA=",
"default_value_utf8_null": false,
"default_value_utf8": "CURRENT_TIMESTAMP",
"default_option": "CURRENT_TIMESTAMP",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "default=99ad78325a;physical_pos=5;table_id=2254;version_added=1;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "datetime",
"elements": [],
"collation_id": 8,
"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": 5,
"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": "physical_pos=1;table_id=2254;",
"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": 6,
"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": "physical_pos=2;table_id=2254;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "",
"elements": [],
"collation_id": 63,
"is_explicit_collation": false
}
],
"schema_ref": "POC",
"se_private_id": 2254,
"engine": "InnoDB",
"last_checked_for_upgrade_version_id": 0,
"comment": "",
"se_private_data": "autoinc=0;version=6;",
"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": false,
"is_generated": false,
"ordinal_position": 1,
"comment": "",
"options": "flags=0;",
"se_private_data": "id=2034;root=4;space_id=342;table_id=2254;trx_id=19077544;",
"type": 1,
"algorithm": 2,
"is_algorithm_explicit": false,
"is_visible": true,
"engine": "InnoDB",
"engine_attribute": "",
"secondary_engine_attribute": "",
"elements": [
{
"ordinal_position": 1,
"length": 4,
"order": 2,
"hidden": false,
"column_opx": 0
},
{
"ordinal_position": 2,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 4
},
{
"ordinal_position": 3,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 5
},
{
"ordinal_position": 4,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 1
},
{
"ordinal_position": 5,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 2
},
{
"ordinal_position": 6,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 3
}
],
"tablespace_ref": "POC/t1"
}
],
"foreign_keys": [],
"check_constraints": [],
"partitions": [],
"collation_id": 255
}
}
}
,
{
"type": 2,
"id": 346,
"object":
{
"mysqld_version_id": 80030,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Tablespace",
"dd_object": {
"name": "POC/t1",
"comment": "",
"options": "autoextend_size=0;encryption=N;",
"se_private_data": "flags=16417;id=342;server_version=80030;space_version=1;state=normal;",
"engine": "InnoDB",
"engine_attribute": "",
"files": [
{
"ordinal_position": 1,
"filename": "./POC/t1.ibd",
"se_private_data": "id=342;"
}
]
}
}
}
]
[root@ POC]# 上記の様に細かいObjectの情報(COLLATION等含めて)を確認する事が出来ます。
–skip-data, -s
シリアライズされたディクショナリ情報 (SDI) からの data フィールド値の取得をスキップし、SDI レコードの主キーである id および type フィールド値のみを取得します。
[root@POC]# ibd2sdi --skip-data t1.ibd
["ibd2sdi"
,
{
"type": 1,
"id": 3958
}
,
{
"type": 2,
"id": 346
}
]
[root@POC]# –id=#, -i #
指定されたテーブルまたはテーブルスペースオブジェクト ID に一致するシリアライズディクショナリ情報 (SDI) を取得します。 オブジェクト ID はオブジェクトタイプに対して一意です。 テーブルおよびテーブルスペースのオブジェクト ID は、mysql.tables および mysql.tablespace データディクショナリテーブルの id カラムにもあります。
[root@POC]# ibd2sdi --id=346 t1.ibd
["ibd2sdi"
,
{
"type": 2,
"id": 346,
"object":
{
"mysqld_version_id": 80030,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Tablespace",
"dd_object": {
"name": "POC/t1",
"comment": "",
"options": "autoextend_size=0;encryption=N;",
"se_private_data": "flags=16417;id=342;server_version=80030;space_version=1;state=normal;",
"engine": "InnoDB",
"engine_attribute": "",
"files": [
{
"ordinal_position": 1,
"filename": "./POC/t1.ibd",
"se_private_data": "id=342;"
}
]
}
}
}
]
[root@POC]#
[root@POC]# ibd2sdi --id=3958 t1.ibd
["ibd2sdi"
,
{
"type": 1,
"id": 3958,
"object":
{
"mysqld_version_id": 80030,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Table",
"dd_object": {
"name": "t1",
"mysql_version_id": 80030,
"created": 20220728030926,
"last_altered": 20220728030926,
"hidden": 1,
"options": "avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;",
"columns": [
{
"name": "id",
"type": 4,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": true,
"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": false,
"srs_id_null": true,
"srs_id": 0,
"default_value": "AAAAAA==",
"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": "physical_pos=0;table_id=2254;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 2,
"column_type_utf8": "int",
"elements": [],
"collation_id": 8,
"is_explicit_collation": false
},Information_schemaからは、ID等は取得出来なそう。Debugオプションを付けてコンパイルしてないので、MySQLデータベース内のデータディクショナリは参照出来ない。
root@localhost [information_schema]> select * from information_schema.tables where TABLE_SCHEMA = 'POC' and TABLE_NAME = 't1'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: POC
TABLE_NAME: t1
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 7
AVG_ROW_LENGTH: 2340
DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 0
AUTO_INCREMENT: 8
CREATE_TIME: 2022-07-28 03:09:26
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_0900_ai_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
1 row in set (0.00 sec)
root@localhost [information_schema]> –type=#, -t #
指定されたオブジェクト型に一致するシリアライズディクショナリ情報 (SDI) を取得します。 SDI は、テーブル (タイプ = 1) およびテーブルスペース (タイプ = 2) オブジェクトに提供されます。
[root@POC]# ibd2sdi --type=2 t1.ibd
["ibd2sdi"
,
{
"type": 2,
"id": 346,
"object":
{
"mysqld_version_id": 80030,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Tablespace",
"dd_object": {
"name": "POC/t1",
"comment": "",
"options": "autoextend_size=0;encryption=N;",
"se_private_data": "flags=16417;id=342;server_version=80030;space_version=1;state=normal;",
"engine": "InnoDB",
"engine_attribute": "",
"files": [
{
"ordinal_position": 1,
"filename": "./POC/t1.ibd",
"se_private_data": "id=342;"
}
]
}
}
}
]
[root@POC]# ibd2sdi --type=1 t1.ibd
["ibd2sdi"
,
{
"type": 1,
"id": 3958,
"object":
{
"mysqld_version_id": 80030,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Table",
"dd_object": {
"name": "t1",
"mysql_version_id": 80030,
"created": 20220728030926,
"last_altered": 20220728030926,
"hidden": 1,
"options": "avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;",
"columns": [
{
"name": "id",
"type": 4,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": true,
"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": false,
"srs_id_null": true,
"srs_id": 0,
"default_value": "AAAAAA==",
"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": "physical_pos=0;table_id=2254;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 2,
"column_type_utf8": "int",
"elements": [],
"collation_id": 8,
"is_explicit_collation": false
},
{
"name": "note",
"type": 16,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 2,
"char_length": 1020,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": true,
"default_value_null": false,
"srs_id_null": true,
"srs_id": 0,
"default_value": "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=",
"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": "physical_pos=3;table_id=2254;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "varchar(255)",
"elements": [],
"collation_id": 255,
"is_explicit_collation": false
},
{
"name": "footnote",
"type": 16,
"is_nullable": true,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 3,
"char_length": 1020,
"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": "interval_count=0;",
"se_private_data": "physical_pos=4;table_id=2254;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "varchar(255)",
"elements": [],
"collation_id": 255,
"is_explicit_collation": false
},
{
"name": "updated_time",
"type": 19,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 4,
"char_length": 19,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 0,
"has_no_default": false,
"default_value_null": false,
"srs_id_null": true,
"srs_id": 0,
"default_value": "gAAAAAA=",
"default_value_utf8_null": false,
"default_value_utf8": "CURRENT_TIMESTAMP",
"default_option": "CURRENT_TIMESTAMP",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "default=99ad78325a;physical_pos=5;table_id=2254;version_added=1;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "datetime",
"elements": [],
"collation_id": 8,
"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": 5,
"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": "physical_pos=1;table_id=2254;",
"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": 6,
"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": "physical_pos=2;table_id=2254;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "",
"elements": [],
"collation_id": 63,
"is_explicit_collation": false
}
],
"schema_ref": "POC",
"se_private_id": 2254,
"engine": "InnoDB",
"last_checked_for_upgrade_version_id": 0,
"comment": "",
"se_private_data": "autoinc=0;version=6;",
"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": false,
"is_generated": false,
"ordinal_position": 1,
"comment": "",
"options": "flags=0;",
"se_private_data": "id=2034;root=4;space_id=342;table_id=2254;trx_id=19077544;",
"type": 1,
"algorithm": 2,
"is_algorithm_explicit": false,
"is_visible": true,
"engine": "InnoDB",
"engine_attribute": "",
"secondary_engine_attribute": "",
"elements": [
{
"ordinal_position": 1,
"length": 4,
"order": 2,
"hidden": false,
"column_opx": 0
},
{
"ordinal_position": 2,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 4
},
{
"ordinal_position": 3,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 5
},
{
"ordinal_position": 4,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 1
},
{
"ordinal_position": 5,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 2
},
{
"ordinal_position": 6,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 3
}
],
"tablespace_ref": "POC/t1"
}
],
"foreign_keys": [],
"check_constraints": [],
"partitions": [],
"collation_id": 255
}
}
}
]
[root@POC]# –pretty, -p
SDI データを JSON プリティ印刷形式で出力します。 デフォルトで有効。 無効にすると、SDI は人間が読めるようになりませんが、サイズは小さくなります。 –skip-pretty を使用して無効にします。
[root@POC]# ibd2sdi --pretty --type=2 t1.ibd
["ibd2sdi"
,
{
"type": 2,
"id": 346,
"object":
{
"mysqld_version_id": 80030,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Tablespace",
"dd_object": {
"name": "POC/t1",
"comment": "",
"options": "autoextend_size=0;encryption=N;",
"se_private_data": "flags=16417;id=342;server_version=80030;space_version=1;state=normal;",
"engine": "InnoDB",
"engine_attribute": "",
"files": [
{
"ordinal_position": 1,
"filename": "./POC/t1.ibd",
"se_private_data": "id=342;"
}
]
}
}
}
]
[root@ip-172-30-2-38 POC]# ibd2sdi --skip-pretty --type=2 t1.ibd
["ibd2sdi"
,
{
"type": 2,
"id": 346,
"object":
{"mysqld_version_id":80030,"dd_version":80023,"sdi_version":80019,"dd_object_type":"Tablespace","dd_object":{"name":"POC/t1","comment":"","options":"autoextend_size=0;encryption=N;","se_private_data":"flags=16417;id=342;server_version=80030;space_version=1;state=normal;","engine":"InnoDB","engine_attribute":"","files":[{"ordinal_position":1,"filename":"./POC/t1.ibd","se_private_data":"id=342;"}]}}
}
]
[root@POC]#