MySQL8.0 根据ibd文件恢复表结构
先diss一下mysql ！！！垃圾，自己的文件不能像mssql那样附加数据，狗屎！！！
好了，比如我硬盘坏了数据能拷贝出来，进u盘pe吧mysql下面的data文件全部拷贝出来了
问题1：在什么地方拷贝data文件？
mysql安装的时候如果默认没有修改那边在你的c盘中，注意勾 “隐藏的项目”
data文件路径：C:\ProgramData\MySQL\MySQL 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%\System32\WindowsPowerShell\v1.0\powershell.exe" -NoProfile -InputFormat None -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin"
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\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA==", 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 ]
格式化输出想要的字段
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": "" }
参阅：
