今天早上启动了 phpstudy 软件中的 MySQL 8.0 数据库时，发现它启动后约过了 3 秒就自行关闭了。接着又自动启动然后再次关闭，似乎陷入了一个“开-闭-开”的循环。这导致我无法正常使用数据库。在尝试修改配置文件和端口号无果后，我决定卸载数据库并重新安装。根据之前的经验，我直接备份了 MySQL 安装路径下的 data 文件夹。之前使用的是 MySQL 5.7 版本，直接备份 data 文件夹下的文件可以保留数据库的库名、表名、字段、索引等信息。在重新安装后，将之前备份的数据直接导入新的数据库的 data 文件夹下也是顺利的，使用像 Navicat 这样的 MySQL 可视化工具可以看到表名和表结构。但是在 MySQL 8.0 及以上版本，情况就有所不同了，这导致后续出现了很多问题。
在 MySQL 8.0 中，数据库文件存储在 data 文件夹中，每个目录代表一个数据库。在每个数据库目录下，有多个 .ibd 文件，每个 .ibd 文件对应一张表，文件名即表名。.ibd 文件是 MySQL 的表数据文件，而在 MySQL 8 之前，表定义存储在 .frm 后缀的文件中，表数据和表索引存储在 .ibd 文件中。所有这些文件都在 MySQL 的 data 目录下，即数据库名所在的目录中。闲来无事记录一下。
查看mysql数据库data文件夹路径：
1 | show variables like ‘%datadir%' |
.ibd文件是以二进制形式存储的，这个时候想要查看存储内容需要用mysql8自带的 ibd2sdi命令 进行解析，首先查看是否能执行这个命令，找不到命令或者不存在就需要配置系统环境变量了，起初我尝试到phpstudy环境下的mysql8的安装目录找bin目录，看看能否执行ibd2sdi命令，不行于是我到官网下载一个mysql在本地安装，接着配置环境变量。
ibd2sdi 命令：
1 | G:PhpStudyphpstudy_proExtensionsMySQL8.0.12dataruibaiqiche>ibd2sdi --dump-file rb_position.txt rb_position.ibd |
解析内容：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 | ["ibd2sdi",{ "type": 1, "id": 1929, "object": { "mysqld_version_id": 80012, "dd_version": 80012, "sdi_version": 1, "dd_object_type": "Table", "dd_object": { "name": "rb_position", "mysql_version_id": 80012, "created": 20231214074915, "last_altered": 20231214074915, "hidden": 1, "options": "avg_row_length=0;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": "ID", "generation_expression": "", "generation_expression_utf8": "", "options": "interval_count=0;", "se_private_data": "table_id=2219;", "column_key": 2, "column_type_utf8": "int(11)", "elements": [], "collation_id": 8, "is_explicit_collation": false }, { "name": "position", "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": 400, "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=2219;", "column_key": 1, "column_type_utf8": "varchar(100)", "elements": [], "collation_id": 45, "is_explicit_collation": false }, { "name": "createtime", "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=2219;", "column_key": 1, "column_type_utf8": "datetime", "elements": [], "collation_id": 8, "is_explicit_collation": false }, { "name": "updatetime", "type": 19, "is_nullable": true, "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": 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=2219;", "column_key": 1, "column_type_utf8": "datetime", "elements": [], "collation_id": 8, "is_explicit_collation": false }, { "name": "deletetime", "type": 19, "is_nullable": true, "is_zerofill": false, "is_unsigned": false, "is_auto_increment": false, "is_virtual": false, "hidden": 1, "ordinal_position": 5, "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=2219;", "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": 6, "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=2219;", "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": 7, "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=2219;", "column_key": 1, "column_type_utf8": "", "elements": [], "collation_id": 63, "is_explicit_collation": false } ], "schema_ref": "ruibaiqiche", "se_private_id": 2219, "engine": "InnoDB", "comment": "职位表", "se_private_data": "autoinc=0;version=2;", "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=1732;root=4;space_id=1162;table_id=2219;trx_id=153333;", "type": 1, "algorithm": 2, "is_algorithm_explicit": false, "is_visible": true, "engine": "InnoDB", "elements": [ { "ordinal_position": 1, "length": 4, "order": 2, "column_opx": 0 }, { "ordinal_position": 2, "length": 4294967295, "order": 2, "column_opx": 5 }, { "ordinal_position": 3, "length": 4294967295, "order": 2, "column_opx": 6 }, { "ordinal_position": 4, "length": 4294967295, "order": 2, "column_opx": 1 }, { "ordinal_position": 5, "length": 4294967295, "order": 2, "column_opx": 2 }, { "ordinal_position": 6, "length": 4294967295, "order": 2, "column_opx": 3 }, { "ordinal_position": 7, "length": 4294967295, "order": 2, "column_opx": 4 } ], "tablespace_ref": "ruibaiqiche/rb_position" } ], "foreign_keys": [], "partitions": [], "collation_id": 45 }}},{ "type": 2, "id": 1167, "object": { "mysqld_version_id": 80012, "dd_version": 80012, "sdi_version": 1, "dd_object_type": "Tablespace", "dd_object": { "name": "ruibaiqiche/rb_position", "comment": "", "options": "", "se_private_data": "flags=16417;id=1162;server_version=80012;space_version=1;", "engine": "InnoDB", "files": [ { "ordinal_position": 1, "filename": ".\ruibaiqiche\rb_position.ibd", "se_private_data": "id=1162;" } ] }}}] |
后面想要恢复数据库中的表结构的话，只能看解析后的字段名进行创建表了。解析后的.idb文件是一个json格式的数据，再此基础上key值基本不变，可以用代码实现获取表名和字段名再拼接成SQL语句，接着再拿到数据库中进行创建表。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | function test(){ // 获取传入的json //$data = $this->request->param(); // 替换为你的 JSON 数据 $jsonData = 'YOUR_JSON_DATA_HERE'; // 解析 JSON 数据 $data = json_decode($jsonData, true); // 提取表名和字段信息 $tableName = $data[1]['object']['dd_object']['name']; $columns = $data[1]['object']['dd_object']['columns']; // 构建创建表的 SQL 查询语句 $sql = "CREATE TABLE $tableName ("; foreach ($columns as $column) { $columnName = $column['name']; // 如果字段名不是 DB_TRX_ID 和 DB_ROLL_PTR，则构建该字段的 SQL if ($columnName !== 'DB_TRX_ID' && $columnName !== 'DB_ROLL_PTR' && $columnName !== 'DB_ROW_ID') { $columnType = $column['column_type_utf8']; $isNullable = $column['is_nullable'] ? 'NULL' : 'NOT NULL'; $default = ($column['default_value_null'] || $column['default_value_utf8_null']) ? '' : "DEFAULT '{$column['default_value']}'"; // 构建列的注释，如果注释为空，不包含 COMMENT 部分 $comment = isset($column['comment']) && !empty($column['comment']) ? "COMMENT '{$column['comment']}'" : ''; // 构建列的 SQL $sql .= "$columnName $columnType $isNullable $default $comment, "; } } // 去除最后的逗号和空格 $sql = rtrim($sql, ', ') . ");"; echo $sql; } |
到此这篇关于mysql8.0 .ibd文件恢复表结构的实现的文章就介绍到这了,更多相关mysql .ibd文件恢复表结构内容请搜索IT俱乐部以前的文章或继续浏览下面的相关文章希望大家以后多多支持IT俱乐部！
