今天早上启动了 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文件夹路径：
show variables like ‘%datadir%’ .ibd文件是以二进制形式存储的，这个时候想要查看存储内容需要用mysql8自带的 ibd2sdi命令 进行解析，首先查看是否能执行这个命令，找不到命令或者不存在就需要配置系统环境变量了，起初我尝试到phpstudy环境下的mysql8的安装目录找bin目录，看看能否执行ibd2sdi命令，不行于是我到官网下载一个mysql在本地安装，接着配置环境变量。
ibd2sdi 命令：
G:\PhpStudy\phpstudy_pro\Extensions\MySQL8.0.12\data\ruibaiqiche>ibd2sdi --dump-file rb_position.txt rb_position.ibd解析内容：
["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语句，接着再拿到数据库中进行创建表。
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;
}今日一结：没事不要删数据库，卸载数据库，记得多备份。
7122
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
