收藏闪存小组博问

小满人生

园龄：5年6个月粉丝：0关注：0

利用ibd2sql直接读取mysql8数据文件数据

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文件存放目录
复制代码

 

本文作者：小满人生

本文链接：https://www.cnblogs.com/elanjie/p/18375497

版权声明：本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行许可。

posted @   小满人生  阅读(360)  评论(0)  编辑  收藏  举报
相关博文：
·  Xtrabackup全备恢复部分数据库
·  GBase8aGDCA认证考试
·  MySQL8.0自带工具ibd2sdi解析ibd文件
·  MySQL 8 的 Serialized Dictionary Information (SDI)
·  MySQL高级篇之第02章 MySQL的数据目录
阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 从HTTP原因短语缺失研究HTTP/2和HTTP/3的设计差异
· 三行代码完成国际化适配，妙~啊~
点击右上角即可分享
微信分享提示
SQL AI 助手