首页 >> 工具知识
多文件表空间处理（如
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参数跳过校验，但仅限紧急情况！
最新文章
mysql分页问题2025-08-04
千万数据先insert和先建索引哪个快2025-08-04
MySQL 中大小表关联查询如何优化2025-08-04
sql技巧-每个班年龄排前两名的人2025-08-03
MySQL 导致 cpu 飙升的话，要怎么处理呢？2025-07-29
MySQL 中为千万级大表添加字段2025-07-29
mysql中百万级别以上的数据如何删除2025-07-29
分库分表带来的问题2025-07-29
mysql中常用的分库分表中间件有哪些2025-07-29
mysql不停机扩容2025-07-29