MySQL8.0之后，表结构和表数据统一放到了ibd文件中，该文件包含了表的结构和索引以及数据信息。
MySQL支持利用ibd2sdi来进行解析ibd文件
下载地址：https://github.com/ddcw/ibd2sql/archive/refs/tags/v1.5.tar.gz
[root@node01 ibd2sql-1.5]# ibd2sdi /data/mysqldata/data/test01/test01.ibd
["ibd2sdi"
,
{
"type": 1,
"id": 418,
"object":
{
"mysqld_version_id": 80028,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Table",
"dd_object": {
"name": "test01",
"mysql_version_id": 80028,
"created": 20240822071004,
"last_altered": 20240822071004,
"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": "id",
"type": 4,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"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": 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=1111;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 2,
"column_type_utf8": "int",
"elements": [],
"collation_id": 33,
"is_explicit_collation": false
},
{
"name": "name",
"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": 96,
"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=1111;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "varchar(32)",
"elements": [],
"collation_id": 33,
"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": 3,
"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=1111;",
"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": 4,
"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=1111;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "",
"elements": [],
"collation_id": 63,
"is_explicit_collation": false
}
],
"schema_ref": "test01",
"se_private_id": 1111,
"engine": "InnoDB",
"last_checked_for_upgrade_version_id": 0,
"comment": "",
"se_private_data": "",
"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=222;root=4;space_id=50;table_id=1111;trx_id=9515;",
"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": 2
},
{
"ordinal_position": 3,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 3
},
{
"ordinal_position": 4,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 1
}
],
"tablespace_ref": "test01/test01"
}
],
"foreign_keys": [],
"check_constraints": [],
"partitions": [],
"collation_id": 33
}
}
}
,
{
"type": 2,
"id": 55,
"object":
{
"mysqld_version_id": 80028,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Tablespace",
"dd_object": {
"name": "test01/test01",
"comment": "",
"options": "autoextend_size=0;encryption=N;",
"se_private_data": "flags=16417;id=50;server_version=80028;space_version=1;state=normal;",
"engine": "InnoDB",
"engine_attribute": "",
"files": [
{
"ordinal_position": 1,
"filename": "./test01/test01.ibd",
"se_private_data": "id=50;"
}
]
}
}
}
]
解析出的文件，为表结构和表数据的json文件，需要经过二次梳理，才可以导入数据库使用。
现利用开源工具ibd2sql解析ibd文件，ibd2sq工具，依赖python3环境，使用前请安装python3。
ibd2sql采用python编写，无需编译即可运行。
示例：
[root@node01 ibd2sql-1.5]# ls -rtl
total 84
-rw-rw-r-- 1 root root 27451 Jul 10 11:20 test.sh
-rw-rw-r-- 1 root root 2739 Jul 10 11:20 README.md
-rw-rw-r-- 1 root root 6558 Jul 10 11:20 main.py
-rw-rw-r-- 1 root root 35149 Jul 10 11:20 LICENSE
drwxrwxr-x. 3 root root 4096 Jul 10 11:20 ibd2sql
drwxrwxr-x. 2 root root 99 Jul 10 11:20 docs
-rw-------. 1 root root 0 Jul 24 16:06 test.txt
-rw-r--r-- 1 root root 1142 Aug 23 09:44 customers.sql
[root@node01 ibd2sql-1.5]# python3 main.py /data/mysqldata/data/test01/test01.ibd --ddl --sql
CREATE TABLE IF NOT EXISTS `test01`.`test01`(
`id` int NOT NULL,
`name` varchar(32) NULL,
PRIMARY KEY (`id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ;
INSERT INTO `test01`.`test01` VALUES (1, 'a');
INSERT INTO `test01`.`test01` VALUES (2, 'b');
INSERT INTO `test01`.`test01` VALUES (3, 'c');
[root@node01 ibd2sql-1.5]#
可以看到ibd文件，直接被解析为sql可执行语句。
当生产环境中ibd文件较多时，可以使用一下shell脚本批量将ibd文件解析为sql文件。
#!/bin/bash
for ibd_file in *.ibd; do
sql_file="/data/mysqldata/backup/$(basename "$ibd_file" .ibd).sql"
python3 /root/ibd2sql-1.5/main.py "$ibd_file" --ddl --sql > "$sql_file"
done
--sql_file:为需要将sql解析文件放置的位置，案例中为：/data/mysqldata/backup
脚本需放置于ibd数据文件同目录下，for循环会遍历所有ibd文件，并生成对应sql文件。
生成批量sql文件后，需要导入数据库环境中，可利用以下脚本实现：
#!/bin/bash
MYSQL_USER="your_username"
MYSQL_PASSWORD="your_password"
MYSQL_DATABASE="your_database"
SQL_DIR="/data/mysqldata/backup"
for sql_file in "$SQL_DIR"/*.sql; do
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" < "$sql_file"
done
--MYSQL_USER：数据库用户
--MYSQL_PASSWORD：数据库密码
--MYSQL_DATABASE：需要导入的数据库
--SQL_DIR：SQL文件存放目录
标签：false,数据文件,mysql8,true,default,ibd2sql,test01,root,id From： https://www.cnblogs.com/elanjie/p/18375497