1.使用背景
mysql 8.0.31升级mysql 8.3.0失败，升级前没有数据备份，导致退回mysql 8.0.31后数据库服务启动不了了，再次提醒操作数据库前一定要备份。
2.ibd恢复数据需要先根据原来表结构建表
最好是保存有原表的结构，没有的话就需要从ibd文件中获取表结构
确保C:\Program Files\MySQL\MySQL Server 8.0.31\bin已经加入环境变量path
这样我们就能使用mysql带的ibd2sdi.exe工具获取表结构信息
在cmd中执行
ibd2sdi --dump-file mix_data_re_translate.txt mix_data_re_translate.ibd
会得到文件mix_data_re_translate.txt
["ibd2sdi"
,
{
"type": 1,
"id": 1374,
"object":
{
"mysqld_version_id": 80031,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Table",
"dd_object": {
"name": "mix_data_re_translate",
"mysql_version_id": 80031,
"created": 20230331020755,
"last_altered": 20230331020755,
"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": "mix_data_re_translate_id",
"type": 4,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": true,
"is_auto_increment": true,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 1,
"char_length": 10,
"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": "table_id=2104;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 2,
"column_type_utf8": "int unsigned",
"elements": [],
"collation_id": 255,
"is_explicit_collation": false
},
{
"name": "mix_data_id",
"type": 4,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 2,
"char_length": 11,
"numeric_precision": 10,
"numeric_scale": 0,
"numeric_scale_null": false,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": true,
"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": "table_id=2104;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 3,
"column_type_utf8": "int",
"elements": [],
"collation_id": 255,
"is_explicit_collation": false
},
{
"name": "translate_title",
"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": 1200,
"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=2104;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "varchar(300)",
"elements": [],
"collation_id": 255,
"is_explicit_collation": false
},
{
"name": "translate_abstract",
"type": 16,
"is_nullable": true,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 4,
"char_length": 4000,
"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=2104;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "varchar(1000)",
"elements": [],
"collation_id": 255,
"is_explicit_collation": false
},
{
"name": "translate_content",
"type": 26,
"is_nullable": true,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 5,
"char_length": 4294967295,
"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=2104;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "longtext",
"elements": [],
"collation_id": 255,
"is_explicit_collation": false
},
{
"name": "translate_keyword",
"type": 16,
"is_nullable": true,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 6,
"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=2104;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "varchar(200)",
"elements": [],
"collation_id": 255,
"is_explicit_collation": false
},
{
"name": "last_update_time",
"type": 18,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 7,
"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": "AAAAAA==",
"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": "table_id=2104;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 4,
"column_type_utf8": "timestamp",
"elements": [],
"collation_id": 8,
"is_explicit_collation": false
},
{
"name": "website_id",
"type": 4,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 2,
"char_length": 10,
"numeric_precision": 10,
"numeric_scale": 0,
"numeric_scale_null": false,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": true,
"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": "table_id=2112;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 4,
"column_type_utf8": "int",
"elements": [],
"collation_id": 255,
"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": 8,
"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=2104;",
"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": 9,
"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=2104;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "",
"elements": [],
"collation_id": 63,
"is_explicit_collation": false
}
],
"schema_ref": "caiji",
"se_private_id": 2104,
"engine": "InnoDB",
"last_checked_for_upgrade_version_id": 0,
"comment": "",
"se_private_data": "autoinc=5537;version=0;",
"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=4709;root=4;space_id=1042;table_id=2104;trx_id=24971846;",
"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": 7
},
{
"ordinal_position": 3,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 8
},
{
"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
},
{
"ordinal_position": 7,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 4
},
{
"ordinal_position": 8,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 5
},
{
"ordinal_position": 9,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 6
}
],
"tablespace_ref": "caiji/mix_data_re_translate"
},
{
"name": "uk_mix_data_id",
"hidden": false,
"is_generated": false,
"ordinal_position": 2,
"comment": "",
"options": "flags=0;",
"se_private_data": "id=4710;root=5;space_id=1042;table_id=2104;trx_id=24971846;",
"type": 2,
"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": 1
},
{
"ordinal_position": 2,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 0
}
],
"tablespace_ref": "caiji/mix_data_re_translate"
},
{
"name": "idx_lastUpdateTime",
"hidden": false,
"is_generated": false,
"ordinal_position": 3,
"comment": "",
"options": "flags=0;",
"se_private_data": "id=4711;root=6;space_id=1042;table_id=2104;trx_id=24971846;",
"type": 3,
"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": 6
},
{
"ordinal_position": 2,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 0
}
],
"tablespace_ref": "caiji/mix_data_re_translate"
},
{
"name": "FK_website_websiteColumn",
"hidden": false,
"is_generated": false,
"ordinal_position": 2,
"comment": "",
"options": "flags=0;",
"se_private_data": "id=4782;root=5;space_id=1050;table_id=2112;trx_id=30338966;",
"type": 3,
"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": 7
},
{
"ordinal_position": 2,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 0
}
],
"tablespace_ref": "caiji/website_column"
}
],
"foreign_keys": [
{
"name": "FK_website_websiteColumn",
"match_option": 1,
"update_rule": 1,
"delete_rule": 1,
"unique_constraint_name": "PRIMARY",
"referenced_table_catalog_name": "def",
"referenced_table_schema_name": "caiji",
"referenced_table_name": "website",
"elements": [
{
"column_opx": 1,
"ordinal_position": 1,
"referenced_column_name": "website_id"
}
]
}
],
"check_constraints": [],
"partitions": [],
"collation_id": 255
}
}
}
,
{
"type": 2,
"id": 1047,
"object":
{
"mysqld_version_id": 80031,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Tablespace",
"dd_object": {
"name": "caiji/mix_data_re_translate",
"comment": "",
"options": "autoextend_size=0;encryption=N;",
"se_private_data": "flags=16417;id=1042;server_version=80031;space_version=1;state=normal;",
"engine": "InnoDB",
"engine_attribute": "",
"files": [
{
"ordinal_position": 1,
"filename": ".\\caiji\\mix_data_re_translate.ibd",
"se_private_data": "id=1042;"
}
]
}
}
}
]
需要关注的是columns中的:
is_nullable (false时需要加上NOT NULL)
is_unsigned（false时需要加上 unsigned）
column_type_utf8 表示字段的数据类型
default_value_utf8 表示字段的默认值
注意只关注"hidden": 1 的字段，"hidden": 2的字段不是原来表中的自定义字段
indexes中的时索引相关的结构：
name是索引名称
elements中是索引影响的字段，只关注"hidden": false的字段
column_opx代表的是相关字段的位置，第一个位置为0
foreign_keys里存储的是外键相关的数据结构
可以得到建表语句：
CREATE TABLE `mix_data_re_translate` (
`mix_data_re_translate_id` int unsigned NOT NULL AUTO_INCREMENT,
`mix_data_id` int NOT NULL,
`translate_title` varchar(300) DEFAULT NULL,
`translate_abstract` varchar(1000) DEFAULT NULL,
`translate_content` longtext DEFAULT NULL,
`translate_keyword` varchar(200) DEFAULT NULL,
`last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`website_id` int NOT NULL,
PRIMARY KEY (`mix_data_re_translate_id`),
UNIQUE KEY `uk_mix_data_id` (`mix_data_id`) USING BTREE,
KEY `idx_lastUpdateTime` (`last_update_time`) USING BTREE,
KEY `FK_website_websiteColumn` (`website_id`),
CONSTRAINT `FK_website_websiteColumn` FOREIGN KEY (`website_id`) REFERENCES `website` (`website_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;3.恢复数据
1）在数据库中执行建表语句
2）关闭mysql服务将原来的.ibd文件覆盖新建的同名的.ibd文件
3）启动mysql服务执行一下语句
use caiji;
#移除表空间
ALTER TABLE mix_data_re_translate DISCARD TABLESPACE;
#重新导入表空间
ALTER TABLE mix_data_re_translate IMPORT TABLESPACE;然后查询表数据，能查到数据了，就说明恢复成功了！
注意：本文归作者所有，未经作者允许，不得转载
