MySQL8.0 根据ibd文件恢复表结构

先diss一下mysql ！！！垃圾，自己的文件不能像mssql那样附加数据，狗屎！！！

好了，比如我硬盘坏了数据能拷贝出来，进u盘pe吧mysql下面的data文件全部拷贝出来了

问题1：在什么地方拷贝data文件？

mysql安装的时候如果默认没有修改那边在你的c盘中，注意勾 “隐藏的项目”

 data文件路径：C:ProgramDataMySQLMySQL Server 8.0

 此刻你别以为你的表结构和数据回来了，mmp技术饭不好吃

-----下面这段话复制的

ibd2sdi
Oracle 将frm文件的信息及更多信息移动到叫做序列化字典信息（Serialized Dictionary Information，SDI），SDI被写在ibd文件内部，它是数据字典包含的信息的一个冗余副本。
为了从IBD文件中提取SDI信息，Oracle提供了一个应用程序 ibd2sdi。

这个工具不需要下载，mysql8自带的有，只要你配好环境变量就能到处用。

查看表结构
到存储ibd文件的目录下，执行下面的命令：

ibd2sdi --dump-file=***.txt  ***.ibd

　我的建议，先生成一个文件看看输出的txt中的格式，便于后面写jq的语法　

生成的txt中的数据比较乱，比较杂，所以我们需要jq来帮忙处理一下生成的文件

Windows安装jq

1.安装chocolatey

以管理员方式运行cmd。输入@"%SystemRoot%System32WindowsPowerShellv1.0powershell.exe" -NoProfile -InputFormat None -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%chocolateyin"

 

2.安装jq

运行cmd，输入命令：chocolatey install jq

看一下我没有处理的文本数据格式

