mysql命令-ibd2sdi

1. 核心功能

• 作用：从 InnoDB 表空间文件（.ibd）中提取序列化字典信息（SDI）。SDI 以 JSON 格式存储表/列定义等元数据，替代旧版 MySQL 的 .frm 文件。

• 支持文件类型：

• 独立表空间（如 表名.ibd）

• 通用表空间（如 tablespace.ibd）

• 系统表空间（ibdata*）

• 数据字典表空间（mysql.ibd）

• 不支持：临时表空间或 Undo 表空间。

2. 典型使用场景

• 表结构恢复：当 .frm 文件丢失时，从 .ibd 文件重建表结构。

• 离线元数据分析：不启动 MySQL 即可查看表定义。

• 修复数据字典：利用 SDI 冗余恢复损坏的字典。

• 跨版本迁移：分析不同 MySQL 版本的表结构（SDI 不会自动更新）。

3. 命令语法与参数

ibd2sdi [选项] 文件1.ibd [文件2.ibd ...]

参数

说明

示例

--dump-file=<文件>

输出到文件（默认输出到终端）

ibd2sdi --dump-file=输出.txt t1.ibd

--skip-data

仅提取对象 ID 和类型，跳过详细元数据

ibd2sdi --skip-data t1.ibd

--id=<对象ID>

按对象 ID 过滤（如 mysql.tables 中的表 ID）

ibd2sdi --id=123 t1.ibd

--type=<类型>

按对象类型过滤（1=表，2=表空间）

ibd2sdi --type=1 t1.ibd

--no-check

跳过校验和验证（用于文件损坏时）

ibd2sdi --no-check 损坏文件.ibd

--pretty/--skip-pretty

启用/禁用 JSON 格式化（默认启用）

ibd2sdi --skip-pretty t1.ibd

多文件表空间处理（如 ibdata1, ibdata2）：

ibd2sdi ibdata1 ibdata2  # 必须按页号升序排列

4. 输出示例

../../mysql-8.0.20-macos10.15-x86_64/bin/ibd2sdi test2.ibd

返回值:

["ibd2sdi"
,
{
	"type": 1,
	"id": 222,
	"object":
		{
    "mysqld_version_id": 80020,
    "dd_version": 80017,
    "sdi_version": 80019,
    "dd_object_type": "Table",
    "dd_object": {
        "name": "test2",
        "mysql_version_id": 80020,
        "created": 20240313142147,
        "last_altered": 20240313142147,
        "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": 9,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": true,
                "is_virtual": false,
                "hidden": 1,
                "ordinal_position": 1,
                "char_length": 20,
                "numeric_precision": 19,
                "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": "AAAAAAAAAAA=",
                "default_value_utf8_null": true,
                "default_value_utf8": "",
                "default_option": "",
                "update_option": "",
                "comment": "??id",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "interval_count=0;",
                "se_private_data": "table_id=1098;",
                "column_key": 2,
                "column_type_utf8": "bigint",
                "elements": [],
                "collation_id": 8,
                "is_explicit_collation": false
            },
            {
                "name": "name",
                "type": 16,
                "is_nullable": true,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": 1,
                "ordinal_position": 2,
                "char_length": 800,
                "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": "table_id=1098;",
                "column_key": 1,
                "column_type_utf8": "varchar(200)",
                "elements": [],
                "collation_id": 45,
                "is_explicit_collation": false
            },
            {
                "name": "create_date",
                "type": 19,
                "is_nullable": true,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": 1,
                "ordinal_position": 3,
                "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": 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=1098;",
                "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": 4,
                "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=1098;",
                "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": 5,
                "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=1098;",
                "column_key": 1,
                "column_type_utf8": "",
                "elements": [],
                "collation_id": 63,
                "is_explicit_collation": false
            }
        ],
        "schema_ref": "testdb",
        "se_private_id": 1098,
        "engine": "InnoDB",
        "last_checked_for_upgrade_version_id": 80020,
        "comment": "????1",
        "se_private_data": "autoinc=0;version=0;",
        "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=99;root=3;space_id=61;table_id=1098;trx_id=0;",
                "type": 1,
                "algorithm": 2,
                "is_algorithm_explicit": false,
                "is_visible": true,
                "engine": "InnoDB",
                "elements": [
                    {
                        "ordinal_position": 1,
                        "length": 8,
                        "order": 2,
                        "hidden": false,
                        "column_opx": 0
                    },
                    {
                        "ordinal_position": 2,
                        "length": 4294967295,
                        "order": 2,
                        "hidden": true,
                        "column_opx": 3
                    },
                    {
                        "ordinal_position": 3,
                        "length": 4294967295,
                        "order": 2,
                        "hidden": true,
                        "column_opx": 4
                    },
                    {
                        "ordinal_position": 4,
                        "length": 4294967295,
                        "order": 2,
                        "hidden": true,
                        "column_opx": 1
                    },
                    {
                        "ordinal_position": 5,
                        "length": 4294967295,
                        "order": 2,
                        "hidden": true,
                        "column_opx": 2
                    }
                ],
                "tablespace_ref": "testdb/test2"
            }
        ],
        "foreign_keys": [],
        "check_constraints": [],
        "partitions": [],
        "collation_id": 45
    }
}
}
,
{
	"type": 2,
	"id": 38,
	"object":
		{
    "mysqld_version_id": 80020,
    "dd_version": 80017,
    "sdi_version": 80019,
    "dd_object_type": "Tablespace",
    "dd_object": {
        "name": "testdb/test2",
        "comment": "",
        "options": "encryption=N;",
        "se_private_data": "flags=16417;id=61;server_version=80020;space_version=1;state=normal;",
        "engine": "InnoDB",
        "files": [
            {
                "ordinal_position": 1,
                "filename": "./testdb/test2.ibd",
                "se_private_data": ""
            }
        ]
    }
}
}
]

5. 注意事项与限制

• 操作时机：执行 DDL（如 ALTER TABLE）时可能短暂失败，需重试。

• 分区表：SDI 仅存储在第一分区的 .ibd 文件中。

• 字符编码：若表定义含非 UTF8 字符（如中文注释），输出可能乱码。

• 版本兼容：

• MySQL 5.7 升级到 8.0 后需手动重建表以生成 SDI。

• SDI 不随版本升级自动更新。

• 文件权限：需操作系统权限读取 .ibd 文件。

紧急恢复提示：对损坏文件使用 --no-check 参数跳过校验，但仅限紧急情况！