下面时json，看着是不是很杂乱

  1 ["ibd2sdi"
  2 ,
  3 {
  4     "type": 1,
  5     "id": 544,
  6     "object":
  7         {
  8     "mysqld_version_id": 80025,
  9     "dd_version": 80023,
 10     "sdi_version": 80019,
 11     "dd_object_type": "Table",
 12     "dd_object": {
 13         "name": "sys_dict",
 14         "mysql_version_id": 80025,
 15         "created": 20210610071048,
 16         "last_altered": 20210610071048,
 17         "hidden": 1,
 18         "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;",
 19         "columns": [
 20             {
 21                 "name": "Id",
 22                 "type": 16,
 23                 "is_nullable": false,
 24                 "is_zerofill": false,
 25                 "is_unsigned": false,
 26                 "is_auto_increment": false,
 27                 "is_virtual": false,
 28                 "hidden": 1,
 29                 "ordinal_position": 1,
 30                 "char_length": 150,
 31                 "numeric_precision": 0,
 32                 "numeric_scale": 0,
 33                 "numeric_scale_null": true,
 34                 "datetime_precision": 0,
 35                 "datetime_precision_null": 1,
 36                 "has_no_default": true,
 37                 "default_value_null": false,
 38                 "srs_id_null": true,
 39                 "srs_id": 0,
 40                 "default_value": "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA==",
 41                 "default_value_utf8_null": true,
 42                 "default_value_utf8": "",
 43                 "default_option": "",
 44                 "update_option": "",
 45                 "comment": "编号",
 46                 "generation_expression": "",
 47                 "generation_expression_utf8": "",
 48                 "options": "interval_count=0;",
 49                 "se_private_data": "table_id=1185;",
 50                 "engine_attribute": "",
 51                 "secondary_engine_attribute": "",
 52                 "column_key": 2,
 53                 "column_type_utf8": "varchar(50)",
 54                 "elements": [],
 55                 "collation_id": 33,
 56                 "is_explicit_collation": true
 57             },
 58             {
 59                 "name": "ParentDictID",
 60                 "type": 4,
 61                 "is_nullable": true,
 62                 "is_zerofill": false,
 63                 "is_unsigned": false,
 64                 "is_auto_increment": false,
 65                 "is_virtual": false,
 66                 "hidden": 1,
 67                 "ordinal_position": 2,
 68                 "char_length": 11,
 69                 "numeric_precision": 10,
 70                 "numeric_scale": 0,
 71                 "numeric_scale_null": false,
 72                 "datetime_precision": 0,
 73                 "datetime_precision_null": 1,
 74                 "has_no_default": false,
 75                 "default_value_null": false,
 76                 "srs_id_null": true,
 77                 "srs_id": 0,
 78                 "default_value": "AAAAAA==",
 79                 "default_value_utf8_null": false,
 80                 "default_value_utf8": "0",
 81                 "default_option": "",
 82                 "update_option": "",
 83                 "comment": "父id",
 84                 "generation_expression": "",
 85                 "generation_expression_utf8": "",
 86                 "options": "interval_count=0;",
 87                 "se_private_data": "table_id=1185;",
 88                 "engine_attribute": "",
 89                 "secondary_engine_attribute": "",
 90                 "column_key": 1,
 91                 "column_type_utf8": "int",
 92                 "elements": [],
 93                 "collation_id": 8,
 94                 "is_explicit_collation": false
 95             }
 96             
 97         ],
 98         "schema_ref": "ms_ws_db",
 99         "se_private_id": 1185,
100         "engine": "InnoDB",
101         "last_checked_for_upgrade_version_id": 0,
102         "comment": "系统字典表",
103         "se_private_data": "",
104         "engine_attribute": "",
105         "secondary_engine_attribute": "",
106         "row_format": 2,
107         "partition_type": 0,
108         "partition_expression": "",
109         "partition_expression_utf8": "",
110         "default_partitioning": 0,
111         "subpartition_type": 0,
112         "subpartition_expression": "",
113         "subpartition_expression_utf8": "",
114         "default_subpartitioning": 0,
115         "indexes": [
116             {
117                 "name": "PRIMARY",
118                 "hidden": false,
119                 "is_generated": false,
120                 "ordinal_position": 1,
121                 "comment": "",
122                 "options": "flags=0;",
123                 "se_private_data": "id=274;root=4;space_id=126;table_id=1185;trx_id=21099;",
124                 "type": 1,
125                 "algorithm": 2,
126                 "is_algorithm_explicit": false,
127                 "is_visible": true,
128                 "engine": "InnoDB",
129                 "engine_attribute": "",
130                 "secondary_engine_attribute": "",
131                 "elements": [
132                     {
133                         "ordinal_position": 1,
134                         "length": 150,
135                         "order": 2,
136                         "hidden": false,
137                         "column_opx": 0
138                     },
139                     {
140                         "ordinal_position": 2,
141                         "length": 4294967295,
142                         "order": 2,
143                         "hidden": true,
144                         "column_opx": 19
145                     },
146                     {
147                         "ordinal_position": 3,
148                         "length": 4294967295,
149                         "order": 2,
150                         "hidden": true,
151                         "column_opx": 20
152                     },
153                     {
154                         "ordinal_position": 4,
155                         "length": 4294967295,
156                         "order": 2,
157                         "hidden": true,
158                         "column_opx": 1
159                     },
160                     {
161                         "ordinal_position": 5,
162                         "length": 4294967295,
163                         "order": 2,
164                         "hidden": true,
165                         "column_opx": 2
166                     },
167                     {
168                         "ordinal_position": 6,
169                         "length": 4294967295,
170                         "order": 2,
171                         "hidden": true,
172                         "column_opx": 3
173                     },
174                     {
175                         "ordinal_position": 7,
176                         "length": 4294967295,
177                         "order": 2,
178                         "hidden": true,
179                         "column_opx": 4
180                     },
181                     {
182                         "ordinal_position": 8,
183                         "length": 4294967295,
184                         "order": 2,
185                         "hidden": true,
186                         "column_opx": 5
187                     },
188                     {
189                         "ordinal_position": 9,
190                         "length": 4294967295,
191                         "order": 2,
192                         "hidden": true,
193                         "column_opx": 6
194                     },
195                     {
196                         "ordinal_position": 10,
197                         "length": 4294967295,
198                         "order": 2,
199                         "hidden": true,
200                         "column_opx": 7
201                     },
202                     {
203                         "ordinal_position": 11,
204                         "length": 4294967295,
205                         "order": 2,
206                         "hidden": true,
207                         "column_opx": 8
208                     },
209                     {
210                         "ordinal_position": 12,
211                         "length": 4294967295,
212                         "order": 2,
213                         "hidden": true,
214                         "column_opx": 9
215                     },
216                     {
217                         "ordinal_position": 13,
218                         "length": 4294967295,
219                         "order": 2,
220                         "hidden": true,
221                         "column_opx": 10
222                     },
223                     {
224                         "ordinal_position": 14,
225                         "length": 4294967295,
226                         "order": 2,
227                         "hidden": true,
228                         "column_opx": 11
229                     },
230                     {
231                         "ordinal_position": 15,
232                         "length": 4294967295,
233                         "order": 2,
234                         "hidden": true,
235                         "column_opx": 12
236                     },
237                     {
238                         "ordinal_position": 16,
239                         "length": 4294967295,
240                         "order": 2,
241                         "hidden": true,
242                         "column_opx": 13
243                     },
244                     {
245                         "ordinal_position": 17,
246                         "length": 4294967295,
247                         "order": 2,
248                         "hidden": true,
249                         "column_opx": 14
250                     },
251                     {
252                         "ordinal_position": 18,
253                         "length": 4294967295,
254                         "order": 2,
255                         "hidden": true,
256                         "column_opx": 15
257                     },
258                     {
259                         "ordinal_position": 19,
260                         "length": 4294967295,
261                         "order": 2,
262                         "hidden": true,
263                         "column_opx": 16
264                     },
265                     {
266                         "ordinal_position": 20,
267                         "length": 4294967295,
268                         "order": 2,
269                         "hidden": true,
270                         "column_opx": 17
271                     },
272                     {
273                         "ordinal_position": 21,
274                         "length": 4294967295,
275                         "order": 2,
276                         "hidden": true,
277                         "column_opx": 18
278                     }
279                 ],
280                 "tablespace_ref": "ms_ws_db/sys_dict"
281             }
282         ],
283         "foreign_keys": [],
284         "check_constraints": [],
285         "partitions": [],
286         "collation_id": 45
287     }
288 }
289 }
290 ,
291 {
292     "type": 2,
293     "id": 131,
294     "object":
295         {
296     "mysqld_version_id": 80025,
297     "dd_version": 80023,
298     "sdi_version": 80019,
299     "dd_object_type": "Tablespace",
300     "dd_object": {
301         "name": "ms_ws_db/sys_dict",
302         "comment": "",
303         "options": "autoextend_size=0;encryption=N;",
304         "se_private_data": "flags=16417;id=126;server_version=80025;space_version=1;state=normal;",
305         "engine": "InnoDB",
306         "engine_attribute": "",
307         "files": [
308             {
309                 "ordinal_position": 1,
310                 "filename": ".\ms_ws_db\sys_dict.ibd",
311                 "se_private_data": "id=126;"
312             }
313         ]
314     }
315 }
316 }
317 ]
View Code

格式化输出想要的字段

ibd2sdi sys_dict.ibd |jq  ".[]|.object?|.dd_object|({table:.name,comment:.comment},(.columns|.[]|{name:.name,column_type_utf8,comment}))" > sys_dict.json

 结果

{
  "table": "sys_dict",
  "comment": "系统字典表"
}
{
  "name": "Id",
  "column_type_utf8": "varchar(50)",
  "comment": "编号"
}
{
  "name": "ParentDictID",
  "column_type_utf8": "int",
  "comment": "父id"
}
{
  "name": "Fiexd_action",
  "column_type_utf8": "int",
  "comment": "1:系统参数不可修改0：不是系统参数可以修改"
}
{
  "name": "DictName",
  "column_type_utf8": "varchar(255)",
  "comment": "字典名称 如：SYS_USER_POST"
}
{
  "name": "DictLable",
  "column_type_utf8": "varchar(255)",
  "comment": "参数中文 如：质检"
}
{
  "name": "DictValue",
  "column_type_utf8": "varchar(255)",
  "comment": "中文对应的值 如：1"
}
{
  "name": "DictRemark",
  "column_type_utf8": "varchar(500)",
  "comment": "备注"
}
{
  "name": "DeleteMark",
  "column_type_utf8": "bit(1)",
  "comment": "删除状态"
}
{
  "name": "EnabledMark",
  "column_type_utf8": "bit(1)",
  "comment": "启用状态"
}
{
  "name": "Description",
  "column_type_utf8": "varchar(255)",
  "comment": "备注描述"
}
{
  "name": "CreatorTime",
  "column_type_utf8": "datetime",
  "comment": "创建时间"
}
{
  "name": "CreatorUserId",
  "column_type_utf8": "varchar(50)",
  "comment": "创建用户"
}
{
  "name": "CreatorName",
  "column_type_utf8": "varchar(255)",
  "comment": "用户名称"
}
{
  "name": "LastModifyTime",
  "column_type_utf8": "datetime",
  "comment": "最后一次修改时间"
}
{
  "name": "LastModifyUserId",
  "column_type_utf8": "varchar(50)",
  "comment": "最后一次修改用户"
}
{
  "name": "DeleteTime",
  "column_type_utf8": "datetime",
  "comment": "删除时间"
}
{
  "name": "LastModifyUserNmae",
  "column_type_utf8": "varchar(255)",
  "comment": "最后一次修改用户名称"
}
{
  "name": "DeleteUserId",
  "column_type_utf8": "varchar(50)",
  "comment": "删除用户"
}
{
  "name": "DeleteUserNmae",
  "column_type_utf8": "varchar(255)",
  "comment": "删除用户名称"
}
{
  "name": "DB_TRX_ID",
  "column_type_utf8": "",
  "comment": ""
}
{
  "name": "DB_ROLL_PTR",
  "column_type_utf8": "",
  "comment": ""
}
{
  "table": "ms_ws_db/sys_dict",
  "comment": ""
}
View Code

参阅：

jqplay

MySQL8.0 ibd2sdi 根据ibd文件恢复表结构

Windows安装jq

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/nimeide/p/14930686.html